Hello Readers,
In this article, we will see Recovery models in SQL Server.
1. Take backup of the primary database
|
1 2 3 4 5 6 7 8 9 10 |
run { allocate channel ch1 device type disk maxpiecesize 2G; allocate channel ch2 device type disk maxpiecesize 2G; backup incremental level 0 cumulative database format ='/rman/dbbackups/DB_BKP/full_%d_%T_df_%f_%s' tag 'INCREMENTAL LEVEL 0'; release channel ch1; release channel ch2; } allocate channel for maintenance type disk; delete noprompt obsolete device type disk; release channel; |
2. Create standby control file from primary database.
|
1 |
RMAN> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/ForStandbyCTRL.bck'; |
3. Transfer backup pieces along with control file from primary server to standby server.
SCP backup pieces to standby database
4. Take standby database controlfile backup to trace from standby database
|
1 |
RMAN> alter database backup controlfile to trace as ‘/tmp/stndby_ctlr’; |
5. Stop the standby database using srvctl utility.
|
1 2 |
Srvctl status database –d TEST_DG Srvctl stop database –d TEST_DG |
6. Open standby database to unmount mode using srvctl utility.
|
1 |
Srvctl start database –d <dbname> -o unmount |
7. Connect to RMAN and restore the control file , catalog the backup pieces and restore the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
RMAN> restore controlfile from '/tmp/ForStandbyCTRL.bck'; RMAN> alter database mount; RMAN> catalog start with '/tmp/standby/' RMAN> run { set newname for datafile 1 to '+DATA/TEST_DG/DATAFILE/system.305.1053801259'; set newname for datafile 3 to '+DATA/TEST_DG/DATAFILE/sysaux.294.1053801303'; set newname for datafile 4 to '+DATA/TEST_DG/DATAFILE/undotbs1.309.1053801329'; set newname for datafile 5 to '+DATA/TEST_DG//DATAFILE/system.311.1053801661'; set newname for datafile 6 to '+DATA/TEST_DG//DATAFILE/sysaux.295.1053801661'; set newname for datafile 7 to '+DATA/TEST_DG/DATAFILE/users.301.1053801329'; set newname for datafile 8 to '+DATA/TEST_DG//DATAFILE/undotbs1.310.1053801661'; set newname for datafile 9 to '+DATA/TEST_DG/DATAFILE/undotbs2.308.1053801889'; set newname for datafile 15 to '+DATA/TEST_DG/DATAFILE/system.293.1062803067'; set newname for datafile 16 to '+DATA/TEST_DG/DATAFILE/sysaux.299.1062803067'; set newname for datafile 17 to '+DATA/TEST_DG/DATAFILE/undotbs1.298.1062803067'; set newname for datafile 18 to '+DATA/TEST_DG/DATAFILE/undo_2.291.1062803277'; set newname for datafile 19 to '+DATA/TEST_DG/DATAFILE/users.290.1062804503'; set newname for datafile 57 to '+DATA/TEST_DG/DATAFILE/system.328.1063061305'; set newname for datafile 58 to '+DATA/TEST_DG/DATAFILE/sysaux.327.1063061305'; set newname for datafile 59 to '+DATA/TEST_DG/DATAFILE/undotbs1.318.1063061305'; set newname for datafile 60 to '+DATA/TEST_DG/DATAFILE/undo_2.330.1063061305'; set newname for datafile 61 to '+DATA/TEST_DG/DATAFILE/users.329.1063061305'; set newname for datafile 80 to '+DATA/TEST_DG/DATAFILE/reportsobi_stb.338.1063307309'; set newname for datafile 81 to '+DATA/TEST_DG/DATAFILE/reportsobi_biplatform.337.1063307309'; set newname for datafile 82 to '+DATA/TEST_DG/DATAFILE/reportsobi_wls.335.1063307311'; set newname for datafile 83 to '+DATA/TEST_DG/DATAFILE/reportsobi_iau.332.1063307311'; set newname for datafile 84 to '+DATA/TEST_DG/DATAFILE/reportsobi_ias_opss.336.1063307313'; set newname for datafile 85 to '+DATA/TEST_DG/DATAFILE/reportsobi_mds.333.1063307313'; set newname for datafile 86 to '+DATA/TEST_DG/DATAFILE/common_prim_dat.313.1063578865'; set newname for datafile 87 to '+DATA/TEST_DG/DATAFILE/common_prim_ndx.339.1063578865'; set newname for datafile 88 to '+DATA/TEST_DG/DATAFILE/common_prim_lob.340.1063578865'; set newname for datafile 89 to '+DATA/TEST_DG/DATAFILE/common_prim_sf_lob.341.1063578867'; set newname for datafile 90 to '+DATA/TEST_DG/DATAFILE/common_prim_ht_dat.342.1063578867'; set newname for datafile 91 to '+DATA/TEST_DG/DATAFILE/common_prim_ht_ndx.343.1063578867'; set newname for datafile 92 to '+DATA/TEST_DG/DATAFILE/common_prim_mcd.344.1063578867'; set newname for datafile 93 to '+DATA/TEST_DG/DATAFILE/common_prim_ov_dat.345.1063578869'; set newname for datafile 94 to '+DATA/TEST_DG/DATAFILE/common_prim_ov_ndx.346.1063578869'; restore database; switch datafile all; } *Hint: Get all the datafille and file id from v$datafile(select file#,name from v$datafile) from primary database and create script according to OMF. |
8. Drop the stand by redo log and create redo log
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
alter database drop standby logfile group 5; alter database drop standby logfile group 6; alter database drop standby logfile group 7; alter database drop standby logfile group 8; alter database drop standby logfile group 9; alter database drop standby logfile group 10; alter database add standby logfile THREAD 1 group 5 '+DATA/TEST_DG/ONLINELOG/standby_redo05.log' size 512m; alter database add standby logfile THREAD 1 group 6 '+DATA/TEST_DG/ONLINELOG/standby_redo06.log' size 512m; alter database add standby logfile THREAD 1 group 7 '+DATA/TEST_DG/ONLINELOG/standby_redo07.log' size 512m; alter database add standby logfile THREAD 2 group 8 '+RECO/TEST_DG/ONLINELOG/standby_redo08.log' size 512m; alter database add standby logfile THREAD 2 group 9 '+RECO/TEST_DG/ONLINELOG/standby_redo09.log' size 512m; alter database add standby logfile THREAD 2 group 10 '+RECO/TEST_DG/ONLINELOG/standby_redo10.log' size 512m; |
9. Start mrp process of the database and verify archive log is applying into database.
|
1 |
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; |
10. Monitor the shipping and logs apply status
|
1 2 |
SELECT SEQUENCE#,BLOCK#,PROCESS,STATUS FROM V$MANAGED_STANDBY; SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; |
11. Enable Active Dataguard mode
|
1 2 3 4 |
sqlplus / as sysdba SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL>ALTER DATABASE OPEN READ ONLY; SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Note: Please test scripts in Non Prod before trying in Production.
Loading...
