Sunday, November 6, 2011

Voting disk and OCR Management

The recommendations for the addition or removal of a voting disk is shutdown Oracle Cluster ware first on all nodes and then utilize the commands below as root user where path is completely qualified path for the additional voting disk. In the case of new voting disk over the network file system (NFS), create an empty voting disk file location with the correct owner and permissions before execute of commands mentioned below. Three extra raw partitions /dev/raw/raw3/, dev/raw/raw4, /dev/raw/raw5 have been created in order to practice following excercises

Run the following command to find path of voting disks

[oracle@crs1 ~]$ crsctl query css votedisk

0. 0 /dev/raw/raw2

located 1 votedisk(s).

Run the following command as the root user to add a voting disk

[root@crs1 oracle]# crsctl stop crs

[root@crs2 oracle]# crsctl stop crs

[root@crs1 oracle]# crsctl add css votedisk /dev/raw/raw3 -force

Now formatting voting disk: /dev/raw/raw3

successful addition of votedisk /dev/raw/raw3.

[root@crs1 oracle]# crsctl start crs

[root@crs2 oracle]# crsctl start crs

[oracle@crs2 oracle]$ crsctl query css votedisk

0. 0 /dev/raw/raw2

1. 0 /dev/raw/raw3

located 2 votedisk(s).

Run the following command as the root user to remove a voting disk

# crsctl delete css votedisk path

Backup and Recovery of Voting Disk

Back up the voting

dd if=voting_disk_name of=backup_file_name

[oracle@crs1 ~]$ dd if=/dev/raw/raw2 of=/home/oracle/backup/votdisk1

Recovering Voting Disks

dd if=backup_file_name of=voting_disk_name

Administering the Oracle Cluster Registry in Oracle Real Application Clusters

OCR file location is in following file

For Sun Solaris /var/opt/oracle/ocr.loc

For Linux /etc/oracle/ocr.loc

If OCR resides on a cluster, file system file or if the OCR is on a network file system, then create the target OCR file before adding OCR.

Use the OCRCHECK utility to verify the OCR integrity

The things displayed by OCRCHECK utility are – the version of the OCR’s block format; total space used and available; OCRID; and the OCR locations that have been configured. A block-by-block checksum operation for all of the blocks in all the configured OCRs is performed by OCRCHECK. It also returns an individual status for each file as well as a result for the overall OCR integrity check.

[root@crs1 oracle]# ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 524184

Used space (kbytes) : 5644

Available space (kbytes) : 518540

ID : 87847809

Device/File Name : /dev/raw/raw1

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Device/File not configured means ocrmirror is not configured

OCRCHECK creates a log file in the directory CRS_Home/log/hostname/client. To change amount of logging, edit the file CRS_Home/srvm/admin/ocrlog.ini.

Run the following command to add an OCR mirror location using either destination_file or disk to designate the target location of the additional OCR

First, make sure you have proper backup of ocr

[root@crs2 oracle]# ocrconfig –showbackup

Run the following command to add an OCR location using either destination_file or disk to designate the target location of the additional OCR

ocrconfig -replace ocr destination_file or disk

ocrconfig -replace ocrmirror destination_file or disk

First zero out the raw device

If your OCR configuration were altered while a particular node is stopped, you would be required to repair the OCR configuration on that particular node. For instance, there may be need to repair the OCR on a node that was not up at the time of removing, replacing or adding an OCR. For repairing an OCR configuration, run the following command on the node on which the Oracle Clusterware daemon was stopped.

ocrconfig –repair ocrmirror device_name

This operation only changes the OCR configuration on the node from which you run this command. For example, if the OCR mirror device name is /dev/raw1, then use the command syntax ocrconfig -repair ocrmirror /dev/raw1 on this node to repair its OCR configuration.

You cannot perform this operation on a node on which the Oracle Clusterware daemon is running.

Run the following command on any node in the cluster to remove the OCR

ocrconfig -replace ocr

Run the following command on any node in the cluster to remove the mirrored OCR

ocrconfig -replace ocrmirror

The above commands update the OCR configuration on all of the nodes on which Oracle Clusterware is running.
The ocrconfig Command Options

Option	Purpose
-backuploc	To change an OCR backup file location. For this entry , use a full path that is accessible by all of the nodes.
-downgrade	To downgrade an OCR to an earlier version.
-export	To export the contents of an OCR into a target file.
-help	To display help for the ocrconfig commands.
-import	To import the OCR contents from a previously exported OCR file.
-overwrite	To update an OCR configuration that is recorded on the OCR with the current OCR configuration information that is found on the node from which you are running this command.
-repair	To update an OCR configuration on the node from which you are running this command with the new configuration information specified by this command.
-replace	To add, replace, or remove an OCR location.
-restore	To restore an OCR from an automatically created OCR backup file.
showbackup	To display the location, timestamp, and the originating node name of the backup files that Oracle created in the past 4 hours, 8 hours, 12 hours, and in the last day and week. You do not have to be the root user to execute the -showbackup option.
-upgrade	To upgrade an OCR to a later version.

For example, to export the OCR contents to a binary file, use the ocrconfig command with the following syntax where file_name is the file to which you want to export the OCR contents as follows

ocrconfig -export file_name

Managing Backups and Recovering the OCR Using OCR Backup Files

This section explains the two methods for the purpose of copying of OCR content and further utilizing it for recovery. The first method uses automatically generated OCR file copies and the second method uses manually created OCR export files.

The Oracle Clusterware automatically creates OCR backups every four hours. At any one time, Oracle always retains the last three backup copies of the OCR. The CRSD process that creates the backups also creates and retains an OCR backup for each full day and at the end of each week.

Restoring the Oracle Cluster Registry on UNIX-Based Systems: use oracheck to check OCR status

Use the following procedure to restore the OCR on UNIX-based systems:

Identify the OCR backups using the ocrconfig -showbackup command. Review the contents of the backup using ocrdump -backupfile file_name where file_name is the name of the backup file.

Stop Oracle Clusterware on all of the nodes in your Oracle RAC database by executing the init.crs stop command on all of the nodes.

Perform the restore by applying an OCR backup file that you identified in Step 1 using the following command where file_name is the name of the OCR that you want to restore. Make sure that the OCR devices that you specify in the OCR configuration exist and that these OCR devices are valid before running this command.

ocrconfig -restore file_name

Restart Oracle Clusterware on all of the nodes in your cluster by restarting each node or by running the init.crs start command.

Run the following command to verify the OCR integrity where the -n all argument retrieves a listing of all of the cluster nodes that are configured as part of your cluster.

cluvfy comp ocr -n all [-verbose]

Use the OCRDUMP utility to write the OCR contents to a file so that you can examine the OCR content.

ocrdump [file_name|-stdout] [-backupfile backup_file_name] [-keyname keyname] [-xml] [-noheader]

OCRDUMP Options and Option Descriptions

Options	Description
file_name	Name of a file to which you want OCRDUMP to write output.
-stdout	The predefined output location that you can redirect with, for example, a filename.
-keyname	The name of an OCR key whose subtree is to be dumped.
-xml	Writes the output in XML format.
-noheader	Does not print the time at which you ran the command and when the OCR configuration occurred.
-backupfile	Option to identify a backup file.
backup_file_name	The name of the backup file the content of which you want to view. You can query the backups using the ocrconfig -showbackup command

OCR Exports/Imports

ocrconfig -export file_name

ocrconfig -import file_name

Restart Oracle Clusterware on all of the nodes in your cluster by restarting each node.

Run the following Cluster Verification Utility (CVU) command to verify the OCR integrity where the -n all argument retrieves a listing of all of the cluster nodes that are configured as part of your cluster:

cluvfy comp ocr -n all [-verbose]

Managing Data Guard using DGMGRL

Make dg_broker_start= TRUE on both primary (ocmdb) and secondary (phyocm) databases.

SQL> SHOW PARAMETER dg
SQL> ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;

Configure listener.ora for DGMGRL

These ‘_DGMGRL’ entries in the listener.ora files on each system are required for Broker operations invoked using DGMGRL that require connecting remotely to a database to function normally. Without them, the Broker cannot connect to the target database when it is down, as is the case in a switchover or failover or any other operation that requires a restart of a database.

As non-default port, local listener is used for ocmdb and phyocm. Add SID_DESC for DGMGRL for both Primary and Secondary on non-default port listener SID_LIST as mentioned in BOLD

SID_LIST_LISTENER_OCM =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ocmdb)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = ocmdb)
)
(SID_DESC =
(GLOBAL_DBNAME = ocmdb_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = ocmdb)
)
)

SID_LIST_LISTENER_PHYOCM =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = phyocm)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = phyocm)
)
(SID_DESC =
(GLOBAL_DBNAME = phyocm_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = phyocm)
)
)

Run “netmgr” and verify that the Oracle Net pre-requisites for using the DGMGRL command line interface have been satisfied.
Commands to get dgmgrl help

DGMGRL>help edit
DGMGRL>help remove
DGMGRL> help enable
DGMGRL> help start

DGMGRL Create Configuration

[oracle@ocm ~]$ dgmgrl

DGMGRL> connect sys@ocmdb

Create configuration with “ocmdb” as the Primary database. Add in the information about the physical standby database

DGMGRL> CREATE CONFIGURATION OCMDB AS PRIMARY DATABASE IS OCMDB
CONNECT IDENTIFIER IS OCMDB;
Configuration “ocmdb” created with primary database “ocmdb”

DGMGRL> ADD DATABASE phyocm AS CONNECT IDENTIFIER IS phyocm
MAINTAINED AS PHYSICAL;
Database “phyocm” added

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;
Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

DGMGRL> show database verbose ocmdb ;
Database
Name: ocmdb
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
ocmdb

Properties:
InitialConnectIdentifier = ‘ocmdb’
LogXptMode = ‘ASYNC’
Dependency = ”
DelayMins = ’0′
Binding = ‘OPTIONAL’
MaxFailure = ’0′
MaxConnections = ’1′
ReopenSecs = ’300′
NetTimeout = ’180′
LogShipping = ‘ON’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ’0′
ApplyParallel = ‘AUTO’
StandbyFileManagement = ‘AUTO’
ArchiveLagTarget = ’0′
LogArchiveMaxProcesses = ’2′
LogArchiveMinSucceedDest = ’1′
DbFileNameConvert = ”
LogFileNameConvert = ‘phyocm, ocmdb’
FastStartFailoverTarget = ”
StatusReport = ‘(monitor)’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘ocm.prusolutions.com’
SidName = ‘ocmdb’
LocalListenerAddress = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=ocm.prusolutions.com)(PORT=2010))’
StandbyArchiveLocation = ‘location=use_db_recovery_file_dest’
AlternateLocation = ”
LogArchiveTrace = ’0′
LogArchiveFormat = ‘%t_%s_%r.dbf’
LatestLog = ‘(monitor)’
TopWaitEvents = ‘(monitor)’

Current status for “ocmdb”:
SUCCESS

Managing Databases using DGMGRL

You can view and change database properties using dgmgrl

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> show database ocmdb nettimeout;
NetTimeout = ’180′

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> show database phyocm nettimeout;
NetTimeout = ’180′

Using sqlplus connect to primary database and change value of log_archive_max_processes parameter

SQL> SHOW PARAMETER log_archive_max_processes;

The current value is two, increase it to five and again check database configuration from dgmgrl

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=5 SCOPE=MEMORY;

Use show configuration command to get Dataguard status

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
Warning: ORA-16608: one or more databases have warnings

Use show database verbose ocmdb to get ocmdb database details

DGMGRL> show database verbose ocmdb ;

Database
Name: ocmdb
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
ocmdb

Current status for “ocmdb”:
Warning: ORA-16792: configuration property value is inconsistent with database setting

Get database inconsistent properties and fix the problem

DGMGRL> show database ocmdb inconsistentproperties;

INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
ocmdb LogArchiveMaxProcesses 5 2 2

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=2 SCOPE=MEMORY;

DGMGRL> show database ocmdb inconsistentproperties;

INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

Open new terminals connect to ocmdb and phyocm using sqlplus and execute following sql

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

Edit Database property using DG Broker

DGMGRL> edit database ocmdb set property LogArchiveMaxProcesses=10;
Property “logarchivemaxprocesses” updated

Change configuration set Protection Mode to MaxAvailability from MaxPerformance

When you try to change set Protection Mode to MaxAvailability, it will give error, as Standby database phyocm property logxptmode is ‘ASYNC’

DGMGRL> edit configuration set protection mode as maxavailability;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed.

Check database property logxptmode

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘ASYNC’

Change LogXptMode to SYNC of Physical Standby database PHYOCM ONLY and then change the protection mode

DGMGRL> edit database phyocm set property logxptmode=’SYNC’;
Property “logxptmode” updated

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> edit configuration set protection mode as maxavailability;
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.

Now change the property LogXptMode of ocmdb database to ‘SYNC’

DGMGRL> edit database ocmdb set property logxptmode=’SYNC’;
Property “logxptmode” updated

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

If due to some reason, the Dataguard Protection modes is not change to MaxAvailability using DGMGRL then do it from SQLPLUS

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Open new terminals connect to ocmdb and phyocm using sqlplus and execute following sql

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

Switch Over to Standby database phyocm

DGMGRL> switchover to phyocm;
Performing switchover NOW, please wait…
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “phyocm” on database “phyocm”
Shutting down instance “phyocm”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “phyocm” on database “phyocm”
Starting instance “phyocm”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “phyocm”

If instances are down start them in mount mode, the primary database will automatically open

SQL> STARTUP MOUNT;

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
ocmdb – Physical standby database
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

Open new terminals connect to ocmdb and phyocm using sqlplus and execute following sql

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

Switchover back to ocmdb

DGMGRL> switchover to ocmdb;

DGMGRL> show configuration;

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

Failover to phyocm

From sqlplus shutdown, abort primary database

[oracle@ocm ~]$ echo $ORACLE_SID
ocmdb

SQL> SHUTDOWN abort;

Using dgmgrl connect to new phyocm

[oracle@ocm ~]$ dgmgrl sys/oracle@phyocm

DGMGRL> failover to phyocm;

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Physical standby database (disabled)
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

The Protection Mode automatically goes back to MaxPerformance, but the logxptmode properties remains ‘SYNC’

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘SYNC’

Open a new terminal and set ORACLE_SID for phyocm

[oracle@ocm scripts]$ export ORACLE_SID=phyocm

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

Connect to ocmdb and startup database in mount state

SQL> STARTUP mount;

Now reinstate database ocmdb as physical standby

DGMGRL> reinstate database ocmdb;
Reinstating database “ocmdb”, please wait…
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Continuing to reinstate database “ocmdb” …
Reinstatement of database “ocmdb” succeeded

If reinstate fails for first time again start ocmdb database in mount state and reinstate, it will succeed in second or third attempt.

Switchover back to ocmdb to make ocmdb as Primary Database

DGMGRL> switchover to ocmdb;

Performing switchover NOW, please wait…
Operation requires shutdown of instance “phyocm” on database “phyocm”
Shutting down instance “phyocm”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “phyocm” on database “phyocm”
Starting instance “phyocm”…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “ocmdb”

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

Fast Start Failover

Connect to physical standby using dgmgrl

[oracle@ocm ~]$ dgmgrl sys/oracle@phyocm

Currently the Protection Mode is MaxPerformance, For fast start failover the Protection Mode should be MaxAvailability.

Anytime to change configuration to “MaxAvailability” make sure the primary database property is logxptmode is “ASYNC” and standby database “logxptmode” is “SYNC then only it can be changed to “MaxAvailability” otherwise it will fail. If maxavailability does not work with DGMGRL then you do it from SQLPLUS

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> edit database ocmdb set property logxptmode=’ASYNC’;
Property “logxptmode” updated

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> edit configuration set protection mode as maxavailability;

Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Now make primary database “logxptmode” is “SYNC
DGMGRL> edit database ocmdb set property logxptmode=’SYNC’;
Property “logxptmode” updated

Make sure both primary and secondary databases have FLASHBACK ON

SQL> SELECT db_unique_name,
database_role,
open_mode,
flashback_on
FROM v$database;

Edit database property faststartfailovertarget and enable fast_start failover

DGMGRL> edit database phyocm set property faststartfailovertarget=ocmdb;
Property “faststartfailovertarget” updated

DGMGRL> edit database ocmdb set property faststartfailovertarget=phyocm;
Property “faststartfailovertarget” updated

DGMGRL> enable fast_start failover;
Enabled.

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database
– Fast-Start Failover target

Current status for “ocmdb”:
Warning: ORA-16608: one or more databases have warnings

DGMGRL> show database verbose ocmdb;

You will see warning in the end mentioned below

Current status for “ocmdb”:
Warning: ORA-16819: Fast-Start Failover observer not started

Connect to ocmdb using sqlplus and execute following commands

SQL> SELECT fs_failover_status, fs_failover_current_target, fs_failover_threshold
FROM v$database;

SQL> SELECT fs_failover_observer_present, fs_failover_observer_host
FROM v$database;

Now go to FSFO Observer window and start it >it is best recommended to use the ‘-logfile’ qualifier on the DGMGRL command to direct any of its output message to a file that can be examined in case of a problem. Additionally it is also suggested to specify unique data file name that Observer uses. This can be done by adding the ‘FILE=’ command to the ‘start observer’ command.

This will also allow you to start multiple Observers for multiple Primary/Standby pairs on the same Observer system using the same Oracle Home. In this example, since everything is on the same system, the log in was made using the current ORACLE_SID, which was setup to be the standby database phyocm. In a real setup neither of your databases should be on the Observer system so you would have to use the ‘@tnsname’ qualifier on the ‘sys/password’ login

[oracle@ocm ~]$ export ORACLE_SID=phyocm

[oracle@ocm ~]$ echo $ORACLE_SID
phyocm

[oracle@ocm ~]$ dgmgrl -logfile $ORACLE_HOME/rdbms/log/dgmgrlOCMDB.LOG sys/oracle
DGMGRL for Linux: Version 10.2.0.1.0 – Production

Welcome to DGMGRL, type “help” for information.
Connected.

DGMGRL> start observer file=’/u01/app/oracle/afoocmdb.dat’;

Make sure to correct observer file correctly having “afo.dat” in start

Connect to ocmdb using sqlplus and execute following commands
SQL> SELECT fs_failover_status, fs_failover_current_target, fs_failover_threshold
FROM v$database;

FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD
——————— —————————— ———————
SYNCHRONIZED phyocm 30

SQL> SELECT fs_failover_observer_present, fs_failover_observer_host
FROM v$database;

FS_FAIL FS_FAILOVER_OBSERVER_HOST
——- —————————————-
YES ocm.prusolutions.com

Open a new session using dgmgrl and connect to standby database phyocm

[oracle@ocm ~]$ dgmgrl sys/oracle@phyocm
DGMGRL> show configuration;

Current status for “ocmdb”:
SUCCESS

Connect to ocmdb database and shutdown abort;

SQL> SHUTDOWN abort;
ORACLE instance shut down.

$ps –ef | grep –i pmon

Kill pmon process to primary database ocmdb to failover

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
ocmdb – Physical standby database (disabled)
– Fast-Start Failover target
phyocm – Primary database

Current status for “ocmdb”:
Warning: ORA-16608: one or more databases have warnings

DGMGRL> show database ocmdb;

Database
Name: ocmdb
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: ONLINE
Instance(s):
ocmdb

Current status for “ocmdb”:
Error: ORA-16661: the standby database needs to be reinstated

Connect to ocmdb instance and startup

[oracle@ocm ~]$ export ORACLE_SID=ocmdb

[oracle@ocm ~]$ sqlplus / as sysdba

SQL> STARTUP;
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 209715348 bytes
Database Buffers 314572800 bytes
Redo Buffers 11362304 bytes
Database mounted.
ORA-16649: database will open after Data Guard broker has evaluated Fast-Start
Failover status

It will take few minutes to get database synchronized. Execute following sql on both primary and secondary.

SQL> SELECT db_unique_name, open_mode, database_role FROM v$database;

SQL> SELECT fs_failover_status, fs_failover_current_target, fs_failover_threshold
FROM v$database;

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
ocmdb – Physical standby database
– Fast-Start Failover target
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

DGMGRL> STOP OBSERVER;
Done.

DGMGRL> disable fast_start failover;
Disabled.

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
ocmdb – Physical standby database
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

Switchover back to ocmdb

DGMGRL> switchover to ocmdb;

Performing switchover NOW, please wait…
Operation requires shutdown of instance “phyocm” on database “phyocm”
Shutting down instance “phyocm”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

Recovery Scenarios through RMAN

Complete Recovery when SYSTEM tablespace is missing.

Problem Generation

Delete system tablespace datafile from operating system when database is up and running.

[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk3/ocmdb/system01.dbf

Solution

i. [oracle@ocm ocmdb]$ sqlplus / as sysdba
ii. SQL> SHUTDOWN abort;
iii. SQL> STARTUP mount;
iv. SQL> SELECT file#, name FROM v$datafile;
v. Using rman connect to target database and catalog database or directly to target database.
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
vi. RMAN> run
{
restore datafile 1;
recover datafile 1;
sql ‘ alter database open ‘;
}

Complete Recovery when NON-SYSTEM tablespace is missing and database is open and restoring datafile to different location.

Problem Generation

i. Delete USERS tablespace datafile from operating system
[oracle@ocm ocmdb]$ rm -i users01.dbf
ii. Connect as user sh/sh and create a table in users tablespace
SQL> CREATE TABLE sales_test
TABLESPACE users
AS
SELECT *
FROM sales
WHERE ROWNUM < 10;

ERROR at line 3:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk3/ocmdb/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Solution

i. As DBA user make datafile users01.dbf off line
SQL> SELECT file#, name FROM v$datafile;
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;
ii. Restoring and recovering datafile 4 at new location
RMAN> run
{
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk5/ocmdb/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}

iii. Connect as user sh/sh and create a table in users tablespace

SQL> CREATE TABLE sales_test
TABLESPACE users
AS
SELECT *
FROM sales
WHERE ROWNUM < 10;

Table created.

Complete Recovery when NON-SYSTEM tablespace is missing and database is closed and restoring datafile to different location.

Problem Generation

i. Shutdown the database.
SQL> SHUTDOWN immediate;
ii. Delete USERS tablespace datafile from operating system
[oracle@ocm ocmdb]$ rm -i users01.dbf
iii. Start the database
SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 213909652 bytes
Database Buffers 318767104 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk5/ocmdb/users01.dbf’

Solution

i. Make datafile 4 offline
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;
ii. Open the database with datafile 4 oflfine
SQL> ALTER DATABASE OPEN;
iii. Using RMAN again connect to target database
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
iv. Restoring and recovering datafile 4 at new location
RMAN> run
{
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk3/ocmdb/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}

Recovery of datafile which has no backups

Problem Generation

i. Create Oracle Managed File tablespace
SQL> CREATE TABLESPACE reco_test;

ii. Create table sh_sales on tablespace reco_test.

SQL> CREATE TABLE sh_sales
TABLESPACE reco_test
AS
SELECT *
FROM sh.sales
WHERE ROWNUM < 10;

iii. Delete RECO_TEST tablespace datafile from operating system.
[oracle@ocm ~]$ rm –i /u01/app/oracle/oradata/disk5/OCMDB/datafile/o1_mf_reco_tes_6n8dnc7z_.dbf

iv. Select from table sh_sales.
SQL> SELECT COUNT (*) FROM sh_sales;

*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5:
‘/u01/app/oracle/oradata/disk5/OCMDB/datafile/o1_mf_reco_tes_6n8dnc7z_.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Solution

i. Recover Datafile 5.
RMAN> run {
sql ‘ alter database datafile 5 offline ‘;
restore datafile 5;
recover datafile 5;
sql ‘ alter database datafile 5 online ‘;
}

ii. Select from table sh_sales.
SQL> SELECT COUNT (*) FROM sh_sales;

Recover of missing controlfile ( Copy other controlfile and mount database).

Problem Generation

i. Shutdown the Database
SQL> SHUTDOWN immediate;
ii. Delete Control file control01.ctl from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl
iii. Startup Database.
SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 218103956 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

Solution

i. Check the control file location
SQL> SHOW PARAMETER control

NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/disk1/
ocmdb/control01.ctl, /u01/app/
oracle/admin/ocmdb/control/con
trol02.ctl
ii. Copy control01.ctl from control02.ctl
[oracle@ocm ocmdb]$ cp /u01/app/oracle/admin/ocmdb/control/control02.ctl /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl
iii. Mount the Database and Open it.
SQL> ALTER DATABASE MOUNT;

SQL> ALTER DATABASE OPEN;

Incomplete recovery is required when archived log, redo log is missing, then recovery can only be made until the previous sequence, or when an important object was dropped and recovery needs to be made until before the object was dropped. Use until sequence or until time or until cancel for incomplete recovery.

Problem Generation

i. Shutdown Database.
SQL> SHUTDOWN immediate;
ii. Delete redo log files from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk4/ocmdb/redo01b.log[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk2/ocmdb/redo01a.log

If redo log file is lost when database is up , always switch logfile few times until sql statement hangs to archive the available redo logfiles, which are still not archived.

iii. Startup the Database.
SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 218103956 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
‘/u01/app/oracle/oradata/disk2/ocmdb/redo01a.log’
ORA-00312: online log 1 thread 1:
‘/u01/app/oracle/oradata/disk4/ocmdb/redo01b.log’

Solution

i. Complete database restore is required ,execute sql below to get latest sequence# archived ,add 1 to last sequence and then use in RMAN to recover database.
SQL> SELECT thread#,
resetlogs_change#,
archived,
sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’) completion_time
FROM v$archived_log
WHERE archived = ‘YES’
AND completion_time = (SELECT MAX (completion_time)
FROM v$archived_log
WHERE archived = ‘YES’);

THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———- —————– — ———- ——————-
1 1 YES 58 2011-01-29:21:02:46

ii. Connect to target database using RMAN catalog
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb
iii. RMAN command to recover database.
RMAN> run
{
restore database;
recover database until sequence 59 thread 1;
sql ‘ alter database open resetlogs ‘;
}
iv. After database is open switch logfile few times
SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SWITCH LOGFILE;

Recovery if all controlfiles are missing

Problem Generation

i. Shutdown Database.
SQL> SHUTDOWN immediate;
ii. Delete all Control files from operating system
[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/oradata/disk1/ocmdb/control01.ctl

[oracle@ocm ocmdb]$ rm -i /u01/app/oracle/admin/ocmdb/control/control02.ctl
iii. Connect as sysdba and startup Database.
[oracle@ocm ocmdb]$ sqlplus / as sysdba

SQL> STARTUP;

ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 222298260 bytes
Database Buffers 310378496 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

Solution

i. Using RMAN restore contolfile from Autobackup and Recover Database
RMAN> run
{
restore controlfile from autobackup;
sql ‘ alter database mount’;
recover database;
sql ‘ alter database open resetlogs’;
}
ii. List Database Incarnation
RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 2 OCMDB 4083722939 PARENT 1 15-JAN-11
1 1415 OCMDB 4083722939 PARENT 503961 29-JAN-11
1 1867 OCMDB 4083722939 CURRENT 519078 29-JAN-11
iii. After database is open switch logfile few times
SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM SWITCH LOGFILE;
v. Always backup controlfile after open resetlogs even if CONTROLFILE AUTOBACKUP ON. Let us NOT BACKUP controlfile and see what happens in next scenario.
RMAN> backup current controlfile; — For now do not execute this command

Recover if all controlfiles,datafiles,spfile,redologs are lost

Problem Generation

i. Shutdown Database.

SQL> SHUTDOWN immediate;
ii. Delete all the datafiles ,controlfiles ,redo logfiles ,spfile ,initocmdb.ora files from Operating System
iii. Startup Database
SQL> STARTUP;

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/10.2.0/db_1/dbs/initocmdb.ora’

Make sure you have lost all the files not just spfile, if only spfile is lost , then too you will get above error you need to create only spfile.

Solution

i. Using RMAN connect to target database using catalog
[oracle@oem scripts]$ rman target sys/oracle@ocmdb catalog rman/rman@oemdb

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Jan 30 06:02:44 2011

connected to target database (not started)
connected to recovery catalog database
ii. Set Database DBID
RMAN> set dbid — dbid 4083722939
iii. Startup Database in Nomount State
RMAN> startup nomount;
iv. Restore spfile from Autobackup
RMAN> restore spfile from autobackup;

v. Again start the Database in Nomount State
RMAN> startup force nomount;
vi. Restore controlfile from Autobackup
RMAN> restore controlfile from autobackup;
vii. Mount Database
RMAN> alter database mount;
viii. Open a terminal in OCMDB machine and check archive log completion time
[oracle@ocm ~]$ sqlplus / as sysdba

SQL> SELECT thread#,
resetlogs_change#,
archived,
sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’)
completion_time
FROM v$archived_log
WHERE archived = ‘YES’
AND completion_time = (SELECT MAX (completion_time)
FROM v$archived_log
WHERE archived = ‘YES’);

THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———- —————– — ———- ——————-
1 519078 YES 1 2011-01-29:23:18:17

ix. By executing the command below we may not be able to recover database because control file is from older incarnation. This is because we have not taken backup of controlfile after reset logs and immediately crashed the database after recovery in previous scenario.
RMAN> run
{
restore database;
recover database until sequence 1 thread 1;
sql ‘ alter database open resetlogs’;
}
x. Now, we have to reset database to older incarnation and recover the database.
xi. List Database Incarnation.
RMAN> list incarnation;

xii. Open a terminal in OCMDB machine and check archive log completion time.
SQL> SELECT thread#,
resetlogs_change#,
archived,
sequence#,
TO_CHAR (completion_time, ‘YYYY-MM-DD:HH24:MI:SS’)
completion_time
FROM v$archived_log
WHERE archived = ‘YES’
ORDER BY completion_time;

THREAD# RESETLOGS_CHANGE# ARC SEQUENCE# COMPLETION_TIME
———- —————– — ———- ——————-
1 503961 YES 4 2011-01-29:22:18:18

Add 1 to the latest sequence of last incarnation i.e. 4+1 =5

xiii. Reset the Database to old incarnation and recover.
RMAN> reset DATABASE TO incarnation 1415;

RMAN> run
{
restore database;
recover database until sequence 5 thread 1;
sql ‘ alter database open resetlogs’;
}

Recovery if Temporary tablespace is lost

Recreate the temporary tablespace.

Saturday, July 30, 2011

Differential backup and Cumulative backup

Differencial backup: This is the default type of incremental backup which backs up all blocks changed after the most recent backup at level n or lower.

Cumulative backup: Backup all blocks cahnged after the most recent backup at level n-1 or lower.

A cumulative incremental backup is the backup of all files that have changed since the last full backup.

A differential incremental backup is the backup of all files that have changed since the last backup.

Hi,
Here I'm descrbing RMAN backups Types,Commands and Usage in Brief in Oracle 11g Database.Hope it will help one and all.

RMAN BACKUPS:
----------------

1)RMAN HOT BACKUP =>database up & running
2)RMAN COLD BACKUP =>database is not up(shutdown =>mount(now take backup).

1)RMAN HOT BACKUP: OPEN STATE (ARCHIVE_LOG MODE)
2)RMAN COLD BACKUP:MOUNT STATE (NO ARCHIVE LOG MODE)

INCREMENTAL BACKUP:
1)DIFFERENTIAL BACKUP =>BY DEFAULT INCREMENTAL BACKUP IS DIFFERENTIAL.
=>BACKUP FROM SAME OR LOWER LEVEL.
=>LEVEL 0,LEVEL 1,LEVEL 2.....
2)CUMMULATIVE BACKUP =>BACKUP FROM LEVEL 0(LOWEST LEVEL)
=>LEVEL 0,LEVEL 1,LEVEL 2.....

WHERE:
LEVEL 0=FULL BACKUP
LEVEL 1=CHANGE DATA FROM LEVEL 0
LEVEL 2=CHANGE DATA FROM LEVEL 1...

BACKUP STRATERGY:
--------------------

SUNDAY =LEVEL 0 =>INCREMENTAL (DIFFERENTIAL)=>BACKUP LEVEL 0(LOWEST LEVEL)
MONDAY =LEVEL 1 =>INCREMENTAL
TUESDAY=LEVEL 2 =>INCREMENTAL
WED =LEVEL 0 =>INCREMENTAL (CUMMULATIVE) =>BACKUP LEVEL 0(LOWEST LEVEL)
THUR =LEVEL 1 =>INCREMENTAL
FRI =LEVEL 2 =>INCREMENTAL
SAT= =LEVEL 1 =>CUMMULATIVE =>BACKUP LEVEL 1(CHANGE DATA FROM LEVEL 0)

TIMESTAMP SYMBOLS:
-------------------

%U=UNIQUE BACKUPSET NAME
%T= TIMESTAMP OF TIME OF BACKUPSET

RMAN COMMANDS:
----------------

INCREMENTAL BACKUP:
---------------------

1)DIFFERENTIAL BACKUP
2)CUMULATIVE BACKUP

1)DIFFERENTIAL BACKUP:
-- INCREMENTAL LEVEL 0
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=0 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

-- INCREMENTAL LEVEL 1
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=1 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

-- INCREMENTAL LEVEL 2
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=2 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

2)CUMULATIVE BACKUP:
----------------------

-- CUMMULATIVE BACKUP
RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> Backup incremental level=0 CUMULATIVE database tag='complete_backup';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }

released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK

Starting backup at 24-JUL-11
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1HMI7EPO_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1IMI7EPP_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:15
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1JMI7ES5_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11

released channel: ch1

-- CUMULATIVE BACKUP LEVEL LOWEST(0)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=0 CUMULATIVE DATABASE FILESPERSET 4 tag='cumulative';
tag='complete_cummulative_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

-- CUMULATIVE BACKUP LEVEL LOWEST(1)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }

released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK

Starting backup at 24-JUL-11
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1NMI7F9M_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:25
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1OMI7FCC_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:35
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1PMI7FDF_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11

released channel: ch1

allocated channel: c1
channel c1: SID=73 device type=DISK

Starting backup at 24-JUL-11
channel c1: starting datafile copy
copying current control file
output file name=D:\RMAN_BACKUP\ORCL_CTRL_BACKUP2_CF_D-ORCL_ID-1280115002_1QMI7FDK_20110724 tag=TAG20110724T055044 R
D=31 STAMP=757317045
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11

released channel: c1

ARCHIVE LOG DELETION POLICY FOR A DATABASE:
----------------------------------------------

run {
allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate -5';
}

I do backups from the primary database to a local drive and have been puzzled
how to delete the standby archive logs after they ship.
The ‘obvious’ solution is the documented feature in RMAN:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

COMPRESSION LEVEL ORACLE 11g:
----------------------------------

basic =DEFAULT
none =Not recomended
medium = license
high =license

Drawbacks:
------------

medium =>consume hight system resource
high =>consume high system resource

II)RMAN COLD BACKUP:
----------------------

=>NOARCHIVE LOG MODE
=>MOUNT STATE
=> DEVELOPMENT => MUCH DISK SPACE IS NOT THERE
=>DOWNTIME TOLERABLE..

run_orcl.txt :
-----------------------------------------------------

* SNAPSHOT CONTROLFILE:RMAN USES FOR BACKUP OF CONTROL FILE.

Configure setting in RMAN :
----------------------------
We can use configure command to change any setting in RMAN.I would definetly like to outside my script like below.

$rman target / catalog rman/rman@catdb
RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';

rman_cold.txt:
--------------

run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
backup database TAG='ORCL_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\TESTDB_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}

rman_orcl.bat:
---------------

-- orcl DB RMAN COLD BACKUP
set ORACLE_SID=orcl
set ORACLE_HOME=D:\app\RafiAlvi\product\11.2.0\dbhome_1
set ORACLE_BASE=D:\app\RafiAlvi\
rman target sys/orcldba @D:\RMAN_BACKUP_SCRIPTS\run_ORCL.txt log=D:\RMAN_BACKUP_SCRIPTS\LOGS\rman_cold_orcl_24july_cum.log

Note: In Unix environment use export for setting the enviromnmet and Write shell script 'rman_orcl.sh',rest all script is almost same and can fit as per our path in Unix environment.

RMAN BACKUP DETAILS Views:
---------------------------------

We can use Dictionary Views for checking the status of rman backups.

1)USE AFTER SCHEDULING RMAN BACKUP:
SELECT START_TIME||' '||END_TIME||' '||STATUS FROM V$RMAN_BACKUP_JOB_DETAILS;

2)USE TO CHECK BYYES PROCESSED IN RMAN BACKUP:
SELECT SID||' '||STATUS||' '||MBYTES_PROCESSED||' '||START_TIME||' '||END_TIME
FROM V$RMAN_STATUS;

3)USE TO CHECK SESSION AND RMAN BACKUP OUTPUT:
SELECT SID||' '||RECID||' '||OUTPUT||' '||SESSION_STAMP
FROM V$RMAN_OUTPUT;

Note: On Unix terminal,It will be very much useful to check the process in order to check the status by using 'ps' command.
$ps -eaf|grep rman

Incremental backup and restore in oracle 10g

Here I am presenting the simple steps to demonstrate how to take incremental level backup,perform restore and recovery using RMAN.One can learn Rman incremental level backup easily by going through this post. I used oracle 10g express edition.

Difference between differential and cumulative incremental backup will be clear by going through this simple demo.

Simple scenarios has been taken.

Demonstrating DIFFERENTIAL INCREMENTAL BACKUP

1. Database is in NOARCHIVELOG mode.
2. Not using recovery catalog.
3. RMAN configuration setting that I used is following. I am posting the output of
RMAN> SHOW ALL;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 400 M FORMAT '/usr/lib/oracle/xe/backup/df_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/snapcf_XE.f'; # default

4. Since database is in NOARCHIVELOG mode, it is necessary to shut down the database cleanly using immediate,normal or transactional options.

RMAN> shutdown

5. To take incremental 0 level backup,mount the database. This step is required even in non-incremental backups.

RMAN> startup mount

6. Issue
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
This takes the full backup of the database and also includes controlfile as well as spfile.

7. Issue
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0

2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1084366

8. RMAN> ALTER DATABASE OPEN;

9. Now, perform some DML operations. For example,

SQL> CREATE TABLE T1(C1 NUMBER);
SQL> INSERT INTO T1 VALUES(10);
SQL> /
SQL> /
SQL> COMMIT;

10. Again, shutdown the database to take incremental level backup.
RMAN>SHUTDOWN
RMAN>STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';
It backups only the changes made since the last incremental level n or lower backup.

11. Open the database again to perform some insertions.
RMAN> ALTER DATABASE OPEN;

12. SQL> INSERT INTO T1 VALUES(1);
SQL> /
SQL> /
SQL> /
SQL> COMMIT;

13. Shutdown the database again to take incremental level 2 backup.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 2 DATABASE TAG 'INC_1';

14. Open database again to made some insertions.
RMAN> ALTER DATABASE OPEN;
SQL> INSERT INTO T1 VALUES(9);
SQL> /
SQL> /
SQL> COMMI;

15. select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0

2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1084663

Here all the changes since the last backup has been stored in the redo logs.

16. Now, delete controlfile.
$ mv oradata/XE/controlfile cf.bak

17. RMAN> SHUTDOWN ABORT;

18. RMAN> STARTUP
Database will not open. Database will only go upto the NOMOUNT state since controlfile has been lost.

19. RMAN> Now following steps are needed to recover the database.

20. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
If controlfile autobackup is located in default location, then we can skip this step.

21. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP

22. RMAN> ALTER DATABASE MOUNT;

23. RMAN> RESTORE DATABASE;
It will restore the database using the 0 level backup.

24. RMAN> RECOVER DATABASE
Watch the output carefully. You can recognise various backups that are being applied. Look for the tags that you have given to backupsets. It will applies all the incrementals one by one. First it will apply level 1 incremental, and then level 2. Then it will search for appropriate log sequence and applies the same if found. If log switching has not been taken place after the last incremental backup, then we get all the data without any data loss. And database will restore upto the current point in time. In our case no log switching has taken place and all the data since the last backup exist in the redologs. NOREDO option is needed when log switching has taken place. NOREDO option is not needed if log switch has not taken place.

25.RMAN> ALTER DATABASE OPEN RESETLOGS;

26. Now, view the table t1 and you will find the table restored upto the latest point in time if all the redos has been applied.

27. Always take full backup of the database after opening the database in resetlogs mode.

Demonstrating CUMULATIVE INCREMENTAL BACKUP

Here we will utilize same scenario as above,i.e. no recovery catalog mode,no archivelog mode.
We will start here by taking incremental level 0 backup.

1. RMAN> SHUTDOWN
2. RMAN> STARTUP MOUNT
3. RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
4. RMAN> ALTER DATABASE OPEN;

5. SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;
6. Make some insertion.
SQL> INSERT INTO T1 VALUES(2);
SQL> /
SQL> /
SQL> COMMIT;

7. Again shutdown the database to take incremental level 1 database which will copies only the changed blocks since the last incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';

8.ALTER DATABASE OPEN;

9. SQL> INSERT INTO T1 VALUES(3);
SQL> /
SQL> /
SQL> COMMIT;

10. Again shutdown the database to take cumulative incremental level 1 backup, this time. This backups all the changes made after the last n-1 or lower backup,here it will backup all the changes since the incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'CUM_1';

11. RMAN> ALTER DATABASE OPEN;

12. Perform some DML again.
SQL> INSERT INTO T1 VALUES(9);
SQL>/
SQL> /
SQL> COMMIT;

13. Now, we will shutdown the database to take incremental level 1 backup this time.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1_1';

14. RMAN> ALTER DATABASE OPEN;

15. SQL> INSERT INTO T1 VALUES(0);
SQL> /
SQL> COMMIT;

16. Delete controlfile.
$ mv oradata/XE/controlfile cf.bak

17. RMAN> SHUTDOWN ABORT

18. RMAN> STARTUP
Database will not open.It will go upto only NOMOUNT state.

19. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/XE/backup/cf_%F';

20. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

21. RMAN> ALTER DATABASE MOUNT;

22. RMAN> RESTORE DATABASE;,
It will restore the database from the last incremental level 0 database.

23. RMAN> RECOVER DATABASE;
Here NOREDO option is not necessary as it is assumed that redo logs has not been switched and complete recovery is possible. Check the same by looking at the contents of v$log and comparing with the checkpoint_change# that have noted in step 5. If all the changes since that value exist in the redo logs then NOREDO option is not needed. Else it is required.

Here first of all, cumulative incremental level 1 backup would restore as there is no need for the backup having tag 'INC_1' as cumulative incremental level 1 backup take backup of all the changes since the last level 0 backup. Thus our first incremental level 1 backup is not applied. Then it applies backup having tag 'INC_1_1' which we take after cumulative incremental level backup. After that it will apply all the changes recorded in the online redologs.
Thus , only two incremental level backups needed to apply here. But in the first scenario all the incremental backups had been applied. Thus we can say that using cumulative incremental backup in your incremental strategy provides faster recovery as number of incremental backups to be applied is less.

24. RMAN> ALTER DATBASE OPEN RESETLOGS;

25. Take whole database backup after opening database in resetlogs mode. It is a good practice to perform th same.

26. Now check your table. It must have all the changes that we made, keeping all the scenario same.

ROHIT Blog

Sunday, November 6, 2011

Voting disk and OCR Management

Voting disk and OCR Management

Run the following command to find path of voting disks

Run the following command as the `root` user to add a voting disk

Run the following command as the root user to remove a voting disk

Backup and Recovery of Voting Disk

Administering the Oracle Cluster Registry in Oracle Real Application Clusters

First, make sure you have proper backup of ocr

Managing Backups and Recovering the OCR Using OCR Backup Files

Managing Data Guard using DGMGRL

Managing Data Guard using DGMGRL

Recovery Scenarios through RMAN

Saturday, July 30, 2011

Differential backup and Cumulative backup

Incremental backup and restore in oracle 10g

Followers

Blog Archive

About Me

ROHIT Blog

Sunday, November 6, 2011

Voting disk and OCR Management

Voting disk and OCR Management

Run the following command to find path of voting disks

Run the following command as the root user to add a voting disk

Run the following command as the root user to remove a voting disk

Backup and Recovery of Voting Disk

Administering the Oracle Cluster Registry in Oracle Real Application Clusters

First, make sure you have proper backup of ocr

Managing Backups and Recovering the OCR Using OCR Backup Files

Managing Data Guard using DGMGRL

Managing Data Guard using DGMGRL

Recovery Scenarios through RMAN

Saturday, July 30, 2011

Differential backup and Cumulative backup

Incremental backup and restore in oracle 10g

Followers

Blog Archive

About Me

Run the following command as the `root` user to add a voting disk