readonly.html
09-10-2001 Bikle
-----------------
This page discusses some details associated with changing the state of
a standby database in Managed Recovery mode to Read-Only mode. Below,
we display some text from the Oracle documentation which discusses
this task:
To open the standby database in read-only mode when in managed recovery mode:
1. Start a SQL*Plus session and execute the following statement:
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL
2. Open the database in read-only mode:
SQL> ALTER DATABASE OPEN READ ONLY;
We followed these exact steps and display the resulting screen dump below.
To give the demo more impact, we start the demo with a SHUTDOWN ABORT to give
the reader a clearer context:
18:27:39 SQL@BBIKLE>SHUTDOWN ABORT
ORACLE instance shut down.
18:27:52 SQL@BBIKLE>
18:29:13 SQL@BBIKLE>STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 186908832 bytes
Fixed Size 73888 bytes
Variable Size 75399168 bytes
Database Buffers 100933632 bytes
Redo Buffers 10502144 bytes
18:29:54 SQL@BBIKLE> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
18:30:17 SQL@BBIKLE> RECOVER MANAGED STANDBY DATABASE
After we issue the above command, the terminal session becomes
"locked" by the above command. So, we then continue the demonstration
on terminal session number two:
18:31:21 SQL@BBIKLE> RECOVER MANAGED STANDBY DATABASE CANCEL
Media recovery complete.
18:31:46 SQL@BBIKLE>
At this point, terminal session one becomes unlocked and issues this message:
18:30:17 SQL@BBIKLE> RECOVER MANAGED STANDBY DATABASE
ORA-00283: recovery session canceled due to errors
ORA-16037: user requested cancel of sustained recovery operation
We continue the demonstration on terminal session one:
18:31:52 SQL@BBIKLE> ALTER DATABASE OPEN READ ONLY;
Database altered.
We try a query:
18:32:19 SQL@BBIKLE> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/u2/oracle/data/BBIKLE/system01.dbf
/u2/oracle/data/BBIKLE/ABIKLE_tools_01.dbf
/u2/oracle/data/BBIKLE/ABIKLE_users_01.dbf
/u2/oracle/data/BBIKLE/ABIKLE_gendata_01.dbf
/u2/oracle/data/BBIKLE/ABIKLE_rbs_01.dbf
According to the documentation, we may create a temporary tablespace:
18:32:50 SQL@BBIKLE> CREATE TEMPORARY TABLESPACE TEMP
18:34:14 2 TEMPFILE '/u2/oracle/data/BBIKLE/tempb.dbf' size 200m reuse
18:35:19 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m;
CREATE TEMPORARY TABLESPACE TEMP
*
ERROR at line 1:
ORA-01543: tablespace 'TEMP' already exists
Since tablespace 'TEMP' already exists, we issue some syntax to add a
file to it:
18:38:09 SQL@BBIKLE> ALTER TABLESPACE TEMP ADD TEMPFILE '/u2/oracle/data/BBIKLE/tempb.dbf' size 200m reuse
18:38:11 SQL@BBIKLE> /
Tablespace altered.
18:38:12 SQL@BBIKLE>
Next, we demonstrate what happens when log switches occur on the primary database while
the standby is in read-only mode:
19:21:22 SQL@ABIKLE> ALTER SYSTEM SWITCH LOGFILE;
System altered.
19:21:36 SQL@ABIKLE> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
MAX(SEQUENCE#)
--------------
149
19:21:56 SQL@ABIKLE>
The resulting archived redolog file appears at the primary archive directory
as we would expect:
sf49er.bikle.com 28 % ls -latr /u2/oracle/data/ABIKLE/arch/
total 9498
-rw-r----- 1 oracle dba 4715008 Sep 8 13:24 arch_1_131.arc
-rw-r----- 1 oracle dba 1024 Sep 8 13:55 arch_1_132.arc
-rw-r----- 1 oracle dba 29696 Sep 9 02:28 arch_1_133.arc
-rw-r----- 1 oracle dba 1024 Sep 9 02:39 arch_1_134.arc
-rw-r----- 1 oracle dba 1024 Sep 9 03:07 arch_1_135.arc
-rw-r----- 1 oracle dba 1024 Sep 9 03:11 arch_1_136.arc
-rw-r----- 1 oracle dba 58880 Sep 9 03:39 arch_1_137.arc
-rw-r----- 1 oracle dba 1024 Sep 9 03:39 arch_1_138.arc
-rw-r----- 1 oracle dba 1024 Sep 9 03:52 arch_1_139.arc
-rw-r----- 1 oracle dba 1024 Sep 9 22:53 arch_1_140.arc
-rw-r----- 1 oracle dba 1024 Sep 9 23:01 arch_1_141.arc
-rw-r----- 1 oracle dba 1024 Sep 9 23:04 arch_1_142.arc
-rw-r----- 1 oracle dba 1024 Sep 9 23:04 arch_1_143.arc
-rw-r----- 1 oracle dba 1024 Sep 10 16:19 arch_1_144.arc
-rw-r----- 1 oracle dba 1024 Sep 10 16:34 arch_1_145.arc
-rw-r----- 1 oracle dba 19968 Sep 10 17:54 arch_1_146.arc
-rw-r----- 1 oracle dba 1024 Sep 10 18:07 arch_1_147.arc
drwxr-xr-x 3 oracle dba 512 Sep 10 18:35 ../
-rw-r----- 1 oracle dba 10240 Sep 10 18:35 arch_1_148.arc
-rw-r----- 1 oracle dba 1024 Sep 11 19:21 arch_1_149.arc
drwxr-xr-x 2 oracle dba 512 Sep 11 19:21 ./
sf49er.bikle.com 29 %
Next, we look for it in the standby archive directory:
sf49er.bikle.com 31 % ls -latr /u2/oracle/data/BBIKLE/archstandby
total 9498
-rw-r----- 1 oracle dba 1024 Sep 9 02:55 arch_1_134.arc
-rw-r----- 1 oracle dba 29696 Sep 9 02:55 arch_1_133.arc
-rw-r----- 1 oracle dba 1024 Sep 9 02:55 arch_1_132.arc
-rw-r----- 1 oracle dba 4715008 Sep 9 02:55 arch_1_131.arc
-rw-r----- 1 oracle dba 1024 Sep 9 03:07 arch_1_135.arc
-rw-r----- 1 oracle dba 1024 Sep 9 03:11 arch_1_136.arc
-rw-r----- 1 oracle dba 58880 Sep 9 03:39 arch_1_137.arc
-rw-r----- 1 oracle dba 1024 Sep 9 03:39 arch_1_138.arc
-rw-r----- 1 oracle dba 1024 Sep 9 03:52 arch_1_139.arc
-rw-r----- 1 oracle dba 1024 Sep 9 23:01 arch_1_141.arc
-rw-r----- 1 oracle dba 1024 Sep 9 23:01 arch_1_140.arc
-rw-r----- 1 oracle dba 1024 Sep 9 23:04 arch_1_142.arc
-rw-r----- 1 oracle dba 1024 Sep 9 23:04 arch_1_143.arc
-rw-r----- 1 oracle dba 1024 Sep 10 16:20 arch_1_144.arc
-rw-r----- 1 oracle dba 1024 Sep 10 16:34 arch_1_145.arc
-rw-r----- 1 oracle dba 19968 Sep 10 17:54 arch_1_146.arc
-rw-r----- 1 oracle dba 1024 Sep 10 18:07 arch_1_147.arc
-rw-r----- 1 oracle dba 10240 Sep 10 18:35 arch_1_148.arc
drwxr-xr-x 4 oracle dba 512 Sep 11 18:38 ../
-rw-r----- 1 oracle dba 1024 Sep 11 19:23 arch_1_149.arc
drwxr-xr-x 2 oracle dba 512 Sep 11 19:23 ./
sf49er.bikle.com 32 %
This is good news, the log file did get propagated. Next, we check
the contents of the the standby database. According to the
documentation we should expect that the read-only mode would block the
application of arch_1_149.arc:
19:20:57 SQL@BBIKLE> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
MAX(SEQUENCE#)
--------------
148
19:28:18 SQL@BBIKLE>
As we can see this is indeed the case; arch_1_149.arc is blocked by
read-only mode.
Next, we try to transform the standby database back into Managed Recovery mode:
19:35:05 SQL@BBIKLE> RECOVER MANAGED STANDBY DATABASE
At this point, the terminal session now "locks" which by now is
expected behavior. We then use terminal session two to see if
arch_1_149.arc has been applied:
19:36:57 SQL@BBIKLE> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
MAX(SEQUENCE#)
--------------
149
19:37:29 SQL@BBIKLE>
This is very good news; we just demonstrated moving a database from
Managed Recovery mode, to Read-Only mode, and then back to Managed
Recovery mode. This exercise required only four simple SQL commands:
RECOVER MANAGED STANDBY DATABASE (This "locks" the calling terminal session)
RECOVER MANAGED STANDBY DATABASE CANCEL (Issue this from another terminal session)
ALTER DATABASE OPEN READ ONLY; (We are now free to query the standby db)
RECOVER MANAGED STANDBY DATABASE (This "locks" the calling terminal session)