## CREATED A PRIMARY DATABASE : PRIM
SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;
INSTANCE_NAME STATUS
---------------- ------------
prim OPEN
##CURRENTLY THE DATABASE IS IN THE NOARCHIVELOG MODE
SQL> ARCHIVE LOG LIST;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 45
Current log sequence 47
##THE VARIOUS DATAFILE, LOGFILE, CONTROL FILES ARE AS FOLLOWS FOR DB PRIM
SQL> SELECT GROUP#, BYTES, MEMBERS, ARCHIVED, STATUS FROM V$LOG;
GROUP# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 52428800 1 NO INACTIVE
2 52428800 1 NO CURRENT
3 52428800 1 NO INACTIVE
SQL>
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;
GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
1
D:\APP\ORADATA\PRIM\REDO01.LOG
2
D:\APP\ORADATA\PRIM\REDO02.LOG
3
D:\APP\ORADATA\PRIM\REDO03.LOG
SQL> SELECT NAME FROM V$DATAFILE;
NAME
--------------------------------------------------------------------------------
D:\APP\ORADATA\PRIM\SYSTEM01.DBF
D:\APP\ORADATA\PRIM\SYSAUX01.DBF
D:\APP\ORADATA\PRIM\UNDOTBS01.DBF
D:\APP\ORADATA\PRIM\USERS01.DBF
SQL> SELECT NAME FROM V$CONTROLFILE;
NAME
--------------------------------------------------------------------------------
D:\APP\ORADATA\PRIM\CONTROL01.CTL
D:\APP\FLASH_RECOVERY_AREA\PRIM\CONTROL02.CTL
## NEED TO CHANGE THE DB TO THE ARCHIVE MODE
SQL> STARTUP MOUNT FORCE;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1374304 bytes
Variable Size 234882976 bytes
Database Buffers 71303168 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL>
## ADD THE STANDBY LOG FILES TO PRIMARY TO HAVE THE SAME IN THE STANDBY DB
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'D:\APP\ORADATA\PRIM\STANDBYLOG1.RDO' SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'D:\APP\ORADATA\PRIM\STANDBYLOG2.RDO' SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'D:\APP\ORADATA\PRIM\STANDBYLOG3.RDO' SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'D:\APP\ORADATA\PRIM\STANDBYLOG4.RDO' SIZE 50M;
Database altered.
SQL> SELECT GROUP#, BYTES/1024/1024 MB FROM V$LOG;
GROUP# MB
---------- ----------
1 50
3 50
2 50
SQL> SELECT GROUP#, BYTES/1024/1024 MB FROM V$STANDBY_LOG;
GROUP# MB
---------- ----------
4 50
5 50
6 50
7 50
SQL> SELECT MEMBER, TYPE, GROUP# FROM V$LOGFILE;
MEMBER
--------------------------------------------------------------------------------
TYPE GROUP#
------- ----------
D:\APP\ORADATA\PRIM\REDO01.LOG
ONLINE 1
D:\APP\ORADATA\PRIM\REDO02.LOG
ONLINE 2
D:\APP\ORADATA\PRIM\REDO03.LOG
ONLINE 3
MEMBER
--------------------------------------------------------------------------------
TYPE GROUP#
------- ----------
D:\APP\ORADATA\PRIM\STANDBYLOG1.RDO
STANDBY 4
D:\APP\ORADATA\PRIM\STANDBYLOG2.RDO
STANDBY 5
D:\APP\ORADATA\PRIM\STANDBYLOG3.RDO
STANDBY 6
MEMBER
--------------------------------------------------------------------------------
TYPE GROUP#
------- ----------
D:\APP\ORADATA\PRIM\STANDBYLOG4.RDO
STANDBY 7
7 rows selected.
## MOVE THE DATABASE TO THE ARCHIVE MODE
SQL> CREATE PFILE FROM SPFILE;
File created.
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
## MAKE THE FOLLOWING CHANGES IN INIT FILE FOR THE PRIMARY DB
DB_UNIQUE_NAME=prim
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stand)'
LOG_ARCHIVE_DEST_1='LOCATION=D:\app\arch\PRIM VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim'
LOG_ARCHIVE_DEST_2='SERVICE=stand LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stand'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
## STARTUP NOMOUNT USING THE EDITED PFILE
SQL> STARTUP NOMOUNT PFILE='D:\app\product\11.2.0\dbhome_1\database\INITprim.ORA';
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1374304 bytes
Variable Size 234882976 bytes
Database Buffers 71303168 bytes
Redo Buffers 6299648 bytes
SQL>
SQL>
SQL>
## IMMEDIATELY CREATE THE SERVER PARAMETER FILE FROM THE PFILE
SQL>
SQL> CREATE SPFILE FROM PFILE;
File created.
SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=D:\app\arch\PRIM VALI
D_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=prim
SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=stand LGWR ASYNC VALID
_FOR=(ONLINE_LOGFILES,PRIMARY_
ROLE) DB_UNIQUE_NAME=stand
SQL> SHOW PARAMETER LOG_ARCHIVE_CONFIG;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(prim,stand)
SQL> SHOW PARAMETER LOG_ARCHIVE_MAX
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 30
SQL> SHOW PARAMETER LOG_ARCHIVE_FORMAT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.arc
SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_STATE_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string ENABLE
SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_STATE_2;
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
log_archive_dest_state_2 string ENABLE
## STILL THE DATABASE IS IN THE NOARCHIVE MODE
SQL> ALTER DATABASE ARCHIVELOG;
ALTER DATABASE ARCHIVELOG
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> STARTUP
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1374304 bytes
Variable Size 234882976 bytes
Database Buffers 71303168 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1374304 bytes
Variable Size 234882976 bytes
Database Buffers 71303168 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL>
SQL>
SQL>
SQL> ALTER DATABASE ARCHIVELOG;
Database altered.
SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\app\arch\PRIM
Oldest online log sequence 46
Next log sequence to archive 48
Current log sequence 48
SQL>
## STILL SOME UPDATE IN THE PFILE FOR THE STANDBY MODE
SQL> CREATE PFILE FROM SPFILE;
File created.
SQL>
## OPEN AND EDIT THE PFILE AS FOLLOWS
FAL_SERVER=stand
FAL_CLIENT=prim
DB_FILE_NAME_CONVERT='D:\app\oradata\STAND','D:\app\oradata\PRIM'
LOG_FILE_NAME_CONVERT='D:\app\oradata\STAND','D:\app\oradata\PRIM'
STANDBY_FILE_MANAGEMENT=AUTO
SQL>
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> STARTUP MOUNT PFILE='D:\app\product\11.2.0\dbhome_1\database\INITprim.ORA';
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1374304 bytes
Variable Size 234882976 bytes
Database Buffers 71303168 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL>
SQL> CREATE SPFILE FROM PFILE;
File created.
## CONFIRM IF THE UDPATE TOOK EFFECT
SQL>
SQL> SHOW PARAMETER FAL;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string prim
fal_server string stand
SQL> SHOW PARAMETER DB_FILE_NAME_CONVERT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string D:\app\oradata\STAND, D:\app\o
radata\PRIM
SQL>
SQL>
SQL> SHOW PARAMETER LOG_FILE_NAME_CONVERT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string D:\app\oradata\STAND, D:\app\o
radata\PRIM
SQL> SHOW PARAMETER STANDBY_FILE_MANAGEMENT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL>
##############################################################################################
ENOUGH CHANGES DONE IN THE PRIMARY DATABASE NOW NEED TO SETUP THE STANDBY DATABASE
##############################################################################################
## CREATE THE STANDBY CONTROL FILE AND INIT PARAMETER FILE
SQL>
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\app\CONTROL01.CTL';
Database altered.
SQL>
SQL> CREATE PFILE='D:\app\INITstand.ora' FROM SPFILE;
File created.
SQL>
## NOW UPDATE THE INIT FILE FOR THE STANDBY DATABASE CREATED IN THE UPPER STATEMENT
*.FAL_SERVER='prim'
*.FAL_CLIENT='stand'
*.DB_FILE_NAME_CONVERT='D:\app\oradata\PRIM','D:\app\oradata\STAND'
*.LOG_FILE_NAME_CONVERT='D:\app\oradata\PRIM','D:\app\oradata\STAND'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stand)'
*.LOG_ARCHIVE_DEST_1='LOCATION=D:\app\arch\STAND VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stand'
*.LOG_ARCHIVE_DEST_2='SERVICE=prim LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.db_name='PRIM'
*.DB_UNIQUE_NAME='stand'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.control_files='D:\app\oradata\STAND\control01.ctl','D:\app\flash_recovery_area\STAND\control02.ctl'
## COPY THE CONTROL FILE CREATE IN THE SECOND LAST STEP TO THE LOCATION SPECIFIED PARAMETER CONTROL_FILES
## NOW ALL SET NEED TO START THE STANDBY DATABASE
## NEED TO COPY THE DATAFILE, LOG AND STANDBY LOG FROM PRIMARY TO SECONDARY, AFTER PRIMARY SHUTDOWN
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
## NOW COPY
## CREATE THE SERVICE FOR THE STANDBY DATABASE ON WINDOWS
C:\Users\viswajeet.samal1>oradim -NEW -SID stand -SYSPWD SYS -STARTMODE manual
Instance created.
## MAKE CORRESPONDING CHANGES IN TNS AND LISTENER
C:\Users\viswajeet.samal1>LSNRCTL RELOAD
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 19-JUL-2012 13:43:05
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.19.44.63)(PORT=1521)))
The command completed successfully
C:\Users\viswajeet.samal1>
C:\Users\viswajeet.samal1>LSNRCTL STATUS
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 19-JUL-2012 13:43:22
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.19.44.63)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 18-JUL-2012 16:59:01
Uptime 0 days 20 hr. 44 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\app\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\app\diag\tnslsnr\INGJAHDDT0507\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.19.44.63)(PORT=1521)))
Services Summary...
Service "DEST" has 1 instance(s).
Instance "DEST", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCLDB" has 1 instance(s).
Instance "ORCLDB", status UNKNOWN, has 1 handler(s) for this service...
Service "PRIM" has 1 instance(s).
Instance "PRIM", status UNKNOWN, has 1 handler(s) for this service...
Service "SOURCE" has 1 instance(s).
Instance "SOURCE", status UNKNOWN, has 1 handler(s) for this service...
Service "STAND" has 1 instance(s).
Instance "STAND", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
## CHECK FOR THE TNSPING
C:\Users\viswajeet.samal1>TNSPING STAND
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 19-JUL-2012 13:44:52
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
D:\app\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.44.63)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STAND)))
OK (0 msec)
C:\Users\viswajeet.samal1>TNSPING PRIM
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 19-JUL-2012 13:44:57
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
D:\app\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.44.63)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRIM)))
OK (10 msec)
## CONNECT TO THE STANDBY DATABASE AND START USING THE NEWLY CREATED PFILE
C:\Users\viswajeet.samal1>SQLPLUS SYS@STAND AS SYSDBA
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 19 13:45:31 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> STARTUP NOMOUNT PFILE='D:\app\product\11.2.0\dbhome_1\database\INITstand.ora';
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1374304 bytes
Variable Size 234882976 bytes
Database Buffers 71303168 bytes
Redo Buffers 6299648 bytes
SQL>
SQL>
## CREATE SPFILE FROM PFILE AND CHECK FOR THE PARAMETER SETTING THAT WE HAD DONE
SQL> CREATE SPFILE FROM PFILE;
File created.
SQL> SHOW PARAMETER FAL;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string stand
fal_server string prim
SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=D:\app\arch\STAND VAL
ID_FOR=(ALL_LOGFILES,ALL_ROLES
) DB_UNIQUE_NAME=stand
SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=prim LGWR ASYNC VALID_
FOR=(ONLINE_LOGFILES,PRIMARY_R
OLE) DB_UNIQUE_NAME=prim
SQL> SHOW PARAMETER DB_FILE_NAME_CONVERT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string D:\app\oradata\PRIM, D:\app\or
adata\STAND
SQL>
SQL> SHOW PARAMETER LOG_FILE_NAME_CONVERT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string D:\app\oradata\PRIM, D:\app\or
adata\STAND
SQL>
SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1374304 bytes
Variable Size 234882976 bytes
Database Buffers 71303168 bytes
Redo Buffers 6299648 bytes
Database mounted.
## CHECK FOR THE DATAFILE AND LOGFILE OF THE STANDBY DATABASE
SQL> SELECT NAME FROM V$DATAFILE;
NAME
--------------------------------------------------------------------------------
D:\APP\ORADATA\STAND\SYSTEM01.DBF
D:\APP\ORADATA\STAND\SYSAUX01.DBF
D:\APP\ORADATA\STAND\UNDOTBS01.DBF
D:\APP\ORADATA\STAND\USERS01.DBF
SQL> SELECT MEMBER FROM V$LOGFILE;
MEMBER
--------------------------------------------------------------------------------
D:\APP\ORADATA\STAND\REDO01.LOG
D:\APP\ORADATA\STAND\REDO02.LOG
D:\APP\ORADATA\STAND\REDO03.LOG
D:\APP\ORADATA\STAND\STANDBYLOG1.RDO
D:\APP\ORADATA\STAND\STANDBYLOG2.RDO
D:\APP\ORADATA\STAND\STANDBYLOG3.RDO
D:\APP\ORADATA\STAND\STANDBYLOG4.RDO
7 rows selected.
SQL>
######################################################################################
FOLLOW THE FOLLOWING STEPS TO CREATE THE PASSWORD FILE ON BOTH THE INSANCE SERVERS
######################################################################################
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL>
SQL>
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE;
System altered.
SQL>
SQL>
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL>
SQL> CONN SYS/SYS@PRIM AS SYSDBA
Connected.
SQL>
SQL>
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE;
System altered.
SQL>
SQL>
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL>
SQL>
SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\Users\viswajeet.samal1>orapwd file=D:\app\product\11.2.0\dbhome_1\database\pwdSTAND.ORA entries=10 ignorecase=Y
Enter password for SYS:
C:\Users\viswajeet.samal1>orapwd file=D:\app\product\11.2.0\dbhome_1\database\pwdPRIM.ORA entries=10 ignorecase=Y
Enter password for SYS:
C:\Users\viswajeet.samal1>
#########################################################
STARTUP BOTH THE PRIMARY IN OPEN AND STANDBY IN MOUNT ONCE
#########################################################
## START THE RECOVERY OF THE STANDBY DATABASE
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
## CHECK IF THE STANDBY IS WORKING
SQL> select sequence#, first_time, applied from v$archived_log;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
54 19-JUL-12 NO
48 19-JUL-12 NO
49 19-JUL-12 NO
56 19-JUL-12 NO
50 19-JUL-12 NO
51 19-JUL-12 NO
52 19-JUL-12 NO
53 19-JUL-12 NO
55 19-JUL-12 NO
57 19-JUL-12 NO
58 19-JUL-12 NO
11 rows selected.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> select sequence#, first_time, applied from v$archived_log;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
54 19-JUL-12 NO
48 19-JUL-12 YES
49 19-JUL-12 YES
56 19-JUL-12 NO
50 19-JUL-12 IN-MEMORY
51 19-JUL-12 NO
52 19-JUL-12 NO
53 19-JUL-12 NO
55 19-JUL-12 NO
57 19-JUL-12 NO
58 19-JUL-12 NO
11 rows selected.
SQL> select sequence#, first_time, applied from v$archived_log;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
54 19-JUL-12 YES
48 19-JUL-12 YES
49 19-JUL-12 YES
56 19-JUL-12 YES
50 19-JUL-12 YES
51 19-JUL-12 YES
52 19-JUL-12 YES
53 19-JUL-12 YES
55 19-JUL-12 YES
57 19-JUL-12 NO
58 19-JUL-12 NO
11 rows selected.
SQL> select sequence#, first_time, applied from v$archived_log;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
54 19-JUL-12 YES
48 19-JUL-12 YES
49 19-JUL-12 YES
56 19-JUL-12 YES
50 19-JUL-12 YES
51 19-JUL-12 YES
52 19-JUL-12 YES
53 19-JUL-12 YES
55 19-JUL-12 YES
57 19-JUL-12 YES
58 19-JUL-12 YES
11 rows selected.
SQL> CONN SYS/SYS@PRIM AS SYSDBA
Connected.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL>
SQL>
SQL> CONN SYS/SYS@STAND AS SYSDBA
Connected.
SQL>
SQL>
SQL> select sequence#, first_time, applied from v$archived_log;
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
54 19-JUL-12 YES
48 19-JUL-12 YES
49 19-JUL-12 YES
56 19-JUL-12 YES
50 19-JUL-12 YES
51 19-JUL-12 YES
52 19-JUL-12 YES
53 19-JUL-12 YES
55 19-JUL-12 YES
57 19-JUL-12 YES
58 19-JUL-12 YES
SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
59 19-JUL-12 YES
12 rows selected.
SQL>
###################################################################################
STEPS FOR SWITCH OVER TO A PHYSICAL STANDBY
###################################################################################
CURRENCT STATUS
PRIMARY : STAND
STANDBY : PRIM
SQL>
SQL> conn sys/sys@stand as sysdba
Connected.
SQL>
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL>
SQL>
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
Database altered.
SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1374304 bytes
Variable Size 234882976 bytes
Database Buffers 71303168 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL>
SQL>
SQL>
SQL> CONN SYS/SYS@PRIM AS SYSDBA
Connected.
SQL>
SQL>
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SQL>
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SQL>
SQL>
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.
SQL>
SQL>
SQL>
SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;
INSTANCE_NAME STATUS
---------------- ------------
prim MOUNTED
SQL>
SQL>
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>
SQL>
SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;
INSTANCE_NAME STATUS
---------------- ------------
prim OPEN
SQL>
SQL>
SQL> CONN SYS/SYS@STAND AS SYSDBA
Connected.
SQL>
SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
SQL>
SQL>
SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG WHERE APPLIED='NO';
SEQUENCE# APPLIED
---------- ---------
115 NO
116 NO
116 NO
117 NO
117 NO
SQL>
SQL> CONN SYS/SYS@PRIM AS SYSDBA
Connected.
SQL>
SQL>
SQL>
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL>
SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG WHERE APPLIED='NO';
SEQUENCE# APPLIED
---------- ---------
113 NO
118 NO
119 NO
120 NO
120 NO
120 NO
6 rows selected.
SQL>
SQL>
SQL> CONN SYS/SYS@STAND AS SYSDBA;
Connected.
SQL>
SQL>
SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG WHERE APPLIED='NO';
SEQUENCE# APPLIED
---------- ---------
115 NO
116 NO
116 NO
117 NO
117 NO
SQL>
SQL>
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL>
SQL> CONN SYS/SYS@PRIM AS SYSDBA
Connected.
SQL>
SQL>
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PRIMARY
SQL>
No comments:
Post a Comment