Perform the Switchover in our Test DB ENV

-
Perform the Switchover in our Test DB ENV

Primary database - IP (10.0.0.1)

=====================================
Before Switchover from primary to standby
=====================================

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PR        READ WRITE           PRIMARY

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
pr

SQL> select process from v$managed_standby;

PROCESS
---------
ARCH
LNS

[oracle@pr ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/xxx
Connected

DGMGRL> show configuration
Configuration - PRDG
  Protection Mode: MaxPerformance
  Databases:
    pr  - Primary database
    std - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

=======================================
Now convert the Primary Database to Standby
=======================================

[oracle@pr ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/xxx
Connected

DGMGRL> switchover to std

Performing switchover NOW, please wait...
Operation requires a connection to instance "std" on database "std"
Connecting to instance "std"...
Connected.
New primary database "std" is opening...
Operation requires startup of instance "pr" on database "pr"
Starting instance "pr"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.   -- This we can resolve & start the primary db as mount automatically (Working to resolve)
Please complete the following steps to finish switchover: 
        start up and mount instance "pr" of database "pr"

Note: Other mrp process will be started automatically

======================================
After Converting the Primary database to Standby – Verify Sync, config
======================================

In Primary Database converted as standby database – IP (10.0.0.1)

SQL> startup mount
ORACLE instance started.

Total System Global Area 1503199232 bytes
Fixed Size                  2253424 bytes
Variable Size            1392512400 bytes
Database Buffers          100663296 bytes
Redo Buffers                7770112 bytes
Database mounted.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PR        MOUNTED              PHYSICAL STANDBY

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
pr

SQL> select process from v$managed_standby;

PROCESS
---------
ARCH
ARCH
FS
RFS
MRP0

=============================
Now – Verify the Standby database
=============================

Here Standby Database converted as primary database - Successfully Perform the Switchover & Open the database as “OPEN” Mode

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PR        READ WRITE           PRIMARY

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
std

SQL>  select process from v$managed_standby;
PROCESS
---------
ARCH
LGWR

SQL> alter system switch logfile;
System altered.

SQL> SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,(SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

   THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_   ARC_DIFF
---------- ---------- ----------- --------- ----------
         1       1245        1243 30-MAY-17          2

SQL> SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,(SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

   THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_   ARC_DIFF
---------- ---------- ----------- --------- ----------
         1       1245        1244 30-MAY-17          1

Hence the MRP Process is started & shipped the archivelog  etc – Therefore the Primary DB as Standby DB & Standby DB as Primary DB


====================================
Before Switchover from Standby to Primary – Perform the Vice Versa (Standby DB as Standby & Primary DB as Primary)
====================================

In Standby DB

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PR        READ WRITE           PRIMARY

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
std

SQL> select process from v$managed_standby;

PROCESS
---------
ARCH
LGWR

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@std ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/sys
Connected.

DGMGRL> show configuration
Configuration - PRDG
  Protection Mode: MaxPerformance
  Databases:
    std - Primary database
    pr  - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

======================================================================
Now – Revert backup the DB as before – Primary to Primary & Standby to Standby
======================================================================

[oracle@std ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/xxx
Connected.

DGMGRL> switchover to pr
Performing switchover NOW, please wait...
Operation requires a connection to instance "pr" on database "pr"
Connecting to instance "pr"...
Connected.
New primary database "pr" is opening...
Operation requires startup of instance "std" on database "std"
Starting instance "std"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:

Due to the above error – Just mount the Standby DB – Other mrp process will be started automatically

SQL> startup mount
ORACLE instance started.

Total System Global Area 1503199232 bytes
Fixed Size                  2253424 bytes
Variable Size            1392512400 bytes
Database Buffers          100663296 bytes
Redo Buffers                7770112 bytes
Database mounted.
SQL>
SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PR        MOUNTED              PHYSICAL STANDBY

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
std

SQL> select process from v$managed_standby;

PROCESS
---------
ARCH
RFS
MRP0

DGMGRL> show configuration
Configuration - PRDG
  Protection Mode: MaxPerformance
  Databases:
    pr  - Primary database
    std - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

Switch some archive from the primary database & verify

SQL> alter system switch logfile;
System altered.

SQL> @/home/oracle/scripts/pritest.sql;

   THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_   ARC_DIFF
---------- ---------- ----------- --------- ----------
         1       1253        1246 30-MAY-17          7

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@pr ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue May 30 10:22:28 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/home/oracle/scripts/pritest.sql;

   THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_   ARC_DIFF
---------- ---------- ----------- --------- ----------
         1       1253        1252 30-MAY-17          1


Hence the MRP Process is started & shipped the archivelog  etc – Therefore revert back to the original stage Primary DB  as Primary DB & Standby DB as Standby DB




If there is any clarification, Please revert me back 24/7 - E-mail: sayeed202@gmail.com

Comments

Post a Comment

Popular posts from this blog

RAC Failure Scenario - Testing in our RAC Test DB - Node 1 & Node 2

-
RAC Failure Scenario - Testing in our RAC Test DB - Node 1 & Node 2 Testing Various RAC Failure scenario like: 1 - Node Failure 2 - DB Instance Failure 3 - ASM Instance Failure 4 - Listener failure 5 - Public Network Failure 6 - Private Network Failure 7 - OCR & Voting Disk Failure 8 - ASM Disk Failure ============== 1 - Node Failure ============== It can be planned, unplanned or all the nodes it could be any scenario Let us start the workload & shutdown the node The expected result will be: Resources will go offline Instance recovery will be performed Node vip & scan vip fail to surviving node Scan listener will fail over surviving node Client connection are moved to surviving instance Now shutdown Node 1 in OS Level intentionally or due to some reason the node 1 is shutdown #Shutdown –h now Check the node 2 status [oracle@test-rac2 ~]$ crs_stat -t Name           Type           Target    State     Host ----------------------------
Read more

ORA-01110: data file 1: '/oradata/datafiles/system01.dbf'

-
ORA-01110: data file 1: '/oradata/datafiles/system01.dbf' Error occur while restoring the database from the backup of RMAN RMAN> alter database open resetlogs; using target database control file instead of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 09/03/2017 16:05:14 ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oradata/datafiles/system01.dbf' To resolve this error fallow the below procedure – Hopefully you will succeed If you’re primary database is flashback & it has the standby – Fallow below procedure RMAN> run { 2> recover database; 3> } ORA-00283: recovery session canceled due to errors RMAN-11003: failure during parse/execution of SQL statement: alter datab
Read more

Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4)

-
Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4) Export on 12c (12.1.0.2)   – Tested in our env : 10.20.0.82 ==================== SQL> select instance_name,version from v$instance; INSTANCE_NAME    VERSION ---------------- ----------------- ora12c           12.1.0.2.0 SQL> conn sys/sys as sysdba Connected. SQL> conn hassan/( password ); Connected. SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TEST SQL> select * from test; COLUMN1              NAME -------------------- -------------------- 1                    abc 2                    xyz [oracle@ora12c u02]$ mkdir dump [oracle@ora12c u02]$ chmod -R 777 dump/ [oracle@ora12c u02]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 25 16:22:46 2017 Copyright (c) 1982, 2014, Oracle.  All
Read more