Sunday, 27 November 2011
Friday, 14 October 2011
How to move the OCR diskgroup contents to another group online
How to move the OCR diskgroup contents to another group online in a RAC enviornment
Sometime due to some reason we may need to move the ocr contents to new diskgroup , the reason could be like Asm lable is wrong,diskmapper is wrong,disk is having problem,disk location need to be changed,
1. Check the current Disk Group name
[root@ocservera 11.2.0.2]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3284
Available space (kbytes) : 258836
ID : 1837802780
Device/File Name : +OCR
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
2. Create another diskgroup
CREATE DISKGROUP OCR1
external redundancy
disk 'ORCL:ASM_DISK_0011',
'ORCL:ASM_DISK_0012',
attribute 'compatible.asm' = '11.2',
'compatible.advm' = '11.2'
/
Diskgroup created.
3. Verify through asmcmd or sqlplus
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 3057 1372 563 404 0 Y OCR/
MOUNTED NORMAL N 512 4096 1048576 558030 556983 349 278317 0 N OCR1/
ASMCMD> exit
4. Add the OCR to the new DISK group
[root@ocservera 11.2.0.2]# ocrconfig -add +OCR1
5 . Verify if new disk has been added.
[root@ocservera 11.2.0.2]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3304
Available space (kbytes) : 258816
ID : 1837802780
Device/File Name : +OCR
Device/File integrity check succeeded
Device/File Name : +OCR1
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
6. Delete the ocr from the old diskgroup
[root@ocservera 11.2.0.2]# ocrconfig -delete +OCR
7. Check ocr is moved to new diskgroup
[root@ocservera 11.2.0.2]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3304
Available space (kbytes) : 258816
ID : 1837802780
Device/File Name : +OCR1
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
8. Move the voting disk from the current diskgroup to new diskgroup
8.1 Verify the current location
crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE a63ccacdc0b84f6cbfeeeee913657936 (ORCL:ASM_OCR_0001P1) [OCR]
2. ONLINE 15f320594b7d4fbdbf6fb952b3665683 (ORCL:ASM_OCR_0002P1) [OCR]
3. ONLINE 1e80bcca0d994fc5bfc403e9ce01b951 (ORCL:ASM_OCR_0003P1) [OCR]
8.2 replcate the disk group.
crsctl replace votedisk +OCR1
Successful addition of voting disk 1a8593de9c654f77bf7d55440dc20ce0.
Successful addition of voting disk 49e5d45ebe874f7fbfbc514fa51cc641.
Successful addition of voting disk 248240fba9784f57bf2021416760d415.
Successful deletion of voting disk a63ccacdc0b84f6cbfeeeee913657936.
Successful deletion of voting disk 15f320594b7d4fbdbf6fb952b3665683.
Successful deletion of voting disk 1e80bcca0d994fc5bfc403e9ce01b951.
Successfully replaced voting disk group with +OCR1.
CRS-4266: Voting file(s) successfully replaced
8.3 verify the current location
[oragrid@ocservera ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 1a8593de9c654f77bf7d55440dc20ce0 (ORCL:ASM_FRA_0001) [OCR1]
2. ONLINE 49e5d45ebe874f7fbfbc514fa51cc641 (ORCL:ASM_FRA_0002) [OCR1]
3. ONLINE 248240fba9784f57bf2021416760d415 (ORCL:ASM_FRA_0003) [OCR1]
Located 3 voting disk(s).
8.4 Verify using asmcmd if voting disk is 'Y' against the disk group
[oragrid@ocservera ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 3057 1468 531 468 0 N OCR/
MOUNTED NORMAL N 512 4096 1048576 558030 556342 563 277889 0 Y OCR1/
9. move the spfile to new diskgroup using spmove
9.1 create spfile directory in the new diskgroup
asmcmd> mkdir +OCR1/ocserver-cl/asmparameterfile
9.2 spmove will copy the current spfile to the new location and update the gpn profile for next startup. This command throw erros as not all alterations performed as current spfile is being used, in the next startup
the new spfile will be effective and we can manually remove the old spfile.
ASMCMD> spmove +OCR/ocserver-cl/asmparameterfile/registry.253.762275705 +OCR1/ocserver-cl/asmparameterfile/spfileASM.ora
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/asm/asmparameterfile/REGISTRY.253.715881237' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute
Alternatively we can do this using the below steps Ref:How to move ASM spfile to a different disk group [ID 1082943.1
To move ASM spfile to another disk group, either make use of intermediate pfile:
Create intermediate pfile from the current spfile
Create spfile in a new disk group from the intermediate pfile
Restart the HA stack to verify that ASM starts up fine with moved spfile
Remove the original spfile
or use 'asmcmd spcopy' command
Copy spfile with -u option - to update GPnP profile in RAC environment
Restart the HA stack to verify that ASM starts up fine with copiedspfile
Remove the original spfile
9.3 . Restart the HA stack to verify that ASM starts up fine with moved spfile
crsctl stop cluster -all
crsctl start cluster -all
9.4. Verify spfile has been changed
[oragrid@ocservera ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 26 15:12:42 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn / as sysasm
Connected.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +OCR1/ocserver-cl/asmparamete
rfile/spfileasm.ora
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
10. Dismount the ocr diskgroup from all the instances
SQL> alter diskgroup ocr mount;
Diskgroup altered.
if the disk group is used or mounted by any of the process/instance we may get the below error
ASMCMD> dropdg -r -f OCR
ORA-15039: diskgroup not dropped
ORA-15073: diskgroup OCR is mounted by another ASM instance (DBD ERROR: OCIStmtExecute)
11. Beside spfile,ocr,votingdisk if any of the files are created, please remove the files manualy using asmcmd rm command or drop/move the files from the respective database
12. Now we are ready to drop the diskgroup
dropdg -r -f OCR
13. Verify all are intact.
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 558030 556342 563 277889 0 Y OCR1/
Tuesday, 11 October 2011
Flashback database on a RAC database with rac standby setup
Flashback Database Test case in a RAC with Data guard Setup
This has been tested in a 8 Node RAC with 8 node Standby RAC database.
1. Verify if flashback and force_logging database is enabled on both primary and standby.
Primary:
select flashback_on,force_logging from v$database;
[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:23:58 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON FOR
------------------ ---
YES YES
Stanbdy:
select flashback_on,force_logging from v$database;
[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:23:58 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON FOR
------------------ ---
YES YES
SQL>
2. Verify if DG configuration is fine through dgmgrl
If any issue with any of the configuration, pelase fix the issue before create restore point or the release
DGMGRL> show configuration
DGMGRL> show database RACFDP
DGMGRL> show database RACFDP_sb
DGMGRL> show configuration
Configuration - RACFDP_dataguard
Protection Mode: MaxAvailability
Databases:
RACFDP - Primary database
RACFDP_sb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database RACFDP
Database - RACFDP
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
RACFDP1
RACFDP2
RACFDP3
RACFDP4
RACFDP5
RACFDP6
RACFDP7
RACFDP8
Database Status:
SUCCESS
DGMGRL> show database RACFDP_sb
Database - RACFDP_sb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
RACFDP_SB1
RACFDP_SB2
RACFDP_SB3
RACFDP_SB4
RACFDP_SB5 (apply instance)
RACFDP_SB6
RACFDP_SB7
RACFDP_SB8
Database Status:
SUCCESS
3. Create restore points on Both primary and stanbdy
3.1 First create restore point in the standby database. To create restore point in the standby we need to disable the log apply and create the restore point and then enable back the log shipping
3.1.1 Disable log apply using dgmgrl
edit database RACFDP_sb SET STATE='APPLY-OFF';
DGMGRL> edit database RACFDP_sb SET STATE='APPLY-OFF';
Succeeded.
DGMGRL>
DGMGRL> show database RACFDP_sb
Database - RACFDP_sb
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
RACFDP_SB1
RACFDP_SB2
RACFDP_SB3
RACFDP_SB4
RACFDP_SB5 (apply instance)
RACFDP_SB6
RACFDP_SB7
RACFDP_SB8
Database Status:
SUCCESS
DGMGRL> show configuration
Configuration - RACFDP_dataguard
Protection Mode: MaxAvailability
Databases:
RACFDP - Primary database
RACFDP_sb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
3.1.2 Create restore point:
CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;
[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:39:43 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;
Restore point created.
SQL>
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
21015113
3.1.3 Enable log apply on standby server
edit database RACFDP_sb SET STATE='APPLY-ON';
DGMGRL> edit database RACFDP_sb SET STATE='APPLY-ON';
Succeeded.
DGMGRL> show database RACFDP_sb
Database - RACFDP_sb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
RACFDP_SB1
RACFDP_SB2
RACFDP_SB3
RACFDP_SB4
RACFDP_SB5 (apply instance)
RACFDP_SB6
RACFDP_SB7
RACFDP_SB8
Database Status:
SUCCESS
DGMGRL> show configuration
Configuration - RACFDP_dataguard
Protection Mode: MaxAvailability
Databases:
RACFDP - Primary database
RACFDP_sb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
3.2 Now create restore point in the primary database.
CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;
[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:48:24 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;
Restore point created.
SQL>
3.3 verify if restore points are created from the primary ( node1)
SQL> select INST_ID,name from gv$restore_point;
INST_ID NAME
------- ------------------------------
1 FLASHBACK_TEST1
2 FLASHBACK_TEST1
6 FLASHBACK_TEST1
3 FLASHBACK_TEST1
5 FLASHBACK_TEST1
8 FLASHBACK_TEST1
7 FLASHBACK_TEST1
4 FLASHBACK_TEST1
8 rows selected.
4. Do the release – run @ primary database
The below scripts are just for testing purpose. We need to run the actual scripts
4A. Create user ,tables,insert,update and deletes
[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 07:51:17 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create user flashback_test identified by flashback_test;
User created.
SQL> grant dba to flashback_test;
Grant succeeded.
SQL> grant create session to flashback_test;
Grant succeeded.
SQL> conn flashback_test/flashback_test
Connected.
SQL> create table test1 as select * from dba_objects;
Table created.
SQL> insert into test1 values ( select * from dba_objects);
insert into test1 values ( select * from dba_objects)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> insert into test1 ( select * from dba_objects);
13717 rows created.
SQL> insert into test1 ( select * from dba_objects);
13717 rows created.
SQL> insert into test1 ( select * from dba_objects);
13717 rows created.
SQL> update test1 set object_name='null';
54868 rows updated.
SQL> rollback;
Rollback complete.
SQL> update test1 set object_name='null';
13717 rows updated.
SQL> commit;
Commit complete.
SQL> delete from test1;
13717 rows deleted.
SQL> rollback;
Rollback complete.
SQL> truncate table test1;
Table truncated.
SQL> exit
5. Flashback the primary and stanbdy databases
5. Flashback the primary database
5.1 Convert the DG mode to Maxperformance
EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
Succeeded.
DGMGRL> show configuration
Configuration - RACFDP_dataguard
Protection Mode: MaxPerformance
Databases:
RACFDP - Primary database
RACFDP_sb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
5.1 Disable log transport from primary, we need to convert the database to maxperformance to make
edit database RACFDP set state='TRANSPORT-OFF';
DGMGRL> edit database RACFDP set state='TRANSPORT-OFF';
Succeeded.
DGMGRL> show configuration
Configuration - RACFDP_dataguard
Protection Mode: MaxPerformance
Databases:
RACFDP - Primary database
RACFDP_sb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
5.3 Switch the logfiles from all primary instances
alter system switch logfile ( on all primary instances)
5.4 Disable log apply on stanbdy database
EDIT DATABASE RACFDP_sb SET STATE='APPLY-OFF';
DGMGRL> EDIT DATABASE RACFDP_sb SET STATE='APPLY-OFF';
Succeeded.
DGMGRL> show database RACFDP_sb
Database - RACFDP_sb
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 3 minutes 23 seconds
Real Time Query: OFF
Instance(s):
RACFDP_SB1
RACFDP_SB2
RACFDP_SB3
RACFDP_SB4
RACFDP_SB5 (apply instance)
RACFDP_SB6
RACFDP_SB7
RACFDP_SB8
Database Status:
SUCCESS
5.5 stop both primary and standby database.
[RACFDP1]oracle@primarydb1.ora.com$ srvctl stop database -d RACFDP
[RACFDP1]oracle@primarydb1.ora.com$ srvctl stop database -d RACFDP
[RACFDP1]oracle@primarydb1.ora.com$ srvctl status database -d RACFDP
Instance RACFDP1 is not running on node primarydb1
Instance RACFDP2 is not running on node primarydb2
Instance RACFDP3 is not running on node primarydb3
Instance RACFDP4 is not running on node primarydb4
Instance RACFDP5 is not running on node primarydb5
Instance RACFDP6 is not running on node primarydb6
Instance RACFDP7 is not running on node primarydb7
Instance RACFDP8 is not running on node primarydb8
[RACFDP1]oracle@primarydb1.ora.com$
[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl stop database -d RACFDP_SB
[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl stop database -d RACFDP_SB
[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl status database -d RACFDP_SB
Instance RACFDP_SB1 is not running on node standbydb1
Instance RACFDP_SB2 is not running on node standbydb2
Instance RACFDP_SB3 is not running on node standbydb3
Instance RACFDP_SB4 is not running on node standbydb4
Instance RACFDP_SB5 is not running on node standbydb5
Instance RACFDP_SB6 is not running on node standbydb6
Instance RACFDP_SB7 is not running on node standbydb7
Instance RACFDP_SB8 is not running on node standbydb8
5.6. Start the primary in mount mode from node1 (primarydb1.uk.db.com)
[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 11:35:37 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.6034E+10 bytes
Fixed Size 2229184 bytes
Variable Size 1.0402E+10 bytes
Database Buffers 5469372416 bytes
Redo Buffers 160735232 bytes
Database mounted.
5.7 Start the standby in mount mode from node1(standbydb1.uk.db.com)
[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 11:35:26 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.6034E+10 bytes
Fixed Size 2229184 bytes
Variable Size 1.0805E+10 bytes
Database Buffers 5066719232 bytes
Redo Buffers 160735232 bytes
Database mounted.
SQL>
5.8 Flashback primary database
flashback database to restore point FLASHBACK_TEST1;
SQL> flashback database to restore point FLASHBACK_TEST1;
Flashback complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
0
SQL>
5.9 open primary with resetlogs ( from node1 primarydb1.uk.db.com). It may sometime as it has to resetlogs all the redolog in all the threads
alter database open resetlogs;
SQL> alter database open resetlogs;
Database altered.
SQL>
5.10. flashback standby database from node1 (standbydb1.uk.db.com)
flashback database to restore point FLASHBACK_TEST1;
SQL> flashback database to restore point FLASHBACK_TEST1;
Flashback complete.
SQL>
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
21015113
5.11. Shutdown the primary database from node1 (primarydb1.uk.db.com)
[RACFDP1]oracle@primarydb1.uk.db.com
SQL> SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
5.11. Shutdown the standby from node1 (standbydb1.uk.db.com)
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
5.12 start both primary and standby databases
srvctl start database -d RACFDP
srvctl start database -d RACFDP_SB
[RACFDP1]oracle@primarydb1.ora.com$ srvctl start database -d RACFDP
[RACFDP1]oracle@primarydb1.ora.com$ srvctl status database -d RACFDP
Instance RACFDP1 is running on node primarydb1
Instance RACFDP2 is running on node primarydb2
Instance RACFDP3 is running on node primarydb3
Instance RACFDP4 is running on node primarydb4
Instance RACFDP5 is running on node primarydb5
Instance RACFDP6 is running on node primarydb6
Instance RACFDP7 is running on node primarydb7
Instance RACFDP8 is running on node primarydb8
[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl start database -d RACFDP_SB
[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl status database -d RACFDP_SB
Instance RACFDP_SB1 is running on node standbydb1
Instance RACFDP_SB2 is running on node standbydb2
Instance RACFDP_SB3 is running on node standbydb3
Instance RACFDP_SB4 is running on node standbydb4
Instance RACFDP_SB5 is running on node standbydb5
Instance RACFDP_SB6 is running on node standbydb6
Instance RACFDP_SB7 is running on node standbydb7
Instance RACFDP_SB8 is running on node standbydb8
5.13. check the status of both primary and stanbdy using crsctl to find if instance status in proper mode.
Primary:
ora.RACFDP.db
1 ONLINE ONLINE primarydb1 Open
2 ONLINE ONLINE primarydb2 Open
3 ONLINE ONLINE primarydb3 Open
4 ONLINE ONLINE primarydb4 Open
5 ONLINE ONLINE primarydb5 Open
6 ONLINE ONLINE primarydb6 Open
7 ONLINE ONLINE primarydb7 Open
8 ONLINE ONLINE primarydb8 Open
Standby:
ora.RACFDP_sb.db
1 ONLINE INTERMEDIATE standbydb1 Mounted (Closed)
2 ONLINE INTERMEDIATE standbydb2 Mounted (Closed)
3 ONLINE INTERMEDIATE standbydb3 Mounted (Closed)
4 ONLINE INTERMEDIATE standbydb4 Mounted (Closed)
5 ONLINE INTERMEDIATE standbydb5 Mounted (Closed)
6 ONLINE INTERMEDIATE standbydb6 Mounted (Closed)
7 ONLINE INTERMEDIATE standbydb7 Mounted (Closed)
8 ONLINE INTERMEDIATE standbydb8 Mounted (Closed)
6. Drop the Restore points
6.1 Drop the restore points from Primary database ( Node1 - primarydb1.ora.com)
drop restore point FLASHBACK_TEST1;
[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 12:07:54 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> drop restore point FLASHBACK_TEST1;
Restore point dropped.
SQL>
6.2 Drop the restore points from standby database ( Node1 - standbydb1.ora.com)
Please note if the log apply is enabled we need to disable it first to drop the restore point from standby. Since it has been disabled.so we are ignoring that step in this stage
drop restore point FLASHBACK_TEST1;
[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 12:11:08 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> drop restore point FLASHBACK_TEST1;
Restore point dropped.
7. Post Flashback activities
7.1 Verify current configuration
show configuration
show database RACFDP
show database RACFDP_sb
DGMGRL> show configuration
Configuration - RACFDP_dataguard
Protection Mode: MaxPerformance
Databases:
RACFDP - Primary database
RACFDP_sb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database RACFDP
Database - RACFDP
Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
RACFDP1
RACFDP2
RACFDP3
RACFDP4
RACFDP5
RACFDP6
RACFDP7
RACFDP8
Database Status:
SUCCESS
DGMGRL> show database RACFDP_sb
Database - RACFDP_sb
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
RACFDP_SB1 (apply instance)
RACFDP_SB2
RACFDP_SB3
RACFDP_SB4
RACFDP_SB5
RACFDP_SB6
RACFDP_SB7
RACFDP_SB8
Database Status:
SUCCESS
7.2 Enable the log transport from primary node1
edit database RACFDP set state = 'TRANSPORT-ON';
DGMGRL> edit database RACFDP set state = 'TRANSPORT-ON';
Succeeded.
DGMGRL> exit
7.3 Switch logfile
alter system switch logfile ( on all the primary instances)
7.4 Enable the log apply from node1 standby
edit database RACFDP_sb set state='APPLY-ON';
DGMGRL> edit database RACFDP_sb set state='APPLY-ON';
Succeeded.
7.5. Ensure the preferred instances
edit database 'RACFDP_sb' set property PreferredApplyInstance='RACFDP_sb5';
edit database 'RACFDP' set property PreferredApplyInstance='RACFDP5';
EDIT DATABASE RACFDP_sb SET STATE='ONLINE' WITH APPLY INSTANCE=RACFDP_SB5;
show database RACFDP_sb PreferredApplyInstance
show database RACFDP PreferredApplyInstance
DGMGRL> edit database 'RACFDP_sb' set property PreferredApplyInstance='RACFDP_sb5';
Property "preferredapplyinstance" updated
DGMGRL> edit database 'RACFDP' set property PreferredApplyInstance='RACFDP5';
Property "preferredapplyinstance" updated
DGMGRL> EDIT DATABASE RACFDP_sb SET STATE='ONLINE' WITH APPLY INSTANCE=RACFDP_SB5;
Succeeded.
DGMGRL> show database RACFDP_sb PreferredApplyInstance
PreferredApplyInstance = 'RACFDP_sb5'
DGMGRL> show database RACFDP PreferredApplyInstance
PreferredApplyInstance = 'RACFDP5'
7.6 Enable MAXAVAILABILITY Protection Mode
EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
7.7 Verify configuration through dgmgrl
show configuration
show database RACFDP
show database RACFDP_sb
DGMGRL> show configuration
Configuration - RACFDP_dataguard
Protection Mode: MaxPerformance
Databases:
RACFDP - Primary database
RACFDP_sb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database RACFDP
Database - RACFDP
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
RACFDP1
RACFDP2
RACFDP3
RACFDP4
RACFDP5
RACFDP6
RACFDP7
RACFDP8
Database Status:
SUCCESS
DGMGRL> show database RACFDP_sb
Database - RACFDP_sb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
RACFDP_SB1
RACFDP_SB2
RACFDP_SB3
RACFDP_SB4
RACFDP_SB5 (apply instance)
RACFDP_SB6
RACFDP_SB7
RACFDP_SB8
Database Status:
SUCCESS
DBAs can follow the below steps if they want to confirm that standby database flashback is working in a reda-only mode . Thi is not part of the actual restore steps.
A.Create a test table with current date as value - Before restore point
Do this before the restore point created
Create a test table to test if the flashback is working as expected. Infact this is not correct method of testing this feature.
At the primary database – From Node 1 (primarydb1.uk.db.com)
SQL> create table test_flashback ( dt date );
Table created.
SQL> insert into test_flashback values(sysdate);
1 row created.
SQL> commit;
Commit complete.
B.Verify the test_flashback table on both primary and standby – Before restore point/
Please note that to access the application tables in standby database we need to convert the standby database into read-only mode . We need to bounce the standby database to keep the standby in mount mode otherwise the database will be in “real time query on” state
B.1 Primary:
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> select * from test_flashback;
DT
-------------------
11/10/2011 06:57:05
B.2 Stanbdy
B.2.1 Set read-only
DGMGRL> edit database RACFDP_sb set state='read-only';
Succeeded.
B.2.2 Query the test_flashback table
[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 07:22:16 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> select * from test_flashback;
DT
-------------------
11/10/2011 06:57:05
SQL>
B.2.3 Set APPLY-ON
DGMGRL> edit database RACFDP_sb SET STATE='APPLY-ON';
Succeeded.
DGMGRL>
B.2.4. bounce the standby database to bring into mount mode
srvctl stop database -d RACFDP_SB1
srvctl start database –d RACFDP_SB1
C.Update test_flasback table during the release
Update the test_flashback table - run @ primary
SQL> conn / as sysdba
Connected.
SQL> update test_flashback set dt=sysdate;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> select * from test_flashback;
DT
-------------------
11/10/2011 07:56:16
D.Verify the test_flashback table on both primary and standby – After flashback
D.1 verify the test_flashback table from primary;
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> select * from test_flashback;
DT
-------------------
11/10/2011 06:57:05
D.2 Verify the test_flashback table from stanbdy
Set read-only
DGMGRL> edit database RACFDP_sb set state='read-only';
Succeeded.
Query the table
[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 09:43:03 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select * from test_flashback;
DT
---------
11-OCT-11
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> select * from test_flashback;
DT
-------------------
11/10/2011 06:57:05
Set the Apply-ON
DGMGRL> edit database 'RACFDP_sb' set state='APPLY-ON';
Succeeded.
DGMGRL> exit
Bounce the standby database to bring into mount mode
srvctl stop database -d RACFDP_SB1
srvctl start database –d RACFDP_SB1
Subscribe to:
Posts (Atom)
