How to perform DATAPUMP operations on standby database by converting SNAPSHOT standby.
Data Guard is the name for Oracle’s standby database solution, used for disaster recovery and high availability. This article contains an updated version of 12cR1 Converting physical standby into snapshot standby and taking export operation using DATAPUMP Utility.
Primary Database : APACPROD(DBA13)
Standby Database : APACSTAND(DBA12)
Step :1
Check the primary database is in sync with standby database or not.
At Primary Side:
|
1 2 3 4 5 6 7 8 9 10 11 |
SYS>>select name,open_mode,database_role,controlfile_type from v$database; NAME OPEN_MODE DATABASE_ROLE CONTROL ---------- -------------------- ---------------- ------- APACPROD READ WRITE PRIMARY CURRENT SYS>>archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oradata/apacprod/arch Oldest online log sequence 259 Next log sequence to archive 260 Current log sequence 260 |
At standby Side :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SYS>>select name,open_mode,database_role,controlfile_type from v$database; NAME OPEN_MODE DATABASE_ROLE CONTROL ---------- -------------------- ---------------- ------- APACPROD MOUNTED PHYSICAL STANDBY STANDBY SYS>>select name,applied from v$archived_log; NAME APPLIED -------------------------------------------------- --------- /u01/oradata/apacprod/arch/1_250_976404635.dbf YES /u01/oradata/apacprod/arch/1_249_976404635.dbf YES /u01/oradata/apacprod/arch/1_251_976404635.dbf YES /u01/oradata/apacprod/arch/1_252_976404635.dbf YES /u01/oradata/apacprod/arch/1_253_976404635.dbf YES /u01/oradata/apacprod/arch/1_254_976404635.dbf YES /u01/oradata/apacprod/arch/1_255_976404635.dbf YES /u01/oradata/apacprod/arch/1_257_976404635.dbf YES /u01/oradata/apacprod/arch/1_256_976404635.dbf YES /u01/oradata/apacprod/arch/1_258_976404635.dbf YES /u01/oradata/apacprod/arch/1_259_976404635.dbf YES |
Step 2 :
Get the current scn on physical standby side.
|
1 2 3 4 |
SYS>>select current_scn from v$database ; CURRENT_SCN ----------- 501779 |
Step 3 :
Enable Flash Recovery area with required parameters in (s)pfile.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SYS>>show parameter reco NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 db_recovery_file_dest string db_recovery_file_dest_size big integer 0 SYS>>select FLASHBACK_ON from v$database; FLASHBACK_ON ------------------ NO SYS>>ALTER SYSTEM SET db_flashback_retention_target=1440 SCOPE=BOTH; System altered. SYS>>ALTER SYSTEM SET db_recovery_file_dest_size=600M SCOPE=BOTH; System altered. SYS>>ALTER SYSTEM SET db_recovery_file_dest='/u01/oradata/apacprod'; System altered. SYS>>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SYS>>alter database flashback on; Database altered. SYS>>select FLASHBACK_ON from v$database; FLASHBACK_ON ------------------ YES |
Step 4 :
Create Restore point by using guarantee flashback database.
Incase if we are using Guarantee flashback database the flashlogs will not remove at physical location even after retention period.
|
1 2 3 4 5 6 |
SYS>>CREATE RESTORE POINT Standby_flashback_export GUARANTEE FLASHBACK DATABASE; Restore point created. SYS>>select NAME,SCN,TIME from v$restore_point; NAME SCN TIME ------------------------- ---------- ----------------------------------- STANDBY_FLASHBACK_EXPORT 501779 15-JUN-18 08.52.18.000000000 PM |
Step 5 :
At primary side :
Take logswitch and set LOG_ARCHIVE_DEST_STATE_2=defer to stop archivelog sync with stanby database.
|
1 2 3 4 |
SYS>>ALTER SYSTEM ARCHIVE LOG CURRENT; System altered. SYS>>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=defer; System altered. |
Step 6:
At standby Side :
Convert Physical standby database into snapshot standby database and check controlfile type.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SYS>>select CONTROLFILE_TYPE,open_mode from v$database; CONTROL OPEN_MODE ------- ------------ STANDBY MOUNTED SYS>>ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; Database altered. SYS>>ALTER DATABASE OPEN; Database altered. SYS>>select name,open_mode,database_role,controlfile_type from v$database; NAME OPEN_MODE DATABASE_ROLE CONTROL --------------- -------------------- ---------------- ------- APACPROD READ WRITE SNAPSHOT STANDBY CURRENT SYS>>exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options |
Step 7:
Check par file parameters and take full export using DATAPUMP utility.
|
1 2 3 4 5 6 7 8 9 10 |
[oracle@dba12 ~]$ cat exp_apacprod_full.par DIRECTORY=DBA_EXPORT LOGFILE=apacprod_03272018.log DUMPFILE=apacprod_03272018_%U.dmp job_name=apacprod_03272018_exp_job PARALLEL=20 EXCLUDE=STATISTICS full=y flashback_scn=502847 [oracle@dba12 ~]$ nohup expdp \"/ as sysdba\" parfile=expdp_apacprod_full.par |
Step 8 :
After export is done do startup mount force then move to database to restore point.
|
1 2 3 4 5 6 7 8 9 10 |
SYS>>STARTUP MOUNT FORCE; ORACLE instance started. Total System Global Area 222298112 bytes Fixed Size 2922760 bytes Variable Size 163579640 bytes Database Buffers 50331648 bytes Redo Buffers 5464064 bytes Database mounted. SYS>>FLASHBACK DATABASE TO RESTORE POINT Standby_flashback_export ; Flashback complete. |
Step 9 :
Convert snapshot standby database to physical standby database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SYS>>select controlfile_type from v$database; CONTROL ------- BACKUP SYS>>ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Database altered. SYS>>STARTUP MOUNT FORCE; ORACLE instance started. Total System Global Area 222298112 bytes Fixed Size 2922760 bytes Variable Size 163579640 bytes Database Buffers 50331648 bytes Redo Buffers 5464064 bytes Database mounted. SYS>>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; Database altered. SYS>>select controlfile_type from v$database; CONTROL ------- STANDBY |
Step 10:
At Primary Side :
Take Loswitch and set log_archived_dest_state_2=enable for sending log files to standby server.
|
1 2 3 4 |
SYS>>ALTER SYSTEM ARCHIVE LOG CURRENT; System altered. SYS>>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable; System altered. |
Step 11 :
At standby Side :
Enable MRM mode and check archive log files are applied are not.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SYS>>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SYS>> select name,applied from v$archived_log; NAME APPLIED ---------------------------------------- --------- /u01/oradata/apacprod/arch/1_250_976404635.dbf YES /u01/oradata/apacprod/arch/1_249_976404635.dbf YES /u01/oradata/apacprod/arch/1_251_976404635.dbf YES /u01/oradata/apacprod/arch/1_252_976404635.dbf YES /u01/oradata/apacprod/arch/1_253_976404635.dbf YES /u01/oradata/apacprod/arch/1_254_976404635.dbf YES /u01/oradata/apacprod/arch/1_255_976404635.dbf YES /u01/oradata/apacprod/arch/1_257_976404635.dbf YES /u01/oradata/apacprod/arch/1_256_976404635.dbf YES /u01/oradata/apacprod/arch/1_258_976404635.dbf YES /u01/oradata/apacprod/arch/1_259_976404635.dbf YES /u01/oradata/apacprod/arch/1_1_97890101835.dbf YES /u01/oradata/apacprod/arch/1_261_976404635.dbf IN-MEMORY /u01/oradata/apacprod/arch/1_260_976404635.dbf YES /u01/oradata/apacprod/arch/1_263_9764046 35.dbf IN-MEMORY /u01/oradata/apacprod/arch/1_262_976404635.dbf YES /u01/oradata/apacprod/arch/1_264_976404635.dbf YES |
Step 12 :
Drop restore point by using following command.
|
1 2 3 4 5 6 |
SYS>>select NAME,SCN,TIME from v$restore_point; NAME SCN TIME --------------------------- ------------- -------------------------------------------------------- STANDBY_FLASHBACK_EXPORT 501779 18-JUN-18 06.11.19.000000000 PM SYS>>DROP RESTORE POINT Standby_flashback_export ; Restore point dropped. |
|
1 |
<em>Thank You for referring the article if any suggestions put in comment section.</em> |
Note: Please test scripts in Non Prod before trying in Production.
Loading...
Ajay Kumar
If you are interested to know more details about future session please join below telegram group :
https://t.me/joinchat/JFVAtAv1TE9DGHLbJ6rZbw
G
Not sure why you created a guaranteed restore point.. When you convert the standby into snapshot standby, Oracle implicitly creates a restore point.. Isn’t it the case