Oracle 12c-Step by Step Manual Switchover Data Guard

 Switchover:

          It is reversal of role between a standby database and a primary database. The primary database assumes role of standby database and the standby database assumes role of primary database after the switchover. There is no data loss in a switchover. This is generally a planned activity.

Step 1:

 ON Primary

 Select switchover_status from v$database..

 Step 2 :

 Check that there is no active users connected to the databases.

 Select  distinct ousers,username from v$session

 Step 3:

 Switch the current online redo log file on primary database and verify that it has been applied.

 SQL>alter system switch logfile

 Step  4 :

 Connect with primary database and initiate the switchover.

 SQL>alter database commit to switchover to physical standby;

 Now, the primary database is converted into standby database.The controlfile is backed up to the current SQL session trace file before the switchover. This makes it possible to reconstruct a current control file,if necessary.

 If we try to perform a switchover when other instances are running then we will get ORA-01105 as follows :

SQL>alter database commit to switchover to physical standby with Session shutdown;

 The above statement first terminates all active sessions by closing the primary database. Then any non-archived redo log files are transmitted and applied to standby database. Apart from that an end-of-redo marker is added to the header of the last log file that was archived.A backup of current control file is created and the current control file is converted into a standby control file.

 Step  5 : 

Shut down and restart the  primary instance(RED).

SQL>Shut immediate; 

SQL> Startup mount;

Step  6 : 

Verify the switchover status in the v$database view.

After we change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, we should verify if the switchover notification was processed by the target standby database by querying the "  column of the v$database fixed view on the target standby database.

On Primary Database(prim)

SQL> select name,open_mode,db_unique_name,switchover_status from v$database.

NAME      OPEN_MODE       DB_UNIQUE_NAME         SWITCHOVER_STATUS
------       -----------          --------------------         ----------------------
PRIM     MOUNTED                prim                    TO PRIMARY

On Old Standby Database(PRIM)
SQL> select name,open_mode,db_unique_name,switchover_status from v$database.

Important Notes :

The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request

Flashback Query & Version Query Concepts On Oracle 12c

 1)Flashback Query:(To retrieve the data from past existing Table not current data)(Don’t Drop the Table).

 Flashback Query allows the contents of a table to be queried with reference to a specific point in time,  using the AS OF clause.

 [oracle@trichy ~]$ export ORACLE_SID=test

[oracle@trichy ~]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@trichy ~]$ export ORACLE_BASE=/u01/app/oracle

[oracle@trichy ~]$ export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1

[oracle@trichy ~]$ sqlplus / as sysdba

 SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 10 00:00:43 2020

 Copyright (c) 1982, 2016, Oracle.  All rights reserved.

 Connected to an idle instance.

 SQL> startup

ORACLE instance started.

 

Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

Database opened.

 SQL> create table emp(id int);

 Table created.

 SQL> SELECT current_scn,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

    6352947 2020-10-10 00:03:43

 SQL> insert into emp values(111);

 1 row created.

 SQL> /

 1 row created.

 SQL> /

 1 row created.

 SQL> /

 1 row created.

 SQL> commit;

 Commit complete.

 SQL> SELECT current_scn,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

    6353013 2020-10-10 00:06:26

 SQL> delete from emp;

 4 rows deleted.

 SQL> commit;

 Commit complete.

 SQL> SELECT COUNT(*) FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2020-10-10 00:03:43','YYYY-MM-DD HH24:MI:SS');

   COUNT(*)

----------

 0

 SQL> select * from emp;

 no rows selected

 SQL> SELECT COUNT(*) FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2020-10-10 00:06:26','YYYY-MM-DD HH24:MI:SS');

  COUNT(*)

----------

 4

 SQL> SELECT * FROM emp as of scn 6352947;

 no rows selected

 SQL> SELECT * FROM emp as of scn 6353013;       

 ID

----------

       111

       111

       111

       111

       Screen shots                 

                                                 

2)Flashback Version Query

 Flashback version query allows the versions of a specific row to be tracked during a specific time period using the VERSIONS BETWEEN clause.

 SQL> CREATE TABLE emp1 (id  NUMBER(10),name  VARCHAR2(50));

 Table created.

 SQL> INSERT INTO emp1 (id, name) VALUES (1, 'dinesh');

 1 row created.

 SQL> commit;

 Commit complete.

 SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

    6356132 2020-10-10 00:41:48

 SQL> UPDATE emp1 SET name = 'raja' WHERE id = 1;

 1 row updated.

 SQL> COMMIT;

 Commit complete.

 SQL> UPDATE emp1 SET name = 'senthil' WHERE id = 1;

 1 row updated.

 SQL> COMMIT;

 Commit complete.

 SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

    6356817 2020-10-10 00:43:23

 SQL> COLUMN versions_startscn FORMAT 99999999999999999

COLUMN versions_starttime FORMAT A24

COLUMN versions_endscn FORMAT 99999999999999999

COLUMN versions_endtime FORMAT A24

COLUMN versions_xid FORMAT A16

COLUMN versions_operation FORMAT A1

COLUMN description FORMAT A11

SET LINESIZE 200

SELECT versions_startscn, versions_starttime,

versions_endscn, versions_endtime,

versions_xid, versions_operation,

name  

FROM  emp1

VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2020-10-10 00:41:48', 'YYYY-MM-DD HH24:MI:SS')

AND TO_TIMESTAMP('2020-10-10 00:43:23', 'YYYY-MM-DD HH24:MI:SS')

WHERE  id = 1;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8  

 VERSIONS_STARTSCN VERSIONS_STARTTIME        VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID  V NAME

------------------ ------------------------ ------------------ ------------------------ ---------------- - --------------------------------------------------

   6356809 10-OCT-20 12.43.07 AM 0600020004160000 U senthil

   6356801 10-OCT-20 12.42.45 AM        6356809 10-OCT-20 12.43.07 AM 090004000D160000 U raja

   6356130 10-OCT-20 12.41.43 AM        6356801 10-OCT-20 12.42.45 AM 0200070028160000 I dinesh 

Screen shots

                                                             

 

 

Create Physical Standby Database in Data Gaurd Using RMAN Backup on Oracle 12C

 Primary Database - Dhee

Standby Database - Dheedr

 

STEP 1;

Check the Primary DB is in archive log mode or not.If it is not then enable it

Sql>starup mount

Sql>alter database archivelog

Sql>archive log list

 

STEP 2:

Enable force logging in the Primary Database.

Sql>alter database force logging;

  Check it by

  Select force_logging from v$database;

 

STEP 3 :

 Verify database initialization parameters db_name and db_unique_name on primary

 Show parameter db_name

 Show parameter db_unique_name

 

STEP 4:

 Set the Log_archive_config for datagaurd configuration.

 Sql>alter system set log_archive_config=’DG_config=(dhee,dheedr)’;

 Then

Show parameter log_archive_config

 

STEP 5:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=dheedr NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dheedr';

 Show parameter log_archive_dest_2

 Alter system set log_archive_dest_2=enable;

 Show parameter log_archive_dest_state_2.

 

STEP 6:

 alter system set log_archive_format='%t_%s_%r.arc' scope=spfile

 Show parameter log_archive_format.

 

STEP 7:

 Set log_Archive_max_processes=30

 Sql>alter system set log_archive_max_processes=30;

 Show parameter log_archive_max_processes

 

STEP 8:

 Set remote_login_passwordfile to exclusive

 Sql>alter system set remote_login_passwordfile=exclusive;

 Show parameter remote_login_passwordfile

 

STEP 9:

 Set fal_server and fal_client for the primary database.

 Sql>alter system set fal_server=dheedr

 Sql>alter system set fal_client=dhee

 

STEP 10:

 Set standby_file_management to auto

 Sql>alter system set standby_file_management = auto;

 Show parameter standby_file_management

 

STEP 11:

 Configure the listener and tns entries in both the servers

 Listener.ora:

 SID_LIST_LISTENER =

 (SID_LIST =

 (SID_DESC =

 (GLOBAL_DBNAME = dhee)

 (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)

 (SID_NAME = dhee)

 ) )

 LISTENER =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = dheedr)(PORT = 1521))

 )

 ADR_BASE_LISTENER = /u01/app/oracle

 

STEP 12:

 Tnsnames.ora:

 dhee =

 (DESCRIPTION =

 (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)(HOST = dhee)(PORT = 1521))

 )

 (CONNECT_DATA =

 (SERVICE_NAME = dhee)

 (GLOBAL_NAME = dhee)

 (UR=A)

 ) )

 dheedr =

 (DESCRIPTION =

 (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)(HOST =dheedr)(PORT = 1521))

 )

 (CONNECT_DATA =

 (SERVICE_NAME = dhee)

 (GLOBAL_NAME = dhee)

 (UR=A)

 ) )

 

STEP 13:

 Check the tnsping utility on Primary and Standby.

 Tnsping dhee

 Tnsping dheedr

 

STEP 14:

 Backup the primary database via RMAN utility.

 RMAN>backup database plus archivelog;

 

STEP 15:

 Create the standby control file on the primary database.

 RMAN>alter database create standby controlfil  as ‘$ORACLE_HOME/dbs’

 Create the passwordfile on the primary database.

 Orapwd file=’$ORACLE_HOME/dbs/orapwdhee Password=siva@1998

 Entries=10.

 Create pfile from spfile.

 Create pfile=’$ORACLE_HOME/dbs/initdhee.ora’ from spfile

 

 STEP 16:

 Move the files like standby controlfile,passwordfile & backup pieces to the standby database through scp connection.

 

STEP 17:

 Connect the database using the pfile by changing some parameters:

 *.db_unique_name='dheedr'

 *.fal_server='dhee'

 *.log_archive_dest_2='SERVICE=dhee ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dhee'

 

STEP 18:

 Take the database to nomount stage.

 Sql>startup nomount pfile=’$ORACLE_HOME/dbs/initdhee.ora’

 Take the database to mount stage.

 RMAN>restore controlfile from ‘$ORACLE_HOME/dbs/standbyctl.ctl’

 

STEP 19:

 Sql>alter database mount;

 

STEP 20:

 Create the Standby database

 RMAN>duplicate database ‘dhee’ for standby backup location ‘$ORACLE_HOME/dbs/’;

Apply the redo logs from the primary to standby database.

 

STEP 21:

                                                   

Configure a Physical Standby Database using Active Data Gaurd in oracle

Primary Database - Prim

      Standby Database - Primdr

 

STEP 1:

 Check the Primary DB is in archive log mode or not.If it is not then enable it.

 Sql>starup mount

 Sql>alter database archivelog

 Sql>archive log list

 

STEP 2:

 Enable force logging in the Primary Database.

 Sql>alter database force logging;

 Check it by

       Select force_logging from v$database;

 

STEP 3:

 Add the Standby redolog files on the primary database.

 Sql>Alter database add standby logfile thread 1 group 4(‘/u01/app/oracle/oradata/redo04.log’) size 50M;

 Alter database add standby logfile thread 1 group 5(‘/u01/app/oracle/oradata/redo05.log’) size 50M;

 Alter database add standby logfile thread 1 group 6(‘/u01/app/oracle/oradata/redo06.log’) size 50M;

 Alter database add standby logfile thread 1 group 7(‘/u01/app/oracle/oradata/redo07.log’) size 50M;

 

STEP 4:

 Verify database initialization parameters db_name and db_unique_name on primary

 Show parameter db_name

 Show parameter db_unique_name

 

STEP 5:

 Set the Log_archive_config for datagaurd configuration.

 Sql>alter system set log_archive_config=’DG_config=(prim,primdr)’;

 Then

 Show parameter log_archive_config

 

STEP 6:

 

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=primdr NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primdr';

 

Show parameter log_archive_dest_2

 Alter system set log_archive_dest_2=enable;

 Show parameter log_archive_dest_state_2.

 

STEP 7:

 alter system set log_archive_format='%t_%s_%r.arc' scope=spfile

 Show parameter log_archive_format.

 

STEP 8:

 Set log_Archive_max_processes=30

 Sql>alter system set log_archive_max_processes=30;

 Show parameter log_archive_max_processes

 

STEP 9:

 Set remote_login_passwordfile to exclusive

 Sql>alter system set remote_login_passwordfile=exclusive;

 Show parameter remote_login_passwordfile

 

STEP 10:

 Set fal_server and fal_client for the primary database.

 Sql>alter system set fal_server=primdr

 Sql>alter system set fal_client=prim

 

STEP 11:

 Set standby_file_management to auto

 Sql>alter system set standby_file_management = auto;

 Show parameter standby_file_management

 

STEP 12:

 Configure tns entry in the primary database.

  

Configure the listener in the Primary database.

 

STEP 13:

 Configure the tns entry in the Standby database.

                                                            

Configure the listener in the Standby database.

 

STEP 14:

 Check the tnsping utility on Primary and Standby.

 Tnsping prim

 Tnsping primdr

 

STEP 15:

 Create the Password file on the primary database and send it to standby database.

 

STEP 16:

 Create the pfile from spfile on the primary database and send it to the Standby database.

 

STEP 17:

 Using the pfile take the database at nomount stage.

                                                    

STEP 18:

 Connect the target and axiliary database using RMAN utility.

                                                                  

STEP 19:

 Once finished the duplicate,mount the database

 Sql>alter database mount;

 And then apply log files using MRP

 Sql>alter database recover managed standby database using current logfile disconnect from session;

 

STEP 20:

 Shutdown the database.

 Startup mount

 Alter database read only.

                                                  

STEP 21:

 Check the current log sequence on both side.

                                                              

                                                 

STEP 22:

 You can use the Primdr database as read only.