Continuing this series of posts where I have a SingleInstance/FileSystem Standby database for a RAC/ASM database ...
Checking the status of the configuration :
The dgmgrl status above is from node1 (collabn1) of the RAC (Primary)database.
The query on v$archived_log is from node2 (collabn2), instance RAC2 of the RAC (Primary) database.
The listing of messages in the alert log are from the Standby database.
Let me add some rows to the data I have in the PDB in the RAC database.
Let me try a SWITCHOVER now.
Querying on the new "standby" that is RAC :
While, the old Standby :
So, host "oem132.racattack" now has the Primary database in Read Write mode. The instance name is STBY because I chose that to be the instance name through the testing. (I could have chosen BOSTON and CHICAGO as is used in some Oracle examples / documentation).
Let me verify my data on STBY :
The rows inserted at the RAC Primary are now in the Pluggable Database PDB at STBY.
.
.
.
Checking the status of the configuration :
DGMGRL> show configuration;
Configuration - rac
Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 28 seconds ago)
DGMGRL>
SQL> select thread#, max(sequence#)
2 from v$archived_log
3 group by thread#
4 order by 1
5 /
THREAD# MAX(SEQUENCE#)
---------- --------------
1 66
2 36
SQL> alter system archive log current;
System altered.
RFS[2]: Selected log 8 for thread 2 sequence 38 dbid 2519807290 branch 931825279
Mon Apr 10 23:03:17 2017
Archived Log entry 39 added for thread 2 sequence 37 ID 0x96312536 dest 1:
Mon Apr 10 23:03:18 2017
Media Recovery Waiting for thread 2 sequence 38 (in transit)
Mon Apr 10 23:03:18 2017
Recovery of Online Redo Log: Thread 2 Group 8 Seq 38 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/STBY/onlinelog/group_8.300.937936389
Mem# 1: /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_8.306.937936389
RFS[1]: Selected log 6 for thread 1 sequence 68 dbid 2519807290 branch 931825279
Mon Apr 10 23:03:23 2017
Archived Log entry 40 added for thread 1 sequence 67 ID 0x96312536 dest 1:
Mon Apr 10 23:03:23 2017
Media Recovery Waiting for thread 1 sequence 68 (in transit)
Mon Apr 10 23:03:23 2017
Recovery of Online Redo Log: Thread 1 Group 6 Seq 68 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/STBY/onlinelog/group_6.298.937936361
Mem# 1: /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_6.304.937936363
The dgmgrl status above is from node1 (collabn1) of the RAC (Primary)database.
The query on v$archived_log is from node2 (collabn2), instance RAC2 of the RAC (Primary) database.
The listing of messages in the alert log are from the Standby database.
Let me add some rows to the data I have in the PDB in the RAC database.
[oracle@collabn1 ~]$ sqlplus hemant/hemant@PDB
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 10 23:06:31 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Apr 03 2017 22:49:41 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select count(*) from my_data;
COUNT(*)
----------
100
SQL> insert into my_data select rownum+100, to_char(rownum+100)
2 from dual
3 connect by level < 201;
200 rows created.
SQL> select count(*) from my_data;
COUNT(*)
----------
300
SQL> commit;
Commit complete.
SQL>
Let me try a SWITCHOVER now.
DGMGRL> show configuration;
Configuration - rac
Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 102 seconds ago)
DGMGRL> switchover to stby;
Performing switchover NOW, please wait...
Operation requires a connection to instance "STBY" on database "stby"
Connecting to instance "STBY"...
Connected as SYSDBA.
New primary database "stby" is opening...
Oracle Clusterware is restarting database "rac" ...
Switchover succeeded, new primary is "stby"
DGMGRL>
Querying on the new "standby" that is RAC :
[oracle@collabn1 ~]$ sqlplus '/ as sysdba' SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 10 23:36:12 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> select open_mode, database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- MOUNTED PHYSICAL STANDBY SQL> select instance_name, host_name from gv$instance; INSTANCE_NAME ---------------- HOST_NAME ---------------------------------------------------------------- RAC1 collabn1.racattack RAC2 collabn2.racattack SQL>
While, the old Standby :
[oracle@oem132 trace]$ sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 10 23:37:34 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select open_mode, databas_role from v$database;
select open_mode, databas_role from v$database
*
ERROR at line 1:
ORA-00904: "DATABAS_ROLE": invalid identifier
SQL> select open_mode, database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
SQL> select instance_name, host_name from gv$instance;
INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
STBY
oem132.racattack
SQL>
So, host "oem132.racattack" now has the Primary database in Read Write mode. The instance name is STBY because I chose that to be the instance name through the testing. (I could have chosen BOSTON and CHICAGO as is used in some Oracle examples / documentation).
Let me verify my data on STBY :
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 230689520 bytes
Database Buffers 599785472 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdb open;
Pluggable database altered.
SQL> connect hemant/hemant@PDB
Connected.
SQL> select count(*) from my_data;
COUNT(*)
----------
300
SQL>
The rows inserted at the RAC Primary are now in the Pluggable Database PDB at STBY.
.
.
.
9 comments:
Hi Mr Hemant,
I’m not familiar with RAC environment.
My Deputy Database Manager said that,
when I startup mount and if spfile does not exist, it must have read from source controlfile of RAC database.
Is this true?
No.
The pfile/spfile contents and the controlfile contents are *entirely* different. Oracle first reads the pfile/spfile to (a) identify where the controlfile is/are and (b) to set the database instance parameters. After having identified the controlfile from the pfile/spfile, Oracle then opens the controlfile during the MOUNT stage and reads the list of datafiles and redo log files from this to identify the files needed to open the database.
If your startup MOUNT succeeded, it means that the instance read *both* pfile/spfile and controlfile. If a startup NOMOUNT succeeds but a MOUNT fails, it means that the pfile/spfile was accessible but the controlfile was not available.
Hi Mr Hemant,
If a copy Server Parameter File (spfile) is registered in Server Control (srvctl) that is stored in ASM, when using sqlplus to startup it is correct that it will reads this copy of spfile first before the copy of the spfile that is stored in OS directory e.g., $ORACLE_HOME/dbs.
As describe in the oracle documentation, but documentation is not very clear as to the read sequence of the spfile is using sqlplus or srvctl to startup database instance.
https://docs.oracle.com/database/121/ADMIN/start.htm#ADMIN12825
As indicated in para => 3.1.2 Specifying Initialization Parameters at Startup.
"If DBCA created the SPFILE in an Oracle Automatic Storage Management disk group, then the database searches for the SPFILE in the disk group."
Thanks You! Greatly appreciate your expert advice.
Leon,
DBCA registers the spfile with srvctl so that would cause srvctl to use the registered spfile.
I believe that sqlplus command-line is not aware of what is registered with srvctl so would use the spfile in $ORACLE_HOME/dbs
Hi Mr Hemant,
May I know is there a difference of a Control File that is stored in ASM and one that is stored in OS filesystem?
This Control file question is pertaining to an Oracle Database Instance and NOT an ASM instance.
Thanks You!
Leon,
A controlfile is a binary file that can reside on ASM (and in older versions of Oracle, Raw device) or filesystem. There is no difference.
Normally, you setup a database with two controlfiles. You can have one controlfile in ASM and one in a local filesystem if you choose to do so (and a third in a NFS filesystem).
You can "migrate" a controlfile to/from ASM/Filesystem using RMAN as well.
You can have the Primary database with controlfiles on ASM and the Standby with controlfiles on filesystem.
Hi Mr Hemant,
I like to ask a question pertaining to a Recovered Controlfile and it's relationship with the Controlfile parameter of the initialization file (e.g., spfile).
A typical "startup mount" or "alter database mount" in RMAN/sqlplus will locate and open the Control file that we specified Controlfile parameter of the initialization file. If the Controlfile name does not match the Controlfile parameter of the initialization file, the Mount will failed.
However, following an RMAN recovery of the Controlfile from Backup, it is able to Mount successfully even the Controlfile name does not match the Controlfile parameter of the initialization file.
Does it means that in this scenario, the Mount ignores the specified Controlfile parameter of the initialization file or does NOT even read the initialization file in this case? The pointer is to the recovered Controlfile in the RMAN memory realm?
Noting also that if we exit RMAN after recovering the Controlfile, but comes back with a new login subsequently to Mount the database instance, it will failed. The pointer is lost when exiting RMAN?
The above was observed for mounting the database instance when we are not using an RMAN catalog for recovery, and the Controlfile OMF name is changed every time it restored to ASM.
Thanks! Greatly appreciate your expert advice
Leon,
Use an SPFILE.
In the SPFILE specify only the DiskGroup name for the CONTROL_FILES parameter.
RMAN will update the SPFILE with the new controlfile names when it does a RESTORE.
Alternatively you can use the RESTORE .. TO option to specify a hard-coded filename if you want.
Hi Hemant , I searched and searched the entire web for a decent documentation to clarify this RAC to Single instance failover , even oracle docs failed me. None could explain clearly what happens to standby when it become primary.
Thank you for such a detailed post.
Please also include a failover scenario in such a case of asymmetrical setup
Post a Comment