Developers always desire the most actual with production data in their developer's database.
Let me show how it could be achieved with Oracle Data Guard.
As in my previous post:
primary host - prod2
standby host - ilstal
primary database - T
standby database - TSTB
The both nods running Oracle 10.2.0.5 on AIX 5.3
Flashback is turned on on both databases, primary and standby.
1. I created for testing purpose table scott.t
prod2 $ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 17 10:12:09 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc scott.t;
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(100)
SQL> select * from scott.t;
no rows selected
SQL> insert into scott.t values('record inserted into primary database');
1 row created.
SQL> commit;
Commit complete.
2. Now we have to disable standby database (TSTB) from Data Guard
prod2 $ dgmgrl sys/password@T DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - 64bit Production Copyright (c) 2000, 2005, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> show configuration; Configuration Name: DG_T Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: T - Primary database TSTB - Physical standby database Current status for "DG_T": SUCCESS DGMGRL> disable database 'TSTB'; Disabled. DGMGRL> exit
3. Important step. We want to make our standby database as primary opened for read/write operations during working day.
But at the same time we want to be able to put this database back into standby role. The core thing is Flashback Restore Point.
ilstal $ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 17 09:41:51 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 2098312 bytes
Variable Size 167775096 bytes
Database Buffers 436207616 bytes
Redo Buffers 6287360 bytes
Database mounted.
SQL>
-- here we create guarantee restore point and give it name "stdb_became_rwdb"
SQL> create restore point stdb_became_rwdb guarantee flashback database;
Restore point created.
SQL>
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database altered.
-- open for read/write
SQL> alter database open;
Database altered.
SQL>
-- test our developer's database, make some DML-s
SQL> select * from scott.t;
A
--------------------------------------------------------------------------------
record inserted into primary database
SQL> update scott.t set a = 'record updated in the standby database';
1 row updated.
SQL> commit;
Commit complete.
SQL> insert into scott.t values('record inserted into standby database');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scott.t;
A
--------------------------------------------------------------------------------
record inserted into standby database
record updated in the standby database
Here let me show that our production primary database (T) works fine
prod2 $ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 17 09:51:43 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into scott.t values('another record inserted into primary database');
1 row created.
SQL> select * from scott.t;
A
--------------------------------------------------------------------------------
another record inserted into primary database
record inserted into primary database
4. Now assume that working day ending... and we need to put our developer's database (TSTB) back to standby role for main production database (T)
ilstal $ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 17 09:49:57 2011 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 2098312 bytes Variable Size 167775096 bytes Database Buffers 436207616 bytes Redo Buffers 6287360 bytes Database mounted. -- remember restore point we have created? -- Here we back database into time before it has become developer's database SQL> flashback database to restore point stdb_became_rwdb; Flashback complete. SQL> alter database convert to physical standby; Database altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 2098312 bytes Variable Size 167775096 bytes Database Buffers 436207616 bytes Redo Buffers 6287360 bytes Database mounted. SQL> prod2 $ dgmgrl sys/password@T DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - 64bit Production Copyright (c) 2000, 2005, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> enable database 'TSTB'; Enabled. DGMGRL> show database 'TSTB'; Database Name: TSTB Role: PHYSICAL STANDBY Enabled: YES Intended State: ONLINE Instance(s): TSTB Current status for "TSTB": SUCCESS DGMGRL>
Let's check that primary database have only its data.
prod2 $ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 17 10:01:16 2011 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from scott.t; A -------------------------------------------------------------------------------- another record inserted into primary database record inserted into primary database SQL>
No comments:
Post a Comment