As we know Oracle 11g Snapshot Standby Database (Read- Write
mode for standby db), a new feature in 11g. The same can be achieved in Oracle
10g as well with the help of flashback feature. Standby database from
read/write mode back to its original state using restore point.
=================> Primary Database Details
<===================
C:\Users\Rao>set
oracle_sid=db
C:\Users\Rao>sqlplus
SQL*Plus:
Release 10.2.0.1.0 - Production on Wed May 4 10:08:52 2011
Copyright
(c) 1982, 2005, Oracle. All rights reserved.
Enter
user-name: sys as sysdba
Enter
password:
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With
the Partitioning, OLAP and Data Mining options
10:09:13
sys@ORCL>@dt
STARTTIME
SYSDATETIME DB_NAME
LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS HOST_NAME
VERSION
--------------------
-------------------- -------- ---------- ---------- -------------
----------------- --------- ----------
03-05-2011
17:08:28 04-05-2011 10:09:20 DB
ARCHIVELOG READ WRITE PRIMARY TO
STANDBY HOME-PC
10.2.0.1.0
Elapsed:
00:00:00.62
10:09:21
sys@ORCL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
423
Elapsed:
00:00:00.09
10:10:00
sys@ORCL>
================> Physical Standby Details
<=================
C:\Users\Rao>set
oracle_sid=dbdr
C:\Users\Rao>sqlplus
SQL*Plus:
Release 10.2.0.1.0 - Production on Wed May 4 10:09:01 2011
Copyright
(c) 1982, 2005, Oracle. All rights reserved.
Enter
user-name: sys as sysdba
Enter
password:
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With
the Partitioning, OLAP and Data Mining options
10:09:11
dbdr >@dt
STARTTIME
SYSDATETIME DB_NAME
LOG_MODE OPEN_MODE DATABASE_ROLE
SWITCHOVER_STATUS HOST_NAME VERSION
--------------------
-------------------- -------- ---------- ---------- ----------------
----------------- --------- ----------
03-05-2011
18:07:02 04-05-2011 10:09:18 DB
ARCHIVELOG MOUNTED PHYSICAL STANDBY NOT
ALLOWED HOME-PC 10.2.0.1.0
Elapsed:
00:00:00.97
10:09:18
dbdr >select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
--------------
423
Elapsed:
00:00:01.34
10:10:11
dbdr >
Now lets start with the testing.
Step 1 – In Standby database
-> Setting up a flash recovery area.
Enabling Flash Recovery Area ( FRA ) in the physical standby
Database.
C:\Users\Rao>set
oracle_sid=dbdr
C:\Users\Rao>sqlplus
SQL*Plus:
Release 10.2.0.1.0 - Production on Wed May 4 10:09:01 2011
Copyright
(c) 1982, 2005, Oracle. All rights reserved.
Enter
user-name: sys as sysdba
Enter
password:
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With
the Partitioning, OLAP and Data Mining options
12:02:35
dbdr >sho parameter recovery_file
NAME
TYPE VALUE
------------------------------------
----------- ------------------------------
db_recovery_file_dest
string
db_recovery_file_dest_size
big integer 0
12:02:42
dbdr >ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G;
System
altered.
Elapsed:
00:00:00.07
12:04:28
dbdr >ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='C:\DBDR\FRA\DBDR';
System
altered.
Elapsed:
00:00:00.25
12:05:39
dbdr >sho parameter RECOVERY_FILE
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_recovery_file_dest string C:\DBDR\FRA\DBDR
db_recovery_file_dest_size big integer 5G
12:05:39
dbdr >select process from v$managed_standby;
PROCESS
---------
ARCH
ARCH
RFS
MRP0
Elapsed:
00:00:00.43
-> Cancel Redo Apply and create a guaranteed restore point.
12:06:13
dbdr >ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database
altered.
Elapsed:
00:00:05.16
12:08:08
dbdr >select process from v$managed_standby;
PROCESS
---------
ARCH
ARCH
RFS
Elapsed:
00:00:00.01
12:08:15
dbdr >CREATE RESTORE POINT Standby_Restore_Point GUARANTEE FLASHBACK
DATABASE;
Restore
point created.
Elapsed:
00:00:01.78
in alert log
Wed May 04 12:08:28 2011
db_recovery_file_dest_size of 5120 MB is 0.15% used. This is a user-specified
limit on the amount of space that will be used by this database for
recovery-related files, and does not reflect the amount of space available in
the underlying filesystem or ASM diskgroup. Allocated 3981204 bytes in shared
pool for flashback generation buffer Starting background process RVWR RVWR
started with pid=16, OS id=1760
Wed May 04 12:08:29 2011 Created guaranteed
restore point STANDBY_RESTORE_POINT
Crosscheck for details of restore point, its scn and time stamp
run
12:08:29
dbdr >col NAME for a30
12:08:37
dbdr >col to_char(SCN) for a20
12:08:37
dbdr >col TIME for a50
12:08:37
dbdr >select NAME,to_char(SCN),TIME from v$restore_point;
NAME TO_CHAR(SCN) TIME
------------------------------
-------------------- -------------------------------------------
STANDBY_RESTORE_POINT 25739221855 04-MAY-11 12.08.29.000000000 PM
Elapsed:
00:00:00.09
12:08:44
dbdr >
Step 2 – In Primary Database
-> On the primary database, switch logs so the SCN of the
restore point will be archived on the physical standby database. When using
standby redo log files, this step is essential to ensure the database can be
properly flashed back to the restore point.
12:02:32
sys@ORCL>ALTER SYSTEM ARCHIVE LOG CURRENT;
System
altered.
Elapsed:
00:00:00.71
12:12:12
sys@ORCL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
424
Elapsed:
00:00:00.11
12:12:21
sys@ORCL>
-> Defer log archive destinations at primary database
pointing to the standby which will be activated.
12:12:21
sys@ORCL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=DEFER;
System
altered.
Elapsed:
00:00:00.14
12:12:42
sys@ORCL>sho parameter LOG_ARCHIVE_DEST_STATE_4;
NAME TYPE VALUE
------------------------------------
----------- --------------------------
log_archive_dest_state_4 string DEFER
12:12:47
sys@ORCL>
Step 3 – In Standby database
-> Activating the physical standby database:
12:08:44
dbdr >ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database
altered.
Elapsed:
00:00:03.20
12:13:17
dbdr >
Check the cotnrolfile status this will be changed from Standby
to Current
12:13:17
dbdr >select CONTROLFILE_TYPE from v$database;
CONTROL
-------
CURRENT
Elapsed:
00:00:00.06
12:14:30
dbdr >
Open the the physical standby database.
12:14:30
dbdr >ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Database
altered.
Elapsed:
00:00:00.14
12:14:48
dbdr >ALTER DATABASE OPEN;
Database
altered.
Elapsed:
00:00:19.80
12:15:17
dbdr >
Step 4 – In Standby database
So, once the standby has been activated, you can perform the
activity for which it was activated. Remember to save your data. As once the
database is flashback, the database will be restored back to the restored
point. All data will then be lost.
For example :
C:\Users\Rao>set
oracle_sid=dbdr
C:\Users\Rao>sqlplus
SQL*Plus:
Release 10.2.0.1.0 - Production on Wed May 4 12:16:38 2011
Copyright
(c) 1982, 2005, Oracle. All rights reserved.
Enter
user-name: test
Enter
password:
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With
the Partitioning, OLAP and Data Mining options
12:16:41
dbdr >select table_name from user_tables;
TABLE_NAME
------------------------------
TB
Elapsed:
00:00:00.40
12:16:48
dbdr >create table tab (id varchar2 (10));
Table
created.
Elapsed:
00:00:00.68
12:16:54
dbdr >insert into tab values('A0001');
1
row created.
Elapsed:
00:00:00.07
12:16:59
dbdr >commit;
Commit
complete.
Elapsed:
00:00:00.00
12:17:04
dbdr >
12:17:04
dbdr >select * from tab;
ID
----------
A0001
Elapsed:
00:00:00.09
12:18:33
dbdr >
Step 5 – In standby database
-> Now reverting back the active standby database to Physical
standby database
Mount the database. We
need to go for startup mount
force option. If we try with “alter database mount standby database”,
we will receive following error
ERROR at line 1:
ORA-00750: database has been previously mounted or dismounted.
ORA-00750: database has been previously mounted or dismounted.
12:20:53
dbdr >STARTUP MOUNT FORCE;
ORA-32004:
obsolete and/or deprecated parameter(s) specified
ORACLE
instance started.
Total
System Global Area 167772160 bytes
Fixed
Size
1247900 bytes
Variable
Size
67110244 bytes
Database
Buffers 96468992
bytes
Redo
Buffers
2945024 bytes
Database
mounted.
Flashback the database to restore point.
12:21:17
dbdr >FLASHBACK DATABASE TO RESTORE POINT Standby_Restore_Point;
FLASHBACK
DATABASE TO RESTORE POINT Standby_Restore_Point
*
ERROR
at line 1:
ORA-38770:
FLASHBACK DATABASE failed during recovery.
ORA-00279:
change 25739221856 generated at 05/04/2011 11:55:34 needed for thread 1
ORA-00289:
suggestion : C:\DBDR\ARCHIVE_DBDR\ARC_0741355170_00424_001
ORA-00280:
change 25739221856 for thread 1 is in sequence #424
Elapsed:
00:00:04.61
You will receive this error, if the archive log were not shipped
properly at step 2. In that case, manually copy the archive log to standby
location from primary location.
12:21:53
dbdr >FLASHBACK DATABASE TO RESTORE POINT Standby_Restore_Point;
Flashback
complete.
Elapsed:
00:00:03.28
12:28:02
dbdr >
In alert log
Wed May 04 12:27:59 2011 FLASHBACK DATABASE TO
RESTORE POINT Standby_Restore_Point Wed May 04 12:28:00 2011 Flashback Restore
Start Flashback Restore Complete Flashback Media Recovery Start Flashback Media
Recovery Log C:\DBDR\ARCHIVE_DBDR\ARC_0741355170_00424_001 Wed May 04 12:28:01
2011 Incomplete Recovery applied until change 25739221856 Flashback Media
Recovery Complete Completed: FLASHBACK DATABASE TO RESTORE POINT
Standby_Restore_Point
Checking the controlfile status. It will be now backup
controlfile.
12:28:02
dbdr >select controlfile_type from v$database;
CONTROL
-------
BACKUP
Elapsed:
00:00:00.04
Convert to Standby database
12:30:05
dbdr >ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database
altered.
Elapsed:
00:00:01.20
In alert log
Wed May 04 12:30:41 2011 ALTER DATABASE
CONVERT TO PHYSICAL STANDBY Wed May 04 12:30:42 2011 Clearing standby
activation ID 1506990492 (0x59d2d99c) The primary database controlfile was
created using the 'MAXLOGFILES 16' clause. There is space for up to 13 standby
redo logfiles Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database: ALTER DATABASE ADD
STANDBY LOGFILE 'srl1.f' SIZE 10485760; ALTER DATABASE ADD STANDBY LOGFILE
'srl2.f' SIZE 10485760; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE
10485760; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 10485760; Completed:
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
12:30:43
dbdr >STARTUP MOUNT FORCE;
ORA-32004:
obsolete and/or deprecated parameter(s) specified
ORACLE
instance started.
Total
System Global Area 167772160 bytes
Fixed
Size
1247900 bytes
Variable
Size
67110244 bytes
Database
Buffers 96468992
bytes
Redo
Buffers
2945024 bytes
Database
mounted.
12:31:14
dbdr >ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database
altered.
Elapsed:
00:00:10.51
12:31:27
dbdr >select controlfile_type from v$database;
CONTROL
-------
STANDBY
Elapsed:
00:00:00.21
12:31:29
dbdr >
Step 6 – In standby database
Bringing the standby database in managed recovery mode. If there
is any archive gap, all missing archived redo log will be applied.
12:31:29
dbdr >recover managed standby database disconnect from session;
ORA-01153:
an incompatible media recovery is active
12:33:24
dbdr >select process from v$managed_standby;
PROCESS
---------
ARCH
ARCH
MRP0
Elapsed:
00:00:00.00
12:33:32
dbdr >
Step 7 – In Primary database
Enable archiving to the physical standby database:
12:12:47
sys@ORCL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System
altered.
Elapsed:
00:00:00.10
12:33:47
sys@ORCL>ALTER SYSTEM ARCHIVE LOG CURRENT;
System
altered.
Elapsed:
00:00:00.95
12:33:55
sys@ORCL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
425
Elapsed:
00:00:00.09
12:34:53
sys@ORCL>
Step 8 – In Standby database
12:33:32
dbdr >select process from v$managed_standby;
PROCESS
---------
ARCH
ARCH
MRP0
RFS
Elapsed:
00:00:00.01
12:36:30
dbdr >select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
--------------
425
Elapsed:
00:00:00.39
12:36:40
dbdr >
Step 9 – In Standby database, Test
Open the database in Read only mode and checking whether the
activity performed exists, which should not be.
12:36:40
dbdr >ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database
altered.
Elapsed:
00:00:04.31
12:38:25
dbdr >ALTER DATABASE OPEN READ ONLY;
Database
altered.
Elapsed:
00:00:07.54
12:38:46
dbdr >conn test/test
Connected.
12:38:50
dbdr >select * from tab;
select
* from tab
*
ERROR
at line 1:
ORA-00942:
table or view does not exist
Dropping the restore point
12:41:33
dbdr >conn sys as sysdba
Enter
password:
Connected.
12:53:50
dbdr >STARTUP FORCE MOUNT;
ORA-32004:
obsolete and/or deprecated parameter(s) specified
ORACLE
instance started.
Total
System Global Area 167772160 bytes
Fixed
Size
1247900 bytes
Variable
Size
67110244 bytes
Database
Buffers 96468992
bytes
Redo
Buffers
2945024 bytes
Database
mounted.
12:54:30
dbdr >ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database
altered.
Elapsed:
00:00:07.54
12:54:37
dbdr >DROP RESTORE POINT Standby_Restore_Point;
Restore
point dropped.
Elapsed:
00:00:01.33
12:54:45
dbdr >
Note:- Try everything at your own risk, if any thing happens wrong with your database so blogger will not be responsible.
What an amazing blog. I have found this blog very interesting because I have gotten the most read information. This blog help me out otherwise I don’t know how much time I have to spend for getting right information.
ReplyDelete