Oracle Database Cloning Using Cold Backup

Cold database backup means while taking backup or doing cloning, we need to shutdown the source database. This method is usually used for test database when database is in noarchivelog mode.

Note- Both the source and target db server should be on same platform and the target db version will be that of the source db.So make sure oracle binary is already installed on target db server.

SOURCE DB – jupiter
TARGET DB – sakthi

Steps:

1.Take backup of controlfile as trace:[SOURCE DB]

2. Note down the location of datafiles[SOURCE DB]

3. Shutdown the database:[SOURCE DB]

4. Copy the data files and temp files to the target db server

5. Prepare the init file for target db:[TARGET DB]

6. Start the database in nomount stage:[TARGET DB]

7. Re-recreate the controlfile [ TARGET DB ]

8. Open the database in resetlog mode

Send the datafile,log files and control files to target destination

 

[oracle@jupiter rock]$ scp -r *.log oracle@192.168.1.135:/u01/ram

The authenticity of host '192.168.1.135 (192.168.1.135)' can't be established.

RSA key fingerprint is 9b:35:ae:ab:bf:6b:33:b4:43:86:f5:98:8b:bb:11:1c.

Are you sure you want to continue connecting (yes/no)? oracle

Please type 'yes' or 'no': yes

Warning: Permanently added '192.168.1.135' (RSA) to the list of known hosts.

oracle@192.168.1.135's password:

redo01.log                     100%  200MB   3.1MB/s   01:05    

redo02.log                     100%  200MB   4.0MB/s   00:50    

redo03.log                     100%  200MB   3.5MB/s   00:58    

[oracle@jupiter rock]$ scp -r users01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

users01.dbf                  100% 5128KB   5.0MB/s   00:01    

[oracle@jupiter rock]$ scp -r undotbs01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

undotbs01.dbf               100%   70MB   3.7MB/s   00:19    

[oracle@jupiter rock]$ scp -r temp01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

temp01.dbf                100%   32MB   2.9MB/s   00:11    

[oracle@jupiter rock]$ scp -r system01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

system01.dbf             100%  810MB   3.0MB/s   04:31    

[oracle@jupiter rock]$ scp -r sysaux01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

sysaux01.dbf            100%  490MB   3.2MB/s   02:34    

[oracle@jupiter u01]$ scp -r ctrl.sql oracle@192.168.1.135:/u01/ram
oracle@192.168.1.135's password:

ctrl.sql               100% 5865     5.7KB/s   00:00

Now all the files are in the target destination.

[oracle@sakthi ram]$ ls

ctrl.sql         redo01.log  redo03.log    system01.dbf  undotbs01.dbf

initjupiter.ora  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

Edit the control file

[oracle@sakthi ram]$ vi ctrl.sql

CREATE CONTROLFILE SET  DATABASE "ROCK" RESETLOGS ARCHIVELOG

    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/ram/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/u01/ram/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/u01/ram/redo03.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/u01/ram/system01.dbf',
  '/u01/ram/sysaux01.dbf',
  '/u01/ram/undotbs01.dbf',
  '/u01/ram/users01.dbf'
CHARACTER SET AL32UTF8 

Edit the PFILE

[oracle@sakthi ram]$ vi initjupiter.ora

db_name=rock
control_files='/u01/ram/ctrl.ctl'

NOW open the database.

[oracle@sakthi ram]$ export ORACLE_SID=rock

[oracle@sakthi ram]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 18 01:20:35 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup pfile='/u01/ram/initjupiter.ora' nomount

ORACLE instance started.
Total System Global Area  243269632 bytes
Fixed Size                  8619256 bytes
Variable Size             180357896 bytes
Database Buffers           50331648 bytes
Redo Buffers                3960832 bytes

SQL> @ctrl.sql
Control file created.

SQL> alter database open resetlogs;
Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
------    -------------
ROCK      READ WRITE
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle utility dbverify (DBV) Tips and Tricks

FEW BLOCKS IN DATAFILE ARE CORRUPT

There can be a corrupt block in a large datafile in your database which RMAN can easily report. Now it would take significant effort and time to perform the traditional restore and recover of a large datafile. So instead we will just recover the corrupt block and not the entire datafile.

For Oracle Database 10g or Oracle9i Database, use the blockrecover command to perform block media recovery.

As for Oracle Database 11g or newer, we will use the recover datafile … block command as shown below:
ONLY FOR TESTING FIRST WE CREATED SOME DB BLOCK CORRUPTION ??

SYS@ram > SELECT header_block FROM dba_segments WHERE segment_name=’EMP’;

HEADER_BLOCK
————
146

$ dd of=/u01/oracle/DB11G/oradata/ram/users01.dbf bs=8192 conv=notrunc seek=147 << EOF
> corruption test
> EOF
0+1 records in
0+1 records out
16 bytes (16 B) copied, 0.000159796 s, 100 kB/s

SYS@ram> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

SYS@ram> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 147)
ORA-01110: data file 4: ‘/u01/oracle/DB11G/oradata/ram/users01.dbf’
A) CHECK CORRUPTION USING RMAN

RMAN will automatically detect corruption in blocks whenever a backup or backup validate command is issued. These blocks are reported as corrupt in the alert.log file and the V$DATABASE_BLOCK_CORRUPTION view.

Using RMAN command:

RMAN> backup validate database archivelog all;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
4 FAILED 0 18 667 1086086
File Name: /u01/oracle/DB11G/oradata/ram/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 90
Index 0 39
Other 1 493
In Alert Log:

Wed JAN 18  1:53:28 2019
Hex dump of (file 4, block 147) in trace file /u01/oracle/DB11G/diag/rdbms/ram/ram/trace/ram_ora_6734.trc
Corrupt block relative dba: 0x01000093 (file 4, block 147)
Bad header found during validation
Data in bad block:
type: 99 format: 7 rdba: 0x69747075
last change scn: 0x7365.74206e6f seq: 0x74 flg: 0x0a
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x0eef0602
check value in block header: 0xb843
block checksum disabled
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data

In V$DATABASE_BLOCK_CORRUPTION view:

RMAN backup populates V$DATABASE_BLOCK_CORRUPTION.

SYS@ram > select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———-   ———-   ———-       ——————           ———
4      147      1            0          CORRUPT
Using DBV:
dbv file=/u01/oracle/DB11G/oradata/ram/users01.dbf blocksize=8192

DBVERIFY: Release 12.2.0.2.0 – Production on Wed JAN 18 1:06:26 2019

Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.

DBVERIFY – Verification starting : FILE = /u01/oracle/DB11G/oradata/ram/users01.dbf
Page 147 is marked corrupt
Corrupt block relative dba: 0x01000093 (file 4, block 147)
Bad header found during dbv:
Data in bad block:
type: 99 format: 7 rdba: 0x69747075
last change scn: 0x7365.74206e6f seq: 0x74 flg: 0x0a
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x0eef0602
check value in block header: 0xb843
block checksum disabled

DBVERIFY – Verification complete

Total Pages Examined : 640
Total Pages Processed (Data) : 90
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39
Total Pages Failing (Index): 0
Total Pages Processed (Other): 492
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 18
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1086086 (0.1086086)
B) CORRECT DATA BLOCK CORRUPTION

Database can be in mounted or open when you are performing block-level recovery. Also you do not have to take the datafile being recovered offline. Block-level media recovery allows you to keep your database available hence reducing the mean time to recovery since only the corrupt blocks are offline during the recovery.

RMAN CAN NOT perform block-level recovery on block 1 (datafile header) of the datafile.

RMAN> recover datafile 4 block 147;

Starting recover at 18-JAN-2019 1:07:41
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/ram/backupset/2018_02_26/o1_mf_nnndf_TAG20190226T134738_9jwr7wj3_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/ram/backupset/2018_02_26/o1_mf_nnndf_TAG20190226T134738_9jwr7wj3_.bkp tag=TAG20190226T134738
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 26-JAN-2019 1:07:45

C) VERIFY
SYS@ram > select * from V$DATABASE_BLOCK_CORRUPTION;

no rows selected

SYS@ram > select * from scott.emp;

EMPNO  ENAME  OB   MGR  HIREDATE  SAL  COMM   DEPTNO
———-   ———-   ———  ———-  ———--    ———-  ———-    ———-
7369  SMITH CLERK 7902 17-JUN-80  800    2       0

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba