Data Guard Administration
TOC
- Status Checks: DGMGRL | SQL
- Switchover Database
- Failover Database
- Parameter Change\Resize Session
- Rebuild SB (Data ReSync of Existing SB) 1
- Rebuild SB (Data ReSync of Existing SB)
- Trouble Shooting: Oracle Docs | Enable Archive Tracing
Password Changes
In 18c and later:
- Changing the SYS password is automatically sync'd with the SB.
So you no longer have to copy the password file to the SB after changing SYS password. - SYSTEM, DBSNMP and other password changes are automatically sync'd with the SB.
So you do not need to OPEN the SB to change them on the SB.
Status Checks
Replace oradb with your database\instance name as required.
DG Broker Log Example
$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/drc$ORACLE_SID.log
DB Alert Log Example
$ORACLE_BASE/diag/rdbms/$ORACLE_DB/$ORACLE_SID/trace/alert_$ORACLE_SID.log
DGMGRL>
dgmgrl sys/go@oradb
- show configuration;
- show database oradb;
- show database oradb InconsistentProperties;
- show database oradb StatusReport;
- show database verbose oradb; -- 12.2 and later (shows path to DG log file)
- validate database oradb;
- validate database verbose oradb;
- validate database oradb_sb SPFILE; -- 18c and later (run from SB only)
- validate NETWORK configuration for all; -- 18c and later (run from both)
- validate STATIC CONNECT IDENTIFIER FOR all; -- 18c and later (run from both)
SQL>
Ensure Logs Being Transferred
-- On Both SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) GROUP BY THREAD#; If in sync, MAX(SEQUENCE#) will be the same on both. -- On Primary ALTER SYSTEM SWITCH LOGFILE; -- On Both SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) GROUP BY THREAD#; If in sync, MAX(SEQUENCE#) will be the same on both. SWITCH LOGFILE increments value.
-- Show Database, Unique and Instance Names
show parameter db_name show parameter db_unique_name show parameter instance_name
-- Show DB Status
COL database_role FORMAT a20 COL db_unique_name FORMAT a15 COL instance FORMAT a15 COL open_mode FORMAT a15 COL protection_level FORMAT a25 COL switchover_status FORMAT a15 SELECT database_role, db_unique_name Instance, open_mode, protection_level, switchover_status FROM V$DATABASE;
-- Quick Mode Check SELECT open_mode FROM v$database;
- PRI: READ WRITE
- SB: MOUNTED | READ ONLY WITH APPLY
-- Get Current SCN SELECT to_char(CURRENT_SCN) FROM V$DATABASE;
-- Show Data Guard Status and Config Files
sho parameter dg_broker
-- On SB: Verify Managed Recovery Process (MRPn) Running on the Standby
SELECT process,thread#,sequence#,status FROM v$managed_standby WHERE process LIKE 'MRP%';
Normally the SB should show WAIT_FOR_LOG or APPLYING_LOG.
-- Check RECOVERY_MODE
SELECT DEST_ID, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
Normally the Primary should show MANAGED REAL TIME APPLY and SB IDLE.
-- Show Log Status (shows if logs applied at SB)
SELECT sequence#, first_time, applied FROM v$archived_log ORDER BY sequence#;
Also
archive log list
-- On Primary: Gap Status
SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
Normally the Primary should show VALID - NO GAP and SB INACTIVE.
-- Determine the most recently archived sequence# for each thread
SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) GROUP BY THREAD#;
-- Determine the most recently archived redo log at destination (run from primary).
COL destination FORMAT a35 COL status FORMAT a15 COL archived_thread# FORMAT 999999 COL archived_seq# FORMAT 999999 SELECT destination, status, archived_thread#, archived_seq# FROM v$archive_dest_status WHERE status <> 'DEFERRED' AND status <> 'INACTIVE';
-- Find out if archived redo log files have been received at a redo transport destination (run from primary).
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);
-- Show Data Guard Processes SELECT process, status FROM V$MANAGED_STANDBY;
-- Standby_Became_Primary_SCN SELECT standby_became_primary_scn FROM v$database;
Purge SB ASM Files
su - grid Run: asmcmd rm -rf +DATA/oradb_sb/CONTROLFILE/* rm -rf +DATA/oradb_sb/DATAFILE/* rm -rf +DATA/oradb_sb/ONLINELOG/* rm -rf +DATA/oradb_sb/PARAMETERFILE/* rm -rf +DATA/oradb_sb/PASSWORD/* rm -rf +DATA/oradb_sb/TEMPFILE/* rm -rf +FRA/oradb_sb/ARCHIVELOG/* rm -rf +FRA/oradb_sb/CONTROLFILE/* rm -rf +FRA/oradb_sb/FLASHBACK/* rm -rf +FRA/oradb_sb/ONLINELOG/*
Enable-Disable Log Xfer to SB (for non-Broker Env)
-- Disable alter system set log_archive_dest_state_2=defer scope=both; show parameter log_archive_dest_state_2 -- Enable alter system set log_archive_dest_state_2=enable scope=both; show parameter log_archive_dest_state_2
Must be performed on Primary!
Enable-Disable Data Guard Broker
dgmgrl sys/go@oradb DGMGRL> disable configuration ... DGMGRL> enable configuration
Must be performed on Primary!
Start-Stop SB Database on OS Boot
1. gedit /etc/oratab
oradb_sb:/u01/app/oracle/product/18.3.0.0.0/dbhome_1:Y
2. Configure dbora script.
3. Edit the $ORACLE_HOME/bin/dbstart
Change startup command to startup mount.
4. Testing
oracle> $ORACLE_HOME/bin/dbstart $ORACLE_HOME cat /u01/app/oracle/product/18.3.0.0.0/db_home1/rdbms/log/startup.log
Change Data Guard Broker Setting
EDIT DATABASE <db_name> SET PROPERTY <PropertyName>='<Value>';
dgmgrl sys/go@oradb DGMGRL> EDIT DATABASE oradb SET PROPERTY TransportLagThreshold='0';
See all properties: show database verbose oradb;
Database Switchover
Quick Status Checks
Before performing any operation it is a good idea to make sure everything is working OK. Run: dgmgrl sys/pw@oradb
State
show database verbose oradb; TRANSPORT-ON show database verbose oradb_sb; APPLY-ON
Database Status
show database oradb; Database Status: SUCCESS show database oradb_sb; Database Status: SUCCESS
Database Ready for a Role Change?
show configuration; Primary Configuration Status: SUCCESS Standby Configuration Status: SUCCESS validate database oradb; Ready for Switchover: Yes validate database oradb_sb; Ready for Switchover: Yes
Network (if 18c or later)
validate NETWORK configuration for all; Succeeded. The static connect identifier allows for a connection to database "oradb". ... Succeeded. The static connect identifier allows for a connection to database "oradb_sb".
sqlplus / as sysdba
SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) GROUP BY THREAD#; ALTER SYSTEM SWITCH LOGFILE; SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) GROUP BY THREAD#; SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
Switch
-- Switch to Standby (from Primary host) # lnx01> dgmgrl sys/go # DGMGRL> SWITCHOVER TO oradb_sb; ... Switchover succeeded, new primary is oradb_sb -- Switch Back (from SB host) # lnx02> dgmgrl sys/go@oradb_sb # DGMGRL> SWITCHOVER TO oradb; ... Switchover succeeded, new primary is oradb
• In some instances you may need to manually perform a STARTUP MOUNT on the SB after a switch back.
• In some instances I've seen the DGMGRL> stay on the Oracle Clusterware is restarting database "oradb_sb"... point until I manually start\mount the SB from another console sqlplus session.
• If SB is using Oracle-Restart, it may take several minutes to restart the CRS and database on the SB when switching back. If database is not registered you will have to manually start it (STARTUP MOUNT). DGMGR> may stay on SWITCHOVER TO cmd until you start DB from another console via sqlplus.
Database Failover
If the primary database is not available the standby database can be activated as a primary database using the following. Connect to the standby database (oradb_sb) and failover.
Whether this is a real DR scenario or just a test, make sure your original Primary database does not do a normal startup. Start it as mounted so both don't become available as a Primary.
-- Primary Shutdown or Failed If Primary not already down. lnx01> sqlplus / as sysdba SQLPlus> shutdown immediate; SQLPlus> exit If RAC or Oracle Restart: srvctl stop database -d <DB_Name> -- Failover lnx02> dgmgrl sys/go@oradb_sb DGMGRL> FAILOVER TO oradb_sb; Performing failover NOW, please wait...%5 Failover succeeded, new primary is "oradb_sb"
-- Reinstate Primary Startup Original Primary lnx01> sqlplus / as sysdba SQLPlus> startup mount; If RAC or Oracle Restart: srvctl start database -d <DB_Name> From Standby: lnx02> dgmgrl sys/go@oradb_sb DGMGRL> REINSTATE DATABASE oradb; Reinstating database "oradb", please wait... Reinstatement of database "oradb" succeeded QC (run on both Primary and SB) SELECT database_role, db_unique_name Instance FROM V$DATABASE; If Primary's role = PHYSICAL STANDBY 1. From Primary: DGMGRL> enable database oradb; 2. From SB: DGMGRL> SWITCHOVER TO oradb; Performing switchover NOW, please wait... Operation requires a connection to database "oradb" Connecting ... Connected to "oradb" Connected as SYSDBA. New primary database "oradb" is opening... Operation requires start up of instance "oradb" on database "oradb_sb" Starting instance "oradb"... Connected to an idle instance. ORACLE instance started. Connected to "oradb_sb" Database mounted. Database opened. Switchover succeeded, new primary is oradb QC (run on both Primary and SB) SELECT database_role, db_unique_name Instance FROM V$DATABASE; PRI DATABASE_ROLE INSTANCE ---------------- ------------------------------ PRIMARY oradb SB DATABASE_ROLE INSTANCE ---------------- ------------------------------ PHYSICAL STANDBY oradb_sb
When RAC starts it will detect if it is Primary or SB and start accordingly (startup vs. startup mount).
Reinstate\Recreate SB
On Primary dgmgrl sys/go@oradb
DGMGRL> remove configuration;
On Standby
- Shutdown SB Database
sqlplus / as sysdba
SQLPlus> shutdown immediate;
SQLPlus> exit
- Start SB Database Using Initial PFile
sqlplus / as sysdba
SQLPlus> STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/init_tmp.ora';
- Perform RMAN Duplicate database? operation.
- Start Recovery
alter system set log_archive_dest_2='' scope=both; # Required for 12.x alter database recover managed standby database disconnect from session;
- Enable Broker
ALTER SYSTEM SET dg_broker_start=true sid='*' scope=both;
On Primary
- dgmgrl sys/go@oradb
- CREATE CONFIGURATION dg_cfg1 AS PRIMARY DATABASE IS oradb CONNECT IDENTIFIER IS oradb;
- ADD DATABASE oradb_sb AS CONNECT IDENTIFIER IS oradb_sb MAINTAINED AS PHYSICAL;
- ENABLE CONFIGURATION;
- show configuration;
On SB
If show configuration; displays any ORA-16nnn errors they should resolve automatically in a few minutes. If not, on the SB do a shutdown immediate
then startup mount;
Common Errors
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
Fix: alter system set log_archive_dest_2='' scope=both;
Complete Session
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set Workaround - Save the log archive destination settings from both the Primary and Standby databases then remove the configuration. sho parameter log_archive_dest_2 From oradb: SERVICE=oradb_sb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb_sb From oradb_sb: service=oradb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=oradb - Remove the Dataguard configuration DGMGRL> remove configuration; Removed configuration - Set the log_archive_dest_2 settings from both the Primary and Standby databases to be nothing. alter system set log_archive_dest_2='' scope=both sid='*'; - Disable then Enable the broker parameter on both the Primary and Standby databases. -- Primary alter system set dg_broker_start=false scope=both sid='*'; alter system set dg_broker_start=true scope=both sid='*'; -- Standby alter system set dg_broker_start=false scope=both sid='*'; alter system set dg_broker_start=true scope=both sid='*'; - On the Primary database create the broker configuration for the Primary and Standby database and this time it should work fine with no issues since the log archive destination 2 setting is not set. This is the workaround/solution.
Warning: ORA-16714: the value of property xxx is inconsistent with the member setting
- On Primary get the current correct value.
SQLPlus> show parameter <param_name>
- Set the value(s) on the Standby
Common Changes Example
alter system set log_archive_max_processes=4 scope=both sid='*'; alter system set archive_lag_target=0 scope=both sid='*'; alter system set log_archive_min_succeed_dest=1 scope=both sid='*'; alter system set data_guard_sync_latency=0 scope=both sid='*'; show parameter log_archive_max_processes; show parameter archive_lag_target; show parameter log_archive_min_succeed_dest; show parameter data_guard_sync_latency;
Remove Broker Configuration
DGMGRL> remove configuration;
To Manually Delete Files
cd $ORACLE_HOME/dbs ls dr* -rw-r----- 1 oracle oinstall 8192 Oct 10 12:18 dr1oradb.dat -rw-r----- 1 oracle oinstall 16384 Oct 10 12:18 dr2oradb.dat rm dr1oradb.dat rm dr2oradb.dat
If Primary is RAC:
rm -rf +DATA/oradb/DGBROKER/* rm -rf +FRA/oradb/DGBROKER/*
RESET
--PRIMARY dgmgrl sys/go@oradb remove configuration; OS> rm -rf $ORACLE_HOME/dbs/dr* alter system NO FORCE LOGGING; alter system set log_archive_dest_state_2=defer scope=both; show parameter log_archive_dest_state_2 --SB sqlplus> shutdown abort rm -rf /u01/app/oracle/admin/oradb_sb rm -rf /u01/oradata/ORADB rm -rf /u01/oradata/ORADB_SB rm -rf /u02/oradata/ORADB rm -rf /u02/oradata/ORADB_SB rm -rf $ORACLE_HOME/dbs/*.dat rm -rf $ORACLE_HOME/dbs/*.f rm -rf $ORACLE_HOME/dbs/*.ora rm -rf $ORACLE_HOME/dbs/lk* rm -rf $ORACLE_HOME/dbs/dr* rm -rf $ORACLE_HOME/dbs/orapw*
QC Data Test
Can be used to QC data changes upon a SWITCH or FAILOVER.
UPDATE HR.cust SET name='Test111' WHERE cust_id=100; COMMIT; SELECT name FROM HR.cust WHERE cust_id=100;
Uses HR.cust (schema.table). Change as required for your environment.
Usage:
- Verify value before switch on Primary.
- Perform SWITCH or FAILOVER.
- From SB change (UPDATE) value.
For instance: name='Test222' - Reinstate Primary.
- Verify value on Primary.
Value on Primary should be the same as what it was changed to on the SB.
Alert and DG Logs
Examples where Primary is oradb1 and SB oradb_sb.
Alert
Primary: tail -f /u01/app/oracle/diag/rdbms/oradb/oradb1/trace/alert_oradb1.log SB: tail -f /u01/app/oracle/diag/rdbms/oradb_sb/oradb_sb/trace/alert_oradb_sb.log
DG
Get Log Path: SELECT value FROM GV$DIAG_INFO WHERE name='Diag Trace'; Primary: tail -f /u01/app/oracle/diag/rdbms/oradb/oradb1/trace/drcoradb1.log SB: tail -f /u01/app/oracle/diag/rdbms/oradb_sb/oradb_sb/trace/drcoradb_sb.log
Aliases
alias dglog='tail -f /u01/app/oracle/diag/rdbms/oradb/oradb1/trace/drcoradb1.log' alias dgloge='gedit /u01/app/oracle/diag/rdbms/oradb_sb/oradb_sb/trace/drcoradb_sb.log &'
Parameter Change\Resize Session
Change Params
-- On both PRI and SB show parameter DB_RECOVERY_FILE_DEST_SIZE ALTER SYSTEM SET PARALLEL_MAX_SERVERS = 64 SCOPE=BOTH; ALTER SYSTEM SET PARALLEL_MIN_SERVERS = 32 SCOPE=BOTH; ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2T SCOPE=BOTH;
Part I: Resize Online Redo Logs
1. Check current standby logs: SELECT GROUP#, BYTES/1024/1024 AS SIZE_MB, STATUS, MEMBERS FROM V$LOG; 2. Add new standby logs (1 more than the number of redo groups): PRI ALTER DATABASE ADD LOGFILE SIZE 1G; x 4 SB ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; ALTER DATABASE FLASHBACK OFF; >> ALTER DATABASE ADD LOGFILE SIZE 1G; x4 ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; ALTER DATABASE FLASHBACK ON; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 3. Drop old redo groups (once STATUS='INACTIVE'): SELECT GROUP#, BYTES/1024/1024 AS SIZE_MB, STATUS, MEMBERS FROM V$LOG; ALTER SYSTEM SWITCH LOGFILE; x n ALTER SYSTEM CHECKPOINT; ALTER DATABASE DROP LOGFILE GROUP 1; 1-4
Part II: Resize Standby Redo Logs
1. Check current redo log groups: SELECT THREAD#, GROUP#, BYTES/1024/1024 AS SIZE_MB FROM V$STANDBY_LOG; 2. Add new 1GB redo log groups (OMF) on BOTH: PRI ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 1G; x5 SB ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; ALTER DATABASE FLASHBACK OFF; >> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 1G; x5 ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; ALTER DATABASE FLASHBACK ON; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 3. Drop old redo groups SELECT THREAD#, GROUP#, BYTES/1024/1024 AS SIZE_MB FROM V$STANDBY_LOG; 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; Post Actions\Check -- Bounce SB SHUTDOWN IMMEDIATE; STARTUP MOUNT; -- Check dgmgrl sys/***@dnatra_sb SHOW CONFIGURATION; -- This will confirm if the standby is in APPLY-OFF or DISABLED state. -- If needed: ENABLE DATABASE dnatra_sb; EDIT DATABASE dnatra_sb SET STATE = APPLY-ON; SHOW CONFIGURATION; -- Confirm: Configuration Status: SUCCESS -- Check Standby Apply Status SHOW DATABASE dnatra_sb; Look for: Apply-Related Status: APPLYING_LOG If instead you see APPLY-OFF or Not Started, run: EDIT DATABASE dnasolo_sb SET STATE = APPLY-ON; -- Verify with SQL (optional) SELECT process, status FROM v$managed_standby WHERE process LIKE 'MRP%'; PROCESS STATUS --------- ------------ MRP0 APPLYING_LOG
QC
-- On BOTH (ensure thread# same) SELECT thread#, max(sequence#) "Last Applied" FROM v$archived_log WHERE applied='YES' GROUP BY thread#; -- Redo Logs (ensure number of files and sizes OK) SELECT GROUP#, BYTES/1024/1024 AS SIZE_MB, STATUS, MEMBERS FROM V$LOG; -- SRLs (ensure number of files, sizes and Thread# OK) SELECT THREAD#, GROUP#, BYTES/1024/1024 AS SIZE_MB FROM V$STANDBY_LOG;
Rebuild Process
Rebuild Standby Steps Cmds SELECT DB_UNIQUE_NAME FROM V$DATABASE; OVERVIEW | Role | Hostname | ORACLE\_SID | DB\_UNIQUE\_NAME | | ------- | -------------- | ----------- | ---------------- | | Primary | d2-orasolo-p01 | dnasolo | dnasolo | | Standby | d1-orasolo-p01 | dnasolo | dnasolo\_sb | 1. On PRIMARY (d2-orasolo-p01): Defer log transport ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH; 2. On STANDBY (d1-orasolo-p01): Clean up old DB export ORACLE_SID=dnasolo sqlplus / as sysdba -- Stop recovery and shutdown ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SHUTDOWN IMMEDIATE; EXIT; Remove all database files: rm -rf /u01/app/oracle/oradata/dnasolo/* rm -rf /u02/fra/dnasolo/* rm -rf /u03/controlfiles/dnasolo/* rm -f $ORACLE_HOME/dbs/orapwdnasolo 3. On STANDBY: Setup initdnasolo.ora Create init file for dnasolo (SID) but with unique name dnasolo_sb: cat <<EOF > $ORACLE_HOME/dbs/initdnasolo.ora DB_NAME=dnasolo DB_UNIQUE_NAME=dnasolo_sb CONTROL_FILES='/u03/controlfiles/dnasolo/control01.ctl' DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/dnasolo' DB_RECOVERY_FILE_DEST='/u02/fra/dnasolo' DB_RECOVERY_FILE_DEST_SIZE=7000G FAL_SERVER=dnasolo FAL_CLIENT=dnasolo_sb LOG_ARCHIVE_CONFIG='DG_CONFIG=(dnasolo,dnasolo_sb)' LOG_ARCHIVE_DEST_2='SERVICE=dnasolo LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dnasolo' EOF -- Check cat $ORACLE_HOME/dbs/initdnasolo.ora Then start NOMOUNT: export ORACLE_SID=dnasolo sqlplus / as sysdba STARTUP NOMOUNT; -- Prep Dirs mkdir -p /u01/app/oracle/oradata/dnasolo mkdir -p /u02/fra/dnasolo mkdir -p /u03/controlfiles/dnasolo chown -R oracle:oinstall /u01/app/oracle/oradata chown -R oracle:oinstall /u02/fra chown -R oracle:oinstall /u03/controlfiles 4. On PRIMARY (d2-orasolo-p01): Run RMAN Duplicate ?? cd /u03/rman -- From PRI: init adump SHOW PARAMETER audit_file_dest; /u01/app/oracle/admin/dnasolo/adump ---- Then on SB mkdir -p /u01/app/oracle/admin/dnasolo/adump chown -R oracle:oinstall /u01/app/oracle/admin -- From PRI: cp PW File scp $ORACLE_HOME/dbs/orapwdnasolo oracle@d1-orasolo-p01:$ORACLE_HOME/dbs/orapwdnasolo PW Prompt: X9#m!A4b -- On SB rm -rf /u02/oradata/* sqlplus / as sysdba SHUTDOWN IMMEDIATE; STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initdnasolo.ora'; -- On PRI rman target sys/***@dnasolo auxiliary sys/***@dnasolo_sb Then run: DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET DB_UNIQUE_NAME='dnasolo_sb' SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/dnasolo','/u01/app/oracle/oradata/dnasolo' SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/dnasolo','/u01/app/oracle/oradata/dnasolo' SET DB_RECOVERY_FILE_DEST='/u02/fra/dnasolo' SET CONTROL_FILES='/u03/controlfiles/dnasolo/control01.ctl' NOFILENAMECHECK; 5. On STANDBY (d1-orasolo-p01): Start MRP sqlplus / as sysdba ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 6. On PRIMARY: Resume log transport ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH; 7. Verify On SB: SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY; SELECT NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE; On PRI: SELECT DEST_ID, STATUS, ERROR, DESTINATION, DB_UNIQUE_NAME FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2; Post-Standby Rebuild Checklist (Supplemental) --------------------------------------------- 1. ✅ Reconfigure RMAN Policies on PRIMARY (If Needed) RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY; 2. ✅ Re-enable Flashback if your environment uses it -- PRI ALTER DATABASE FLASHBACK ON; -- SB ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE FLASHBACK ON; 3. ✅ (Optional) Recreate Standby Redo Logs if needed SELECT GROUP#, THREAD#, BYTES/1024/1024 AS SIZE_MB FROM V$STANDBY_LOG; SELECT GROUP#, THREAD#, BYTES/1024/1024 AS SIZE_MB FROM V$LOG; -- RMAN restore (SB Creation) should havve already created Redo SRLs. --ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 1G; 4. ✅ Start Redo Apply on Standby -- If not done yet (probably wont need this) -- ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; -- STATUS SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY; 5. ✅ Validate SELECT DEST_ID, STATUS, ERROR, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID > 1; SELECT NAME, OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS FROM V$DATABASE; On SB You want: OPEN_MODE = MOUNTED DATABASE_ROLE = PHYSICAL STANDBY SWITCHOVER_STATUS = NOT ALLOWED or TO PRIMARY (depending on state)