hot clone restore operation

Database cloning using hot backup

http://dbaclass.com/article/database-cloning-using-hot-backup/

https://dbatricksworld.com/oracle-11g-manual-online-hot-backup/

 

Test Instance :

PATH=$PATH:$HOME/.local/bin:$HOME/bin

 

export PATH

export PATH

export ORACLE_HOME=/ASCPDB/app/oraascp/11.2.0

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export ORACLE_SID=TJTGHRT

 

 

 

Oracle 11g Manual Online Hot Backup

 

For manual online hot backup, follow the steps mentioned below. I have simulated mentioned scenario on my test database, i.e. RTS.

Step 1>>

Verify your database is in ARCHIVELOG mode OR not.

 SQL> archive log list;

 Database log mode Archive Mode

 Automatic archival Enabled

 Archive destination USE_DB_RECOVERY_FILE_DEST

 Oldest online log sequence 88

 Next log sequence to archive 90

 Current log sequence 90

 Database is in ARCHIVELOG mode.

Step 2>>

List down the all oracle data files which you have to backup, with the help of following SQL.

 SQL> select file_id,file_name from dba_data_files;

 FILE_ID    FILE_NAME

 ---------  ----------------------------------------------

 4          /u01/app/oracle/oradata/RTS/users01.dbf

 3          /u01/app/oracle/oradata/RTS/undotbs01.dbf

 2          /u01/app/oracle/oradata/RTS/sysaux01.dbf

 1          /u01/app/oracle/oradata/RTS/system01.dbf

Step 3>>

We need to know current online log sequence number at this point, Plus all log sequence generated during backup. Because we need these archive logs in order to restore database.

 SQL> select group#, sequence#, status from v$log;

 GROUP#     SEQUENCE#  STATUS

 ---------- ---------- ----------------

 1          88         INACTIVE

 2          89         INACTIVE

 3          90         CURRENT

In my case, CURRENT log sequence number : 90, GROUP# : 3

Step 4>>

To start with hot backup, all the online datafile status should be in NOT ACTIVE mode in V$BACKUP dictionary view, that displays the backup status of all online datafiles.

select * from v$backup;

     FILE# STATUS             CHANGE#    TIME

---------- ------------------ ---------- ---------

         1 NOT ACTIVE         0

         2 NOT ACTIVE         0

         3 NOT ACTIVE         0

         4 NOT ACTIVE         0

Put your database in hot backup mode with the help of following SQL:

 SQL> alter database begin backup;

 Database altered.

Verify status of all online datafiles after begin backup mode:

SQL> Select * from v$backup;

     FILE# STATUS             CHANGE#    TIME

---------- ------------------ ---------- ---------

         1 ACTIVE             1114633    12-JUL-17

         2 ACTIVE             1114633    12-JUL-17

         3 ACTIVE             1114633    12-JUL-17

         4 ACTIVE             1114633    12-JUL-17

Step 5>>

Create backup directory in order to copy backup files to backup directory.

 [oracle@PR ~]$ mkdir -p /u01/bkup/manual_online_hot

 [oracle@PR ~]$ cd /u01/bkup/manual_online_hot

 [oracle@PR manual_online_hot]$ pwd

 /u01/bkup/manual_online_hot

It is recommended to backup your pfile or spfile by coping it to backup location.

[oracle@PR ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

[oracle@PR dbs]$ cp initRTS.ora /u01/bkup/manual_online_hot/

[oracle@PR dbs]$ cp spfileRTS.ora /u01/bkup/manual_online_hot/

Step 6>>

Copy all the files (i.e. .DBF) from the database directory “/u01/app/oracle/oradata/RTS” to backup directory “/u01/bkup/manual_online_hot”.

 [oracle@PR ~]$ cd /u01/app/oracle/oradata/RTS

 [oracle@PR RTS]$ cp *.dbf /u01/bkup/manual_online_hot

Step 7>>

Take your database out from hot backup mode:

 SQL> alter database end backup;

 Database altered.

Step 8>>

Verify current log sequence number with the help of same SQL mentioned in step-3.

 SQL> select group#, sequence#, status from v$log;

 GROUP#     SEQUENCE#  STATUS

 ---------- ---------- ----------------

 1          88         INACTIVE

 2          89         INACTIVE

 3          90         CURRENT

We need the earlier log file that we identified in step-3 & all log files generated during the database backup upto the current log file.

Note: In our case, As you know this is an test environment not production database. current log file before and after backup remain same, i.e. 90. But in case of production system, it may vary and generate more log files during backup.

Step 9>>

Forcefully we need to switch the logfile in order to archive current log sequence number. i.e. 90.

 SQL> alter system switch logfile;

 System altered.

 SQL> select group#, sequence#, status from v$log;

 GROUP#     SEQUENCE#  STATUS

 ---------- ---------- ----------------

 1          91         CURRENT

 2          89         INACTIVE

 3          90         ACTIVE

Now, Current log sequence number is 91, and log sequence number will be archive to archive log location. In my case it is FRA: Flash Recovery Area.

Step 10>>

Verify log sequence number have been archived to archive log location with the help of following SQL:

SQL> select SEQUENCE#,ARCHIVED,STATUS from v$archived_log where SEQUENCE#=90;

 SEQUENCE#  ARC S

 ---------- --- -

 90         YES A

Archive log sequence 90 has archived. Some time we need to wait for ARCH background process to complete copy the last online redo log file to the archive log directory.

Step 11>>

Now, Copy all archived logs (i.e. log sequence number noted in step-3 and all archived logs generated during backup) from archived log location (i.e. FRA) to the backup location.
In our case it was only log sequence 90.

[oracle@PR ~]$ cd /home/oracle/archdir

 [oracle@PR archdir]$ cp 1_90_910140016.dbf /u01/bkup/manual_online_hot

 OR

 [oracle@PR archdir]$ cp * /u01/bkup/manual_online_hot

Step 12>>

Backup the database control file:

SQL> alter database backup controlfile to '/u01/bkup/manual_online_hot/hot_bkup_control.ctl';

Database altered.

Cross verify all backup files are in place(i.e. datafiles, controlfile and archive log files @backup location in order restore database.

Congratulations!! Manual Online hot backup successfully completed.

To restore and recover above online hot backup, kindly visit my next article on Restore Oracle hot backup

 

Restore Oracle hot backup

 

Consider any worst database scenario of your life, let say we got HDD corruption and some online datafile got corrupted and not accessible due to any reason. Now we have to restore database from hot backup, as follows:

Step 1>>

Copy back all the online datafile from backup location to the actual datafile location:

cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/users01.dbf

cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/undotbs01.dbf

cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/sysaux01.dbf

cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/system01.dbf

Step 2>>

Copy back control file to all the controlfile locations, refer controlfile locations from parameter file, i.e. initRTS.ora file.

cp /u01/bkup/manual_online_hot/hot_bkup_control.ctl /u01/app/oracle/oradata/RTS/control01.ctl

cp /u01/bkup/manual_online_hot/hot_bkup_control.ctl /u01/app/oracle/oradata/RTS/control02.ctl

Step 3>>

Copy back pfile or spfile file to its location:

cp /u01/bkup/manual_online_hot/initRTS.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

cp /u01/bkup/manual_online_hot/spfileRTS.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Step 4>>

Startup database in mount mode:

SQL> startup mount;

ORACLE instance started.

Total System Global Area 755769344 bytes

Fixed Size 2217184 bytes

Variable Size 478153504 bytes

Database Buffers 272629760 bytes

Redo Buffers 2768896 bytes

Database mounted.

Step 5>>

Now, recover database with the help of following command and specify log: AUTO

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 1117103 generated at 07/12/2017 04:39:47 needed for thread 1

ORA-00289: suggestion : /home/oracle/archdir/1_90_910140016.dbf

ORA-00280: change 1117103 for thread 1 is in sequence #90

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

Step 6>>

Open database with resetlogs:

SQL> alter database open resetlogs;

Cheers!! Hot backup restored and recovered successfully.

If you are facing following error then kindly visit my blog for the solution: ORA-01194: file 1 needs more recovery to be consistent

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS_NEW/system_new.dbf'

 

ORA-01194: file 1 needs more recovery to be consistent

 

Error log:

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS_NEW/system_new.dbf'

Workaround for this error is to provide all the available archive log files to the recovery:

SQL> recover database using backup controlfile until cancel;

...

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

Above command will apply all the available archive logs automatically. Now try to open database with resetlogs:

SQL> alter database open resetlogs;

If the error persists due to insufficient archive logs, do the following workaround:

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Startup database in mount mode:

SQL> startup mount

ORACLE instance started.

Total System Global Area 530288640 bytes

Fixed Size 2131120 bytes

Variable Size 310381392 bytes

Database Buffers 209715200 bytes

Redo Buffers 8060928 bytes

Database mounted.

Change “_allow_resetlogs_corruption” parameter to TRUE and undo_management parameter to MANUAL:

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;

SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;

After doing above changes, shutdown database, and startup:

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 530288640 bytes

Fixed Size 2131120 bytes

Variable Size 310381392 bytes

Database Buffers 209715200 bytes

Redo Buffers 8060928 bytes

Database mounted.

Now try resetlogs:

SQL> alter database open resetlogs;

Database altered.

Create new undo tablespace and set “undo_tablespace” parameter to the new undo tablespace and change “undo_management” parameter to AUTO:

SQL> CREATE UNDO TABLESPACE undo2 datafile '/u01/app/oracle/oradata/RTS_NEW/undo2_df1.dbf' size 200m autoextend on maxsize 30G;

Tablespace created.

SQL> alter system set undo_tablespace = undo2 scope=spfile;

System altered.

SQL> alter system set undo_management=auto scope=spfile;

System altered.

Now bounce your database.

SQL> shutdown immediate

SQL> startup

Cheers!! Database started successfully.