Oracle Database Commands and Practical Examples: snapshot standby database

[oracle@srv3 rmanbkp]$ . oraenv
ORACLE_SID = [stdrac] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@srv3 rmanbkp]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 13 17:20:58 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> ALTER SYSTEM SET db_flashback_retention_target = 2880 SCOPE = BOTH;

System altered.

SQL> sho parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4560M
SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC MOUNTED PHYSICAL STANDBY

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

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
RAC MOUNTED PHYSICAL STANDBY stdrac

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CLOSING 610 1
ARCH CLOSING 607 1
ARCH CONNECTED 0 0
ARCH CLOSING 609 1
RFS IDLE 640 2
RFS IDLE 0 0
RFS IDLE 611 1
RFS IDLE 0 0
MRP0 WAIT_FOR_LOG 640 2

9 rows selected.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CLOSING 610 1
ARCH CLOSING 607 1
ARCH CONNECTED 0 0
ARCH CLOSING 609 1
RFS IDLE 640 2
RFS IDLE 0 0
RFS IDLE 611 1
RFS IDLE 0 0

8 rows selected.

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

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

Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 566234808 bytes
Database Buffers 180355072 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> alter database convert to snapshot standby;

Database altered.

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

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC MOUNTED SNAPSHOT STANDBY

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

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
RAC MOUNTED SNAPSHOT STANDBY stdrac

SQL> alter database open;

Database altered.

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

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
RAC READ WRITE SNAPSHOT STANDBY stdrac

[oracle@srv3 rmanbkp]$ sqlplus sys/system123@stdrac as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 13 17:31:28 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

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

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
RAC READ WRITE SNAPSHOT STANDBY stdrac

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/oradata/stdrac/system.279.1020351597
SYSTEM

/u02/oradata/stdrac/sysaux.271.1020351511
SYSAUX

/u02/oradata/stdrac/undotbs2.278.1020352203
UNDOTBS2

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/oradata/stdrac/users.272.1020351691
USERS

/u02/oradata/stdrac/idata.284.1020709157
IDATA

/u02/oradata/stdrac/undotbs1.270.1020351693
UNDOTBS1

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/oradata/stdrac/ilog.285.1020709245
ILOG

/u02/oradata/stdrac/indx.286.1020709561
INDX

/u02/oradata/stdrac/indx.291.1026913655
INDX

9 rows selected.

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

Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 574623416 bytes
Database Buffers 171966464 bytes
Redo Buffers 5455872 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.
stSQL
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 574623416 bytes
Database Buffers 171966464 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> select name,open_mode,database_Role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC MOUNTED PHYSICAL STANDBY

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

Database altered.

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CLOSING 623 1
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 653 2
RFS IDLE 0 0
RFS IDLE 624 1
RFS IDLE 0 0
MRP0 WAIT_FOR_LOG 653 2

9 rows selected.

SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CLOSING 623 1
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 653 2
RFS IDLE 0 0
RFS IDLE 624 1
RFS IDLE 0 0
MRP0 WAIT_FOR_LOG 653 2

9 rows selected.

SQL>

standby snapshot for Oracle 10g
================================

Snapshot standby for 10g

create restore point Apr23 guarantee flashback database;

select name, time, storage_size,GUARANTEE_FLASHBACK_DATABASE from V$restore_point;

alter database activate standby database;

select name, open_mode, controlfile_type from V$database;

alter database open;

select name, open_mode, controlfile_type from V$database;

need to create one table(for confirmation)

5.Revert to standby setup
Now we finished testing and want to revert to the original state.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2209600 bytes
Variable Size 159385792 bytes
Database Buffers 50331648 bytes
Redo Buffers 5230592 bytes
Database mounted.

SQL> flashback database to restore point PRE_ACTIV_SNAP;

SQL> alter database convert to physical standby;

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2209600 bytes
Variable Size 159385792 bytes
Database Buffers 50331648 bytes
Redo Buffers 5230592 bytes
Database mounted.

SQL> select name, open_mode, controlfile_type from V$database;

NAME OPEN_MODE CONTROL
-------------------------------------------------- -------------------- -------
MOUNTED STANDBY

Now the standby database is back again in MOUNT mode after revert.

https://varunyadav27.blogspot.com/2019/06/physical-standby-database-to-snapshot.html

Oracle Database Commands and Practical Examples

HOME

Wednesday 18 December 2019

RAC convert physical standby to snapshot database

Monday 24 June 2019

physical standby to snapshot standby database for Oracle 10g