activation.html
09-12-2001 Bikle
-----------------
This page contains a demonstration of standby database "activation".
Some DBAs will refer to activation as "failover". In simple terms,
the task of activation is the opening of the standby database for
write activities.
Keep in mind that activation will trigger an implicit RESETLOGS
operation on the standby database. In most scenarios this means the
DBA will be in a precarious state. The DBA should immediately place a
safety net under the activated standby database by taking a backup of
the datafiles. This backup coupled with subsequent archived redolog
files will put the activated standby database in a recoverable state
should any datafiles get munched at a later time. After the backup
has been taken, the DBA could further bolster the database against
disaster by designating the activated standby database to be a primary
database and then build another standby database and place it in
managed recovery mode.
A summary of the standby database activation demonstration is
displayed below:
-Flush all transactions out of the primary database into archived redologs
-Ensure all archived redologs have been transferred to the standby database
-Ensure all archived redologs have been applied to the standby database
-Cancel the standby database managed recovery mode
-Shutdown the standby database
-Startup the standby database NOMOUNT
-Issue command: ALTER DATABASE MOUNT STANDBY DATABASE;
-Issue command: ALTER DATABASE ACTIVATE STANDBY DATABASE;
-Issue command: ALTER DATABASE READ WRITE;
-Some loose ends
The details related to the above summary are displayed below:
-Flush all transactions out of the primary database into archived redologs
We demonstrate the flushing of transactions out of the primary
database into archived redologs in the screen dump below:
$ sqia
SET SQLPROMPT SQL@ABIKLE>
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Sep 12 22:26:33 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
22:26:33 SQL> SET SQLPROMPT SQL@ABIKLE>
22:26:40 SQL@ABIKLE> ALTER SYSTEM SWITCH LOGFILE;
System altered.
22:27:01 SQL@ABIKLE> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
MAX(SEQUENCE#)
--------------
150
22:27:13 SQL@ABIKLE>exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
-Ensure all archived redologs have been transferred to the standby database
-Ensure all archived redologs have been applied to the standby database
Now we login to the standby database:
$ sqib
SET SQLPROMPT SQL@BBIKLE>
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Sep 12 22:27:41 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
22:27:41 SQL> SET SQLPROMPT SQL@BBIKLE>
22:27:48 SQL@BBIKLE> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
MAX(SEQUENCE#)
--------------
150
22:28:02 SQL@BBIKLE>
Notice that we skipped a step; since archived redolog #150 was
applied, obviously it had been transferred. We are now confident that
we have transferred all transactions out of the primary database into
the standby database.
-Cancel the standby database managed recovery mode
The screen dump below demonstrates how to cancel the standby database
managed recovery mode:
22:38:59 SQL@BBIKLE> RECOVER MANAGED STANDBY DATABASE CANCEL
Media recovery complete.
22:40:03 SQL@BBIKLE>
-Shutdown the standby database
This is a simple step:
22:40:03 SQL@BBIKLE> SHUTDOWN ABORT
ORACLE instance shut down.
22:40:34 SQL@BBIKLE>
-Startup the standby database NOMOUNT
This too is a simple step:
22:40:34 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
22:41:08 SQL@BBIKLE>
-Issue command: ALTER DATABASE MOUNT STANDBY DATABASE;
This is another simple step:
22:41:08 SQL@BBIKLE> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
22:42:52 SQL@BBIKLE>
-Issue command: ALTER DATABASE ACTIVATE STANDBY DATABASE;
This is the key step:
22:42:52 SQL@BBIKLE> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database altered.
22:43:31 SQL@BBIKLE>
-Issue command: ALTER DATABASE READ WRITE;
This should be a simple step. I think that instead of jumping straight to
ALTER DATABASE READ WRITE, I should have bounced the database. I bumped into
a couple of failed commands and then bounced the database. Bouncing the
database was my key to success here:
22:43:31 SQL@BBIKLE> ALTER DATABASE READ WRITE;
ALTER DATABASE READ WRITE
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
22:44:27 SQL@BBIKLE> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01507: database not mounted
22:45:52 SQL@BBIKLE> SHUTDOWN ABORT
ORACLE instance shut down.
22:46:17 SQL@BBIKLE> STARTUP
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
Database mounted.
Database opened.
22:46:45 SQL@BBIKLE>
So this was a simple demonstration. The main lesson we learned is
that the last step should be: SHUTDOWN/STARTUP rather than:
ALTER DATABASE READ WRITE.
This was a gentle lesson compared some others we encountered at the
school of hard knocks.
-Some loose ends
According to the Oracle documentation, a RESETLOGS operation should
have occurred. We check this via a simple pair of SQL commands:
22:46:45 SQL@BBIKLE> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
MAX(SEQUENCE#)
--------------
22:57:08 SQL@BBIKLE> ALTER SYSTEM SWITCH LOGFILE;
System altered.
22:57:28 SQL@BBIKLE> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
MAX(SEQUENCE#)
--------------
1
22:57:37 SQL@BBIKLE>
Next, we place our attention back on the primary database. What happens
if we force a logfile switch there? Lets' find out:
23:02:54 SQL@ABIKLE> EXIT
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
$ BBIKLE.csh
set prompt="`/usr/bin/hostname` $ORACLE_SID ! % "
sf49er.bikle.com 1 % set prompt="`/usr/bin/hostname` $ORACLE_SID ! % "
sf49er.bikle.com BBIKLE 2 % grep dest $ORACLE_HOME/dbs/init$ORACLE_SID.ora
# 09-08-2001 bikle standby_archive_dest = /u2/oracle/data/BBIKLE/archstandby
log_archive_dest_1 = "location=/u2/oracle/data/BBIKLE/arch"
standby_archive_dest = /u2/oracle/data/BBIKLE/archstandby
background_dump_dest = /u2/oracle/admin/BBIKLE/bdump
core_dump_dest = /u2/oracle/admin/BBIKLE/cdump
user_dump_dest = /u2/oracle/admin/BBIKLE/udump
sf49er.bikle.com BBIKLE 4 % ls -latr /u2/oracle/data/ABIKLE/arch|tail -9
-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
-rw-r----- 1 oracle dba 1024 Sep 12 22:27 arch_1_150.arc
-rw-r----- 1 oracle dba 1024 Sep 12 23:02 arch_1_151.arc <<-- latest arch redo
drwxr-xr-x 2 oracle dba 1024 Sep 12 23:02 ./
sf49er.bikle.com BBIKLE 5 % ls -latr /u2/oracle/data/BBIKLE/archstandby|tail -9
-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
-rw-r----- 1 oracle dba 1024 Sep 12 22:27 arch_1_150.arc
drwxr-xr-x 2 oracle dba 512 Sep 12 22:27 ./
sf49er.bikle.com BBIKLE 6 %
sf49er.bikle.com BBIKLE 6 %
As we can see, the primary database had no problem generating an
archived redolog file. Make note, however, that the primary database
recognized that the standby database had been "activated" (or perhaps
more accurately, "disappeared"). We can see this is true because archived
redolog#151 did not appear in the directory specified by the
standby database parameter: STANDBY_ARCHIVE_DEST.
That wraps up this demonstration. The next demonstration will show us
how to transform this recently activated database into a primary
database which is a source of data for a standby database.