UniversalTemplate/logo.png UniversalTemplate/UTS.png

ReDuplicateBrokenPhysicalStandbyDatabase

1. If you do not already have a valid standby database (for example, if you have just refreshed the TEST database but your TEST standby and TEST reporting database have not yet been rebuilt), ensure that Data Guard is turned off in the primary database:

  • SQL> alter system set dg_broker_start=false scope=both;

2. Do NOT clear log_archive_dest_1! In the primary database, ensure that no invalid standby locations are defined as a log archive destination. For example, if you are planning to recreate TEST_DB2 but that database is defined as a destination in the log_archive_dest_3 parameter, that parameter must be cleared. Clear ALL destinations where the standby defined there must be rebuilt.

NOTE: Do NOT clear log_archive_dest_1! Also, do NOT clear this parameter for valid standby databases. For example, if you have already recreated TESTREPT and are about to recreate TEST_DB2, do not clear the destination where TESTREPT is defined.

Ex: select dest_name, status, destination from v$archive_dest;

LOG_ARCHIVE_DEST_1 VALID USE_DB_RECOVERY_FILE_DEST

LOG_ARCHIVE_DEST_2 ERROR dwtest_db2

So LOG_ARCHIVE_DEST_2 needs to be cleared in this case...

  • SQL> alter system reset log_archive_dest_2 scope=both;

3. Shutdown the physical standby database

  • SQL> shutdown immediate;

4. (Optional, safer in case of failure) Backup all datafiles (*.dbf), redologs, indexes, undo, archive logs, online logs, flashback logs and control files that are used by the shut down instance.

5. In Standby DB Server, Delete all datafiles (*.dbf), redologs, archive logs, undo, indexes, online logs, flashback logs and control files in their referenced locations, but make sure to keep the directories.

Also, in the $ORACLE_HOME/dbs directory, create a basic initSID.ora file EX: oracle@ea-bantestdb-t02 DWTEST /oracle/product/19.3.0/dbhome_test/dbs $ more initDWTEST.ora *.db_name='DWTEST' , then backup the spfile and delete it for your database. The goal being to startup nomount the db using the basic initSID.ora file.

Also remove the Data Guard files (dr[1-2]DB_UNIQUE_NAME.dat, for example: dr1TEST_DB2.dat, dr2TEST_DB2.dat), the health check monitor file (hc_SID.dat, for example: hc_TEST.dat), and instance lock file (lkDB_UNIQUE_NAME, for example: lkTEST_DB2).

6. Start up your Physical Standby Database with NOMOUNT-Option

  • SQL> startup nomount;

7. Now switch to your Primary Database Environment.

8. Start your rman on your standby envoronment:

  • rman target sys/password@PRIMARY_DB auxiliary sys/password@STANDBY_DB
  • Example: rman target sys/????@DWPROD5 auxiliary sys/????@DWPROD5STANDBY #you should be able to tnsping the 2 db's after the @'s...

STANDBY_DB must be substituted with your STANDBY_DB instance name. After connect make sure your connected target database is your primary database connected to target database: PRIMARY_DB (DBID=4135917300) auxiliary database Password: connected to auxiliary database: STANDBY_DB (not mounted). Note that your physical standby database is listed as not mounted primary database. If you see the same information as in target database, chances are that you are connected twice to your primary database. In that case we would create a 100% copy and not a physical standby database. So make sure you are using the right Standby Instance (Data Guard Instance).

  • Example: rman target sys/????@DWPROD5 auxiliary sys/????@DWPROD5STANDBY

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Mar 11 10:18:55 2021 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: DWPROD5 (DBID=3110128211) connected to auxiliary database: DWPROD5 (not mounted)

9. Before you start, do a force log switch:

  • RMAN> sql 'alter system archive log current';

10. Now we are going to start the full replication of our Physical Standby Database. With Oracle 19c, redo log files must exist in a directory named differently than that of the primary, even if those files are on different systems. This is not currently true of data files, but we also rename those to maintain consistency. Substitute the actual database SID for ORACLE_SID (for example, TEST), the actual database unique name for DB_UNIQUE_NAME (for example, TEST_DB2) and the actual primary database unique name of the primary database for PRIMARY_DB (for example, TEST_DB1. If the directories referenced in the log_file_name_convert and db_file_name_convert parameters do not exist on the standby, you must create them.

  • RMAN> duplicate target database for standby from active database dorecover spfile set db_unique_name='DB_UNIQUE_NAME' set log_file_name_convert='/oracle/redo/SID/','/oracle/redo/DB_UNIQUE_NAME/' set db_file_name_convert='/oracle/data/SID/','/oracle/data/DB_UNIQUE_NAME/','/oracle/indices/SID/','/oracle/indices/DB_UNIQUE_NAME/','/oracle/undo/SID/','/oracle/undo/DB_UNIQUE_NAME/','/oracle/redo/SID/','/oracle/redo/DB_UNIQUE_NAME/', set fal_client='DB_UNIQUE_NAME' set fal_server='PRIMARY_DB' set service_names='ORACLE_SID','DB_UNIQUE_NAME','ORACLE_SID.admnet.oakland.edu','DB_UNIQUE_NAME.admnet.oakland.edu' nofilenamecheck;

  • Ex:
  • duplicate target database for standby from active database dorecover spfile set db_unique_name='DWTEST_DB2' SET DB_CREATE_FILE_DEST='/oracle/data/DWTEST_DB2' set log_file_name_convert='/oracle/redo/DWTEST/','/oracle/redo/DWTEST_DB2/','/oracle/redo/DWPROD/','/oracle/redo/DWTEST_DB2/' set db_file_name_convert='/oracle/data/DWTEST/','/oracle/data/DWTEST_DB2/','/oracle/indices/DWTEST/','/oracle/indices/DWTEST_DB2/','/oracle/undo/DWTEST/','/oracle/undo/DWTEST_DB2/','/oracle/redo/DWTEST/','/oracle/redo/DWTEST_DB2/','/oracle/data/DWPROD/','/oracle/data/DWTEST_DB2/','/oracle/undo/DWPROD/','/oracle/undo/DWTEST_DB2/','/oracle/indices/DWPROD/','/oracle/indices/DWTEST_DB2/','/oracle/redo/DWPROD/','/oracle/redo/DWTEST_DB2/' set fal_client='DWTEST_DB2' set fal_server='DWTEST_DB1' set service_names='ORACLE_DWTEST','DWTEST_DB2','ORACLE_DWTEST.admnet.oakland.edu','DWTEST_DB2.admnet.oakland.edu' nofilenamecheck;

11. Now rman will perform a duplication of your physical standby database. Depending on your datafile size, this can take from a few hours to open end.

12. After rman is finished you can exit rman and in the Primary, Enable Broker "alter system set dg_broker_start=true;. Correct any errors. If the errors caused data files or log files to be incorrect, you will need to start this procedure from the beginning.

13. Reconnect to your physical standby database and shut it down:

  • SQL> shutdown immediate;

14. Restart your standby database.

  • per SIGDBA, run $ORACLE_HOME/bin/dbstart1 which should identify the db instance type and start it up correctly.
  • Note: What script is doing for active standby "A" (report clones) there is also a "S" for non-active standby (Degree Works standby)...
  • startup mount

alter database open read only; alter database recover managed standby database disconnect from session; EOF

  • if [ $? -eq 0 ] then
    • echo "" echo "Standby database \"${ORACLE_SID}\" warm started as Active Data Guard standby."

If you use flashback option, do this instead:

  • SQL> startup mount; SQL> alter database flashback on;

DataAdminHowTo