Beiträge getaggt mit Data Guard
Auto Sync for Password Files in #Oracle 12c Data Guard

A useful enhancement in 12cR2 is that password files are automatically synchronized in a Data Guard environment:
[oracle@uhesse1 ~]$ dgmgrl sys/oracle_4U@cdb1
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Jan 10 07:38:15 2017
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cdb1"
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - myconf
Protection Mode: MaxPerformance
Members:
cdb1 - Primary database
cdb1sb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 31 seconds ago)
DGMGRL> exit
[oracle@uhesse1 ~]$ sqlplus sys/oracle_4U@cdb1 as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 10 07:38:49 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter user sys identified by oracle;
User altered.
SQL> connect sys/oracle@cdb1 as sysdba
Connected.
SQL> connect sys/oracle@cdb1sb as sysdba
Connected.
I have just changed the password of sys on the Primary and it got automatically also changed on the Standby. In earlier versions that had to be done manually, typically by replacing the password file on the standby with a new copy from the primary. One little thing less to keep in mind now 🙂
How to reinstate the old Primary as a Standby after Failover in #Oracle

You have done a failover to your Standby database so it becomes the new Primary. It may be possible to convert the old Primary into a Standby database now instead of having to do a time consuming duplicate again. The old Primary must have been running in flashback mode before the failover. The playground:
DGMGRL> show configuration;
Configuration - myconf
Protection Mode: MaxAvailability
Members:
prima - Primary database
physt - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 36 seconds ago)
SYS@prima > host echo kaputt > /home/oracle/prima/users01.dbf
SYS@prima > select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01115: IO error reading block from file (block # )
ORA-01110: data file 4: '/home/oracle/prima/users01.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 130
I did just cause a damage on my Primary database in order to have a reason to failover. I took backups on the Primary previously and could do restore and recovery instead of the failover. But failover ist way faster. After all, that is what we have the Standby for 🙂
[oracle@uhesse scripts]$ dgmgrl sys/oracle@physt DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDBA. DGMGRL> failover to physt; Performing failover NOW, please wait... Failover succeeded, new primary is "physt"
Took less than 10 seconds. Now I want a Standby for my new Primary. If the old Primary has had a power outage only, the Reinstate could be done immediately. But here, one datafile is damaged. Reinstate needs intact datafiles. Therefore first a restore, then reinstate:
[oracle@uhesse scripts]$ rman target sys/oracle@prima
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Sep 15 16:58:24 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 490736368 bytes
Database Buffers 339738624 bytes
Redo Buffers 5455872 bytes
RMAN> restore datafile 4;
Starting restore at 2016-09-15 16:59:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=176 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/prima/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/fra/PRIMA/backupset/2016_09_15/o1_mf_nnndf_TAG20160915T163533_cxomgq0q_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/fra/PRIMA/backupset/2016_09_15/o1_mf_nnndf_TAG20160915T163533_cxomgq0q_.bkp tag=TAG20160915T163533
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2016-09-15 16:59:05
RMAN> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
RMAN> exit
Recovery Manager complete.
[oracle@uhesse scripts]$ dgmgrl sys/oracle@physt
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - myconf
Protection Mode: MaxAvailability
Members:
physt - Primary database
Warning: ORA-16629: database reports a different protection level from the protection mode
prima - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 17 seconds ago)
DGMGRL> reinstate database prima;
Reinstating database "prima", please wait...
Reinstatement of database "prima" succeeded
DGMGRL> show database prima;
Database - prima
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 44.00 KByte/s
Real Time Query: OFF
Instance(s):
prima
Database Status:
SUCCESS
Optionally, I may switch back now:
DGMGRL> switchover to prima;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prima" on database "prima"
Connecting to instance "prima"...
Connected as SYSDBA.
New primary database "prima" is opening...
Operation requires start up of instance "physt" on database "physt"
Starting instance "physt"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prima"
DGMGRL> show configuration;
Configuration - myconf
Protection Mode: MaxAvailability
Members:
prima - Primary database
physt - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 40 seconds ago)
This is one reason why we recommend that you turn on flashback for the databases that are part of a Data Guard configuration. As always: Don’t believe it, test it 🙂
Another reason why you should use the Data Guard Broker for your #Oracle Standby
The Data Guard Broker is recommended for various reasons, this one is less obvious: It prevents a Split-Brain problem that may otherwise occur in certain situations. Let me show you:
[oracle@uhesse ~]$ dgmgrl sys/oracle@prima
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - myconf
Protection Mode: MaxAvailability
Members:
prima - Primary database
physt - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 18 seconds ago)
This is my setup with 12c, but the demonstrated behavior is the same with 11g already. I will cause a crash of the primary database now, without damaging any files – like a power outage on the primary site:
[oracle@uhesse ~]$ ps -ef | grep smon oracle 6279 1 0 08:30 ? 00:00:00 ora_smon_prima oracle 6786 1 0 08:32 ? 00:00:00 ora_smon_physt oracle 7168 3489 0 08:43 pts/0 00:00:00 grep --color=auto smon [oracle@uhesse ~]$ kill -9 6279
Don’t do that at home 🙂 Now the primary is gone, but of course I can failover to the standby:
[oracle@uhesse ~]$ dgmgrl sys/oracle@physt DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDBA. DGMGRL> failover to physt; Performing failover NOW, please wait... Failover succeeded, new primary is "physt"
So far so good, my end users can continue to work now on the new primary. But what happens when the power outage is over and the ex-primary comes back up again?
[oracle@uhesse ~]$ sqlplus sys/oracle@prima as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed May 18 08:47:30 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2923920 bytes
Variable Size 452985456 bytes
Database Buffers 788529152 bytes
Redo Buffers 13852672 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from
being opened
The DMON background process of the new primary communicates with the DMON on the ex-primary, telling it that there cannot be two primary databases within the same Data Guard Broker configuration! Try the same scenario without the broker and you will observe the ex-primary coming up until status OPEN. Just wanted to let you know 🙂
