Examples of oracle12c DataGuard switchover

12c Dataguard provides a new syntax for implementing DG Switchover. Below is the test.

Master Library Execution Check

Sql> ALTER DATABASE switchover to Orcldg VERIFY; --Main library for switch check
ALTER DATABASE switchover to Orcldg VERIFY
*
ERROR at line 1:
ora-16475:succeeded with warnings, check alert log for more details--View alert logs


Sql>
Alert

ALTER DATABASE switchover to orcldg VERIFY--command input
Sun May 10 18:31:29 2015
This is cascading configuration.
Log_archive_dest_3 has already been configured to switchover target ORCLDG. Clearing log_archive_dest_3.--empty Parameters dest-3
Using standby_archive_dest parameter Default value As/backup/archivelog
Sun May 10 18:31:29 2015
ALTER SYSTEM SET log_archive_dest_3= ' scope=memory sid= ' *;
Log_archive_dest3 is cleared.
Configuring a new Log_archive_dest to switchover target ORCLDG.
Using standby_archive_dest parameter Default value As/backup/archivelog
Sun May 10 18:31:29 2015
ALTER SYSTEM SET log_archive_dest_31= ' service=orcldg ASYNC db_unique_name=orcldg ' scope=memory ' * ';
Switchover Verify:send VERIFY request to switchover target ORCLDG
Switchover VERIFY COMPLETE
Switchover VERIFY Warning:switchover target temporary files are not the same with the primary.     Switchover target ' s alert log for details. There are no temporary files on the--DG side, causing the above SQL execution to be problematic, only for testing functionality. Here is ignored
Using standby_archive_dest parameter Default value As/backup/archivelog
Sun May 10 18:31:30 2015
ALTER SYSTEM SET log_archive_dest_31= ' scope=memory sid= ' *;
ORA-16475 signalled During:alter DATABASE switchover to Orcldg VERIFY ...
Sun May 10 18:31:32 2015
Lgwr:standby Redo logfile selected to archive thread 1 sequence 116
Lgwr:standby Redo logfile selected for thread 1 sequence 116 for destination
Sun May 10 18:31:32 2015
Changing destination 3 from the remote to the local during archival of log#: 1 sequence#: the thread#: 1
Sun May 10 18:31:32 2015
Thread 1 advanced to log sequence 116 (LGWR switch)
Current log# 2 seq# 116 mem# 0:/oradata/orcl/redo02.log
Sun May 10 18:31:32 2015
Archived Log entry added for thread 1 sequence 0x53634e8b dest 1:
Switch

Sql> ALTER DATABASE switchover to ORCLDG; --Formal switching

DATABASE altered.
Master Library Log

ALTER DATABASE switchover to ORCLDG-Accept switch command
Sun May 10 18:33:19 2015
This is cascading configuration.
Sun May 10 18:33:19 2015
ALTER SYSTEM SET log_archive_dest_state_2= ' DEFER ' scope=memory sid= ' * '; --Modify Dest_2
Configuring a new Log_archive_dest to switchover target ORCLDG.
Using standby_archive_dest parameter Default value As/backup/archivelog
Sun May 10 18:33:19 2015
ALTER SYSTEM set log_archive_dest_31= ' service=orcldg ASYNC db_unique_name=orcldg ' scope=memory ' * ';--Set parameters
Starting switchover [Process id:2105]
Sun May 10 18:33:20 2015
ALTER DATABASE COMMIT to switchover to physical STANDBY [Process id:2105] (ORCL)--The original command switches to physical STANDBY
Waiting for target standby to receive all redo
Sun May 10 18:33:20 2015
Waiting for all non-current orls to is archived ...
Sun May 10 18:33:20 2015
All non-current orls have been archived.
Sun May 10 18:33:20 2015
Waiting for all FAL entries to is archived ...
Sun May 10 18:33:20 2015
All FAL entries have been archived.
Sun May 10 18:33:20 2015
Waiting for dest_id to become synchronized ...
Sun May 10 18:33:20 2015
Thread 1 cannot allocate new log, sequence 117
Checkpoint not complete
Current log# 2 seq# 116 mem# 0:/oradata/orcl/redo02.log
Sun May 10 18:33:21 2015
Active, synchronized physical Standby switchover target has been
Preventing updates and queries at the Primary
Generating and shipping final logs to target standby
Switchover End-of-redo Log thread 1 sequence 116 has fixed
Switchover:primary highest seen SCN set to 0x0.0x2b49d1 >> toggle SCN
Arch:noswitch Archival of thread 1, sequence 116
Arch:end-of-redo Branch Archival of thread 1 sequence 116
ARCH:LGWR is scheduled to archive destination log_archive_dest_31 after LOG switch
ARCH:LGWR is actively archiving destination log_archive_dest_2
Sun May 10 18:33:23 2015
Process (Ospid 2077) is suspended due to switchover to physical standby.
Sun May 10 18:33:25 2015
Arch:standby Redo logfile selected for thread 1 sequence 116 for destination
Arch:standby Redo logfile selected for thread 1 sequence 116 for destination
Arch:archiving is disabled due to current logfile archival
Primary'll check for some target standby to have received all redo
Waiting for target standby to apply all redo
Backup controlfile written to trace file/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2105.trc
Converting the primary database to a new standby database >> switch to DG
Clearing standby activation ID 1399017099 (0x53634e8b)
The primary database Controlfile was created using the
' Maxlogfiles ' clause.
There is spaces for up to standby redo LogFiles
Use the following SQL commands in the standby database to create
Standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE ' srl1.f ' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ' srl2.f ' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ' srl3.f ' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ' srl4.f ' SIZE 52428800;
Archivelog for thread 1 sequence 116 required for standby recovery
Offline data file 5 marked as online during convert to standby or switchover to standby.
Restore of backup may required if the ' file is ' not ' physically accessible.
Offline data file 7 marked as online during convert to standby or switchover to standby.
Restore of backup may required if the ' file is ' not ' physically accessible.
Offline data file marked as online during convert to standby or switchover to standby.
Restore of backup may required if the ' file is ' not ' physically accessible.
Offline data file marked as online during convert to standby or switchover to standby.
Restore of backup may required if the ' file is ' not ' physically accessible.
Offline data file marked as online during convert to standby or switchover to standby.
Restore of backup may required if the ' file is ' not ' physically accessible.
Offline data file marked as online during convert to standby or switchover to standby.
Restore of backup may required if the ' file is ' not ' physically accessible.
Offline data file marked as online during convert to standby or switchover to standby.
Restore of backup may required if the ' file is ' not ' physically accessible.
Offline data file marked as online during convert to standby or switchover to standby.
Restore of backup may required if the ' file is ' not ' physically accessible.
Switchover:primary Controlfile converted to standby controlfile succesfully. >> Control File Switching success
Switchover:complete-database shutdown required >> instance shutdown
Sending request (convert to primary database) to switchover target orcldg >> Send command to DG END
Switchover complete. Database shutdown required
USER (ospid:2105): terminating the instance
Sun May 10 18:33:29 2015
Instance terminated by USER, PID = 2105
Completed:alter DATABASE switchover to Orcldg
Shutting down instance (abort)
License High Water mark = 17
Sun May 10 18:33:29 2015
Instance shutdown complete
Far Sync Transfer Log

Sun May 10 18:33:25 2015
Changing standby controlfile to MAXIMUM performance mode
RFS[3]: Assigned to RFS process (pid:2153)
RFS[3]: Selected log 4 for thread 1 sequence 116 dbid 1399018635 branch 871237903
Sun May 10 18:33:25 2015
Archived Log entry added for thread 1 sequence 116 ID 0x53634e8b dest 1:
Sun May 10 18:33:29 2015
RFS[4]: Assigned to RFS process (pid:2131)
RFS[4]: Possible network disconnect with primary database
Sun May 10 18:33:29 2015
RFS[2]: Possible network disconnect with primary database
Dataguard End Alert Log

Sun May 10 18:33:26 2015
switchover:received Request ' ALTER dtabase COMMIT to switchover to PRIMARY ' from PRIMARY database. >> Accept the Order
Sun May 10 18:33:26 2015
ALTER DATABASE switchover to PRIMARY (ORCL) >> switch
Maximum wait for role transition is minutes. >> wait for up to 15 minutes
Switchover:media recovery is still active >> discovery in progress recover
Role change:canceling mrp-no more redo to apply >> off MRP
Sun May 10 18:33:26 2015
Mrp0:background Media Recovery cancelled with status 16037
Sun May 10 18:33:26 2015
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/ORCLDG/ORCL/TRACE/ORCL_MRP0_2134.TRC:
Ora-16037:user requested cancel of managed recovery operation
Managed Standby Recovery not using real time Apply
Recovery interrupted! >>recover was suspended.
Sun May 10 18:33:26 2015
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/ORCLDG/ORCL/TRACE/ORCL_MRP0_2134.TRC:
Ora-16037:user requested cancel of managed recovery operation
Sun May 10 18:33:26 2015
Mrp0:background Media Recovery process shutdown (ORCL)
Sun May 10 18:33:27 2015
Role change:canceled MRP >> off MRP
Killing 5 processes (pids:2186,2148,2146,2168,2170) (all RFS) in the order to disallow the current and future RFS. Requested by OS process 2197 >>kill RPS processes
Stopping Emon Pool
All dispatchers and shared servers shutdown
Close:killing server sessions.
Close:all Sessions shutdown successfully.
Stopping Emon Pool
Sun May 10 18:33:29 2015
Smon:disabling Cache Recovery
Sun May 10 18:33:29 2015
Backup controlfile written to trace File/u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_rmi_2197.trc >> Backing up control files
Switchover after complete recovery through change 2836945
Online logfile pre-clearing operation disabled by switchover >> empty redo
Online Log/oradata/orcl/redo01.log:thread 1 Group 1 was previously cleared
Online Log/oradata/orcl/redo02.log:thread 1 Group 2 was previously cleared
Online Log/oradata/orcl/redo03.log:thread 1 Group 3 was previously cleared
Standby became primary scn:2836943 >> become the SCN of the main library
Audit_trail initialization parameter is changed back to it original value as specified in the parameter file.
Switchover:complete-database Mounted as Primary
Switchover:completed request from primary database. >> Switch Success
Sun May 10 18:33:44 2015
Arc1:becoming the ' no SRL ' ARCH

Sun May 10 18:36:19 2015
assigning activation ID 1407090153 (0X53DE7DE9)--Start the database open
Lgwr:primary database is in MAXIMUM availability mode
Sun May 10 18:36:19 2015
Destination Log_archive_dest_2 is unsynchronized
Lgwr:destination log_archive_dest_1 is isn't serviced by LGWR
Sun May 10 18:36:19 2015
Thread 1 advanced to log sequence 118 (thread open)
Thread 1 opened at log sequence 118
Current log# 2 seq# 118 mem# 0:/oradata/orcl/redo02.log
Successful open of Redo thread 1
Sun May 10 18:36:19 2015
MTTR advisory is disabled because fast_start_mttr_target are not set
Sun May 10 18:36:19 2015
Archived Log entry added for thread 1 sequence 117 ID 0x53de7de9 dest 1:
Sun May 10 18:36:19 2015
Smon:enabling Cache Recovery
Sun May 10 18:36:19 2015
[2219] successfully onlined Undo tablespace 2.
Undo initialization finished serial:0 start:525504 end:525654 diff:150 ms (0.2 seconds)
Dictionary Check Beginning
Dictionary Check Complete
Verifying minimum file header compatibility (11g) for tablespace encryption.
Verifying 11g file Header compatibility for tablespace encryption completed
Sun May 10 18:36:19 2015
smon:enabling TX Recovery
Starting background Process SMCO
Sun May 10 18:36:19 2015
Database Characterset is ZHS16GBK
Sun May 10 18:36:19 2015
SMCO started with pid=32, OS id=2226
No Resource Manager Plan Active
Starting background Process IMCO
Sun May 10 18:36:19 2015
IMCO started with pid=35, OS id=2232
Starting background Process AQPC
Sun May 10 18:36:20 2015
AQPC started with pid=36, OS id=2234
Logstdby:validating Controlfile with logical metadata
Logstdby:validation Complete
Sun May 10 18:36:20 2015
Arc1:starting ARCH Processes
Starting background Process ARC4
Sun May 10 18:36:20 2015
ARC4 started with pid=37, OS id=2236
Arc4:archival started
Arc1:starting ARCH Processes COMPLETE
Sun May 10 18:36:21 2015
Pluggable Database Pdb$seed dictionary check beginning
Pluggable Database pdb$seed Dictionary check Complete
Database Characterset for Pdb$seed is ZHS16GBK
Sun May 10 18:36:21 2015
******************************************************************
Lgwr:setting ' active ' Archival for destination log_archive_dest_2
******************************************************************
Lgwr:standby Redo logfile selected to archive thread 1 sequence 119
Lgwr:standby Redo logfile selected for thread 1 sequence 119 for destination
Sun May 10 18:36:22 2015
Thread 1 advanced to log sequence 119 (LGWR switch)
Current log# 3 seq# 119 mem# 0:/oradata/orcl/redo03.log
Shutting down archive processes
Sun May 10 18:36:22 2015
ARCH shutting down
Arc4:archival stopped
Sun May 10 18:36:22 2015
Due to limited spaces in shared pool (need 6094848 bytes, have 3981120), bytes limiting Manager Resource from 2048 to 32
Opening pdb Pdb$seed (2) with no Resource Manager plan active
Sun May 10 18:36:23 2015
Archived Log entry added for thread 1 sequence 118 ID 0x53de7de9 dest 1:
Sun May 10 18:36:23 2015
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/ORCLDG/ORCL/TRACE/ORCL_DBW0_2044.TRC:
Ora-01157:cannot identify/lock data file 203-see DBWR trace file
Ora-01110:data file 203: '/ORADATA/ORCL/TRAVEL/TRAVEL_TEMP012015-03-19_01-30-29-PM.DBF '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Sun May 10 18:36:23 2015
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/ORCLDG/ORCL/TRACE/ORCL_DBW0_2044.TRC:
Ora-01186:file 203 Failed verification tests
Ora-01157:cannot identify/lock data file 203-see DBWR trace file
Ora-01110:data file 203: '/ORADATA/ORCL/TRAVEL/TRAVEL_TEMP012015-03-19_01-30-29-PM.DBF '
Sun May 10 18:36:23 2015
File 203 not verified due to error ORA-01157
Sun May 10 18:36:23 2015
Arc0:standby Redo logfile selected for thread 1 sequence 118 for destination
Sun May 10 18:36:23 2015
Pluggable Database TRAVEL dictionary check beginning
Sun May 10 18:36:23 2015
Pluggable Database PDB03 dictionary check beginning
Pluggable Database PDB03 Dictionary check Complete
Sun May 10 18:36:23 2015
Pluggable Database TRAVEL Dictionary check Complete
Sun May 10 18:36:23 2015
Database Characterset for PDB03 is ZHS16GBK
Sun May 10 18:36:23 2015
Database Characterset for TRAVEL is ZHS16GBK
Sun May 10 18:36:23 2015
--there is no problem with the temp table space
Warning:the following temporary tablespaces in container (PDB03)
contain no files.
This condition can occur when a backup controlfile has
been restored. It may is necessary to add files to
Tablespaces. That can is done using the SQL statement:

ALTER tablespace <tablespace_name> ADD tempfile

Alternatively, if these temporary tablespaces are no longer
Needed, then they can be dropped.
Empty Temporary Tablespace:temp
*********************************************************************
Sun May 10 18:36:24 2015
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/ORCLDG/ORCL/TRACE/ORCL_DBW0_2044.TRC:
Ora-01157:cannot identify/lock data file 203-see DBWR trace file
Ora-01110:data file 203: '/ORADATA/ORCL/TRAVEL/TRAVEL_TEMP012015-03-19_01-30-29-PM.DBF '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Sun May 10 18:36:24 2015
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/ORCLDG/ORCL/TRACE/ORCL_DBW0_2044.TRC:
Ora-01186:file 203 Failed verification tests
Ora-01157:cannot identify/lock data file 203-see DBWR trace file
Ora-01110:data file 203: '/ORADATA/ORCL/TRAVEL/TRAVEL_TEMP012015-03-19_01-30-29-PM.DBF '
Sun May 10 18:36:24 2015
File 203 not verified due to error ORA-01157
Sun May 10 18:36:24 2015
Re-creating TEMPFILE/ORADATA/ORCL/TRAVEL/TRAVEL_TEMP012015-03-19_01-30-29-PM.DBF
ALTER system:flushing buffer Cache inst=0 container=3 Local
Sun May 10 18:36:24 2015
ALTER system:flushing buffer Cache inst=0 container=4 Local
Sun May 10 18:36:25 2015
Destination Log_archive_dest_2 is SYNCHRONIZED
Lgwr:standby Redo logfile selected to archive thread 1 sequence 120
Lgwr:standby Redo logfile selected for thread 1 sequence a for destination log_archive_dest_2
Sun May 10 18:36:25 2015
Thread 1 advanced to log sequence (LGWR switch)
Current log# 1 seq# mem# 0:/oradata/orcl/redo01.log
Sun May 10 18:36:25 2015
Archived Log entry added for thread 1 sequence 119 ID 0x53de7de9 dest 1:
Sun May 10 18:36:25 2015
Cannot start service travel, Reason=-1
Cannot start service pdb03, Reason=-1
Starting background Process CJQ0
Completed:alter Database Open
Sun May 10 18:36:33 2015
CJQ0 started with pid=40, OS id=2289
View the new Home library

Sql> SELECT Name,database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status, SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from V$database;

NAME database_role db_unique_name open_mode protection_mode protection_level S Witchover_status sup sup
--------- ---------------- ------------------------------ -------------------- -------------------- ---------------- ---- -------------------- --- ---
ORCL PRIMARY ORCLDG mounted MAXIMUM availability unprotected N OT allowed no No

Sql> SELECT * from V$dataguard_config;

Db_unique_name Parent_dbun dest_role CURRENT_SCN con_id
------------------------------ ------------------------------ ----------------- ----------- ----------
Orcldg NONE PRIMARY DATABASE 0 0
ORCL orcldg UNKNOWN 0 0
Orclfs UNKNOWN UNKNOWN 0 0

Sql> Show Parameter Dest_2

Parameter_name TYPE VALUE
------------------------------------------------------------ ----------- ---------------------------------------- ------------------------------------------------------------
Db_create_online_log_dest_2 string
Log_archive_dest_2 string SERVICE=ORCL lgwr SYNC affirm valid_for= (online_l Ogfiles,primary_role) DB_UNIQUE_NAME=ORCL
Log_archive_dest_20 string
Log_archive_dest_21 string
Log_archive_dest_22 string
Log_archive_dest_23 string
Log_archive_dest_24 string
Log_archive_dest_25 string
Log_archive_dest_26 string
log_archive_dest_27 string
Log_archive_dest_28 string
log_archive_dest_29 string
sql> ALTER DATABASE OPEN;

DATABASE altered.

Sql>
Sql>
Sql>