Oracle
12c Physical Standby to Snapshot Standby
Prepared by Ranesh .
|
Details
|
Primary Side
|
Standby Side
|
|
Server Name
|
Primary
|
|
|
DB Name
|
DEV
|
|
|
IP
|
192.168.1.24
|
192.168.1.25
|
Check
both primary and standby role.
Check
the archived sequence in standby
Check
the flashback status and db_recovery_file_set location
In standby side, stop
the mrp process
Bounce
the database and keep in mount stage
Convert to snapshot standby database
Open the standby database in READ/WRITE mode
Check the
DATABASE_ROLE and OPEN_MODE
Testing
on the snapshot standby database
Both
side verify the archived sequence
In Standby
Side, bounce and mount the database
Convert
the snapshot standby database to physical standby database
Bounce
the database
Check
the standby database_role and mode
Enable
the MRP process
After
convert to physical standby database, check the table status
After convert back to Physical standby
database once again restart the MRP process
PRIMARY
SQL> select
name,database_role,instance_name,open_mode from v$database,v$instance;
NAME
DATABASE_ROLE
INSTANCE_NAME OPEN_MODE
---------
---------------- ---------------- --------------------
DEV
PRIMARY DEV READ WRITE
SQL> select
max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
119
STANDBY
SQL> select
name,db_unique_name,database_role,open_mode from v$database,v$instance;
NAME
DB_UNIQUE_NAME
DATABASE_ROLE OPEN_MODE
---------
------------------------------ ---------------- --------------------
DEV STAND PHYSICAL STANDBY MOUNTED
SQL> select
max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
119
SQL> select
flashback_on from V$database;
FLASHBACK_ON
------------------
YES
SQL> show parameter
db_recover
NAME TYPE
VALUE
------------------------------------
----------- ------------------------------
db_recovery_file_dest
string
/u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size
big integer 4560M
SQL> alter database
convert to snapshot standby;
Database altered.
|
Details
|
Primary Side
|
Standby Side
|
|
Server Name
|
Primary
|
Snapshot standby
|
|
DB Name
|
DEV
|
|
|
IP
|
192.168.1.24
|
192.168.1.25
|
SQL> select
database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
SQL> select open_mode
from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> startup
ORACLE instance started.
Total System Global Area
2634022912 bytes
Fixed Size
2927864 bytes
Variable Size 687866632 bytes
Database Buffers 1929379840 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> select open_mode
from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> create user hari
identified by oracle;
grant connect,resource
to hari;
conn hari/oracle
create table sample(code
number, name char(20));
insert into sample
values (1‘sam’);
commit;
User created.
SQL>
Grant succeeded.
SQL> commit;
Commit complete.
SQL> select
process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------
----------
ARCH
CONNECTED 0
ARCH
CONNECTED 0
ARCH
CONNECTED 0
ARCH
CONNECTED 0
ARCH
CONNECTED 0
LNS
CONNECTED 0
RFS IDLE 0
RFS IDLE 120
8 rows selected.
SQL> select thread#,max(sequence#)
from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
----------
--------------
1
119
SQL> shutdown
immediate
Database closed.
Database dismounted.
ORACLE instance shut
down.
SQL> startup mount
ORACLE instance started.
Total System Global Area
2634022912 bytes
Fixed Size
2927864 bytes
Variable Size 687866632 bytes
Database Buffers 1929379840 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> alter database
convert to physical standby;
Database altered.
SQL> startup
ORACLE instance started.
Total System Global Area
2634022912 bytes
Fixed Size
2927864 bytes
Variable Size 687866632 bytes
Database Buffers 1929379840 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> select
status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS
INSTANCE_NAME
DATABASE_ROLE OPEN_MODE
------------
---------------- ---------------- --------------------
OPEN
stand PHYSICAL STANDBY READ ONLY
SQL> alter database
recover managed standby database disconnect from session;
Database altered.
SQL> select
max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
124
PRIMARY:
SQL> select name from
v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DEV/system01.dbf
/u01/app/oracle/oradata/DEV/ranesh.dbf
/u01/app/oracle/oradata/DEV/sysaux01.dbf
/u01/app/oracle/oradata/DEV/undotbs01.dbf
/u01/app/oracle/oradata/DEV/example01.dbf
/u01/app/oracle/oradata/DEV/users01.dbf
6 rows selected.
SQL> create
tablespace sam datafile '/u01/app/oracle/oradata/DEV/sam.dbf' size 1m;
Tablespace created.
SQL> alter system set
log_archive_dest_state_2=defer;
System altered.
SQL> alter system set
log_archive_dest_state_2=enable;
System altered.
SQL>
SQL> select
TABLESPACE_NAME from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
RANESH
SAM
8 rows selected.
SQL> select
max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
124
SQL> alter system set
log_archive_dest_state_2=defer;
System altered.
SQL>
SQL> alter system set
log_archive_dest_state_2=enable;
System altered.
No comments:
Post a Comment