Friday 6 May 2016

Db restore of the standby (more or less manual)

  1. remove old db files - controlfiles, log files (online and standby), data files, temp files - it is recommended to do it in the first place before any restore will start as it may be quite easy to choose wrong files for removal if the restored files are mixed with the old ones
  2. check db_create_% parameters
    SQL> alter system set db_create_online_log_dest_1='/ora_log/redolog1';
    
    System został zmieniony.
    
    SQL> alter system set db_create_online_log_dest_2='/ora_log/redolog2';
    
    System został zmieniony.
    
    SQL> show parameter db_create
    
    NAME                                 TYPE         VALUE
    ------------------------------------ ------------ ------------------------------
    db_create_file_dest                  string       /ora_db/
    db_create_online_log_dest_1          string       /ora_log/redolog1
    db_create_online_log_dest_2          string       /ora_log/redolog2
    db_create_online_log_dest_3          string
    db_create_online_log_dest_4          string
    db_create_online_log_dest_5          string
    
    
  3. (optionally) here one may create a standby controlfile on PRIMARY and copy to planned standby, then mount
    ALTER DATABASE CREATE STANDBY CONTROLFILE 
      AS '/disk1/oracle/oradata/payroll/standby/payroll2.ctl';
    
  4. or directly restore from backup (which is much more comfortable)
    connect target sys/pass1;
    connect catalog rman_catalog/pass2@rman_catalog;
    spool log to rman_db_restore_20160505.log
    run
    {
    allocate channel t1 type 'SBT_TAPE' FORMAT 'db_%t_%s_%p' parms 
      'ENV=(TDPO_OPTFILE=/TSM/CFG/tdpo.opt)';
    allocate channel t2 type 'SBT_TAPE' FORMAT 'db_%t_%s_%p' parms 
      'ENV=(TDPO_OPTFILE=/TSM/CFG/tdpo.opt)';
    # in case one restored controlfiles already - next 2 lines has to be removed
    restore standby controlfile; 
    alter database mount;
    SET NEWNAME FOR DATABASE TO NEW;
    restore database;
    SWITCH DATAFILE ALL;
    SWITCH TEMPFILE ALL;
    recover database DELETE ARCHIVELOG MAXSIZE 90G;
    
    release channel t1;
    release channel t2;
    }
    exit;
    

Now it is possible we have to add more archivelogs, but that would mean repeating the last line from the script above, until db is fine. Then one may want to fix some issues like paths to online and standby redo log files, etc.
From this time on the standby is ready to use - the main point at this moment is to restore the managed standby database recovery process. This task would require first checking the parameters like log_archive_config, log_archive_dest_* and log_archive_dest_state_*, fal_server and fal_client if they are properly configured. Of course in the first place the communication between primary and standby db nodes has to be checked by sqlplus for example.
Now we call
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
The output in alert logs on both nodes should confirm that the connections were built and the transfer of archivelogs has started.

Good to know

There exists bug 7553431 - RMAN restore controlfile for standby does not create correct controlfile type (Doc ID 7553431.8), which influences the role of restored database, fixed in 12c version.

No comments: