DBA Masters

Saturday 1 September 2012

11g R2 – Backup Procedure

Friday 31 August 2012

11g R2 - Reducing overheads in a Data Guard Environment

Hi,

My name is Mark Tiger, creator of this blog. I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly. I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks; that typically need a lot of research, before you can do them. I will try to present the information in an easy to understand way. My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA. The illustrations are primarily meant for Linux, since this is a Platform; that enjoys preference from Oracle. However they are easily adaptable for versions of UNIX/AIX and windows etc.

11g R2 In a Data Guard environment to reduce overheads of fast incremental backups on the primary database

RMAN can perform backups with minimal effect on the primary database and quickly recover from the loss of individual datafiles, or the entire database. RMAN and Data Guard can be used together to streamline the administration of a Data Guard setup.

You can’t use a logical standby database to backup the primary database, because the logical standby database is not a block for block copy of the primary database.
About RMAN File Management in a Data Guard configuration

RMAN uses a recovery catalog in a Data Guard environment, to track the filenames for all the datafiles, online redo logs, standby redo logs, tempfiles, archived redo logs, backup sets, and image copies.

Interchangeability of Backups in a Data Guard Environment

RMAN behaves transparently across various physical databases in a Data Guard environment, by using the recovery catalog metadata. You can backup a tablespace a physical standby database, and restore and recover it on the primary database. You can backup a tablespace on a primary database and restore and recover it on a physical standby database. You can’t do this with Logical standby databases.

Backups of standby control files and nonstandby control files are interchangeable. You can restore a standby control file on a primary database and a primary control file on a physical standby database.

This means you can offload control file backups to one database in a Data Guard environment. RMAN will automatically update filenames for database files during restore and recovery at the databases.

Association of Backups in a Data Guard Environment

In the Data Guard environment,the recovery catalog associates every database file or backup file with a DB_UNIQUE_NAME. The database that creates the file is associated with that file.

When RMAN backs up the database called standby1, then the DB_UNIQUE_NAME standby1 is associated with this backup. The backup will remain associated with the database that created it, unless you associate the backup with a different database, by changing the DB_UNIQUE_NAME, by invoking: CHANGE … RESET DB_UNIQUE_NAME

Accessibility of Backups in a Data Guard Environment

The accessibility of a backup is different from its association. In a Data Guard environment, the recovery catalog considers disk backups as accessible only to the database with which it is associated, whereas tape backups created on one database are accessible to all databases. If a backup file is not associated with any database, then the row describing it in the recovery catalog, shows null for the SITE_KEY column. By default, RMAN associates files whose SITE_KEY is NULL with the target database.

RMAN commands such as BACKUP, RESTORE, AND CROSSCHECK work on any accessible backup. For example, for a RECOVER COPY operation, RMAN considers only image copies that are associated with the database as eligible to be recovered.

RMAN considers the incremental backups on disk and tape as eligible to recover the image copies. In a database recovery, RMAN considers only the disk backups associated with the database and all files on tape as eligible to be restored.

Let’s assume that the database PROD resides on a different host to the database STANDBY1. RMAN backs up datafile 1 on prod to /prod/backup/datafile1.dbf, as well as a copy of it to tape. RMAN backups datafile 1 on the standby host to /standby/backup/datafile1.dbf, as well as a copy to tape.

If you are connected with RMAN to PROD, then you cannot use RMAN operations to manipulate the backup located on /standby/backup/datafile1.dbf. However if you really needed this file, then RMAN would consider the backup of this same file on tape to be eligible to be restored.

If you don’t have a tape backup, then you can FTP a backup from the standby host to the production host, connect as TARGET to the production host, then you can CATALOG the backup. Once you have catalogued the backup in the target database, then that backup is associated with the target database, in this case PROD database.
About RMAN Configuration in a Data Guard Environment

In a Data Guard environment, you can offload the process of backing up control files, datafiles, and archived logs to the standby system. By doing this you minimize the effect of backups on the production system. The backups taken in this way can be used to recover the primary or the standby database.

RMAN uses the DB_UNIQUE_NAME initialization parameter to distinguish one database site from another database site. Therefore it is essential that the uniqueness of DB_UNIQU_NAME be maintained in a Data Guard configuration.

Only the primary database must be explicitly registered using the RMAN> REGISTER DATABASE command. You would issue this command after connecting to the recovery catalog, and the primary database as the target.

You can use the RMAN command CONFIGURE, to set up the various configurations in RMAN. When you use the CONFIGURE command in conjunction with the FOR DB_UNIQUE_NAME option, then RMAN sets the CONFIGURE for the site-specific database, based on the DB_UNIQUE_NAME that was specified.

RMAN> set dbid 1625818167;

Use this if you have not connect to RMAN as TARGET.

RMAN> configure default device type to SBT for db_unique_name jhb;

Recommended RMAN and Oracle Database Configurations

These configurations can contribute towards simplifying backup and recovery operations.

Configuration Assumptions

·         The standby database is a physical standby database, and backups are taken only on the standby database.

·         An RMAN recovery catalog is required so that backups taken on one database server can be restored to another database server. It is not sufficient to use only the control file, because the RMAN repository on the primary database, will have no knowledge of backups taken on the standby database.

The RMAN recovery catalog organizes backup history and recovery related metadata in a centralized location. The recovery catalog is configured in a database and maintains backup metadata. A control file has space limitations, whereas the recovery catalog does not, and can store more historical data about the backups.

Ideally you should have a catalog server, physically separate from the primary and standby sites. Oracle recommends this in a Data Guard configuration, because a disaster at either site will not affect the ability to recover the latest backups.

·         Assume that all databases are using the Oracle Database 11g release 1 or higher.

·         Oracle Secure Backup software, or 3rd party media management software is configured with RMAN to make backups to tape.

Oracle Database Configurations on Primary and Standby Databases

The following Oracle Database configurations are recommended on the primary database and all the standby databases in a Data Guard environment.

·         Configure a fast recovery area for each database. The recovery area is local to the database.

The fast recovery area is a single storage area or location on a file system of Oracle ASM disk group, where all the files needed for a recovery reside. These files include the control file, online redo logs, archived logs, flashback logs, and RMAN backups.

As new backups and archived logs are created in the fast recovery area, older files are automatically deleted to make room for them. The files are deleted according to the retention period, or when they have been backed up to tertiary storage. Also notifications can be set up to alert the administrator when space consumption in the fast recovery area is nearing its predefined limit. The DBA can then take an action, such as increasing the recovery area space limit, adding disk hardware space, or decreasing the retention period.

You can set the fast recovery area with these initialization parameters:

DB_RECOVERY_FILE_DEST = <mount point or Oracle ASM Disk Group>

DB_RECOVERY_FILE_DEST_SIZE = <disk space quota>

·         Use a server parameter file (SPFILE), so that it can be backed up, to save the instance parameters in the backup.

·         Enable Flashback Database on the primary and standby databases.

When Flashback Database is enabled, Oracle Database maintains flashback logs in the fast recovery area. These logs can be used to roll the database back to an earlier point in time, without requiring a complete restore.

RMAN configurations at the Primary Database

You can set a number of persistent RMAN configuration settings for each database in the Data Guard environment, in order to simplify the ongoing use of RMAN in the environment. You can configure the backup retention policy, default destinations for backups to tape or disk, default backup device type, etc. You can use the CONFIGURE command to set and change RMAN configurations.

These configurations are recommended at the Primary database site:

·         When you connect, connect RMAN to the primary database and to the recovery catalog.

·         Configure the retention policy for the database in terms of n days.

RMAN> configure retention policy to recovery window of <n> days;

old RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;

new RMAN configuration parameters are successfully stored

This configuration lets you keep the backups necessary to perform database recovery to any point in time within the specified number of days.

Use the DELETE OBSOLETE command to delete any backups that are not required to perform recovery within the specified number of days.

·         Use the CONFIGURE ARCHIVELOG DELETION POLICY command, to specify when the archived logs can be deleted.

If you want to delete logs after ensuring that they shipped to all destinations:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

If you want to delete logs only after ensuring that they were applied to all standby destinations, then use the following configuration.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED TO ALL STANDBY;

·         Configure the connect string for the primary database and all the standby databases, so that RMAN can connect remotely and perform resynchronization when the RESYNC CALTALOG FROM DB_UNIQUE_NAME command is used.

When you connect to the target database, you must provide a net service name. This requirement applies, even if the other database instance from where the resynchronization is done is on the local host.

The Target and remote instances must use the same SYSDBA password, which means that both instances must already have password files. You can create the password file with a single password, so you can start all the database instances with that password file.

If the TNS alias to connect to a standby database in jhb is jhb_connect_str, you can use the following command to configure the connect identifier for the jhb database site:

RMAN> configure db_unique_name jhb connect identifier ‘jhb_connect_str’;

‘jhb_connect_str’ does not include a username and password. It contains only the Oracle Net Service name that can be used from any database site to connect to the jhb database site.

After the connect identifiers are configured for all the standby databases, you can verify the list of standbys by using:

RMAN> LIST DB_UNIQUE_NAME OF DATABASE;

RMAN configurations at a Standby Database Where Backups are Performed

·         Connect RMAN to the standby database (where backups are done), as target, and connect to the recovery catalog.

·         Enable automatic backup of the control file and the server parameter file:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

·         Skip backing up datafiles for which there already exists a valid backup with the same checkpoint:

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

·         Configure the tape channels to create backups as required by media management software:

RMAN> CONFIGURE CHANNEL DEVICE TYPE SBT PARMS ‘<channel parameters>’;

·         Specify when the archived logs can be deleted with:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY ...

Because the logs are backed up at the standby site, it is recommended that you configure the BACKED UP option for the log deletion policy.

RMAN configurations at a Standby where Backups are not Performed

·         Connect RMAN to the standby database as target, and to the recovery catalog.

·         Enable automatic deletion of archived logs once they are applied at the standby database:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

MarkTiger01@gmail.com

P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam. Watch this spot for details on the books, as they start becoming available.

Thursday 30 August 2012

11g R2 – Apply Services

Hi,

My name is Mark Tiger, creator of this blog. I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly. I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks; that typically need a lot of research, before you can do them. I will try to present the information in an easy to understand way. My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA. The illustrations are primarily meant for Linux, since this is a Platform; that enjoys preference from Oracle. However they are easily adaptable for versions of UNIX/AIX and windows etc.

11g R2 – Apply Services

Apply services allows transactional consistent access to the data, by automatically applying redo to the standby database in order to maintain synchronization with the primary database.

By default, apply services will wait for a standby redo log file to be archived before applying the redo that it contains. You can also enable real-time apply, which allows apply services to apply the redo in the current standby redo log file, as it is being filled.

Apply services use these methods to maintain physical and logical standby databases:

· Redo Apply (this is for physical standby databases only)

Uses media recovery to keep the primary and physical standby databases synchronized.

· SQL Apply (Logical standby databases only)

SQL apply, reconstitutes the SQL statements from the redo received from the primary database. SQL apply then executes these statements against the logical standby database.

· You also get real-time apply and delayed apply, which are important considerations.

Apply Services Configuration Options

Using Real-Time Apply to Apply Redo Data Immediately

If the real-time apply feature has been enabled; then apply services can apply data as it is received. There is no need to wait for the current standby redo log file to be archived. This will result in faster switchover and failover times. It will be faster because the standby redo log files would have already been applied by the time the failover or switchover is initiated.

Real-time apply requires the standby database to be configured with a standby redo log configuration. Real-Time apply requires the standby database to be in ARCHIVELOG mode.

Enable the real-time apply feature like this:

· On a physical standby database.

SQL> alter database recover managed standby database using current logfile...;

· ON a logical standby database.

SQL> alter database start logical standby apply immediate...;

In a Data Guard configuration, with a local destination and a standby destination. As the Remote File Server(RFS) process writes the redo data to standby redo log files on the standby database; the apply services can recover redo from standby redo log file as they are being filled.

Specifying a Time Delay for the Application of Archived Redo Log Files

If you define a delay for a destination that has real-time apply enabled, the delay is ignored.

There are cases were you may want to create a time lag or delay between the time when the redo data is received from the primary site, and when it is applied to the standby database. You would specify this time delay in minutes, typically to protect the standby database from corrupted or erroneous data application. The DELAY interval specifies the delay from the time that the redo data is completely archived at the standby destination.

You the delay using the DELAY=<minutes> attribute of the LOG_ARCHIVE_DEST_n initialization parameter. This will delay the applying of the archived redo log files to the standby database. By default there is no time delay. If you specify the DELAY attribute without specifying a value, the the default delay interval is 30 minutes.

You can also cancel a specified delay interval in the following way, both ways will result in the apply services immediately beginning to applay the archived redo log files to the standby database:

· Physical Standby database: use the NODELAY keyword in the RECOVER MANAGED STANDBY DATABASE clause.

SQL> alter database recover managed standby database nodelay...;

· Logical standby database:

SQL> alter database start logical standby apply nodelay...;

Using Flashback Database as an Alternative to Setting a Time Delay

Instead of setting up an apply delay, you can also use Flashback Database to recover from the application of corrupted or erroneous data to the standby database. Flashback Database can quickly flashback the standby database to an arbitrary point in time.

Applying Redo Data to Physical Standby Databases

By default, the redo log data is applied from the archived redo log files. When performing Redo Apply, a physical standby database can use the real-time apply feature to apply redo directly from the standby redo log files as they are being written by the RFS process.

Starting Redo Apply

To start the apply services on a physical standby database, ensure the physical standby database is started and mounted and the start the Redo Apply.

· To start Redo Apply and run it in the foreground:

SQL> alter database recover managed standby database;

In this case, control is not returned to the command prompt until recovery is cancelled by another session.

· To start the Redo Apply in the background, you must include the DISCONNECT keyword in the statement:

SQL> alter database recover managed standby database disconnect;

This will create a detached server process and immediately return control to the user. While the managed recovery process continues in the background, the SQL*Plus session can disconnect or continue performing other tasks.

· To start the real-time apply, include the USING CURRENT LOGFILE clause in the statement:

SQL> alter database recover managed standby database using current logfile;

Stopping redo Apply

To stop the redo apply to the standby database:

SQL> alter database recover managed standby database cancel;

Monitoring Redo Apply on Physical Standby Databases

You can manually query the data dictionary, or use enterprise manager to monitor the progress of the redo apply.

· V$database

You can get the data protection mode, the data protection level, the database role, and the switchover status for a primary, physical standby or snapshot standby database.

SQL> select protection_mode, protection_level, database_role role, switchover_status

From v$database;

The following displays the fast-start failover status:

SQL> SELECT FS_FAILOVER_STATUS "FSFO STATUS",

FS_FAILOVER_CURRENT_TARGET TARGET,

FS_FAILOVER_THRESHOLD THRESHOLD,

FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT"

FROM V$DATABASE;

· V$managed_standby:

To display the redo apply and redo transport status on a physical standby database.

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

· V$archived_log

Information about archived redo log files, that have been received by a physical or snapshot standby database from a primary database:

SQL> select thread#, sequence#, first_change#, next_change# from v$archived_log;

· V$log_history

Archived log history information, the sequence# is a useful diagnostic tool.

SQL> select thread#, sequence#, first_change#, next_change# from v$log_history;

· V$dataguard_status

Messages generated by Data Guard, events that caused a message to be written to the alert log or to a server process trace file.

SQL> select message from v$dataguard_status;

· V$archive_dest

Show te status of each redo transport destination, and for redo transport destinations that are standby databases, the SCN of the last primary database redo applied at that standby database.

SQL> select dest_id, status, applied_scn from v$archive_dest where target =’STANDBY’;

Applying Redo Data to Logical Standby Databases

SQL Apply converts the data from the archived redo log or standby redo log in to SQL statements and then executes these SQL statements on the logical standby database. Because the logical standby database operates in open mode, you can use the standby database simultaneously for other tasks such as reporting, summations and queries.

Starting SQL Apply

The logical standby database needs to be open.

SQL> alter database start logical standby apply;

To start real-time apply on the logical standby database, in order to immediately apply redo data rom the standby redo log files on the logical standby database; you need to include the IMMEDIATE keyword in the statement.

SQL> alter database start logical standby apply immediate;

Stopping SQL Apply on a Logical Standby Database

To stop SQL Apply you can enter this statement.

SQL> alter database stop logical standby apply;

When you issue this statement, SQL Apply waits until it has committed all complete transactions that were in the process of being applied. So the command may not stop the SQL Apply process immediately, you may have to wait for it to finish.

Monitoring SQL Apply on Logical Standby Databases

You can monitor the logical standby database using enterprise manager, or by manual methods.

· Dba_logstdby_events: This view records interesting events that occurred during the operation of SQL Apply. By default the view records the most recent 10,000 events. You can change the number of events recorded by the PL/SQL procedure:

DBMS_LOGSTDBY.APPLY_SET().

Errors that cause SQL Apply to stop are recorded in this view. Such events will also be recorded in the alert log. You can search through the alert log with this search criteria “LOGSTDBY”, because this keyword will be recorded with any reference in the alert log. When querying the view you should order by EVENT_TIME_STAMP, COMMIT_SCN, CURRENT_SCN. This will ensure that the events are ordered in the proper way.

SQL> alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS';

SQL> column status format a60

SQL> select event_time, status, event from dba_logstdby_events

Order by event_timestamp, commit_scn, current_scn;

· DBA_LOGSTDBY_LOG

This view provides dynamic information about archived logs, being processed by SQL Apply.

SQL> select file_name, sequence# as SEQ#, first_change# as F_SCN#,

Next_change# as n_SCN#, timestamp,

Dict_begin as BEG, dict_end as END,

Thread# as thr#, applied from dba_logstdby_log

Order by sequence#;

· V$dataguard_stats

This view provides stats related to the failover characteristics fo the logical standby database.

o The time to failover

o How current the commited data in the logical standby database is

o What will the potential data loss be in the event of a disaster

SQL> select name, value, unit from v$dataguard_stats;

· V$logstdby_process

Information about the current state of the various processes involved with SQL Apply

o Identifying information (sid | serial# | spid)

o SQL Apploy process: COORDINATOR, READER, BUILDER, PREPARER, ANALYZER, OR APPLIER(type)

o Status of the processes current activity (status_code | status)

o Highest redo record processed by this process (high_scn)

SQL> select sid, serial#, spid, type, high_scn from v$logstdby_process;

· V$logstdby_progress

Detailed information regarding the progress made by SQL Apply

o SCN and time at which all transactions that have been committed on the primary database have been applied to the logical standby database.(applied_scn, applied_time)

o SCN and time at which SQL Apply would begin reading redo records (restart_scn, restart_time)

o SCN and time of the latest redo record received on the logical standby database (latest_scn, latest_time)

o SCN and time fo the latest record processed by the BUILDER prcess (minig_scn, mining_time)

SQL> select applied_scn, latest_scn, mining_scn, restart_scn from v$logstdby_progress;

· V$logstdby_state

This view provides a synopsis of the current state of SQL Apply, including:

o The DBID of the primary database (primary_dbid)

o The logminer session ID allocated to SQL Apply (session_id)

o Whether or not SQL Apply is applying in real time (realtime_apply)

SQL> select * from v$logstdby_state;

· V$logstdby_stats

This view displays statistics, current state, andstatus information related to the SQL Apply. No rows are returned form this view when SQL Apply is not running. This view is only meaningful in terms of a logical standby database.

SQL> select substr(name, 1,40) as name,

Substr(value,1,32) as value from v$logstdby_stats;

MarkTiger01@gmail.com

P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam. Watch this spot for details on the books, as they start becoming available.