ORACLE DATABASE Physical refresh from PROD to NON PROD || Restoring database from one system to another

By -
Source db = ABC
Dest db     = DEF

Considering the following:
1. the OS version and db version of both the db is same.
2. the source db is running in archivelog mode.
3. Backup mount points on source is /backup and on destination is /rman_backup

Taking RMAN backup on source database 


Check whether the source DB is running in archivelog mode or not. run the below command

su – oracle;
sqlplus / as sysdba
archive log list

In case the source DB is running in archivelog mode, then do the below steps to take the online backup of source DB using rman:
Copy the below contents into a file /tmp/archivelogdbbkp.rman


RUN {

CROSSCHECK BACKUPSET;

CROSSCHECK BACKUP;
BACKUP CURRENT CONTROLFILE FORMAT '/backup/CONTROLFILE_BACKUP_%d_%T_%t_%s_%p.rman';
CROSSCHECK COPY;
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUPSET;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
ALLOCATE CHANNEL ch03 TYPE DISK;
ALLOCATE CHANNEL ch04 TYPE DISK;
ALLOCATE CHANNEL ch05 TYPE DISK;
BACKUP AS COMPRESSED BACKUPSET FORMAT '/backup/DB_BACKUP_%d_%T_%t_%s_%p.rman' DATABASE PLUS ARCHIVELOG;
BACKUP CURRENT CONTROLFILE FORMAT '/backup/CONTROLFILE_BACKUP_%d_%T_%t_%s_%p.rman';
}



Allocate more or less channel as per need.

Then run the below nohup command to initiate the rman backup from OS prompt

nohup rman target / cmdfile='/tmp/archivelogdbbkp.rman' log='/tmp/archivelogdbbkp.log' &




In case the source DB is running in NOARCHIVELOG mode, do the below steps to take the offline rman backup of source DB:
Take the rman backup of ABC database.
 
Copy the below contents into a file /tmp/noarchivelogdbbkp.rman



RUN{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
CROSSCHECK BACKUP;
CROSSCHECK BACKUP;
CROSSCHECK COPY;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUPSET;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
ALLOCATE CHANNEL ch03 TYPE DISK;
ALLOCATE CHANNEL ch04 TYPE DISK;
BACKUP AS COMPRESSED BACKUPSET FORMAT '/backup/DB_BACKUP_%d_%T_%t_%s_%p.rman' DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT '/backup/CONTROLFILE_BACKUP_%d_%T_%t_%s_%p.rman';
ALTER DATABASE OPEN;
}

Allocate more or less channel as per need.

Then run the below nohup command to initiate the rman backup from OS prompt.

nohup rman target / cmdfile='/tmp/noarchivelogdbbkp.rman' log='/tmp/noarchivelogdbbkp.log' &

Monitor the rman log file and make sure that there is no ORA or RMAN error occurs in it.

Also, run the below sql query to check the backup progress.

set pages 333 lines 222
select inst_id,sid,SOFAR,TOTALWORK,round(SOFAR*100/TOTALWORK) "Percent Complete",
TIME_REMAINING from gv$session_longops
where sid in
(select sid from gv$session where username is not null /*and status='ACTIVE'*/)
and TIME_REMAINING <> 0
order by 4 desc
/

Once the rman backup is complete, scp all the rman backup files onto DEF db server. Make sure the db user of DEF db server has got the read access on each of these rman backup files.

Run report schema on ABC db and save its output. it will be used while restoring this backup on DEF db.

rman target /
report schema;
exit


Restore rman backup on destination database (DEF)

Ensure the name of the database to be refreshed.


sudo su - oracle;   -- on DEF

sqlplus / as sysdba
select name, open_mode from v$database;
create pfile from spfile;
exit

Take the backup of spfile and pfile

cp $ORACLE_HOME/dbs/spfileDEF.ora   $ORACLE_HOME/dbs/spfileDEF.ora_date


Shutdown the DEF database instances (stop all DB instances if it is running in cluster).

sqlplus / as sysdba
Shu immediate;

Change the cluster_database parameter to false in $ORACLE_HOME/dbs/initDEF.ora. (it is only needed if db has more than 1 instances).

startup and mount the database in exclusive mode only on one instance. (you can drop the database by starting database in mount state with spfile as well, but since we have here renamed the spfile, we will be using pfile).

sqlplus / as sysdba
startup mount restrict exclusive pfile='$ORACLE_HOME/dbs/initDEF.ora';  

Drop the DEF database

drop database;

Now check that all space has been cleared. (datafiles, logfiles, and control files should be deleted.) Delete them manually if something left.

Make sure your datafiles filesystem or diskgroup of DEF db server has sufficient space to store data of Live database.



Change the name of the pfile as that of source db.

cp $ORACLE_HOME/dbs/initDEF.ora  $ORACLE_HOME/dbs/initABC.ora

change the value of db_name parameter to ABC in initABC.ora file.

Please note: If there is SID mentioned in pfile before undo tablespace   than that need to be changed as well. Replace the SID from source to target database.

Now set the environment as per the PROD database on NON PROD DB.

export ORACLE_SID=ABC
sqlplus / as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/initABC.ora';
exit

Now restore the control files.

rman target /   -- Please note the DB name it should be ABC
restore controlfile from '/rman_backup/controlfile_backup_piece.ctl';   --Please choose the latest controlfile to restore here
alter database mount;

Now catalog the backup pieces:

catalog start with '/rman_backup/';

Check the report schema output earlier taken on ABC db.

Considering the DEF db using FS to store the datafiles.

Now we would need to allocate the datafiles into different FS on the destination server according the size of each FS. e.g. datafiles1 FS has a free space of 100GB and datafile 1, datafile 2  are of in total size 60GB, then datafile 1 to 2 will be allocated in datafiles1 FS. similary the subsequent datafiles are allocated in the remaining FSs. once this calculation is done, the below rman script should be prepared for reference.


RUN
{
Allocate channel ch00 type disk;
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
Allocate channel ch03 type disk;
Allocate channel ch04 type disk;
Allocate channel ch05 type disk;
Allocate channel ch06 type disk;
SET NEWNAME FOR DATAFILE 1 TO '/oracleDEF/DEF/datafiles1/%b;
SET NEWNAME FOR DATAFILE 2 TO '/oracleDEF/DEF/datafiles1/%b;
SET NEWNAME FOR DATAFILE 3 TO '/oracleDEF/DEF/datafiles2/%b;
SET NEWNAME FOR DATAFILE 4 TO '/oracleDEF/DEF/datafiles2/%b;
SET NEWNAME FOR DATAFILE 5 TO '/oracleDEF/DEF/datafiles3/%b;
SET NEWNAME FOR DATAFILE 6 TO '/oracleDEF/DEF/datafiles3/%b;
SET NEWNAME FOR DATAFILE 7 TO '/oracleDEF/DEF/datafiles4/%b;
SET NEWNAME FOR DATAFILE 8 TO '/oracleDEF/DEF/datafiles4/%b;
SET NEWNAME FOR DATAFILE 9 TO '/oracleDEF/DEF/datafiles5/%b;
SET NEWNAME FOR DATAFILE 10 TO '/oracleDEF/DEF/datafiles5/%b;
restore database;
switch datafile all;
recover database;
}


Now start the restore from the backup. Copy the above contents into a file /tmp/restore.rman
Then run the below nohup command to initiate the rman restore on DEF db server.

nohup rman target / cmdfile='/tmp/restore.rman' log='/tmp/restore.log' &

Monitor the logfile /tmp/restore.log and make sure that there is no ORA or RMAN error occurs in it.

Also, run the below sql query to check the restore progress.

su - oracle -- on DEF server

export ORACLE_SID=ABC
sqlplus / as sysdba

set pages 333 lines 222
select inst_id,sid,SOFAR,TOTALWORK,round(SOFAR*100/TOTALWORK) ""Percent Complete"",
TIME_REMAINING from gv$session_longops
where sid in
(select sid from gv$session where username is not null /*and status='ACTIVE'*/)
and TIME_REMAINING <> 0
order by 4 desc
/

Once the restore is complete it will update the controlfile with new datafiles with the help of "switch datafile all" command and then it will perform database recovery by applying archivelogs, also if you don't want to use rman for recovery or need to apply archivelog separately in that case , Open a new session and start the recovery from sqlplus

export ORACLE_SID=ABC
sqlplus / as sysdba
recover database using backup controlfile until cancel;

in case the archive logs are requested in the sqlplus recovery session and the archivelogs are not restored, restore them manually in another rman session and recover those archivelogs in the recovery session.

Note: Thread can be mentioned if RAC, if it is not a RAC system, no need to mention the thread.


RUN
{
Allocate channel ch00 type disk;
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
Allocate channel ch03 type disk;
Allocate channel ch04 type disk;
set archivelog destination to '/rman_backup/arch/';
restore archivelog from logseq=8619 until logseq=8632 thread=2;
}

post restoring the archivelog, you may start the recovery again, Apply the archivelogs to the point you want to recover your database.

Since we have not taken the redo log backup here, hence this recovery is to the point or incomplete media recovery.

In case of incomplete media recovery database needs to be open with resetlog option.

Since our DB is already in mount state and recovery is done, perform the below steps.

export ORACLE_SID=ABC
sqlplus / as sysdba
alter database open resetlogs;


Now your database is in open state (if error occurs you need to fix the error accordingly).

Now we need the database to be named as DEF not ABC, so we will be converting our database in DEF.

for that first take the trace backup of controlfile.

export ORACLE_SID=ABC
sqlplus / as sysdba
alter database backup controlfile to trace as '/tmp/controle_trace.ctl';
exit


now logout and relogin on server via oracle user and check and ensure that env is set for DEF database.


su - oracle
. oraenv
DEF

now open the /tmp/controle_trace.ctl file and select the one portion from it, which would be looking like below:


CREATE CONTROLFILE REUSE DATABASE "ABC" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2920
LOGFILE
  GROUP 1 (
    '/oracleDEF/DEF/datafiles1/redo/redo01a.log',
    '/oracleDEF/DEF/datafiles2/redo/redo01b.log'
  ) SIZE 512M BLOCKSIZE 512,
  GROUP 2 (
    '/oracleDEF/DEF/datafiles1/redo/redo02a.log',
    '/oracleDEF/DEF/datafiles2/redo/redo02b.log'
  ) SIZE 512M BLOCKSIZE 512,
DATAFILE
'/oracleDEF/DEF/datafiles1/system01.dbf',
'/oracleDEF/DEF/datafiles1/sysaux01.dbf',
'/oracleDEF/DEF/datafiles2/undotbs01.dbf',
'/oracleDEF/DEF/datafiles2/tab1.dbf',
'/oracleDEF/DEF/datafiles3/tab2.dbf',
'/oracleDEF/DEF/datafiles3/tab3.dbf',
'/oracleDEF/DEF/datafiles4/tab4.dbf',
'/oracleDEF/DEF/datafiles4/tab5.dbf',
'/oracleDEF/DEF/datafiles5/tab6.dbf',
'/oracleDEF/DEF/datafiles5/tab7.dbf'
CHARACTER SET AL32UTF8
;


From above set, change the 2 entries:

REUSE --> SET
ABC --> DEF

so it will look like below:


CREATE CONTROLFILE SET DATABASE "DEF" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2920
LOGFILE
  GROUP 1 (
    '/oracleDEF/DEF/datafiles1/redo/redo01a.log',
    '/oracleDEF/DEF/datafiles2/redo/redo01b.log'
  ) SIZE 512M BLOCKSIZE 512,
  GROUP 2 (
    '/oracleDEF/DEF/datafiles1/redo/redo02a.log',
    '/oracleDEF/DEF/datafiles2/redo/redo02b.log'
  ) SIZE 512M BLOCKSIZE 512,
DATAFILE
'/oracleDEF/DEF/datafiles1/system01.dbf',
'/oracleDEF/DEF/datafiles1/sysaux01.dbf',
'/oracleDEF/DEF/datafiles2/undotbs01.dbf',
'/oracleDEF/DEF/datafiles2/tab1.dbf',
'/oracleDEF/DEF/datafiles3/tab2.dbf',
'/oracleDEF/DEF/datafiles3/tab3.dbf',
'/oracleDEF/DEF/datafiles4/tab4.dbf',
'/oracleDEF/DEF/datafiles4/tab5.dbf',
'/oracleDEF/DEF/datafiles5/tab6.dbf',
'/oracleDEF/DEF/datafiles5/tab7.dbf'
CHARACTER SET AL32UTF8
;

save the same content in a file say '/tmp/control.sql'

now start the DEF database instance in nomount state with the help of pfile:

sqlplus / as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/initDEF.ora';

/tmp/control.sql will be creating controlfile so,

sqlplus / as sysdba
@/tmp/control.sql

Now your controlfile is created and database will get mount automatically, now open your database but again with resetlog option will be required.

sqlplus / as sysdba
alter database open resetlogs;

Now shutdown your database and start you instance with spfile:

sqlplus / as sysdba
Shu immediate
startup;

verify your database details:

select name,open_mode,database_role from v$database;

Now since your database physical restoration completed, you may start instances on other node if this is a RAC system.

Comments

Post a Comment

Popular posts from this blog

How to set the root password after creating instance on oracle cloud

By -
How to set the root password after creating instance on oracle cloud? since you have created instance on oracle cloud, it requires you private .ppk key file to login from putty you can login on your instance by directly providing that private key. and on Linux/Unix defult user is "opc". since during creation of instance it  will not ask you for  root password. You dont need any password to connect to root. to connect to root type below command as opc user. [opc@sts ~]$ sudo su [root@sts opc]# by this you will login as root. Now change your password [root@sts opc]# passwd Changing password for user root. New password: Retype new password: passwd: all authentication tokens updated successfully. [root@sts opc]# All done. Thanks & Regards Nikhil Kumar.
Read more

TNS-12541 TNS-12560 TNS-00511 TNS:protocol adapter error No listener

By -
Oracle Listener does not start with errors:  TNS-12541: TNS:no listener TNS-12560, TNS-00511, Linux Error: 2: No such file or directory, The listener supports no services euls25002.sgp.st.com:oraSID 51> ps -ef | grep -i tns root       330     2  0 Jul19 ?        00:00:00 [netns] oraSID   17512     1  0 Jul20 ?        00:01:25 /oracleSID/SID/121/bin/tnslsnr LISTENER_SID -inherit oraSID   56223 56131  0 08:02 pts/1    00:00:00 grep --color=auto -i tns euls25002.sgp.st.com:oraSID 52> lsnrctl status LISTENER_SID LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 25-SEP-2017 08:02:19 Copyright (c) 1991, 2014, Oracle.  All rights reserved. Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=SID.WORLD)) TNS-12541: TNS:no listener  TNS-12560: TNS:protocol adapter error   TNS-00511: No listener    Linux Error: 2: No such file or directory ...
Read more

Queryable inventory could not determine the current opatch status.

By -
[oracle@Testing02 OPatch]$ ./datapatch -verbose SQL Patching tool version 12.2.0.1.0 Production on Fri May 15 15:54:19 2020 Copyright (c) 2012, 2019, Oracle.  All rights reserved. Log file for this invocation: /testing/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_29313_2020_05_15_15_54_19/sqlpatch_invocation.log Connecting to database...OK Bootstrapping registry and package to current versions...done Queryable inventory could not determine the current opatch status. Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual' and/or check the invocation log /testing/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_29313_2020_05_15_15_54_19/sqlpatch_invocation.log for the complete error. Prereq check failed, exiting without installing any patches. Please refer to MOS Note 1609718.1 and/or the invocation log /testing/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_29313_2020_05_15_15_54_19/sqlpatch_invocation.log for information on how to resolve the above errors. SQL...
Read more