Oracle Database Commands and Practical Examples: Physical standby database to Snapshot Database 11g , 12c

standby snanshot for Oracle11g,12c
=================================

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

ALTER SYSTEM SET db_recovery_file_dest_size = 30g SCOPE = BOTH;

ALTER SYSTEM SET db_recovery_file_dest = '' SCOPE = BOTH;

ALTER SYSTEM SET db_flashback_retention_target = 2880 SCOPE = BOTH;

alter database flashback on;
*****************************************************************************************
Step 1: Cancel the Managed Recovery Process (MRP) on the physical standby database, shut it down and place it in Mount mode.

----->alter database recover managed standby database cancel;
----->shut immediate
----->startup mount

Step 2: Once the standby database is mounted, convert the Physical standby database to snapshot standby database.

----->alter database convert to snapshot standby;

Step 3: You can now open the snapshot standby database and check its mode.

----->alter database open;

----->select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
----------- --------------- ---------------- ------------------
OPEN SNAPSHOT STANDBY READ WRITE

Standby:

select process,status,sequence# from v$managed_standby;

Steps on converting back a snapshot standby database to physical standby database.
Step 1: Shut down the snapshot standby database and open it in Mount mode.

----->shut immediate

----->startup mount

Convert the snapshot standby database to physical standby database.

----->alter database convert to physical standby;

Step 3: Once done, bounce the physical standby database and start the Managed Recovery Process (MRP) on it.

----->shut immediate;

----->startup mount;

select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ -------------- ---------------- ----------------
OPEN PHYSICAL STANDBY MOUNTED

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

#################### Example 12 C ##############################################
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size 7657384 bytes
Variable Size 6073354328 bytes
Database Buffers 6777995264 bytes
Redo Buffers 25894912 bytes
Database mounted.
SQL> alter database convert to snapshot standby;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN SMRPRODB SNAPSHOT STANDBY READ WRITE

SQL> SET LINESIZE 400

COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
SQL> SQL> SQL> SQL> SQL> SQL> COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10

SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
SQL> SQL> SQL> 2 action,
3 status,
4 description,
version,
patch_id,
bundle_series
FROM sys.dba_registry_sqlpatch
ORDER by action_time;
5 6 7 8 9
ACTION_TIME ACTION STATUS DESCRIPTION VERSION PATCH_ID BUNDLE_SER
-------------------- ---------- ---------- ---------------------------------------- ---------- ---------- ----------
02-FEB-2019 08:40:52 APPLY SUCCESS DATABASE PATCH SET UPDATE 12.1.0.2.18101 12.1.0.2 28259833 PSU
6

02-FEB-2019 08:49:29 APPLY SUCCESS Database PSU 12.1.0.2.181016, Oracle Jav 12.1.0.2 28440711
aVM Component (OCT2018)

SQL> @dbstatus

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPRODB SMRPRODB SMRPRODB_DR READ WRITE SNAPSHOT STANDBY YES 354178037

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size 7657384 bytes
Variable Size 6073354328 bytes
Database Buffers 6777995264 bytes
Redo Buffers 25894912 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size 7657384 bytes
Variable Size 6073354328 bytes
Database Buffers 6777995264 bytes
Redo Buffers 25894912 bytes
Database mounted.
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
---------- ---------------- ---------------- --------------------
MOUNTED SMRPRODB PHYSICAL STANDBY MOUNTED

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

Database altered.

SQL> @dbstatus

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPRODB SMRPRODB SMRPRODB_DR MOUNTED PHYSICAL STANDBY YES 0

SQL> @standby

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
SMRPRODB MOUNTED PHYSICAL STANDBY

PROCESS STATUS SEQUENCE#
--------- ---------- ----------
ARCH CLOSING 4172
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0

PROCESS STATUS SEQUENCE#
--------- ---------- ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0

PROCESS STATUS SEQUENCE#
--------- ---------- ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 4173
RFS IDLE 0
MRP0 APPLYING_L 4173

PROCESS STATUS SEQUENCE#
--------- ---------- ----------
OG

33 rows selected.

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 4172 4172 0

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
2 FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
3 4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
; 5 6 7

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 4175 4175 0

SQL>

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

Oracle Database Commands and Practical Examples

HOME

Monday, 24 June 2019

Physical standby database to Snapshot Database 11g , 12c

No comments:

Post a Comment