Activate Standby

Here are the Steps to work with the Standby Database as a Prod Database.
This doc consists in three Parts:

I - Activate Standby Database as Production
II - Siwtchover between Production and Standby Database (without resetlogs)
III - Siwtchback between Standby Database and Production (without resetlogs)
 

Activate Standby as Production
 
Primary Site Standby Site
Archive the current online redo log
ALTER SYSTEM ARCHIVE LOG CURRENT;		 Start the database in nomount
STARTUP NOMOUNT
ALTER DATABASE MOUNT STANDBY DATABASE;
Send the ARCH files to the standby site Start Recovery Mode
RECOVER MANAGED STANDBY DATABASE; 
or
RECOVER STANDBY DATABASE; 
Check the alert file to review the last applied logs
Modify your init.ora file. Specifically 
LOG_ARCHIVE_DEST=<Value>
LOG_ARCHIVE_START = TRUE
LOG_ARCHIVE_DEST_STATE = ENABLE
And Comment the parameter:
STANDBY_ARCHIVE_DEST = <Value>
Ensure that your standby database is mounted in EXCLUSIVE mode by executing the following query:
SELECT name,value FROM v$parameter WHERE name='parallel_server';
If the value is TRUE, then the database is not mounted exclusively; if the value is FALSE, then the database is mounted exclusively.
Activate the standby database (this command resets the online redo logs):
ALTER DATABASE ACTIVATE STANDBY DATABASE;
Shut down the NEW PROD instance:
SHUTDOWN IMMEDIATE
Make a COLD BACKUP and check init.ora parameters
Start the new production instance:
STARTUP

 

Switchover between Prod and Standby DB

Important Note: These steps MUST be tested and understood because a mistake can result in the loss of the production or standby database.

The main Advantage of a graceful switchover or switchback is that it avoids the resetlogs operation. So the source database can resume its role as the standby database almost immediately.
When the primary site requires scheduled maintenance, the production database can gracefully switch over to the standby database. Thereby, the graceful switchover technique may be useful for planned site repairs, hardware changes, O/S upgrades.

Prerequisites of Graceful Switchover or Switchbak

  1. Production DB is shutdown normal or immediate or instance down.
  2. There is no loss of any archive log that haven't been yet applied to the standby database
  3. all archives are applied to the standby database
  4. source database's online redo logs are available and intact
  5. target database is still intact and has the same resetlogs version as the source database
Graceful switchover or switchback will not work when either production database's online redo logs or standby database is lost or inaccesible.
 

Steps in detail
 
Primary Site Standby Site
Alter system archive log current; DB is in Recover Mode
alter database backup controlfile to trace noresetlogs;
Send the trace file.		 Copy the controlfile script from prod to standby. You will need to modify this file with the appropiate data file and log file path names. Validate status and existence of all data files (all the online, datafiles from offline tablespaces and read only tablespaces will be offlined in the create controlfile script) and log files. Comment out the RECOVER DATABASE and the ALTER DATABASE OPEN. (Appendix C)
shutdown immediate   (check alert.log file for ALTER DATABASE CLOSE NORMAL) Apply last set of archives and cancel recovery
Copy online redo logs to standby (do not delete them from prod) shutdown immediate (check alert.lof file for ALTER DATABASE CLOSE NORMAL and ALTER DATABASE DISMOUNT)
Reverse production and stanby network connections (if necessary) Reverse production and stanby network connections (if necessary)
Modify your init.ora file to adapt it to the new changes (Appendix A)
Execute the create controlfile script.
startup nomount
create controlfile ......
Recover database (will recover only online datafiles)
Validate oracle database (Appendix B)
alter database open
alter system archive log all (to send archives to the new standby database)
Server start to receive archives log files Now clients can reconnect here, Standby becomes PRODUCTION
alter database create standby controlfile as '/path/standby.ctl';
Send it to the new standby database
Validate existence of all Oracle datafiles and standby controlfiles. Modify your init.ora file to adapt it to the new changes (Appendix A)
Startup nomount
alter database mount standby;
Rename datafiles and control files is necessary (alter database rename file '/path/name' to '/newpath/name'; )
Offline data files if requiered (alter database datafile 'name' offline;)
Check Apendix B for validation
Initiate recovery:
alter database recover managed standby database or recover standby database;
Oracle start to apply archive logs (check alert file)
PRODUCTION BECOMES STANDBY

Apendix A: Modify init.ora

The "new" standby init.ora file
db_file_name_convert = "oldpath","newpath"
log_file_name_convert = "oldpath","newpath"
control_files = pointing to standby controlfile or backup controlfile
standby_archive_dest = <Value>

For Tuning
db_block_buffers
recovery_parallelism
log_buffer
db_writers

The "new" production init.ora
db_file_name_convert = "oldpath","newpath"
log_file_name_convert = "oldpath","newpath"
control_files = pointing to the production controlfiles
Log_archive_format   = arch_NAMEDB_%S.arc
Log_archive_start    = true
log_archieve_dest_1 = "location=<Value> MANDATORY"
log_archieve_state_1 = enable
log_archive_dest_2 = "service=<service_name> OPTIONAL reopen=60"
log_archive_dest_state_2 = enable
log_archive_min_succeed_dest = 1
 

Apendix B: Validate Standby Database
The following scripts will help you to validate the databases.

  • select member, status from v$logfile;

    •     Status must be blank, if it is INVALID then perhaps you didn't rename the file.
  • select sequence#, status from v$log;

    •     The highest sequence number must have a status of "CURRENT". All other must be as INACTIVE. UNUSED implies that this log was just added.
  • select * from v$recover_file;

    •     Should return no rows.
  • select substr(name,1,50) NAME, STATUS, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# from v$datafile;

    •     Check if the statuses of each file is correct (online,systemoffline). if they do have a RECOVER status, then check if the files are fuzzy and recovery in v$datafile_header.
        All the CHECKPOINT_CHANGE# are the same value unless the files are read only or offline.
        The UNRECOVERABLE_CHANGE#  should be < than change# in v$backup for the same file.
        All the files must exist in the first query
  • select substr(name,1,50) NAME, status, error, recover, fuzzy from v$datafile_header;

    •     Validate correct status for each data file. ERROR column must be blank.
        RECOVER=YES more recovery is needed
        FUZZY=YES more revovery is requiered.
  • select resetlogs_change#, resetlogs_time, open_resetlogs from v$database;

    •     Both databases MUST have the same resetlogs version
     

    Apendix C

    STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE "TICPBT09" NORESETLOGS ARCHIVELOG
    #Validate all settings
        MAXLOGFILES 32
        MAXLOGMEMBERS 2
        MAXDATAFILES 1000
        MAXINSTANCES 8
        MAXLOGHISTORY 5899
    LOGFILE
    #Validate and correct log file names and paths.
      GROUP 1 (
        '/u05/oradata/TICPBT09/redo01A.log',
        '/u06/oradata/TICPBT09/redo01B.log'
      ) SIZE 5000K,
      GROUP 2 (
        '/u06/oradata/TICPBT09/redo02A.log',
        '/u07/oradata/TICPBT09/redo02B.log'
      ) SIZE 5000K,
      GROUP 3 (
        '/u07/oradata/TICPBT09/redo03A.log',
        '/u05/oradata/TICPBT09/redo03B.log'
      ) SIZE 5000K
    DATAFILE
    #Validate and correct data file names and paths.
      '/u05/oradata/TICPBT09/system01.dbf',
      '/u05/oradata/TICPBT09/rbs01.dbf',
      '/u06/oradata/TICPBT09/temp01.dbf',
      '/u06/oradata/TICPBT09/users01.dbf',
      '/u06/oradata/TICPBT09/llownerndx01.dbf',
      '/u07/oradata/TICPBT09/llownerdata01.dbf',
      '/u07/oradata/TICPBT09/lpownerndx01.dbf',
      '/u08/oradata/TICPBT09/lpownerdata01.dbf',
      '/u05/oradata/TICPBT09/CO_SNP_LOGS.dbf',
      '/u06/oradata/TICPBT09/temp01_01.dbf',
      '/u07/oradata/TICPBT09/coownerndx01.dbf',
      '/u06/oradata/TICPBT09/coownerdata01.dbf',
      '/u07/oradata/TICPBT09/PB_OWNER_NDX1.dbf',
      '/u08/oradata/TICPBT09/PB_OWNER.dbf',
      '/u04/oradata/TICPBT09/rbs02.dbf'
    CHARACTER SET US7ASCII
    ;
    # Comment out recover and all commands from this point
    #RECOVER DATABASE
    # All logs need archiving and a log switch is needed.
    #ALTER SYSTEM ARCHIVE LOG ALL;
    # Database can now be opened normally.
    #ALTER DATABASE OPEN;
    # Commands to add tempfiles to temporary tablespaces.
    # Online tempfiles have complete space information.
    # Other tempfiles may require adjustment.
    #ALTER TABLESPACE TEMP_LOCAL ADD TEMPFILE '/u04/oradata/TICPBP02/temp_local01.dbf' REUSE;
    # End of tempfile additions.
     

    Graceful Switchback between Standby and Prod DB

    As you can imagine, the steps are the same as the previous table but changing the columns.
    That's all!!!