CONVERTING DATABASE TO SNAPSHOT MODE USING DGMGRL

CONVERTING DATABASE TO SNAPSHOT MODE USING DGMRL

STEP:1 CONNECT TO DATABASE.

oracle@hqphpsodbn03:/orahps/app/oracle/product/11.2.0/db_1/network/admin$ dgmgrl

DGMGRL for Solaris: 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/*****@pwcprod

Connected.

STEP:2 CHECKING DATA GUARD BROKER STATUS.

DGMGRL> show configuration

Configuration - pwc_dg_conf

  Protection Mode: MaxPerformance

  Databases:

    pwcprd   - Primary database

    pwcprod  - Physical standby database

    pwcstdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

STEP:3 CONVERT DATABASE TO SNAPSHOT MODE.

DGMGRL> convert database pwcprod to snapshot standby;

Converting database "pwcprod" to a Snapshot Standby database, please wait...

Database "pwcprod" converted successfully

STEP:4 CHECKING DATA GUARD BROKER STATUS.

DGMGRL> show configuration

Configuration - pwc_dg_conf

 Protection Mode: MaxPerformance

  Databases:

    pwcprd   - Primary database

    pwcprod  - Snapshot standby database

    pwcstdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

STEP:4 CONVERTING DATABASE TO PHYSICAL STANDBY.

DGMGRL> convert database pwcprod to physical standby;

Converting database "pwcprod" to a Physical Standby database, please wait...

Operation requires shutdown of instance "pwcprod2" on database "pwcprod"

Shutting down instance "pwcprod2"...

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "pwcprod2" on database "pwcprod"

Starting instance "pwcprod2"...

ORACLE instance started.

Database mounted.

Continuing to convert database "pwcprod" ...

Operation requires shutdown of instance "pwcprod2" on database "pwcprod"

Shutting down instance "pwcprod2"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "pwcprod2" on database "pwcprod"

Starting instance "pwcprod2"...

ORACLE instance started.

Database mounted.

Database "pwcprod" converted successfully


STEP: 6 CHECK DATA GUARD BROKER STATUS.

DGMGRL> show configuration

Configuration - pwc_dg_conf

  Protection Mode: MaxPerformance

  Databases:

    pwcprd   - Primary database

    pwcprod  - Physical standby database

    pwcstdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS
#############################################################################

Switch over DATABASE from production to DR

Switchover DATABASE from production to DR

1. ############### Perform on Production Database 192.168.5.132 ###############

PRODUCTION IP: 192.168.5.132

DR IP: 192.168.5.138

SQL> sqlplus / as sysdba

SQL> select status, error from v$archive_dest where dest_id = 2;

STATUS    ERROR
--------- -----------------------------------------------------------------
VALID

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

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

Database altered.

SQL> shutdown immediate;
ORA-01012: not logged on
SQL> exit
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@oraprd01:/export/home/oracle/Scripts1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 4 21:55:33 2018

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2572910592 bytes
Fixed Size                  2184312 bytes
Variable Size             788532104 bytes
Database Buffers         1761607680 bytes
Redo Buffers               20586496 bytes
Database mounted.

3.  #################### Perform on DR Database 192.168.5.138   #################

SQL>  sqlplus / as sysdba

SQL>  select switchover_status from v$database;

SQL>  alter database commit to switchover to primary with session shutdown;

SQL>  shutdown immediate;

SQL>  startup;

4. ##############DISCONNECT on PRIMARY 192.168.5.132 ###############

SQL> sqlplus / as sysdba

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> select process,status from v$managed_standby;

SQL> SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE# =
(SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;


#################################  Revert Back ##############################

            ########## Perform on DR 192.168.5.138 ###################

SQL> sqlplus / as sysdba

SQL> alter system switch logfile;

SQL> sqlplus / as sysdba

SQL> select status, error from v$archive_dest where dest_id = 2;

SQL> select switchover_status from v$database;

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

SQL> shutdown immediate;

SQL> startup mount;

##################### Perform on PRD 192.168.5.132 #####################

SQL> sqlplus / as sysdba

SQL> select switchover_status from v$database;

SQL> alter database commit to switchover to primary with session shutdown;

SQl> shutdown immediate;

SQL> startup;

######################  Disconnect on DR 192.168.5.138 ##########################

SQL> sqlplus / as sysdba

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status from v$managed_standby;
PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
RFS       IDLE
RFS       IDLE
RFS       RECEIVING
MRP0      APPLYING_LOG

#################### PRODUCTION 192.168.5.132 ###########################
Please validate the replication.

SQL>SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE# =
(SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1           1460
##################### DR DATABASE 192.168.5.138 ########################

SQL>SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax
FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1              1460             1460

################################################################################

Convert the Physical Standby Database into a Snapshot Standby Database

Manual: Convert the Physical Standby Database into a Snapshot Standby Database 

Reference: 11g Using Snapshot Standby Database. (Doc ID 443720.1)

Step:1 Please set db_recovery_file_dest if  not set.

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /orabin/fast_recovery_area

db_recovery_file_dest_size           big integer 4122M

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

PRDDR    MOUNTED

SQL> select flashback_on from V$database;

FLASHBACK_ON

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

NO

Step:2 Cancel media recovery and validate.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select process,status from v$managed_standby;

PROCESS   STATUS

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

ARCH      CLOSING

ARCH      CONNECTED

ARCH      CLOSING

RFS       IDLE

RFS       IDLE

9 rows selected.

Step:3 Please enable flashback.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from V$database;

FLASHBACK_ON

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

YES

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

no rows selected

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

MOUNTED              PHYSICAL STANDBY

Step:4 CONVERT database to snapshot mode.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

MOUNTED              SNAPSHOT STANDBY

Step:5 Open database in READ/Write mode.

SQL> alter database open;

Database altered.

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;

NAME

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

GUA

---

SNAPSHOT_STANDBY_REQUIRED_01/29/2019 11:35:41

YES

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

READ WRITE           SNAPSHOT STANDBY

SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log

   WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax

    FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;  2    3

    Thread Last Seq Received Last Seq Applied

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

         1                 1                1

Step:6 Shutdown database to convert back in physical standby mode.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

---

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2522791936 bytes

Fixed Size                  2161944 bytes

Variable Size             704643816 bytes

Database Buffers         1795162112 bytes

Redo Buffers               20824064 bytes

Database mounted.

---

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

MOUNTED              SNAPSHOT STANDBY

CONVERT database in physical mode.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> select open_mode,database_role from v$database;

select open_mode,database_role from v$database

                                    *

ERROR at line 1:

ORA-01507: database not mounted

SQL> startup mount;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> select status from v$instance;

STATUS

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

STARTED

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

Step:7 Start database in mount mode.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2522791936 bytes

Fixed Size                  2161944 bytes

Variable Size             704643816 bytes

Database Buffers         1795162112 bytes

Redo Buffers               20824064 bytes

Database mounted.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

MOUNTED              PHYSICAL STANDBY

Step:8 Disable flashback in database.

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from gV$database;

FLASHBACK_ON

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

NO

Step:9 Start the media recovery and check replication.

SQL> alter database recover managed standby database disconnect from session;

alter database recover managed standby database disconnect from session

*

ERROR at line 1:

ORA-01153: an incompatible media recovery is active

SQL> select process,status from v$managed_standby;

PROCESS   STATUS

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

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CLOSING

ARCH      CLOSING

RFS       IDLE

RFS       IDLE

RFS       IDLE

RFS       IDLE

MRP0      APPLYING_LOG

10 rows selected.

SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log

WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax

FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;

    Thread Last Seq Received Last Seq Applied

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

         1             84591            84591

*********************************************************************************

ORA-01017: invalid username/password; logon denied using DGMGRL

CONVERTING DATABASE PHYSICAL STANDBY TO SNAPSHOT IN RAC USING DGMGRL.

STEP:1 CONNECT TO DATABASE USING DGMGRL.

oracle@oraodbn01:/orahps/app/oracle/product/11.2.0/db_1/network/admin$ dgmgrl

DGMGRL for Solaris: 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/*****@pwcprod

Connected.

STEP:2 CHECK DATA BROKER STATUS.

DGMGRL> show configuration

Configuration - pwc_dg_conf

  Protection Mode: MaxPerformance

  Databases:

    pwcprd   - Primary database

    pwcprod  - Physical standby database

    pwcstdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

STEP:3 CONVERT TO SNAPSHOT MODE.

################ CONVERT TO SNAPSHOT MODE #######

DGMGRL> convert database pwcprod to snapshot standby;

Converting database "pwcprod" to a Snapshot Standby database, please wait...

Database "pwcprod" converted successfully

STEP:4 CHECK DATA GUARD STATUS.

DGMGRL> show configuration

Configuration - pwc_dg_conf

  Protection Mode: MaxPerformance

  Databases:

    pwcprd   - Primary database

    pwcprod  - Snapshot standby database

    pwcstdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

STEP:5 CONVERT TO PHYSICAL STANDBY DATABASE.

#################### CONVERT TO PHYSICAL STANDBY #########

DGMGRL> convert database pwcprod to Physical standby;
Converting database "pwcprod" to a Physical Standby database, please wait...
Operation requires shutdown of instance "pwcprod1" on database "pwcprod"
Shutting down instance "pwcprod1"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:
shut down instance "pwcprod1" of database "pwcprod"
start up and mount instance "pwcprod1" of database "pwcprod"


Solutions: Please correct listener.ora on all nodes in Grid Home. if you are using RAC.
**********
ORA-1017 during switchover from data guard broker for RAC database (Doc ID 2115559.1)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /orahps/11.2.0/grid)
      (GLOBAL_DBNAME = +ASM)
      (SID_NAME = +ASM1)
    )
    (SID_DESC =
      (ORACLE_HOME = /orahps/11.2.0/grid)
      (GLOBAL_DBNAME = pwcprd)
      (SID_NAME = pwcprod1)
      (ENVS="TNS_ADMIN=/orahps/11.2.0/grid/network/admin")
    )
    (SID_DESC =
      (ORACLE_HOME = /orahps/app/oracle/product/11.2.0/db_1)  --> Update Oracle Home... earlier it was grid home.
      (GLOBAL_DBNAME = pwcprod_DGMGRL)
      (SID_NAME = pwcprod1)
      (ENVS="TNS_ADMIN=/orahps/app/oracle/product/11.2.0/db_1/network/admin") --> Update Oracle Home... earlier it was grid home.
    )
  )

ADR_BASE_LISTENER = /orahps/app/oracle

*************************************************************************

*********After above changes tried again. It's working fine now.*************

#################### CONVERT TO PHYSICAL STANDBY #########

DGMGRL> convert database pwcprod to physical standby;

Converting database "pwcprod" to a Physical Standby database, please wait...

Operation requires shutdown of instance "pwcprod2" on database "pwcprod"

Shutting down instance "pwcprod2"...

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "pwcprod2" on database "pwcprod"

Starting instance "pwcprod2"...

ORACLE instance started.

Database mounted.

Continuing to convert database "pwcprod" ...

Operation requires shutdown of instance "pwcprod2" on database "pwcprod"

Shutting down instance "pwcprod2"...

ORA-01109: database not open


Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "pwcprod2" on database "pwcprod"

Starting instance "pwcprod2"...

ORACLE instance started.

Database mounted.

Database "pwcprod" converted successfully

STEP:7 CHECKING DATA GUARD STATUS.

DGMGRL> show configuration

Configuration - pwc_dg_conf

  Protection Mode: MaxPerformance
  Databases:
    pwcprd   - Primary database
    pwcprod  - Physical standby database
    pwcstdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


STEP 8: CHECKING DATABASE STATUS.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

oracle@oraodbn01:~$ srvctl status database -d pwcprod
Instance pwcprod1 is running on node oraodbn01
Instance pwcprod2 is running on node oraodbn02

oracle@oraodbn01:~$ srvctl config database -d pwcprod
Database unique name: pwcprod
Database name:
Oracle home: /orahps/app/oracle/product/11.2.0/db_1/
Oracle user: oracle
Spfile: +DATA/PWCPROD/PARAMETERFILE/spfile.345.1004971829
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: pwcprod
Database instances: pwcprod1,pwcprod2
Disk Groups: DATA,ARCH
Mount point paths:
Services: hpspcard
Type: RAC
Database is administrator managed