Cloning a Database with RMAN (DATA GUARD Part 3)

0
154

Performing actions with the Data Guard Broker Manager

There are a number of commands that you can use to change the state of the database

Turn off/on the redo transport service for all standby databases   
On Primary
    DGMGRL> edit database SEADEV set state=transport-off;
    DGMGRL> edit database SEADEV set state=transport-on;

Turn off/on the apply state   
On Standby
    DGMGRL> edit database SEAREPL set state=apply-off;
    DGMGRL> edit database SEAREPL set state=apply-on;

Put a database into a real-time query mode   
On Standby
    DGMGRL> edit database SEAREPL set state=apply-off;
    sql> alter database open read only;
    DGMGRL> edit database SEAREPL set state=apply-on;

Change the protection mode   
On Primary
# Choose what level of protection you require
sql> alter database set standby to maximize performance;
sql> alter database set standby to maximize availability;
sql> alter database set standby to maximize protection;

Check the current configuration mode:
DGMGRL> show database SEADEV LogXptMode


Maximum Protection with Logical Standby Database
dgmgrl> edit database SEADEV set property LogXptMode=SYNC;
dgmgrl> edit database SEAREPL set property LogXptMode=SYNC;
dgmgrl> edit configuration set protection mode as maxprotection;
show configuration;


Change of Protection Level and Transport Method
dgmgrl> connect sys@SEADEV
dgmgrl> show configuration verbose;
dgmgrl> show database verbose SEAREPL ;

Maximum Protection; here 2 Standby Databases are recommended. The changes are always done on Primary and Standby in case of a later SWITCHOVER.
dgmgrl> edit database SEADEV set property LogXptMode=SYNC;
dgmgrl> edit database SEAREPL set property LogXptMode=SYNC;
dgmgrl> edit configuration set protection mode as maxprotection;
dgmgrl> show configuration;

Maximum Availability
dgmgrl> edit database SEADEV set property LogXptMode=SYNC;
dgmgrl> edit database SEAREPL set property LogXptMode=SYNC;
dgmgrl> edit configuration set protection mode as maxavailability;
dgmgrl> show configuration;

Maximum Performance with LGWR-Transport
If there was a higher Protection Level beforehand, it must be lowered to Maximum Performance now
dgmgrl> edit configuration set protection mode as maxperformance;
dgmgrl> edit database SEADEV set property LogXptMode=ASYNC;
dgmgrl> edit database SEAREPL set property LogXptMode=ASYNC;
dgmgrl> show configuration;

Maximum Performance with ARCH-Transport 
dgmgrl> edit configuration set protection mode as maxperformance;
dgmgrl> edit database SEADEV set property LogXptMode=ARCH;
dgmgrl> edit database SEAREPL set property LogXptMode=ARCH;
dgmgrl> show configuration;



Quick List of Equivalent Broker Commands to ‘ALTER SYSTEM’ :
SQL> alter database recover managed SEAREPL database cancel;
DGMGRL> edit database ‘SEAREPL’ set state=’LOG-APPLY-OFF’;

SQL> alter database recover managed SEAREPL database disconnect;
DGMGRL> edit database ‘SEAREPL’ set state=’ONLINE’;

SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database ‘SEADEV’ set property ‘LogArchiveMaxProcesses’=4;

SQL> alter system set log_archive_dest_state_2=’enable’ scope=both;
DGMGRL> edit database ‘SEAREPL’ set property ‘LogShipping’=’ON’;

SQL> alter system set log_archive_dest_state_2=’defer’ scope=both;
DGMGRL> edit database ‘SEAREPL’ set property ‘LogShipping’=’OFF’;
DGMGRL> edit database ‘SEADEV’ set state=’LOG-TRANSPORT-OFF‘;


This will defer all standby databases



Add the Logical Standby to the Broker Configuration if that has been configured:

dgmgrl
dgmgrl> connect sys@SEADEV
dgmgrl> show configuration;
dgmgrl> add database RECO as connect identifier is RECO maintained as logical;
dgmgrl> enable database RECO;
dgmgrl> show configuration;



Switchover
Switchover: Primary and Standby exchange their roles
dgmgrl> connect sys@SEADEV
dgmgrl> switchover to SEAREPL;

After it completes, issue startup mount at old primary site, which is now new standby.
DGMGRL> show configuration

Configuration – orcl_dg
Protection Mode: MaxAvailability
Databases:

SEAREPL  – Primary database
SEADEV  – Physical standby database

Fast-Start Failover: DISABLED
Configuration Status
SUCCESS

You can switch roles back by issuing a new switchover command from dgmgrl
DGMGRL> switchover to PROD;
And issue again startup mount on previous primary after it has been brought down if neccessary.


Failover
Primary is lost, unaccessible or some other type of fault and we need to brind former standby open as new primary.
If old primary will come alive after failover, it will need either flashed back to the scn when the failover took place, or completely re-built from the new primary by duplication.
dgmgrl>failover to SEAREPL;

Performing failover NOW, please wait…
Failover succeeded, new primary is « SEAREPL »

DGMGRL> show configuration
Configuration – orcl_dg

Protection Mode: MaxAvailability
Databases:

SEAREPL – Primary database
Warning: ORA-16629: database reports a different protection level from the protection mode

SEADEV – Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED
Configuration Status:
WARNING

Re-Enabling Disabled Databases
To restore your original disaster-recovery solution after switchover to a logical standby database or after failover to any standby database, you may need to perform additional steps.

Databases that have been disabled after a role transition are not removed from the broker configuration, but they are disabled in the sense that the databases are no longer managed by the broker.

To re-enable broker management of these databases, you must reinstate or re-create the databases using one of the following procedures:
* If a database can be reinstated, the database will show the following status after a complete failover:
ORA-16661: the standby database needs to be reinstated
Reinstate the database using the DGMGRL REINSTATE DATABASE command or the reinstate option in Enterprise Manager

* If a database must be re-created from a copy of the new primary database, it will have the following status:
ORA-16795: the standby database needs to be re-created
Re-create the standby database from a copy of the primary database and then re-enable it

You can use the broker’s REINSTATE command to re-enable the failed primary database after performing a complete failover to either a physical, snapshot, or logical standby database. You can also use the broker’s REINSTATE command to re-enable any physical standby databases that were not the target of the failover operation but were disabled during a complete failover to a physical standby database.

IMPORTANT NOTE:
For the REINSTATE command to succeed, Flashback Database must have been enabled on the original primary database on the database prior to the failover and there must be sufficient flashback logs on that database. In addition, the database to be reinstated and the new primary database must have network connectivity.



To reinstate a database:
1 – Restart the database to the mounted state
2 – Connect to the new primary database
3 – Use Enterprise Manager or DGMGRL to reinstate the database

Reinstatement Using DGMGRL
Issue the following command while connected to any database in the broker configuration, except the database that is to be reinstated:
DGMGRL> REINSTATE DATABASE db_unique_name;
The newly reinstated standby database will begin serving as standby database to the new primary database.

Reinstatement Using Enterprise Manager
On the Data Guard Overview page, click the Database must be reinstated link. This brings up the General Properties page that provides a Reinstate button. After you click the Reinstate button, Enterprise Manager begins reinstating the database.
When the process is complete, the database will be enabled as a standby database to the new primary database, and Enterprise Manager displays the Data Guard Overview page.

When reinstating a failed primary database, the broker re-enable it as a standby database of the same type (physical or logical standby database) as the old standby database. When reinstating physical standby databases that were disabled during a failover, the broker re-enable them as physical standby databases to the new primary database.




Fast-Start Failover

Fast-start failover allows the broker to automatically fail over to a previously chosen standby database in the event of loss of the primary database. Fast-start failover quickly and reliably fails over the target standby database to the primary database role, without requiring the DBA to perform any manual steps to invoke the failover. Fast-start failover can be used only in a broker configuration and can be configured only through DGMGRL or Enterprise Manager.


Either maximum availability mode or maximum performance mode can be used with fast-start failover. Maximum availability mode provides an automatic failover environment guaranteed to lose no data. Maximum performance mode provides an automatic failover environment guaranteed to lose no more than the amount of data (in seconds) specified by the FastStartFailoverLagLimit configuration property. This property indicates the maximum amount of data loss that is permissible in order for an automatic failover to occur. It is only used when fast-start failover is enabled and the configuration is operating in maximum performance mode. FSFO is very nice feature that plays big role in Oracle’s Maximum Availability Architecture.

We are assuming that Flashback Database feature is enabled on both the primary and the standby database
Since it’s certainly possible that more than one physical standby database could exist in a Data Guard configuration, the first thing that it’s needed to establish is which physical standby database should be paired with the primary database in case a fast-start failover is initiated. In this example, I’ll do that by setting a value for the FastStartFailoverTarget parameter via the DGMGRL utility. Note that I’ve chosen the primary database as the fast-start failover target for the selected physical standby database as well:

DGMGRL> EDIT DATABASE SEADEV SET PROPERTY FastStartFailoverTarget = ‘SEAREPL’;
DGMGRL> EDIT DATABASE SEAREPL SET PROPERTY FastStartFailoverTarget = ‘SEADEV’;

Next, we establish how long the Fast-Start Failover Observer should wait until it decides that the primary database is unreachable by setting a value of 180 seconds for the FastStartFailoverThreshold parameter:
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = ‘180’;

Then we will setup the Log Mode and Protection Mode with the following commands:
DGMGRL> EDIT DATABASE ‘SEADEV’ SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT DATABASE ‘SEAREPL’ SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

Now we are ready to enable Fast Failover:
DGMGRL> enable fast_start failover

If you check the configuration at this moment you may find the following warning: ORA-16608: one or more databases have warnings.
If you check the status of one of the databases, you can see this warning: ORA-16819: Fast-Start Failover observer not started

And we can also start the observer. Ideally the observer should be started on another host, to observe the primary, as if the host goes down it can monitor the outage and do specify actions.
In this case we will start it from the Standby Database:
DGMGRL> connect sys@SEAREPL
DGMGRL> start observer

The session will seem to hang. You can do a control C. Or create and run the script as nohup.
nohup dgmgrl sys/oracle@SEAREPL « start observer file=’$ORACLE_HOME/dbs/fsfo.dat' » -logfile $HOME/observer.log &
This will start the observer process and the process will write output to a logfile called ‘observer.log’  under the $HOME Directory

And another way to start:
dgmgrl -logfile $HOME/observer.log sys/oracle@orcl « start observer » &

Now that the basic fast-start failover configuration is completed, we can confirm its status with the SHOW FAST_START FAILOVER command:
DGMGRL> show fast_start failover
Fast-Start Failover: ENABLED
  Threshold:         180 seconds
  Target:            SEADEV   Observer:          SEAREPL
  Lag Limit:         30 seconds
  Shutdown Primary:  TRUE
  Auto-reinstate:    TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES
Oracle Error Conditions:     (none)

Monitoring Data Guard Configuration Health Using SQL:

The following queries can be used to assess overall health on both the primary and the standby.

Primary Database Queries

Check if any remote standby archive destination is getting errors.

Check if all remote standby archive destinations is enabled or « VALID ».

select sysdate,status,error from gv$archive_dest_status where type=’PHYSICAL’ and  status!=’VALID’ or error is not null;    

Good behavior – no rows returned.

If query returns rows, then raise an alert with the returned data.

Check if any nologging activity has occurred on the primary for the last day.      

select file#, name, unrecoverable_change#, unrecoverable_time

from v$datafile 

where unrecoverable_time > (sysdate – 1);

Good behavior – no rows returned.

If query returns rows, then the standby is vulnerable and the subsequent file needs to be refreshed on the standby.

Detect gaps on the standby       

select sysdate,database_mode,recovery_mode, gap_status from v$archive_dest_status where type=’PHYSICAL’ and gap_status !=’NO GAP’;     

Good behavior – no rows returned.

If query returns rows, then there’s an existing gap between the primary and the standby.  Subsequent action is to run the same query on the standby, if the output is identical, then no action required.  If the output on the standby does not match the primary then the datafile on the standby should be refreshed.

Assess if any severe Data Guard event occurred in the last day   

select * from v$dataguard_status where severity in (‘Error’,’Fatal’) and timestamp > (sysdate -1);    

Good behavior – no rows returned.

If query returns rows then the result should raise an alert with the returned output.

FOR SYNC ENVIRONMENTS ONLY:

Assess if running in Maximum Availability mode and configuration is in sync.   

select sysdate,protection_mode, synchronized, synchronization_status from v$archive_dest_status where type=’PHYSICAL’ and synchronization_status !=’OK’;    

Good behavior – no rows returned.

If query returns rows then the result should raise an alert with the returned output.

Physical Standby Database Queries

Determine if there is a transport lag

select name,value,time_computed,datum_time from v$dataguard_stats where name=’transport lag’ and value > ‘+00 00:01:00’;

Good behavior – no rows are returned.

If no rows are returned then this implies that there is no transport lag

Determine if there is an apply lag

select name,value,time_computed,datum_time from v$dataguard_stats where name=’apply lag’ and value > ‘+00 00:01:00’;

Good behavior – no rows are returned.

If no rows are returned then this implies that there is no apply lag

Standby data file check (offline files or files that are not accessible)

select * from v$datafile_header where

status =’OFFLINE’

or ERROR is not null;

Good behavior – no rows returned.

Any rows returned will show which file(s) are having IO or recovery issues.

Verify that the Media Recovery Process is currently running.

 select * from v$managed_standby

where process like ‘MRP%’;

Good behavior – rows returned.

If no rows are returned then the MRP process is not running.

Assess if any severe Data Guard event occurred in the last day

select * from v$dataguard_status where

severity in (‘Error’,’Fatal’) and

timestamp > (sysdate -1);

Good behavior – no rows returned.

If query returns rows, then the result should raise an alert with the returned output.

Great Resources


— File Name : https://github.com/Zaalouni/OracleDba
— Author : Zaalouni Mohamed
— website : http://www.aws-senior.com


— File Name : https://github.com/Zaalouni/OracleDba
— Author : Zaalouni Mohamed
— website : http://www.aws-senior.com

— Github : https://github.com/Zaalouni/OracleDba