ROHIT Blog

Upgrading the Protection Mode and Downgrading the Protection Mode

2011-12-08T14:15:00.000-08:00

Upgrading the Protection Mode
Use the following steps to upgrade the protection mode from the default of Maximum Performance.

Identify the current configuration:

Primary Database (TNS Service Name): TESTDB_VMLINUX3.IDEVELOPMENT.INFO
Standby Database (TNS Service Name): TESTDB_VMLINUX4.IDEVELOPMENT.INFO
Current Protection Mode: Maximum Performance mode using ARCH

Both of the higher protection modes (Maximum Availability and Maximum Protection) require the use of standby redo logs on the destination standby database. If standby redo logs do not exist for the standby database, create them now.

[Connect to the standby database]

SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba

[Verify if using oracle managed files]

SQL> select value from v$parameter where name = 'db_create_online_log_dest_1';

VALUE
-------------------
/u02/oradata/TESTDB

[Do any standby redo logs exist on the standby database?]

SQL> select group#, bytes, 'online' as type from v$log
2 union
3 select group#, bytes, 'standby' as type from v$standby_log
4 order by 1;

GROUP# BYTES TYPE
---------- ---------- -------
1 104857600 ONLINE
2 104857600 ONLINE
3 104857600 ONLINE

[Need to first cancel Managed Recovery mode if it is running]

SQL> alter database recover managed standby database cancel;

[Create appropriate number standby redo log files on the standby database]

SQL> alter database add standby logfile group 4 size 100m;
SQL> alter database add standby logfile group 5 size 100m;
SQL> alter database add standby logfile group 6 size 100m;
SQL> alter database add standby logfile group 7 size 100m;

[Put standby database back into Managed Recovery mode]

SQL> alter database recover managed standby database disconnect from session;

[Verify new standby redo logs on standby database]

SQL> select group#, bytes, 'online' as type from v$log
2 union
3 select group#, bytes, 'standby' as type from v$standby_log
4 order by 1;

GROUP# BYTES TYPE
---------- ---------- -------
1 104857600 ONLINE
2 104857600 ONLINE
3 104857600 ONLINE
4 104857600 STANDBY
5 104857600 STANDBY
6 104857600 STANDBY
7 104857600 STANDBY

Ensure that the attributes for LOG_ARCHIVE_DEST_n are configured on the primary instance to support the desired protection mode. Also note that the destination standby database should be enabled to support the target protection mode as documented in this article.

For the mode of Maximum Protection, the standby database must be up and mounted!

Set the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to support the required protection mode:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba

[Configure log transport services to support the desired protection mode]

SQL> alter system set log_archive_dest_2='service=testdb_vmlinux4.idevelopment.info LGWR SYNC AFFIRM';
SQL> alter system set log_archive_dest_state_2=enable;

The primary database will need to be closed and then placed in the MOUNT stage.

SQL> shutdown immediate
SQL> startup mount

On the primary database, change the protection mode and open the database:

For Maximum Protection mode:

SQL> alter database set standby database to maximize protection;
SQL> alter database open;

For Maximum Availability mode:

SQL> alter database set standby database to maximize availability;
SQL> alter database open;

Query the data dictionary on the primary database to verify the new protection mode:

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE
------------------- ------------------- --------------
MAXIMUM PROTECTION MAXIMUM PROTECTION PRIMARY

(Optional) Although the steps in this section are optional, there are highly recommended so that the primary database can easily and quickly switchover to a standby role without the need for DBA intervention. For example, standby redo logs are only used on the physical standby database; however, creating and having standby redo logs ready to go on the physical primary database makes role transition much easier if the primary would every have to become the standby:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba

[Do any standby redo logs exist on the primary database?]

SQL> select group#, bytes, 'online' as type from v$log
2 union
3 select group#, bytes, 'standby' as type from v$standby_log
4 order by 1;

GROUP# BYTES TYPE
---------- ---------- -------
1 104857600 ONLINE
2 104857600 ONLINE
3 104857600 ONLINE

[Create appropriate number standby redo log files on the primary database]

SQL> alter database add standby logfile group 4 size 100m;
SQL> alter database add standby logfile group 5 size 100m;
SQL> alter database add standby logfile group 6 size 100m;
SQL> alter database add standby logfile group 7 size 100m;

[Verify new standby redo logs on primary database]

SQL> select group#, bytes, 'online' as type from v$log
2 union
3 select group#, bytes, 'standby' as type from v$standby_log
4 order by 1;

GROUP# BYTES TYPE
---------- ---------- -------
1 104857600 ONLINE
2 104857600 ONLINE
3 104857600 ONLINE
4 104857600 STANDBY
5 104857600 STANDBY
6 104857600 STANDBY
7 104857600 STANDBY

Also, on the standby database(s), configure the LOG_ARCHIVE_DEST_n parameter attributes so the Data Guard configuration can continue to operate in the new protection mode after a switchover:

[Connect to the standby database]

SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba

SQL> alter system set log_archive_dest_2='service=testdb_vmlinux3.idevelopment.info LGWR SYNC AFFIRM';
SQL> alter system set log_archive_dest_state_2=defer;

Downgrading the Protection Mode
Use the following steps to downgrade the protection mode from the current higher protection mode.

Identify the current configuration:

Primary Database (TNS Service Name): TESTDB_VMLINUX3.IDEVELOPMENT.INFO
Standby Database (TNS Service Name): TESTDB_VMLINUX4.IDEVELOPMENT.INFO
Current Protection Mode: Maximum protection using LGWR SYNC AFFIRM

Configure the attributes for LOG_ARCHIVE_DEST_n on the primary instance to support the new desired protection mode:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba

[Configure log transport services to support the desired protection mode]

SQL> alter system set log_archive_dest_2='service=testdb_vmlinux4.idevelopment.info ARCH';
SQL> alter system set log_archive_dest_state_2=enable;

Shutdown and mount the primary and standby database:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba
SQL> shutdown immediate
SQL> startup mount

[Connect to the standby database]

SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;

Downgrade the primary database to the desired protection mode:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba
[For Maximum Performance mode]

SQL> alter database set standby database to maximize performance;
[For Maximum Availability mode]

SQL> alter database set standby database to maximize availability;

Both of the higher protection modes (Maximum Availability and Maximum Protection) require the use of standby redo logs on the destination standby database. It you were to downgrade from Maximum Protection mode to Maximum Availability mode, you would need to keep the standby redo logs that exist for the standby database. If downgrading to Maximum Performance mode, you can drop the standby redo logs from the standby unless you are going to be configuring log transport services to use LGWR. For this example, I am going to be downgrading to maximum performance mode and using ARCH for log transport services and can therefore drop any standby redo logs from the standby database (and the primary if they exist):

[Connect to the standby database]

SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba

[Drop any standby redo logs from the standby database]

SQL> alter database drop standby logfile group 4;
SQL> alter database drop standby logfile group 5;
SQL> alter database drop standby logfile group 6;
SQL> alter database drop standby logfile group 7;

If standby redo logs exist on the primary and you are downgrading to Maximum Performance mode, they can be removed:

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba

[Drop any standby redo logs from the primary database]

SQL> alter database drop standby logfile group 4;
SQL> alter database drop standby logfile group 5;
SQL> alter database drop standby logfile group 6;
SQL> alter database drop standby logfile group 7;

Put the standby database in managed recovery mode and open the primary database with the new protection mode enabled:

[Connect to the standby database]

SQL> connect sys/change_on_install@testdb_vmlinux4.idevelopment.info as sysdba
SQL> alter database recover managed standby database disconnect from session;

[Connect to the primary database]

SQL> connect sys/change_on_install@testdb_vmlinux3.idevelopment.info as sysdba
SQL> alter database open;

Query the data dictionary on the primary database to verify the new protection mode:

SQL> select protection_mode, protection_level, database_role from v$database;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE
-------------------- -------------------- --------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY

Different kind of protection modes Oracle offers in a Data Guard Environment

2011-12-08T13:53:00.000-08:00

Maximum Protection—This mode offers the highest level of data protection. Data is synchronously transmitted to the standby database from the primary database and transactions are not committed on the primary database unless the redo data is available on at least one standby database configured in this mode. If the last standby database configured in this mode becomes unavailable, processing stops on the primary database. This mode ensures no-data-loss.

Maximum Availability—This mode is similar to the maximum protection mode, including zero data loss. However, if a standby database becomes unavailable (for example, because of network connectivity problems), processing continues on the primary database. When the fault is corrected, the standby database is automatically resynchronized with the primary database.

Maximum Performance—This mode offers slightly less data protection on the primary database, but higher performance than maximum availability mode. In this mode, as the primary database processes transactions, redo data is asynchronously shipped to the standby database. The commit operation of the primary database does not wait for the standby database to acknowledge receipt of redo data before completing write operations on the primary database. If any standby destination becomes unavailable, processing continues on the primary database and there is little effect on primary database performance.

How to find log sequences used during the recovery

2011-11-15T07:07:00.000-08:00

RMAN> restore database preview;

Starting restore at 2009-MAY-22 11:24:50

using channel ORA_DISK_1

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     

------- ---- -- ---------- ----------- ------------ --------------------

1       Full    209.16M    DISK        00:01:50     2009-MAY-22 11:21:04

        BP Key: 1   Status: AVAILABLE  Compressed: YES  Tag: TAG20090522T111914

        Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_05_22/o1_mf_nnndf_TAG20090522T111914_51d6fm3b_.bkp

  List of Datafiles in backup set 1

  File LV Type Ckp SCN    Ckp Time             Name

  ---- -- ---- ---------- -------------------- ----

  1       Full 1621673    2009-MAY-22 11:19:15 /u01/app/oracle/oradata/ORCL/system01.dbf

  2       Full 1621673    2009-MAY-22 11:19:15 /u01/app/oracle/oradata/ORCL/sysaux01.dbf

  3       Full 1621673    2009-MAY-22 11:19:15 /u01/app/oracle/oradata/ORCL/undotbs01.dbf

  4       Full 1621673    2009-MAY-22 11:19:15 /u01/app/oracle/oradata/ORCL/users01.dbf



List of Archived Log Copies for database with db_unique_name ORCL

=====================================================================



Key     Thrd Seq     S Low Time            

------- ---- ------- - --------------------

30      1    97      A 2009-MAY-22 11:11:24

        Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_05_22/o1_mf_1_97_51d6kvcr_.arc

Media recovery start SCN is 1621093

Recovery must be done beyond SCN 1621673 to clear datafile fuzziness

Finished restore at 2009-MAY-22 11:24:51

RMAN> exit





Recovery Manager complete.

How to find log sequences used during the recovery

From above restore preview, we confirm that all redo logs generated from SCN 1621093 and 1621673 will be used.

SQL> select thread#,sequence# from v$archived_log

  2  where --cross begin backup only (sample SEQ# 90)

  3  (first_change#<=1621093 and next_change#>=1621093 and next_change#<=1621673)

  4  or    --within begin backup and end backup (sample SEQ# 91,92)

  5  (first_change#>=1621093 and next_change#<=1621673)

  6  or   --cross end backup only (sample SEQ# 93)

  7  (first_change#>=1621093 and first_change#<=1621673 and next_change#>=1621673)

  8  or   --cross begin backup and end backup (sample SEQ# 150)

  9  (first_change#<=1621093 and next_change#>=1621673)

 10  ;



   THREAD#  SEQUENCE#

---------- ----------

         1         97

Oracle dba Interview Questions

2011-11-15T06:55:00.000-08:00

Oracle dba Interview Questions --------------------à>>>>>>>>>

Because you did not perform the restore and recovery when connected to the recovery catalog, the recovery catalog contains no records for any of the restored files or the procedures performed during the test. Likewise, the control file of the trgta database is completely unaffected by the test.

Q-1: One of my control file corrupted and I am unable start database, How can I perform recovery?

If one of your control file is missing or corrupted then we have 2 options to recover it. Check alert.log for exact name and location of corrupted control file. Delete it manually and copy from available rest of control file and rename it and start database. Another option is delete corrupted control file and remove name from parameter file/ spfile. After removing said control file from spfile, start your database.

Q-2: What is incremental checkpoint?

In incremental checkpoint process, CKPT process records lowest Low RBA to the control file to keep advancing the Buffer checkpoint Queue (BCQ) to make easy and fastest Active Checkpoint Queue (ACQ).

Q-3: Does incremental checkpoint recorded in Alert.log? How to disable it?

We can enable disable recording incremental checkpoint in alert.log .We can enable/disable recording using parameter log_checkpoints_to_alert=true/false.

Q-4: I am working as Oracle DBA in 24/7 running large production database. Size of database is around 800 GB. We take hot backup every day night. But one day at around 4:00 PM, by mistake one table is dropped by application user. Table is very useful. How to recover that dropped table?

If your database is running on Oracle 10g version then there is new feature available called Recyclebin. You can recover dropped table from user_recyclebin or dba_recyclebin.

Q-5: In continuation of above scenario, No. Recyclebin doesn’t enable in my database then how to I recover my table in above scenario?

Then you should need to restore backup on your UAT or test database server and enable time based recovery for applying all archives before drop command execution. For an instance, apply archives up to 3:55 PM here.

Q-6: In continuation of above scenario, why I should need to perform recovery in UAT or test database server? Can I restore backup in production database server?

No. It is not recommended because your production database is large database and running 24/7 environment. Restoration and recovery will take downtime. It is better to perform restoration and recovery process on UAT or Test database.

Q-7: What is the meaning of LGWR SYNC and LGWR ASYNC in log archive destination parameter for standby configuration?

When use LGWR with SYNC, it means once network I/O initiated, LGWR has to wait for completion of network I/O before write processing. LGWR with ASYNC, means LGWR doesn’t wait to finish network I/O and continuing write processing.

Q-8: How can I know my require table is available in export dump file or not?

Create indexfile of export dump file using import with indexfile command. A text file will be generating with all table and index object name with number of rows. You can confirm your require table object from this text file.

Q-9: Archive log are being generated around 20 GB in my production large database. But one day almost double archives were generated. What is the reason behind this? How can I check it?

There are lots of reason behind increasing size of archives like If more database changes were performed using batch jobs or any special task like merging 2 database or data etc. You can check it using enabling Log Minor utility.

Q-10: How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?

We can perform this recovery in 2 ways. One is open database mode and another is database mount mode. After taking offline lost datafile, we can bring database open and after that restore lost datafile from last backup. After restoration of datafile we can perform datafile level recovery for applying archive logs and make it online. In database mount mode, we can restore datafile from backup and perform datafile recovery using “Recover datafile” command.

Q-11: What is a Complete Recovery?

During this recovery we are applying all database changes using archives log and make it to up to date. Complete recovery can be done full database level or datafile level or tablespace level. Main concept is to apply all data changes using archive logs and bring database up to recent time.

Q-12: Why we should need to open database using RESETLOGS after finishing incomplete recovery?

When we are performing incomplete recovery it means we bring database to past time or rewind period of time using change based, cancel based or time based recovery. These all recovery make database in prior state of database. The forward sequence number already available after performing recovery, due to mismatching of this sequence numbers and prior state of database, it needs to open database with new sequence number of Redo log and archive logs.

Q-13: Why export backup called as logical backup?

Export dump file doesn’t backup or contain any physical structure of database like datafile, control file, redo log file, parameter file, password file. Instead of physical structure, export dump contains logical structure of database like definition of tablespaces, segments, schemas, data of segments etc. Due to these reasons export dump is called as logical backup.

Q-14: Have you faced any recovery scenario? Explain us how you resolved it?

2 months back, a potential table was dropped by an application developer on our large 24/7 running production database. Immediately he informed us. We have standby database for our production database. We checked that archives didn’t apply to standby database up to dropped timing. We stopped archive applying on standby immediately and open database read only. Took export of dropped table and imported in production database. After finishing appropriate checking of table, again we started archive applying in standby database for refreshing.

Q-15: Which command I should need to execute to take backup of database which is running on NOARCHIVELOG mode?

Shutdown immediate. Because without shutdown, we can’t perform online backup of database, which running on NOARCHIVELOG mode. We should need to take cold backup of database.

Voting disk and OCR Management

2011-11-06T23:22:00.000-08:00

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

2011-11-06T23:08:00.000-08:00

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

2011-11-06T22:52:00.001-08:00

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.

Differential backup and Cumulative backup

2011-07-30T01:08:00.000-07:00

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.

Oracle Clusterware Components

2010-12-17T00:52:00.000-08:00

Oracle Clusterware is the software, which enables the nodes to communicate with each other, and forms the cluster and makes the nodes as single logical server. Oracle Clusterware is run by Cluster Ready Services (CRS) using two key components. They are Oracle Cluster Registry voting disk, which acts a tiebreaker during communication failures. Consistent heartbeat information from all the nodes is sent to voting disk when the cluster is running. CRS service has four components namely OPROCd, CRS Daemon (crsd), Oracle Cluster Synchronization Service Daemon (OCSSD) and Event Volume Manager Daemon (evmd) and each handles a variety of functions. Failure or death of the CRS daemon can cause the node failure and it automatically reboots the nodes to avoid the data corruption because of the possible communication failure between the nodes. The CRS daemon runs as the super user UNIX platforms and runs as a service in the windows platforms. (OCR), which records and maintains the cluster and node membership information. The other component is ‘root’ in the

The following functionalities are covered by Oracle Cluster Ready Services

CRS is installed and run from a different oracle home known as ORA CRS HOME, which is independent from ORACLE HOME.

CRSD --

CRSd manages the resources like starting and stopping the services and failing-over the application resources. It spawns separate processes to manage application resources.
CRS daemon has two modes of running. During startup and after a shutdown. During planned clusterware start it is started as ‘reboot’ mode. It is started as ‘restart’ mode after unplanned shutdown.
In reboot mode it ‘auto’ starts all the resources under its management. In restart mode it prevails the previous state and brings back the resources to it previous state before shutdown
Manages the Oracle Cluster Registry and stores the current known state in the Oracle Cluster Registry
Runs as ‘root’ on Unix and ‘LocalSystem’ on windows and automatically restarts in case of failure.
CRS requires the public interface, private interface and the Virtual IP (VIP) for the operation. All these interfaces should be up and running, should be able to ping each other before starting CRS Installation. Without the above network infrastructure CRS cannot be installed.

OCSSD ---

Oracle Cluster Synchronization Services Daemon (ocssd) is the component, which provides the synchronization services between the nodes. OCSSD provides the access to the node membership. It also enables basic cluster services including cluster group services and cluster locking. It can also run without integration with vendor clusterware. Failure of ocssd causes the machine to reboot to avoid split-brain situation. This is also required in a single instance configuration if Automatic Storage Management (ASM) is used. Automatic Storage management was a new feature in oracle database 10g. Ocssd runs as ‘oracle’ user. The following functionalities are covered by CSS daemon

CSS provides basic ‘group services’ support. Group Services is a distributed group membership system that allows the applications to coordinate activities to achieve a common result.
’Group services’ use vendor clusterware group services when vendor clusterware is available. But it is capable of working independently if there is no vendor clusterware group services available
‘Lock services’ is another service from the CSS daemon. Lock services provide the basic cluster wide serialization locking functions. It uses FIFO mechanism to manage locking
Node Services is the third service produced by the CSSD. It uses OCR to store the data and updates the information during reconfiguration. It also manages the OCR data, which is static otherwise.

EVMD --

The third component in OCS is called Event Management Logger. Event Management logger also runs as daemon process ‘evmd’. The daemon process ‘evmd’ spawns a permanent child process called ‘evmlogger’ and generates the events when things happen. EVMD child process ‘evmlogger’ spawns new children processes on demand and scans the callout directory to invoke callouts. It will restart automatically on failures and death of the evmd process does not halt the instance. Evmd runs as ‘oracle’ user.

OPROCD--

Oprocd provides the server fencing solution for the Oracle Clusterware. It is the process monitor for the oracle clusterware and it uses the hang check timer or watchdog timer (depending on the implementation) for the cluster integrity. Oprocd is locked in the memory and runs as a real time process. This sleeps for a fixed time and runs as ‘root’ user. Failure of the Oprocd process causes the node to restart.

Latches

2010-10-07T01:11:00.000-07:00

What is a latch?
Latches are locking mechanisms used to protect shared memory structures from potential corruption due to concurrent access. In other words,latches ensure exclusive access to the shared data structures in the SGA. Access to SGA structures is seralized, using latches. Latches, unlike locks or enqueues are not used to protect database objects.
Latches are used in almost all operations. For example
1. When a session reads a block from disk, it must modify a free block in the buffer cache and adjust the buffer cache LRU chain.
2. When a session reads a block from the SGA, it will modify the LRU chain.
3. When a new SQL statement is parsed, it will be added to the library cache within the SGA.
4. As modifications are made to blocks, entries are placed in the redo buffer.
5. The database writer periodically writes buffers from the cache to disk (and must update their status from “dirty” to “clean”).
The redo log writer writes entries from the redo buffer to the redo logs.

Latch Types

There are three types of latches: parent, child, and solitary latches. The parent and solitary latches are fixed in the Oracle kernel code. Child latches are created at instance startup.

1. Latch operation

2. How are latches acquired?

A process may request a latch in the willing-to-wait or no-wait (immediate) mode. If the latch is available on the first request, the process acquires it. Before modifying the protected data structure, the process writes the recovery information in the latch recovery area so that PMON knows what to clean up if the process dies while holding the latch.

If the latch is not available, the process spins on the CPU for a short while and retries for the latch. This spin and retry activity can be repeated up to the value specified in the intialization parameter _SPIN_COUNT value (default 2000). The values of initialization parameters _MAX_EXPONENTIAL_SLEEP and _MAX_SLEEP_HOLDING_LATCH are used at arriving the spin duration.

Latches are very light and on most systems, a single machine instruction called “test and set” is used to see if the latch is taken (by looking at a specific memory address) and if not, acquire it (by changing the value in the memory address).

---Wakeup mechanisms

1. Timeout

The operating system signals (wakes up) the process when a set alarm is triggered

2. Latch wait posting

The next process to free the required latch will wake up the process waiting for the latch. This is initiated by the requesting

process before going to sleep by putting itself in a latch wait list

Unix for the DBA

2010-10-06T01:36:00.000-07:00

Unix for the DBA

How to kill all similar processes with single command (in this case opmn)

ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}

Locating Files under a particular directory

find . -print |grep -i test.sql

Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)

ps -ef |grep -i oracle |awk '{ print $2 }'

Changing the standard prompt for Oracle Users

Edit the .profile for the oracle user

PS1="`hostname`*$ORACLE_SID:$PWD>"

Display top 10 CPU consumers using the ps command

/usr/ucb/ps auxgw | head -11

Show number of active Oracle dedicated connection users for a particular ORACLE_SID

ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l
Display the number of CPU’s in Solaris

psrinfo -v | grep "Status of processor"|wc -l
Display the number of CPU’s in AIX

lsdev -C | grep Process|wc -l

Display RAM Memory size on Solaris

prtconf |grep -i mem

Display RAM memory size on AIX

First determine name of memory device

lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’

lsattr -El mem0

Swap space allocation and usage

Solaris : swap -s or swap -lAix : lsps -a

Total number of semaphores held by all instances on server

ipcs -as | awk '{sum += $9} END {print sum}'

View allocated RAM memory segments

ipcs -pmb

Manually deallocate shared memeory segments

ipcrm -m ''
Show mount points for a disk in AIX

lspv -l hdisk13

Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory

du -ks * | sort -n| tail

Display total file space in a directory

du -ks .
Cleanup any unwanted trace files more than seven days old

find . *.trc -mtime +7 -exec rm {} \;

Locate Oracle files that contain certain strings

find . -print | xargs grep rollback

Locate recently created UNIX files (in the past one day)

find . -mtime -1 -print

Finding large files on the server (more than 100MB in size)

find . -size +102400 -print

Crontab :

To submit a task every Tuesday (day 2) at 2:45PM

45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every 15 minutes on weekdays (days 1-5)

15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)

15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

SRVCTL:
srvctl command target [options]
commands: enable|disable|start|stop|relocate|status|add|remove|
modify|getenv|setenv|unsetenv|config
targets: database/db|instance/inst|service/serv|nodeapps|asm|listener

srvctl -help or srvctl -v
srvctl -V -- prints version
srvctl version: 10.2.0.0.0 (or) srvctl version: 11.0.0.0.0
srvctl -h -- print usage
srvctl status service –h

Database:
srvctl add database -d db_name -o ORACLE_HOME [-m domain_name][-p spfile] [-A name|ip/netmask]
[-r {PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY}]
[-s start_options] [-n db_name] [-y {AUTOMATIC|MANUAL}]

srvctl remove database -d db_name [-f]

srvctl start database -d db_name [-o start_options] [-c connect_str | -q]
srvctl start database -d db_name [-o open]
srvctl start database -d db_name -o nomount
srvctl start database -d db_name -o mount
srvctl start db -d prod

srvctl stop database -d db_name [-o stop_options] [-c connect_str | -q]
srvctl stop database -d db_name [-o normal]
srvctl stop database -d db_name -o transactional
srvctl stop database -d db_name -o immediate
srvctl stop database -d db_name -o abort

srvctl status database -d db_name [-f] [-v] [-S level]
srvctl status database -d db_name -v service_name
srvctl status database -d hrms

srvctl enable database -d db_name
srvctl disable database -d db_name

srvctl config database
srvctl config database -d db_name [-a] [-t]

srvctl modify database -d db_name [-n db_name] [-o ORACLE_HOME] [-m domain_name] [-p spfile]
[-r {PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY}] [-s start_options] [-y {AUTOMATIC|MANUAL}]
srvctl modify database -d hrms -r physical_standby
srvctl modify db -d RAC -p /u03/oradata/RAC/spfileRAC.ora -- moves parameter file

srvctl getenv database -d db_name [-t name_list]
srvctl setenv database -d db_name {-t name=val[,name=val,...]|-T name=val}
srvctl unsetenv database -d db_name [-t name_list]

Instance:
srvctl add instance –d db_name –i inst_name -n node_name
srvctl remove instance –d db_name –i inst_name [-f]

srvctl start instance -d db_name -i inst_names [-o start_options] [-c connect_str | -q]
srvctl start instance –d db_name –i inst_names [-o open]
srvctl start instance –d db_name –i inst_names -o nomount
srvctl start instance –d db_name –i inst_names -o mount
srvctl start instance –d prod -i prod3

srvctl stop instance -d db_name -i inst_names [-o stop_options] [-c connect_str | -q]
srvctl stop instance –d db_name –i inst_names [-o normal]
srvctl stop instance –d db_name –i inst_names -o transactional
srvctl stop instance –d db_name –i inst_names -o immediate
srvctl stop instance –d db_name –i inst_names -o abort
srvctl stop inst –d prod -i prod6

srvctl status instance –d db_name –i inst_names [-f] [-v] [-S level]
srvctl status inst –d racdb -i racdb2

srvctl enable instance –d db_name –i inst_names
srvctl disable instance –d db_name –i inst_names

srvctl modify instance -d db_name -i inst_name {-s asm_inst_name|-r} -- set a dependency of instance to ASM
srvctl modify instance -d db_name -i inst_name -n node_name -- move the instance
srvctl modify instance -d db_name -i inst_name -r -- remove the instance

srvctl getenv instance –d db_name –i inst_name [-t name_list]
srvctl setenv instance –d db_name [–i inst_name] {-t "name=val[,name=val,...]" | -T "name=val"}
srvctl unsetenv instance –d db_name [–i inst_name] [-t name_list]

Service:
srvctl add service -d db_name -s service_name -r pref_insts [-a avail_insts] [-P TAF_policy]
srvctl add service -d db_name -s service_name -u {-r "new_pref_inst" | -a "new_avail_inst"}
srvctl add service -d RAC -s PRD -r RAC01,RAC02 -a RAC03,RAC04
srvctl add serv -d CRM -s CRM -r CRM1 -a CRM3 -P basic

srvctl remove service -d db_name -s service_name [-i inst_name] [-f]

srvctl start service -d db_name [-s service_names [-i inst_name]] [-o start_options]
srvctl start service -d db_name -s service_names [-o open]
srvctl start service -d db_name -s service_names -o nomount
srvctl start service -d db_name -s service_names -o mount

srvctl stop service -d db_name [-s service_names [-i inst_name]] [-f]

srvctl status service -d db_name [-s service_names] [-f] [-v] [-S level]

srvctl enable service -d db_name -s service_names [–i inst_name]
srvctl disable service -d db_name -s service_names [–i inst_name]

srvctl config service -d db_name [-s service_name] [-a] [-S level]
srvctl config service -d db_name -a -- -a shows TAF configuration
srvctl config service -d WEBTST -s webtest PREF:WEBTST1 AVAIL:WEBTST2

srvctl modify service -d db_name -s service_name -i old_inst_name -t new_inst_name [-f]
srvctl modify service -d db_name -s service_name -i avail_inst_name -r [-f]
srvctl modify service -d db_name -s service_name -n -i preferred_list [-a available_list] [-f]
srvctl modify service -d db_name -s service_name -i old_inst_name -a avail_inst -P TAF_policy
srvctl modify serv -d PROD -s SDW -n -i I1,I2,I3,I4 -a I5,I6

srvctl relocate service -d db_name -s service_name –i old_inst_name -t target_inst [-f]

srvctl getenv service -d db_name -s service_name -t name_list
srvctl setenv service -d db_name [-s service_name] {-t "name=val[,name=val,...]" | -T "name=val"}
srvctl unsetenv service -d db_name -s service_name -t name_list

Nodeapps:
#srvctl add nodeapps -n node_name -o ORACLE_HOME -A name|ip/netmask[/if1[|if2|...]]
#srvctl add nodeapps -n lnx02 -o $ORACLE_HOME -A 192.168.0.151/255.255.0.0/eth0

#srvctl remove nodeapps -n node_names [-f]

#srvctl start nodeapps -n node_name -- Starts GSD, VIP, listener & ONS
#srvctl stop nodeapps -n node_name [-r] -- Stops GSD, VIP, listener & ONS

#srvctl status nodeapps -n node_name

#srvctl config nodeapps -n node_name [-a] [-g] [-o] [-s] [-l]

#srvctl modify nodeapps -n node_name [-A new_vip_address]
#srvctl modify nodeapps -n lnx06 -A 10.50.99.43/255.255.252.0/eth0

#srvctl getenv nodeapps -n node_name [-t name_list]
#srvctl setenv nodeapps -n node_name {-t "name=val[,name=val,...]"|-T "name=val"}
#srvctl unsetenv nodeapps -n node_name [-t name_list]

ASM:
srvctl add asm -n node_name -i asminstance -o ORACLE_HOME [-p spfile]
srvctl remove asm -n node_name [-i asminstance] [-f]

srvctl start asm -n node_name [-i asminstance] [-o start_options] [-c connect_str | -q]
srvctl start asm -n node_name -i asminstance [-o open]
srvctl start asm -n node_name -i asminstance -o nomount
srvctl start asm -n node_name -i asminstance -o mount

srvctl stop asm -n node_name [-i asminstance] [-o stop_options] [-c connect_str | -q]
srvctl stop asm -n node_name -i asminstance [-o normal]
srvctl stop asm -n node_name -i asminstance -o transactional
srvctl stop asm -n node_name -i asminstance -o immediate
srvctl stop asm -n node_name -i asminstance -o abort

srvctl status asm -n node_name

srvctl enable asm -n node_name [-i asminstance]
srvctl disable asm -n node_name [-i asminstance]
srvctl config asm -n node_name
srvctl modify asm -n node_name -i asminstance [-o ORACLE_HOME] [-p spfile]

Listener:
srvctl add listener -n node_name -o ORACLE_HOME [-l listener_name]
-- 11g command
srvctl remove listener -n node_name [-l listener_name] -- 11g command

srvctl start listener -n node_name [-l listener_names]
srvctl stop listener -n node_name [-l listener_names]

srvctl config listener -n node_name

Oracle Clusterware processes for 10g on Unix and Linux

2010-10-06T01:30:00.000-07:00

What are Oracle Clusterware processes for 10g on Unix and Linux

Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart.

Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource's configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user

Event manager daemon (evmd) —A background process that publishes events that crs creates.

Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.

RACG (racgmain, racgimon) —Extends clusterware to support Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur.

What are Oracle database background processes specific to RAC

•LMS—Global Cache Service Process

•LMD—Global Enqueue Service Daemon

•LMON—Global Enqueue Service Monitor

•LCK0—Instance Enqueue Process

To ensure that each Oracle RAC database instance obtains the block that it needs to satisfy a query or transaction, Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances.

What are Oracle Clusterware Components

Voting Disk — Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk.

Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster

How do you troubleshoot node reboot

Please check metalink ...

Note 265769.1 Troubleshooting CRS Reboots
Note.559365.1 Using Diagwait as a diagnostic to get more information for diagnosing Oracle Clusterware Node evictions.

How do you backup the OCR

There is an automatic backup mechanism for OCR. The default location is : $ORA_CRS_HOME\cdata\"clustername"\

To display backups :
#ocrconfig -showbackup
To restore a backup :
#ocrconfig -restore

With Oracle RAC 10g Release 2 or later, you can also use the export command:
#ocrconfig -export -s online, and use -import option to restore the contents back.
With Oracle RAC 11g Release 1, you can do a manaual backup of the OCR with the command:
# ocrconfig -manualbackup

How do you backup voting disk

#dd if=voting_disk_name of=backup_file_name

How do I identify the voting disk location

#crsctl query css votedisk

How do I identify the OCR file location

check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck

Is ssh required for normal Oracle RAC operation ?

"ssh" are not required for normal Oracle RAC operation. However "ssh" should be enabled for Oracle RAC and patchset installation.

What is SCAN?

Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.

Click here for more details from Oracle

What is the purpose of Private Interconnect ?

Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the the clustered nodes. This communication is based on the TCP protocol.
RAC uses the interconnect for cache fusion (UDP) and inter-process communication (TCP). Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster.

Why do we have a Virtual IP (VIP) in Oracle RAC?

Without using VIPs or FAN, clients connected to a node that died will often wait for a TCP timeout period (which can be up to 10 min) before getting an error. As a result, you don't really have a good HA solution without using VIPs.
When a node fails, the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP. Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients. This results in the clients getting errors immediately.

What do you do if you see GC CR BLOCK LOST in top 5 Timed Events in AWR Report?

This is most likely due to a fault in interconnect network.
Check netstat -s
if you see "fragments dropped" or "packet reassemblies failed" , Work with your system administrator find the fault with network.

How many nodes are supported in a RAC Database?

10g Release 2, support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database.

Srvctl cannot start instance, I get the following error PRKP-1001 CRS-0215, however sqlplus can start it on both nodes? How do you identify the problem?

Set the environmental variable SRVM_TRACE to true.. And start the instance with srvctl. Now you will get detailed error stack.

what is the purpose of the ONS daemon?

The Oracle Notification Service (ONS) daemon is an daemon started by the CRS clusterware as part of the nodeapps. There is one ons daemon started per clustered node.
The Oracle Notification Service daemon receive a subset of published clusterware events via the local evmd and racgimon clusterware daemons and forward those events to application subscribers and to the local listeners.

This in order to facilitate:

a. the FAN or Fast Application Notification feature or allowing applications to respond to database state changes.
b. the 10gR2 Load Balancing Advisory, the feature that permit load balancing accross different rac nodes dependent of the load on the different nodes. The rdbms MMON is creating an advisory for distribution of work every 30seconds and forward it via racgimon and ONS to listeners and applications.

ASM REBALANCING

2010-10-05T03:14:00.000-07:00

Dynamic Storage Configuration

ASM enables you to change the storage configuration without having to take the database offline. It automatically rebalances—redistributes file data evenly across all the disks of the disk group—after you add disks to or drop disks from a disk group.

Should a disk failure occur, ASM automatically rebalances to restore full redundancy for files that had extents on the failed disk. When you replace the failed disk with a new disk, ASM rebalances the disk group to spread data evenly across all disks, including the replacement disk.

Tuning Rebalance Operations

The V$ASM_OPERATION view provides information that can be used for adjusting ASM_POWER_LIMIT and the resulting power of rebalance operations. The V$ASM_OPERATION view also gives an estimate in the EST_MINUTES column of the amount of time remaining for the rebalance operation to complete. You can see the effect of changing the rebalance power by observing the change in the time estimate.

Effects of Adding and Dropping Disks from a Disk Group

ASM automatically rebalances whenever disks are added or dropped. For a normal drop operation (without the FORCE option), a disk is not released from a disk group until data is moved off of the disk through rebalancing. Likewise, a newly added disk cannot support its share of the I/O workload until rebalancing completes. It is more efficient to add or drop multiple disks at the same time so that they are rebalanced as a single operation. This avoids unnecessary movement of data.

For a drop operation, when rebalance is complete, ASM takes the disk offline momentarily, and then drops it, setting disk header status to FORMER.

You can add or drop disks without shutting down the database. However, a performance impact on I/O activity may result.

Testing the Fail over in Real Application Clusters Oracle10gR2 using service

2010-10-05T01:54:00.000-07:00

Testing the Fail over in Real Application Clusters Oracle10gR2 using service:

How to test the Fail-over, if the Client is connected to a RAC database and one of the nodes goes down?
1. Client connected to Database from a SQL Plus session from a remote Workstation.
*********************************************
SQL> show user
USER is "SCOTT"
SQL> select INSTANCE_NAME, INSTANCE_NUMBER from v$INSTANCE;
INSTANCE_NAME INSTANCE_NUMBER
---------------- ---------------
devdb1 1
*********************************************
Checking the User /Client status from a DB Server
SQL> select USERNAME, INST_ID, MACHINE, TERMINAL, STATUS
2 from gv$session where USERNAME = 'SCOTT';
USERNAME INST_ID MACHINE TERMINAL STATUS
--------- ---------- ------------------------------ -------------- --------
SCOTT 1 WORKGROUP\PSDBA03-02 PSDBA03-02 INACTIVE
****************************************
3. On the DB Server, force /Kill the Instance devdb1.
4. The status of the DBInstance devdb1 is down.
Name Type Target State Host
------------------------------------------------------------
ora....SDEV.cs application ONLINE ONLINE rac2
ora....db1.srv application ONLINE OFFLINE
ora....db2.srv application ONLINE ONLINE rac2
ora.devdb.db application ONLINE ONLINE rac2
ora....b1.inst application OFFLINE OFFLINE
ora....b2.inst application ONLINE ONLINE rac2
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
****************************
5. You can see difference with Normal Service, the SQL plus connection throws a error, but with Client-Side Load balancing and TAF the user connection still exists. For User the fail-over is transparent and can go ahead with his session without the need to reconnect.
SQL> select INSTANCE_NAME from v$instance;
INSTANCE_NAME
----------------
devdb2
SQL> show user
USER is "SCOTT"
This is a simple test for fail-over, with a SQL Plus connections from a remote server.

RAC Switchover / Switchback

2010-10-04T00:38:00.002-07:00

RAC Switchover / Switchback for 2 Node Primary with 2 Node Standby. Got from oracle forums.
DB Name DB Unique Name Host Name Instance Name

--------------------------------------------------------------------------------
live live linux1 live1
live live linux2 live2
live livestdby linux3 livestdby1
live livestdby linux4 livestdby2

Verify that each database is properly configured for the role it is about to assume and the standby database is in mounted state.
(Verify all Dataguard parameters on each node for Primary & Standby)

Like,
Log_archive_dest_1
Log_archive_dest_2
Log_archive_dest_state_1
Log_archive_dest_state_2
Fal_client
Fal_server
Local_listener
Remote_listener
Standby_archive_Dest
Standby_archive_management
service_names
db_unique_name
instance_name
db_file_name_convert
log_file_name_convert

Verify that both Primary RAC & Dataguard RAC are functioning properly and both are in Sync
On Primary,
Select thread#,max(sequence#) from v$archived_log group by thread#;
On Standby,
Select thread#,max(sequence#) from v$log_history group by thread#;

Before performing a switchover from a RAC primary shut down all but one primary instance (they can be restarted after the switchover has completed).
./srvctl stop instance –d live –i live1
Before performing a switchover or a failover to a RAC standby shut down all but one standby instance (they can be restarted after the role transition has completed).
./srvctl stop instance –d live –i livestdby1

On the primary database initiate the switchover:
alter database commit to switchover to physical standby with session shutdown;
Shutdown former Primary database & Startup in Mount State.
Shut immediate;
Startup mount;
select name,db_unique_name, log_mode,open_mode,controlfile_type,switchover_status,database_role from v$database;

Make log_Archive_Dest_state_2 to DEFER
alter system set log_archive_dest_state_2='DEFER' sid='*';

On the (old) standby database,
select name,log_mode,open_mode,controlfile_type,switchover_status,database_role from v$database;

On the (old) standby database switch to new primary role:
alter database commit to switchover to primary;
shut immediate;
startup;

On new Primary database,
select name,log_mode,open_mode,controlfile_type,switchover_status,database_role from v$database;
Make log_Archive_Dest_state_2 to ENABLE
alter system set log_archive_dest_state_2='ENABLE' sid='*';
Add tempfiles in New Primary database.
Do some archivelog switches on new primary database & verify that archives are getting transferred to Standby database.

On new primary,
select error from v$archive_Dest_status;
select max(sequence#) from v$archived_log;

On new Standby, Start Redo Apply

alter database recover managed standby database using current logfile disconnect;

Select max(sequence#) from v$log_history; (should be matching with Primary)

Now Start RAC databases services (both Primary – in open & Standby – in mount)
On new Primary Server.
./srvctl start instance –d live –i livestdby2
Verify using ./crs_stat –t
Check that database is opened in R/W mode.
On new Standby Server.
./srvctl start instance –d live –i live2 –o mount

Now add TAF services on new Primary (former Standby) Server.
By Command Prompt,
./srvctl add service -d live -s srvc_livestdby -r livestdby1,livestdby2 -P BASIC
OR
By GUI,
dbca -> Oracle Read Application Cluster database -> Service Management -> select database -> add services, details (Preferred / Available), TAF Policy (Basic / Preconnect) - > Finish

Start the services,
./srvctl start service -d live

Verify the same,
./crs_stat -t

Perform TAF testing, to make sure Load Balancing

OCR is automatically backed

2010-10-04T00:32:00.000-07:00

OCR is automatically backed up physically:
Every four hours: CRS keeps the last three copies.
At the end of every day: CRS keeps the last two copies.
At the end of every week: CRS keeps the last two copies

[oracle@rac2 ~]$ ocrconfig -showbackup

rac2 2010/10/04 10:29:12 /u01/app/oracle/product/crs/cdata/crs

rac2 2010/10/04 06:29:11 /u01/app/oracle/product/crs/cdata/crs

rac2 2010/10/04 02:29:11 /u01/app/oracle/product/crs/cdata/crs

rac2 2010/10/03 02:29:07 /u01/app/oracle/product/crs/cdata/crs

dbrac1 2010/09/17 04:17:06 /u01/app/oracle/product/crs/cdata/crs
[oracle@rac2 ~]$ cd /u01/app/oracle/product/crs/cdata/crs
[oracle@rac2 crs]$ ls -ltr
total 27916
-rw-r--r-- 1 root root 4079616 Jun 13 02:16 week.ocr
-rw-r--r-- 1 root root 4079616 Sep 29 00:58 week_.ocr
-rw-r--r-- 1 root root 4079616 Oct 3 02:29 day.ocr
-rw-r--r-- 1 root root 4079616 Oct 4 02:29 day_.ocr
-rw-r--r-- 1 root root 4079616 Oct 4 02:29 backup02.ocr
-rw-r--r-- 1 root root 4079616 Oct 4 06:29 backup01.ocr
-rw-r--r-- 1 root root 4079616 Oct 4 10:29 backup00.ocr

Checkpoint Tuning

2010-09-29T00:06:00.000-07:00

Determining the time to recover from an instance failure is a necessary component for reaching required service levelsagreements. For example, if service levels dictate that when a node fails, instance recovery time can be no more than 3 minutes, FAST_START_MTTR_TARGET should be set to 180

Fast-start checkpointing refers to the periodic writes by the database writer (DBWn) processes for the purpose of writing changed data blocks from the Oracle buffer cache to disk and advancing the thread-checkpoint. Setting the database parameter FAST_START_MTTR_TARGET to a value greater than zero enables the fast-start checkpointing feature.

Fast-start checkpointing should always be enabled for the following reasons:

It reduces the time required for cache recovery, and makes instance recovery time-bounded and predictable. This is accomplished by limiting the number of dirty buffers (data blocks which have changes in memory that still need to be written to disk) and the number of redo records (changes in the database) generated between the most recent redo record and the last checkpoint.

Fast-Start checkpointing eliminates bulk writes and corresponding I/O spikes that occure traditionally with interval- based checkpoints, providing a smoother, more consistent I/O pattern that is more predictable and easier to manage.
If the system is not already near or at its maximum I/O capacity, fast-start checkpointing will have a negligible impact on performance. Although fast-start checkpointing results in increased write activity, there is little reduction in database throughout, provided the system has sufficient I/O capacity.

Check-Pointing

Check-pointing is an important Oracle activity which records the highest system change number (SCN,) so that all data blocks less than or equal to the SCN are known to be written out to the data files. If there is a failure and then subsequent cache recovery, only the redo records containing changes at SCN(s) higher than the checkpoint need to be applied during recovery.

As we are aware, instance and crash recovery occur in two steps - cache recovery followed by transaction recovery. During the cache recovery phase, also known as the rolling forward stage, Oracle applies all committed and uncommitted changes in the redo log files to the affected data blocks. The work required for cache recovery processing is proportional to the rate of change to the database and the time between checkpoints.

Mean time to recover (MTTR)

Fast-start recovery can greatly reduce the mean time to recover (MTTR), with minimal effects on online application performance. Oracle continuously estimates the recovery time and automatically adjusts the check-pointing rate to meet the target recovery time.

With 10g, the Oracle database can now self-tune check-pointing to achieve good recovery times with low impact on normal throughput. You no longer have to set any checkpoint-related parameters.

This method reduces the time required for cache recovery and makes the recovery bounded and predictable by limiting the number of dirty buffers and the number of redo records generated between the most recent redo record and the last checkpoint. Administrators specify a target (bounded) time to complete the cache recovery phase of recovery with the FAST_START_MTTR_TARGET initialization parameter, and Oracle automatically varies the incremental checkpoint writes to meet that target.

The TARGET_MTTR field of V$INSTANCE_RECOVERY contains the MTTR target in effect. The ESTIMATED_MTTR field of V$INSTANCE_RECOVERY contains the estimated MTTR should a crash happen right away.

Enable MTTR advisory

Enabling MTTR Advisory Enabling MTTR Advisory involves setting two parameters:

STATISTICS_LEVEL = TYPICAL
FAST_START_MTTR_TARGET > 0

Estimate the value for FAST_START_MTTR_TARGET as follows:

SELECT TARGET_MTTR,
ESTIMATED_MTTR,
CKPT_BLOCK_WRITES
FROM V$INSTANCE_RECOVERY;

TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES
----------- -------------- -----------------
214 12 269880

FAST_START_MTTR_TARGET = 214;

Whenever you set FAST_START_MTTR_TARGET to a nonzero value, then set the following parameters to 0.

LOG_CHECKPOINT_TIMEOUT = 0
LOG_CHECKPOINT_INTERVAL = 0
FAST_START_IO_TARGET = 0

Disable MTTR advisory

FAST_START_MTTR_TARGET = 0
LOG_CHECKPOINT_INTERVAL = 200000

Duplicate Oracle Database with RMAN

2010-09-28T23:57:00.002-07:00

Overview
A powerful feature of RMAN is the ability to duplicate (clone), a database from a backup. It is possible to create a duplicate database on:

A remote server with the same file structure
A remote server with a different file structure
The local server with a different file structure
A duplicate database is distinct from a standby database, although both types of databases are created with the DUPLICATE command. A standby database is a copy of the primary database that you can update continually or periodically by using archived logs from the primary database. If the primary database is damaged or destroyed, then you can perform failover to the standby database and effectively transform it into the new primary database. A duplicate database, on the other hand, cannot be used in this way: it is not intended for failover scenarios and does not support the various standby recovery and failover options.

To prepare for database duplication, you must first create an auxiliary instance. For the duplication to work, you must connect RMAN to both the target (primary) database and an auxiliary instance started in NOMOUNT mode.

So long as RMAN is able to connect to the primary and duplicate instances, the RMAN client can run on any machine. However, all backups, copies of datafiles, and archived logs used for creating and recovering the duplicate database must be accessible by the server session on the duplicate host.

As part of the duplicating operation, RMAN manages the following:

Restores the target datafiles to the duplicate database and performs incomplete recovery by using all available backups and archived logs.

Shuts down and starts the auxiliary database.

Opens the duplicate database with the RESETLOGS option after incomplete recovery to create the online redo logs.

Generates a new, unique DBID for the duplicate database.

Preparing the Duplicate (Auxiliary) Instance for Duplication
Create an Oracle Password File

First we must create a password file for the duplicate instance.

export ORACLE_SID=APP2
orapwd file=orapwAPP2 password=manager entries=5 force=y

Ensure Oracle Net Connectivity to both Instances

Next add the appropriate entries into the TNSNAMES.ORA and LISTENER.ORA files in the $TNS_ADMIN directory.

LISTENER.ORA

APP1 = Target Database, APP2 = Auxiliary Database

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gentic)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = APP1.WORLD)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(SID_NAME = APP1)
)
(SID_DESC =
(GLOBAL_DBNAME = APP2.WORLD)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(SID_NAME = APP2)
)
)

TNSNAMES.ORA

APP1 = Target Database, APP2 = Auxiliary Database

APP1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gentic)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = APP1.WORLD)
)
)

APP2.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gentic)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = APP2.WORLD)
)
)

SQLNET.ORA

NAMES.DIRECTORY_PATH= (TNSNAMES)
NAMES.DEFAULT_DOMAIN = WORLD
NAME.DEFAULT_ZONE = WORLD
USE_DEDICATED_SERVER = ON

Now restart the Listener

lsnrctl stop
lsnrctl start

Create an Initialization Parameter File for the Auxiliary Instance

Create an INIT.ORA parameter file for the auxiliary instance, you can copy that from the target instance and then modify the parameters.

### Duplicate Database
### -----------------------------------------------
# This is only used when you duplicate the database
# on the same host to avoid name conflicts

DB_FILE_NAME_CONVERT = (/u01/oracle/db/APP1/,/u01/oracle/db/APP2/)
LOG_FILE_NAME_CONVERT = (/u01/oracle/db/APP1/,/u01/oracle/db/APP2/,
/opt/oracle/db/APP1/,/opt/oracle/db/APP2/)

### Global database name is db_name.db_domain
### -----------------------------------------

db_name = APP2
db_unique_name = APP2_GENTIC
db_domain = WORLD
service_names = APP2
instance_name = APP2

### Basic Configuration Parameters
### ------------------------------

compatible = 10.2.0.4
db_block_size = 8192
db_file_multiblock_read_count = 32
db_files = 512
control_files = /u01/oracle/db/APP2/con/APP2_con01.con,
/opt/oracle/db/APP2/con/APP2_con02.con

### Database Buffer Cache, I/O
### --------------------------
# The Parameter SGA_TARGET enables Automatic Shared Memory Management

sga_target = 500M
sga_max_size = 600M

### REDO Logging without Data Guard
### -------------------------------

log_archive_format = APP2_%s_%t_%r.arc
log_archive_max_processes = 2
log_archive_dest = /u01/oracle/db/APP2/arc

### System Managed Undo
### -------------------

undo_management = auto
undo_retention = 10800
undo_tablespace = undo

### Traces, Dumps and Passwordfile
### ------------------------------

audit_file_dest = /u01/oracle/db/APP2/adm/admp
user_dump_dest = /u01/oracle/db/APP2/adm/udmp
background_dump_dest = /u01/oracle/db/APP2/adm/bdmp
core_dump_dest = /u01/oracle/db/APP2/adm/cdmp
utl_file_dir = /u01/oracle/db/APP2/adm/utld
remote_login_passwordfile = exclusive

Create a full Database Backup

Make sure that a full backup of the target is accessible on the duplicate host. You can use the following BASH script to backup the target database.

rman nocatalog target / <<-EOF
configure retention policy to recovery window of 3 days;
configure backup optimization on;
configure controlfile autobackup on;
configure default device type to disk;
configure device type disk parallelism 1 backup type to compressed backupset;
configure datafile backup copies for device type disk to 1;
configure maxsetsize to unlimited;
configure snapshot controlfile name to '/u01/backup/snapshot_controlfile';
show all;

run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup full database noexclude
include current controlfile
format '/u01/backup/datafile_%s_%p.bak'
tag 'datafile_daily';
}

run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup archivelog all
delete all input
format '/u01/backup/archivelog_%s_%p.bak'
tag 'archivelog_daily';
}

run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup format '/u01/backup/controlfile_%s.bak' current controlfile;
}

crosscheck backup;
list backup of database;
report unrecoverable;
report schema;
report need backup;
report obsolete;
delete noprompt expired backup of database;
delete noprompt expired backup of controlfile;
delete noprompt expired backup of archivelog all;
delete noprompt obsolete recovery window of 3 days;
quit
EOF

Creating a Duplicate Database on the Local Host
Before beginning RMAN duplication, use SQL*Plus to connect to the auxiliary instance and start it in NOMOUNT mode. If you do not have a server-side initialization parameter file for the auxiliary instance in the default location, then you must specify the client-side initialization parameter file with the PFILE parameter on the DUPLICATE command.

Get original Filenames from TARGET

To rename the database files you can use the SET NEWNAME command. Therefore, get the original filenames from the target and modify these names in the DUPLICATE command.

ORACLE_SID=APP1
export ORACLE_SID

set feed off
set pagesize 10000
column name format a40 heading "Datafile"
column file# format 99 heading "File-ID"

select name, file# from v$dbfile;

column member format a40 heading "Logfile"
column group# format 99 heading "Group-Nr"

select member, group# from v$logfile;

Datafile File-ID
---------------------------------------- -------
/u01/oracle/db/APP1/sys/APP1_sys1.dbf 1
/u01/oracle/db/APP1/sys/APP1_undo1.dbf 2
/u01/oracle/db/APP1/sys/APP1_sysaux1.dbf 3
/u01/oracle/db/APP1/usr/APP1_users1.dbf 4

Logfile Group-Nr
---------------------------------------- --------
/u01/oracle/db/APP1/rdo/APP1_log1A.rdo 1
/opt/oracle/db/APP1/rdo/APP1_log1B.rdo 1
/u01/oracle/db/APP1/rdo/APP1_log2A.rdo 2
/opt/oracle/db/APP1/rdo/APP1_log2B.rdo 2
/u01/oracle/db/APP1/rdo/APP1_log3A.rdo 3
/opt/oracle/db/APP1/rdo/APP1_log3B.rdo 3
/u01/oracle/db/APP1/rdo/APP1_log4A.rdo 4
/opt/oracle/db/APP1/rdo/APP1_log4B.rdo 4
/u01/oracle/db/APP1/rdo/APP1_log5A.rdo 5
/opt/oracle/db/APP1/rdo/APP1_log5B.rdo 5
/u01/oracle/db/APP1/rdo/APP1_log6A.rdo 6
/opt/oracle/db/APP1/rdo/APP1_log6B.rdo 6
/u01/oracle/db/APP1/rdo/APP1_log7A.rdo 7
/opt/oracle/db/APP1/rdo/APP1_log7B.rdo 7
/u01/oracle/db/APP1/rdo/APP1_log8A.rdo 8
/opt/oracle/db/APP1/rdo/APP1_log8B.rdo 8
/u01/oracle/db/APP1/rdo/APP1_log9A.rdo 9
/opt/oracle/db/APP1/rdo/APP1_log9B.rdo 9
/u01/oracle/db/APP1/rdo/APP1_log10A.rdo 10
/opt/oracle/db/APP1/rdo/APP1_log10B.rdo 10

Create Directories for the duplicate Database

mkdir -p /u01/oracle/db/APP2
mkdir -p /opt/oracle/db/APP2
cd /opt/oracle/db/APP2
mkdir con rdo
cd /u01/oracle/db/APP2
mkdir adm arc con rdo sys tmp usr bck
cd adm
mkdir admp bdmp cdmp udmp utld

Create Symbolic Links to Password and INIT.ORA File

Oracle must be able to locate the Password and INIT.ORA File.

cd $ORACLE_HOME/dbs
ln -s /home/oracle/config/10.2.0/orapwAPP2 orapwAPP2
ln -s /home/oracle/config/10.2.0/initAPP2.ora initAPP2.ora

Duplicate the Database

Now you are ready to duplicate the database APP1 to APP2.

ORACLE_SID=APP2
export ORACLE_SID

sqlplus sys/manager as sysdba
startup force nomount pfile='/home/oracle/config/10.2.0/initAPP2.ora';
exit;

rman TARGET sys/manager@APP1 AUXILIARY sys/manager@APP2

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Oct 28 12:00:13 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: APP1 (DBID=3191823649)
connected to auxiliary database: APP2 (not mounted)

RUN
{
SET NEWNAME FOR DATAFILE 1 TO '/u01/oracle/db/APP2/sys/APP2_sys1.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/oracle/db/APP2/sys/APP2_undo1.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/u01/oracle/db/APP2/sys/APP2_sysaux1.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/u01/oracle/db/APP2/usr/APP2_users1.dbf';
DUPLICATE TARGET DATABASE TO APP2
PFILE = /home/oracle/config/10.2.0/initAPP2.ora
NOFILENAMECHECK
LOGFILE GROUP 1 ('/u01/oracle/db/APP2/rdo/APP2_log1A.rdo',
'/opt/oracle/db/APP2/rdo/APP2_log1B.rdo') SIZE 10M REUSE,
GROUP 2 ('/u01/oracle/db/APP2/rdo/APP2_log2A.rdo',
'/opt/oracle/db/APP2/rdo/APP2_log2B.rdo') SIZE 10M REUSE,
GROUP 3 ('/u01/oracle/db/APP2/rdo/APP2_log3A.rdo',
'/opt/oracle/db/APP2/rdo/APP2_log3B.rdo') SIZE 10M REUSE,
GROUP 4 ('/u01/oracle/db/APP2/rdo/APP2_log4A.rdo',
'/opt/oracle/db/APP2/rdo/APP2_log4B.rdo') SIZE 10M REUSE,
GROUP 5 ('/u01/oracle/db/APP2/rdo/APP2_log5A.rdo',
'/opt/oracle/db/APP2/rdo/APP2_log5B.rdo') SIZE 10M REUSE,
GROUP 6 ('/u01/oracle/db/APP2/rdo/APP2_log6A.rdo',
'/opt/oracle/db/APP2/rdo/APP2_log6B.rdo') SIZE 10M REUSE,
GROUP 7 ('/u01/oracle/db/APP2/rdo/APP2_log7A.rdo',
'/opt/oracle/db/APP2/rdo/APP2_log7B.rdo') SIZE 10M REUSE,
GROUP 8 ('/u01/oracle/db/APP2/rdo/APP2_log8A.rdo',
'/opt/oracle/db/APP2/rdo/APP2_log8B.rdo') SIZE 10M REUSE,
GROUP 9 ('/u01/oracle/db/APP2/rdo/APP2_log9A.rdo',
'/opt/oracle/db/APP2/rdo/APP2_log9B.rdo') SIZE 10M REUSE,
GROUP 10 ('/u01/oracle/db/APP2/rdo/APP2_log10A.rdo',
'/opt/oracle/db/APP2/rdo/APP2_log10B.rdo') SIZE 10M REUSE;
}

The whole, long output is not shown here, but check, that RMAN was able to open the duplicate database with the RESETLOGS option.

.....
.....
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 28-OCT-08

As the final step, eliminate or uncomment the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT in the INIT.ORA file and restart the database.

initAPP2.ora

### Duplicate Database
### -----------------------------------------------
# This is only used when you duplicate the database
# on the same host to avoid name conflicts
# DB_FILE_NAME_CONVERT = (/u01/oracle/db/APP1/,/u01/oracle/db/APP2/)
# LOG_FILE_NAME_CONVERT = (/u01/oracle/db/APP1/,/u01/oracle/db/APP2/,
/opt/oracle/db/APP1/,/opt/oracle/db/APP2/)

sqlplus / as sysdba
shutdown immediate;
startup;

Total System Global Area 629145600 bytes
Fixed Size 1269064 bytes
Variable Size 251658936 bytes
Database Buffers 373293056 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.

Creating a Duplicate Database to Remote Host
This scenario is exactly the same as described for the local host. Copy the RMAN Backup files to the remote host on the same directory as on the localhost.

cd /u01/backup
scp gentic:/u01/backup/* .

The other steps are the same as described under «Creating a Duplicate Database on the Local Host».

Duplicate Oracle Database with RMAN

2010-09-28T23:57:00.001-07:00

Memory Architecture of an Oracle instance

2010-09-27T05:55:00.000-07:00

Oracle Database can set limits on how much virtual memory the database uses for the SGA. It can start instances with minimal memory and allow the instance to use more memory by expanding the memory allocated for SGA components, up to a maximum determined by the SGA_MAX_SIZE initialization parameter.

SGA_MAX_SIZE is not dynamic. You can NOT change the value.

However, as compared to previous versions of Oracle, in 10g SGA_MAX_SIZE does not define the size of memory allocated, but rather the MAXIMUM size that CAN be allocated.

The SGA_TARGET Initialization Parameter

The SGA_TARGET initialization parameter reflects the total size of the SGA and includes memory for the following components:

* Fixed SGA and other internal allocations needed by the Oracle Database instance
* The log buffer
* The shared pool
* The Java pool
* The buffer cache
*

The keep and recycle buffer caches (if specified)
*

Nonstandard block size buffer caches (if specified)
*

The Streams pool

It is significant that SGA_TARGET includes the entire memory for the SGA, in contrast to earlier releases in which memory for the internal and fixed SGA was added to the sum of the configured SGA memory parameters. Thus, SGA_TARGET gives you precise control over the size of the shared memory region allocated by the database. If SGA_TARGET is set to a value greater than SGA_MAX_SIZE at startup, then the latter is bumped up to accommodate SGA_TARGET.

Automatically Managed SGA Components

When you set a value for SGA_TARGET, Oracle Database 10g automatically sizes the most commonly configured components, including:

*

The shared pool (for SQL and PL/SQL execution)
*

The Java pool (for Java execution state)
*

The large pool (for large allocations such as RMAN backup buffers)
*

The buffer cache
*

The Streams pool

You need not set the size of any of these components explicitly. By default the parameters for these components will appear to have values of zero. Whenever a component needs memory, it can request that it be transferred from another component by way of the internal automatic tuning mechanism. This transfer of memory occurs transparently, without user intervention.

The performance of each of these automatically sized components is monitored by the Oracle Database instance. The instance uses internal views and statistics to determine how to distribute memory optimally among the components. As the workload changes, memory is redistributed to ensure optimal performance. To calculate the optimal distribution of memory, the database uses an algorithm that takes into consideration both long-term and short-term trends.

Manually Managed SGA Components

There are a few SGA components whose sizes are not automatically adjusted. The administrator needs to specify the sizes of these components explicitly, if needed by the application. Such components are:

*

Keep/Recycle buffer caches (controlled by DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE)
*

Additional buffer caches for non-standard block sizes (controlled by DB_nK_CACHE_SIZE, n = {2, 4, 8, 16, 32})

The sizes of these components is determined by the administrator-defined value of their corresponding parameters. These values can, of course, be changed any time either using Enterprise Manager or from the command line with an ALTER SYSTEM statement.

The memory consumed by manually sized components reduces the amount of memory available for automatic adjustment. For example, in the following configuration:

SGA_TARGET = 256M
DB_8K_CACHE_SIZE = 32M

The instance has only 224 MB (256 - 32) remaining to be distributed among the automatically sized components.

Automatic Shared Memory Management

In previous database releases, a database administrator (DBA) was required to manually specify different SGA component sizes by setting a number of initialization parameters, including the SHARED_POOL_SIZE, DB_CACHE_SIZE, JAVA_POOL_SIZE, and LARGE_POOL_SIZE parameters. Oracle Database 10g includes the Automatic Shared Memory Management feature which simplifies the SGA memory management significantly. In Oracle Database 10g, a DBA can simply specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter and the Oracle Database will automatically distribute this memory among various subcomponents to ensure most effective memory utilization.

When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration. The database automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory.

Consider a manual configuration in which 1 GB of memory is available for the SGA and distributed to the following initialization parameters:

SHARED_POOL_SIZE=128M
DB_CACHE_SIZE=896M

If an application attempts to allocate more than 128 MB of memory from the shared pool, an error is raised that indicates that the available shared pool has been exhausted. There could be free memory in the buffer cache, but this memory is not accessible to the shared pool. You would have to manually resize the buffer cache and the shared pool to work around this problem.

With automatic SGA management, you can simply set the SGA_TARGET initialization parameter to 1G. If an application needs more shared pool memory, it can obtain that memory by acquiring it from the free memory in the buffer cache.

Setting a single parameter greatly simplifies the administration task. You specify only the amount of SGA memory that an instance has available and forget about the sizes of individual components. No out of memory errors are generated unless the system has actually run out of memory.

Automatic SGA management can enhance workload performance without requiring any additional resources or manual tuning effort. With manual configuration of the SGA, it is possible that compiled SQL statements frequently age out of the shared pool because of its inadequate size. This can increase the frequency of hard parses, leading to reduced performance. When automatic SGA management is enabled, the internal tuning algorithm monitors the performance of the workload, increasing the shared pool if it determines the increase will reduce the number of parses required.

Moving, copying or cloning a database from one server to another with different directory structures can be easily accomplished with RMAN

2010-09-27T05:39:00.000-07:00

Moving, copying or cloning a database from one server to another with different directory structures can be easily accomplished with RMAN. Imagine that you have a database on one node and you want to copy it to another node without shuting down your database and move your datafiles to a different directory structure… This will be demonstrated here by using RMAN.

ASSUMPTIONS

Source Database

* 10.2.0.4 database online (sid neo) at server1 (app)
* archivelog mode is enabled
* db datafiles are in the directory /opt/oracle/oradata/neo2
* database will be backed up online with RMAN to /u01/backup

Destiny Database

* 10.2.0.4 Oracle Home installed without any database running at server2 (mynode2.com)
* db datafiles must be created / moved to different directory: /opt/oracle/oradata/neo
* only the manual backup created at server1 will be moved to server2

AT SERVER1
Moving, copying or cloning a database from one server to another with different directory structures can be easily accomplished with RMAN. Imagine that you have a database on one node and you want to copy it to another node without shuting down your database and move your datafiles to a different directory structure… This will be demonstrated here by using RMAN.

ASSUMPTIONS

Source Database

* 10.2.0.4 database online (sid neo) at server1 (app)
* archivelog mode is enabled
* db datafiles are in the directory /opt/oracle/oradata/neo2
* database will be backed up online with RMAN to /u01/backup

Logon as oracle user software owner at server1 and set your environment variables. Then open RMAN and backup the source database we want to copy /move / clone.

[oracle@neoface oracle]$ export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
[oracle@neoface oracle]$ export ORACLE_SID=neo
[oracle@neoface oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@neoface oracle]$ rman target /
RMAN> backup database plus archivelog;

cf_NEO_c-1689570411-20090106-00 (control file backup)
back_NEO_675389594_736_1
back_NEO_675389780_737_1
back_NEO_675390018_738_1
back_NEO_675390293_739_1

Copy those 5 backup files to server2

[oracle@neoface oracle]$ scp /u01/backup/back_NEO* root@mynode2.com:/u01/backup/

Create an initialization file (pfile) from the current spfile. Then copy it to the server2.

[oracle@neoface oracle]$ sqlplus “/ as sysdba”
SQL> create pfile from spfile;
SQL> exit;
[oracle@neoface oracle]$ scp /opt/oracle/product/10.2.0/db_1/dbs/initneo.ora oracle@mynode2.com:/opt/oracle/product/10.2.0/db_1/dbs/initneo.ora/

AT SERVER2

Logon at server2 to do the following steps:

* create the OS directories to hold the datafiles and the admin log files and pfile:
* edit the pfile to modify the instance name in parameters like bdump, udump, etc
* change the onwership of pfile to belong to oracle user
* connect to RMAN and startup the database in nomount mode
* restore the control file from the backup
* mount the database
* validate catalog by crosschecking and cataloging the 4 backups pieces we copied
* rename the datafiles and redolog files and restoring the database

Switch to oracle user and create datafiles directories :

[root@mynode2 root] su – oracle
[oracle@mynode2 oracle]$ mkdir /opt/oracle/admin/neo -p
[oracle@mynode2 oracle]$ cd /opt/oracle/admin/neo
[oracle@mynode2 oracle]$ mkdir cdump udump bdump pfile

[oracle@mynode2 oracle]$ mkdir /opt/oracle/oradata/neo -p

Edit your pfile accordingly your new directory structure:

[oracle@mynode2 oracle]$ vi /opt/oracle/product/10.2.0/db_1/dbs/initneo.ora

Set environment variables and start working on RMAN:

[oracle@mynode2 oracle]$ export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
[oracle@mynode2 oracle]$ export ORACLE_SID=neo
[oracle@mynode2 oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@mynode2 oracle]$ rman target /
RMAN> startup nomount
RMAN> restore controlfile from ‘/u01/backup/cf_NEO_c-1689570411-20090106-00′;
RMAN> alter database mount ;
RMAN> exit

Now that the database is mounted, we’ll check the correct database SCN from the current log that we’ll use later to recover the database. Take note of your current SCN.

[oracle@mynode2 oracle]$ sqlplus “/ as sysdba”
SQL> select group#, first_change#, status, archived from v$log;

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
1 336565140 ACTIVE YES
2 336415067 CURRENT NO
3 336523814 INACTIVE YES

SQL> exit;

[oracle@mynode2 oracle]$ rman target /

As we only copied to this server the backup we created at the beggining and we did not copy all the backups we had on server1 we must crosscheck the catalog against the OS files. Run the following commands at RMAN prompt :

RMAN> CROSSCHECK backup;
RMAN> CROSSCHECK copy;
RMAN> CROSSCHECK backup of database;
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;

Now let’s catalog the 4 backup pieces that we copy to this server2:

RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675389594_736_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675389780_737_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675390018_738_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675390293_739_1′;

Next, as we changed the directory of our datafiles we must rename the redologs:
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo01.log’ to ‘/opt/oracle/oradata/neo/redo01.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo02.log’ to ‘/opt/oracle/oradata/neo/redo02.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo03.log’ to ‘/opt/oracle/oradata/neo/redo03.log’;

If you use BLOCK CHANGE TRACKING to allow fast incremental backups, and if you want to move the datafiles to different directory you must disable this feature and enabling it by specifying the new dir:

RMAN> ALTER DATABASE disable block change tracking;
RMAN> ALTER DATABASE enable block change tracking using file ‘/opt/oracle/oradata/neo/block_change_tracking.f’;

This will avoid errors like ORA-19751 and ORA-19750

Now let’s run the script that will restore our database, renaming the datafiles and recovering until the archivelog with SCN 336415067, the current one.

RMAN> run {
set newname for datafile 1 to “/opt/oracle/oradata/neo/system01.dbf”;
set newname for datafile 2 to “/opt/oracle/oradata/neo/undotbs01.dbf”;
set newname for datafile 3 to “/opt/oracle/oradata/neo/sysaux01.dbf”;
set newname for datafile 4 to “/opt/oracle/oradata/neo/data01.dbf”;
set newname for datafile 5 to “/opt/oracle/oradata/neo/index01.dbf”;
set newname for datafile 6 to “/opt/oracle/oradata/neo/users01.dbf”;
set newname for datafile 7 to “/opt/oracle/oradata/neo/streams.dbf”;
set newname for datafile 8 to “/opt/oracle/oradata/neo/data01brig.dbf”;
set newname for datafile 9 to “/opt/oracle/oradata/neo/index02.dbf”;
restore database;
switch datafile all;
recover database until scn 336415067;
}

RMAN> ALTER DATABASE open resetlogs;

I didn’t manage to avoid errors like ORA-01110 and ORA-01180 at RMAN without using the “until” clause in the “recover database” sentence instead, like most people use it, as the first instruction after the run command.

----------------------------------------------------------------------------------
Renaming the database in the process

Pfile

Changing the dbname and path,
sqlplus / as sysdba
create pfile from spfile;
exit

cd $ORACLE_HOME/dbs
rm -f spfiledbname3.ora
vi initdbname3.ora
then,

remove the first lines
:%s/dbname/dbname3/g

Database

Look for datafile IDs,

list backup of database;

add the rman statements to relocate the datafiles,
run {
set newname for datafile 1 to '/u02/oradata/dbname3/system01.dbf';
set newname for datafile 2 to '/u02/oradata/dbname3/undotbs01.dbf';
set newname for datafile 3 to '/u02/oradata/dbname3/sysaux01.dbf';
set newname for datafile 4 to '/u02/oradata/dbname3/users01.dbf';
set newname for datafile 5 to '/u02/oradata/dbname3/tsname.dbf';
restore database;
}

Brief explanation of how assorted Oracle files can be renamed or moved to a new location

2010-09-27T05:34:00.000-07:00

To move or rename a controlfile do the following:

* Alter the control_files parameter using the ALTER SYSTEM comamnd.
* Shutdown the database.
* Rename the physical file on the OS.
* Start the database.

To move or rename a logfile do the following:

* Shutdown the database.
* Rename the physical file on the OS.
* Start the database in mount mode.
* Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
* Open the database.

To move or rename a datafile do the following:

* Shutdown the database.
* Rename the physical file on the OS.
* Start the database in mount mode.
* Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
* Open the database.

ORA-01555: snapshot too old during export EXP-00000: Export terminated unsuccessfully

2010-09-27T03:52:00.000-07:00

Seems the other sessions are updating the database during your export.
So use CONSISTENT=N ( which is default).
Dont specify CONSISTENT=Y.
Else
Increase your RBS(in 8i) and look into undo Management in 9i.
There is a long running transaction that needs more undo space than the available one.

sql >show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 0
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1

If the UNDO_RETENTION initialization parameter is not specified, the default value is 900 seconds.( I must have tweaked mine. Ingore it).

to reset this would be the command.
ALTER SYSTEM SET UNDO_RETENTION = 30000;

quoting docs

Committed undo information normally is lost when its undo space is overwritten by a newer transaction. But for consistent read purposes, long running queries might require old undo information for undoing changes and producing older images of data blocks. The initialization parameter, UNDO_RETENTION, provides a means of explicitly specifying the amount of undo information to retain. With a proper setting, long running queries can complete without risk of receiving the "snapshot too old" error.

Cost Based Optimizer (CBO) and Database Statistics

2010-09-27T03:44:00.000-07:00

Cost Based Optimizer (CBO) and Database Statistics

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

•Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.

•Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.

If new objects are created, or the amount of data in the database changes the statistics will no longer represent the real state of the database so the CBO decision process may be seriously impaired. The mechanisms and issues relating to maintenance of internal statistics are explained below:

•Analyze Statement
•DBMS_UTILITY
•DBMS_STATS
•Scheduling Stats
•Transfering Stats
•Issues
Analyze Statement
The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can be computed exactly, or estimated based on a specific number of rows, or a percentage of rows:

ANALYZE TABLE employees COMPUTE STATISTICS;
ANALYZE INDEX employees_pk COMPUTE STATISTICS;

ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;DBMS_UTILITY
The DBMS_UTILITY package can be used to gather statistics for a whole schema or database. Both methods follow the same format as the analyze statement:

EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);

EXEC DBMS_UTILITY.analyze_database('COMPUTE');
EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_percent => 15);

DBMS_STATS

The DBMS_STATS package was introduced in Oracle 8i and is Oracles preferred method of gathering object statistics. Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. Once again, it follows a similar format to the other methods:

EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

This package also gives you the ability to delete statistics:

EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('SCOTT');
EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

Scheduling Stats

Scheduling the gathering of statistics using DBMS_Job is the easiest way to make sure they are always up to date:

SET SERVEROUTPUT ON
DECLARE
l_job NUMBER;
BEGIN
DBMS_JOB.submit(l_job,
'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
SYSDATE,
'SYSDATE + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/

The above code sets up a job to gather statistics for SCOTT for the current time every day. You can list the current jobs on the server using the DBA_JOBS and DBA_JOBS_RUNNING views.

Existing jobs can be removed using:

EXEC DBMS_JOB.remove(X);
COMMIT;

Where 'X' is the number of the job to be removed.

Transfering Stats

It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:

SQL> EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
SQL> EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');

This table can then be transfered to another server using your preferred method
(Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:

SQL> EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
SQL> EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');

Issues

•Exclude dataload tables from your regular stats gathering, unless you know they will be full at the time that stats are gathered.
•I've found gathering stats for the SYS schema can make the system run slower, not faster.
•Gathering statistics can be very resource intensive for the server so avoid peak workload times or gather stale stats only.
•Even if scheduled, it may be necessary to gather fresh statistics after database maintenance or large data loads.

Using EXPLAIN PLAN and TKPROF To Tune Your Applications

2010-09-27T02:42:00.001-07:00

Consider the following query and execution plan:
SELECT a.customer_name, COUNT (DISTINCT b.invoice_id) "Open Invoices",
COUNT (c.invoice_id) "Open Invoice Items"
FROM customers a, invoices b, invoice_items c
WHERE b.invoice_status = 'OPEN'
AND a.customer_id = b.customer_id
AND c.invoice_id (+) = b.invoice_id
GROUP BY a.customer_name;

ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 SORT GROUP BY
2 1 NESTED LOOPS OUTER
3 2 HASH JOIN
4 3 TABLE ACCESS BY INDEX ROWID INVOICES
5 4 INDEX RANGE SCAN INVOICES_STATUS
6 3 TABLE ACCESS FULL CUSTOMERS
7 2 INDEX RANGE SCAN INVOICE_ITEMS_PK
This execution plan is more complex than the previous two, and here you can start to get a feel for the way in which complex operations get broken down into simpler subordinate operations. To execute this query, the database server will do the following: First Oracle will perform a range scan on the invoices_status index to get the ROWIDs of all rows in the invoices table with the desired status. For each ROWID found, the record from the invoices table will be fetched.

This set of invoice records will be set aside for a moment while the focus turns to the customers table. Here, Oracle will fetch all customers records with a full table scan. To perform a hash join between the invoices and customers tables, Oracle will build a hash from the customer records and use the invoice records to probe the customer hash.
Next, a nested loops join will be performed between the results of the hash join and the invoice_items_pk index. For each row resulting from the hash join, Oracle will perform a unique scan of the invoice_items_pk index to find index entries for matching invoice items. Note that Oracle gets everything it needs from the index and doesn’t even need to access the invoice_items table at all. Also note that the nested loops operation is an outer join. A sort operation for the purposes of grouping is performed on the results of the nested loops operation in order to complete the SELECT statement.

It is interesting to note that Oracle choose to use a hash join and a full table scan on the customers table instead of the more traditional nested loops join. In this database there are many invoices and a relatively small number of customers, making a full table scan of the customers table less expensive than repeated index lookups on the customers_pk index. But suppose the customers table was enormous and the relative number of invoices was quite small. In that scenario a nested loops join might be better than a hash join. Examining the execution plan allows you to see which join method Oracle is using. You could then apply optimizer hints to coerce Oracle to use alternate methods and compare the performance.

You may wonder how I got that whole detailed explanation out of the eight line execution plan listing shown above. Did I read anything into the execution plan? No! It’s all there! Understanding the standard inputs and outputs of each type of operation and coupling this with the indenting is key to reading an execution plan.

A nested loops join operation always takes two inputs: For every row coming from the first input, the second input is executed once to find matching rows. A hash join operation also takes two inputs: The second input is read completely once and used to build a hash. For each row coming from the first input, one probe is performed against this hash. Sorting operations, meanwhile, take in one input. When the entire input has been read, the rows are sorted and output in the desired order.

Now let’s look at a query with a more complicated execution plan:
SELECT customer_name
FROM customers a
WHERE EXISTS
(
SELECT 1
FROM invoices_view b
WHERE b.customer_id = a.customer_id
AND number_of_lines > 100
)
ORDER BY customer_name;

ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 SORT ORDER BY
2 1 FILTER
3 2 TABLE ACCESS FULL CUSTOMERS
4 2 VIEW INVOICES_VIEW
5 4 FILTER
6 5 SORT GROUP BY
7 6 NESTED LOOPS
8 7 TABLE ACCESS BY INDEX ROWID INVOICES
9 8 INDEX RANGE SCAN INVOICES_CUSTOMER_ID
10 7 INDEX RANGE SCAN INVOICE_ITEMS_PK

This execution plan is somewhat complex because the query includes a subquery that the optimizer could not rewrite as a simple join, and a view whose definition could not be merged into the query. The definition of the invoices_view view is as follows:

CREATE OR REPLACE VIEW invoices_view
AS
SELECT a.invoice_id, a.customer_id, a.invoice_date, a.invoice_status,
a.invoice_number, a.invoice_type, a.total_amount,
COUNT(*) number_of_lines
FROM invoices a, invoice_items b
WHERE b.invoice_id = a.invoice_id
GROUP BY a.invoice_id, a.customer_id, a.invoice_date, a.invoice_status,
a.invoice_number, a.invoice_type, a.total_amount;

Here is what this execution plan says: Oracle will execute this query by reading all rows from the customers table with a full table scan. For each customer record, the invoices_view view will be assembled as a filter and the relevant contents of the view will be examined to determine whether the customer should be part of the result set or not.

Oracle will assemble the view by performing an index range scan on the invoices_customer_id index and fetching the rows from the invoices table containing one specific customer_id. For each invoice record found, the invoice_items_pk index will be range scanned to get a nested loops join of invoices to their invoice_items records. The results of the join are sorted for grouping, and then groups with 100 or fewer invoice_items records are filtered out.

What is left at the step with ID 4 is a list of invoices for one specific customer that have more than 100 invoice_items records associated. If at least one such invoice exists, then the customer passes the filter at the step with ID 2. Finally, all customer records passing this filter are sorted for correct ordering and the results are complete.

Note that queries involving simple views will not result in a “view” operation in the execution plan. This is because Oracle can often merge a view definition into the query referencing the view so that the table accesses required to implement the view just become part of the regular execution plan. In this example, the GROUP BY clause embedded in the view foiled Oracle’s ability to merge the view into the query, making a separate “view” operation necessary in order to execute the query.

Also note that the filter operation can take on a few different forms. In general, a filter operation is where Oracle looks at a set of candidate rows and eliminates some based on certain criteria. This criteria could involve a simple test such as number_of_lines > 100 or it could be an elaborate subquery.

In this example, the filter at step ID 5 takes only one input. Here Oracle evaluates each row from the input one at a time and either adds the row to the output or discards it as appropriate. Meanwhile, the filter at step ID 2 takes two inputs. When a filter takes two inputs, Oracle reads the rows from the first input one at a time and executes the second input once for each row. Based on the results of the second input, the row from the first input is either added to the output or discarded.

Oracle is able to perform simple filtering operations while performing a full table scan. Therefore, a separate filter operation will not appear in the execution plan when Oracle performs a full table scan and throws out rows that don’t satisfy a WHERE clause. Filter operations with one input commonly appear in queries with view operations or HAVING clauses, while filter operations with multiple inputs will appear in queries with EXISTS clauses.

An important note about execution plans and subqueries: When a SQL statement involves subqueries, Oracle tries to merge the subquery into the main statement by using a join. If this is not feasible and the subquery does not have any dependencies or references to the main query, then Oracle will treat the subquery as a completely separate statement from the standpoint of developing an execution plan—almost as if two separate SQL statements were sent to the database server. When you generate an execution plan for a statement that includes a fully autonomous subquery, the execution plan may not include the operations for the subquery. In this situation, you need to generate an execution plan for the subquery separately.

Use EXPLAIN PLAN and TKPROF To Tune Your Applications

2010-09-27T02:08:00.000-07:00

tkprof [explain=] [sys=n] \
[insert=] [record=] [sort=]

If you invoke TKPROF with no arguments at all, you will get a help screen listing all of the options. This is especially helpful because TKPROF offers many sort capabilities, but you select the desired sort by specifying a cryptic keyword. The help screen identifies all of the sort keywords.

In its simplest form, you run TKPROF specifying the name of a SQL trace file and an output filename. TKPROF will read the trace file and generate a report file with the output filename you specified. TKPROF will not connect to the database, and the report will not include execution plans for the SQL statements. SQL statements that were executed by the SYS user recursively (to dynamically allocate an extent in a dictionary-managed tablespace, for example) will be included in the report, and the statements will appear in the report approximately in the order in which they were executed in the database session that was traced.

If you include the explain keyword, TKPROF will connect to the database and execute an EXPLAIN PLAN statement for each SQL statement found in the trace file. The execution plan results will be included in the report file. As we will see later, TKPROF merges valuable information from the trace file into the execution plan display, making this just about the most valuable way to display an execution plan. Note that the username you specify when running TKPROF should be the same as the username connected in the database session that was traced. You do not need to have a plan table in order to use the explain keyword—TKPROF will create and drop its own plan table if needed.

If you specify sys=n, TKPROF will exclude from the report SQL statements initiated by Oracle as the SYS user. This will make your report look tidier because it will only contain statements actually issued by your application. The theory is that Oracle internal SQL has already been fully optimized by the kernel developers at Oracle Corporation, so you should not have to deal with it. However, using sys=n will exclude potentially valuable information from the TKPROF report. Suppose the SGA is not properly sized on the instance and Oracle is spending a lot of time resolving dictionary cache misses. This would manifest itself in lots of time spent on recursive SQL statements initiated by the SYS user. Using sys=n would exclude this information from the report.

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Sep 27 14:33:22 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 160
Next log sequence to archive 162
Current log sequence 162
SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
IREPS_REVOKED_CERTS_MASTER TABLE

SQL> ALTER SESSION /* Module glpost.c */ SET sql_trace = TRUE;

Session altered.

SQL> select * from DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@crisreplication ~]$ cd /u01/app/oracle/
admin/ oradata/ oraInventory_24nov09.tar.gz product/
flash_recovery_area/ oraInventory/ oraInventory.tar.gz utlfile/
[oracle@crisreplication ~]$ cd /u01/app/oracle/admin/trialrep/udump/
[oracle@crisreplication udump]$ ls -ltr
[oracle@crisreplication udump]$ tkprof trialrep_ora_15837.trc trialrep_ora_15837.html

TKPROF: Release 10.2.0.4.0 - Production on Mon Sep 27 14:35:23 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

[oracle@crisreplication udump]$ tkprof trialrep_ora_15837.trc trialrep_ora_15837.html explain=scott/tiger sys=n

TKPROF: Release 10.2.0.4.0 - Production on Mon Sep 27 14:35:47 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

[oracle@crisreplication udump]$

TKPROF: Release 10.2.0.4.0 - Production on Mon Sep 27 14:35:47 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Trace file: trialrep_ora_15837.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

select *
from
DEPT

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.06 6 8 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.13 6 8 0 4

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57 (SCOTT)

Rows Row Source Operation
------- ---------------------------------------------------
4 TABLE ACCESS FULL DEPT (cr=8 pr=6 pw=0 time=67168 us)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
4 TABLE ACCESS MODE: ANALYZED (FULL) OF 'DEPT' (TABLE)

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.06 6 8 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.13 6 8 0 4

Misses in library cache during parse: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 15 0.01 0.13 0 0 0 0
Execute 69 0.06 0.14 0 0 0 0
Fetch 80 0.01 0.06 0 216 0 471
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 164 0.09 0.34 0 216 0 471

Misses in library cache during parse: 14
Misses in library cache during execute: 14

1 user SQL statements in session.
69 internal SQL statements in session.
70 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: trialrep_ora_15837.trc
Trace file compatibility: 10.01.00
Sort options: default

1 session in tracefile.
1 user SQL statements in trace file.
69 internal SQL statements in trace file.
70 SQL statements in trace file.
15 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
SCOTT.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
648 lines in trace file.
0 elapsed seconds in trace file.

HOW TO REMOVE CRS AUTO START AND RESTART FOR A RAC INSTANCE

2010-09-27T00:43:00.000-07:00

HOW TO REMOVE CRS AUTO START AND RESTART FOR A RAC INSTANCE
-----------------------------------------------------------

OVERVIEW
Oracle Clusterware (CRS) is a new feature of Oracle Database 10g Real
Application Clusters that further differentiates RAC for high availability and
scalability. CRS provides automated management and sophisticated monitoring of
RAC instances and is designed to enhance the overall user experience of cluster
database management.

By default, CRS is configured to auto-start database instances as a part of node
boot and provide lights-out instance failure detection followed by an
auto-restart of the failed instance. However, on some special occasions,
it might be highly desirable to limit the level of protection CRS provides
for a RAC database. Namely, this implies preventing instances from auto-starting
on boot and not auto-restarting failed instances. The latter, however, may be
relaxed to allow a single attempt to restart a failed instance. This way, CRS
will attempt to restore availability of the instance, but avoid thrashing if a
problem that caused the instance to fail also keeps preventing it from
successfully recovering on restart. Either way, the choice to customize this is
left to the DBA.

Oracle Database 10g Real Application Clusters release 10.1.0.4 and above make
it possible to accomplish the above stated change in CRS behavior. This document
lists the steps necessary to limit the level of CRS protection over a RAC
database.

HIGH LEVEL APPROACH
In a nutshell, the procedure amounts to the following two parts
1. Identifying a set of CRS resources that affect the behavior
2. Modifying a few profile attributes to contain special values for the
resources identified in the first step

The following sections will cover both phases in detail.
IDENTIFYING RELEVANT RESOURCES

The automated management of a RAC database is accomplished by modeling various
RAC applications/features as CRS resources. A CRS resource is defined by a
profile, which contains a list of attributes that tell CRS how manage the
resource. CRS resources created to manage a RAC database can be identified as
belonging to a well-known type. There is a finite and relatively small number
of types. The type may be easily identified given the name of a resource: each
name ends with a . For instance, ora.linux.db is of type db,
which happens to mean database. To display the names of the resources managed
by the CRS, use the crs_stat command from a clustered node. The output of the
command is a set of names and states of resources registered on the cluster to
which the node belongs.

Figure 1. Example Listing resource names using crs_stat
$ crs_stat
NAME=ora.linux.ar.us.oracle.com.cs
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE

NAME=ora.linux.ar.us.oracle.com.linux.srv
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE

Additional output is available but has been truncated for clarity's sake

The relevant resources are the resources that belong to the following 4 types:

db - Denotes resources that represent a database
srv -Denotes resources that represent a service member.
cs - Denotes resources that represent a service.
inst - Denotes resources that represent a database instance

The CRS profiles for these resources must be modified for the new CRS behavior
to take effect for all RAC databases installed on the cluster. If, however,
the affect of the change is to be limited to a subset of installed databases,
the list of resources needs to be filtered further. (The rest of this section
should be skipped if the new CRS behavior. is to be in effect for all databases
installed on the cluster.)
Please note that since more than one database may be installed on a cluster,
to modify the level of protection for a particular database, one must identify
the resources that represent entities of this database. This may be easily
accomplished since the names of the resources belonging to the above- stated
types always start with ora.. For instance, ora.linux.db
means that the resource belongs to the database named linux. Only resources of
the above-enumerated types that belong to the selected databases will need to
have their profiles modified.
MODIFYING RESOURCE PROFILES
Please note that Oracle strongly discourages any modifications made to CRS
profiles for any resources starting with . Never make any modifications to
CRS profiles for resources but the ones explicitly described below in
this document.

To modify a profile attribute for a resource, the following steps must be
followed:
1.Generate the resource profile file by issuing the following command:
crs_stat -p resource_name > $CRS_HOME/crs/public/resource_name.cap

2.Update desired attributes by editing the file created in step 1.

3.Commit the updates made as a part of the previous step by issuing the
following command
crs_register -u resource_name

4. Verify the updates have been committed by issuing the following command
crs_stat -p resource_name

For each of the resources identified as a part of the preceding section, the
following modifications must be made:
1.Resources of type inst must have the following attributes modified
AUTO_START must be set to 2
RESTART_ATTEMPTS must be set to 0 or 1. The former value will prevent
CRS from attempting to restart a failed instance at all while the latter
will grant it a single attempt; if this only attempt is unsuccessful,
CRS will leave the instance as is.
2.Resources of type db, srv, cs must have the following attributes modified
AUTO_START must be set to 2

RMAN Terminology

2010-09-27T00:29:00.000-07:00

Explanation of RMAN Terminology

RMAN TARGET DATABASE – An RMAN Target Database is the primary database that will be backed up for RAC Grid creation. In RMAN’s terminology, the term target database identifies the database that is undergoing a backup, restore or recovery operation by the RMAN Recovery Manager.

RMAN AUXILIARY DATABASE – An Auxiliary Database is a RAC Grid that will be created as a result of the duplication of the target database. In RMAN’s terminology, Auxiliary instance identifies an instance which RMAN connects in order to execute the duplicate command.

RMAN CHANNEL – An RMAN Channel is a communication pipeline between a RMAN executable and a target or auxiliary database. An RMAN channel consists of a server session on the target or auxiliary database and a data stream from the database to the backup device or vice-versa. RMAN console sends commands to the database using this channel, and the server session running on the database executes the command on behalf of the RMAN Recovery Manager.

RMAN AUTOMATIC CHANNEL ALLOCATION – RMAN Channels can be configured to use a set of default attributes for each operation when a channel is not allocated manually. By default, RMAN configures a channel of device type, DISK, to be used for automatic channel allocation.

RMAN MANUAL CHANNEL ALLOCATION - As the name suggests, a channel can be configured manually for special needs such as increasing the degree of parallelism. Channels can be allocated manually by using the ALLOCATE CHANNEL command in the RUN block of RMAN statement.

Connecting to an Auxiliary Database
To use the DUPLICATE command or to perform RMAN TSPITR, you need to connect to an auxiliary instance. In these examples, assume that these variables have the following meanings.

Variable Meaning
SYS User with SYSDBA privileges

oracle The password for connecting as SYSDBA specified in the target database's orapwd file

trgt The net service name for the target database

rman Owner of the recovery catalog having RECOVERY_CATALOG_OWNER privilege

cat The password for user RMAN specified in the recovery catalog's orapwd file

catdb The net service name for the recovery catalog database

aux The password for connecting as SYSDBA specified in the auxiliary database's orapwd file

auxdb The net service name for the auxiliary database

If the auxiliary database uses password files for authentication, then you can connect using a password for either local or remote access. If you are connecting remotely through a net service name, then authentication through a password file is mandatory.

Connecting to an Auxiliary Database from the Command Line
To connect to an auxiliary instance from the operating system command line, enter the following:

% rman AUXILIARY SYS/aux@auxdb

To connect to the target, auxiliary, and recovery catalog databases, issue the following (all on one line):

% rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb AUXILIARY SYS/aux@auxdb

Connecting to an Auxiliary Database from the RMAN Prompt
Alternatively, you can start RMAN and connect to the auxiliary database from the RMAN prompt:

% rman
RMAN> CONNECT AUXILIARY SYS/aux@auxdb

To connect to the target, auxiliary, and recovery catalog databases, issue:

% rman
RMAN> CONNECT TARGET SYS/oracle@trgt
RMAN> CONNECT CATALOG rman/cat@catdb
RMAN> CONNECT AUXILIARY SYS/aux@auxdb

Parsing SQL Statements in Oracle

2010-09-23T01:31:00.000-07:00

Parsing SQL Statements in Oracle

One of the first steps Oracle takes in processing a SQL statement is to parse it. During the parsing phase, Oracle will break down the submitted SQL statement into its component parts, determine what type of statement it is (Query, DML, or DDL), and perform a series of checks on it. Two important concepts for the DBA to understand is (1) what are the steps involved in the parse phase and (2) what is the difference between a hard parse and a soft parse.

The Syntax Check & Semantic Analysis

The first two function of the parse phase Syntax Check and Semantic Analysis happen for each and every SQL statement within the database.
Syntax Check
Oracle checks that the SQL statement is valid. Does it make sense given the SQL grammar documented in the SQL Reference Manual? Does it follow all of the rules for SQL?

Semantic Analysis
This function of the parse phase, takes the Syntax Check one step further by checking if the statement is valid in light of the objects in the database. Do the tables and columns referenced in the SQL statement actually exist in the database? Does the user executing the statement have access to the objects and are the proper privileges in place? Are there ambiguities in the statement? For example, consider a statement that references two tables emp1 and emp2 and both tables have a column name. The following statement "select name from emp1, emp where..." is ambiguous; the query doesn't know which table to get name from.

Although Oracle considers the first two functions of the parse phase (checking the validity of the SQL statement and then checking the semantics to ensure that the statement can be properly executed), the difference is sometimes hard to see from the users perspective. When Oracle reports an error to the user during the parse phase, it doesn't just come out and say "Error within the Syntax Function" or "Error within the Semantics Function".

For example, the following SQL statement fails with a syntax error:

SQL> select from where 4;
select from where 4
*
ERROR at line 1:
ORA-00936: missing expression
Here is an example of a SQL statement that fails with a semantic error:

SQL> select * from table_doesnt_exist;
select * from table_doesnt_exist
*
ERROR at line 1:
ORA-00942: table or view does not exist

Hard Parse vs. Soft Parse

We now consider the next and one of the most important functions of Oracle's parse phase. The Oracle database now needs to check in the Shared Pool to determine if the current SQL statement being parsed has already been processed by any other sessions.
If the current statement has already been processed, the parse operation can skip the next two functions in the process: Optimization and Row Source Generation. If the parse phase does, in fact, skip these two functions, it is called a soft parse. A soft parse will save a considerable amount of CPU cycles when running your query. On the other hand, if the current SQL statement has never been parsed by another session, the parse phase must execute ALL of the parsing steps. This type of parse is called a hard parse. It is especially important that developers write and design queries that take advantage of soft parses so that parsing phase can skip the optimization and row source generation functions, which are very CPU intensive and a point of contention (serialization). If a high percentage of your queries are being hard-parsed, your system will function slowly, and in some cases, not at all.

Oracle uses a piece of memory called the Shared Pool to enable sharing of SQL statements. The Shared Pool is a piece of memory in the System Global Area (SGA) and is maintained by the Oracle database. After Oracle completes the first two functions of the parse phase (Syntax and Semantic Checks), it looks in the Shared Pool to see if that same exact query has already been processed by another session. Since Oracle has already performed the semantic check, it has already determined:

Exactly what table(s) are involved

That the user has access privileges to the tables.
Oracle will now look at all of the queries in the Shared Pool that have already been parsed, optimized, and generated to see if the hard-parse portion of the current SQL statement has already been done.

Why not Check the Shared Pool First?

Now that you understand the steps involved in parsing SQL statements, it's time to take it one step further. Oracle will always keep an unparsed representation of the SQL code in the Shared Pool, and that the database will perform a hashing algorithm to quickly located the SQL code. OK, so why doesn't Oracle make this step (checking the Shared Pool for a matching statement) the first step in its parsing phase, before making any other checks.
Even when soft parsing, Oracle needs to parse the statement before it goes looking in the Shared Pool. One of the big reason's for this sequence is the Semantic Check. Consider the following query:

select * from emp;
Assume that this query was first submitted by user "SCOTT" and that the "emp" table in the FROM clause is a table owned by SCOTT. You then submit the same exact query (as a user other than SCOTT) to Oracle. The database has no idea what "emp" is a reference to. Is it a synonym to another table? Is it a view in your schema that references another table? For this reason, Oracle needs to perform a Semantic Check on the SQL statement to ensure that the code you are submitting is going to reference the same exact objects you are requesting in your query.

Then remember that Oracle needs to syntactically parse the query before it can semantically parse it. The hash is good only for finding query strings that are the same; it doesn't help the database figure out if the referenced statements are the same statement as in you execution context.

Master node at Oracle Clusterware level

2010-09-16T04:01:00.001-07:00

[oracle@dbrac1 dump]$ cat $ORA_CRS_HOME/log/`hostname`/cssd/ocssd* |grep master

[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1

HOW TO APPLY ORACLE PATCH (OPATCH)

2010-09-06T01:37:00.000-07:00

HOW TO APPLY ORACLE PATCH (OPATCH)
====================================================

To apply Opatch conditions are db and listener both must be down as opatch will update your current ORACLE_HOME with patches.
in single instance its not possible.
but for RAC instance its possible.
as in RAC there will be two seperate oracle home and two seperate instances running once instance on each oracle_home

use this command:

opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME

when using -local parameter and -oh $ORACLE_HOME this means this patch session will only apply patch to current sourced ORACLE_HOME.

steps before applying patch:
----------------------------

1) check the database status.
wch_db.sql
-----------
select name,
open_mode,
database_name,
created,
log_mode,
platform_name
from v$database;

2) Check the object's invalid.

user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;

count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';

3) Take backup of invalid's

create table bk_inv_ as select * from dba_objects
where status='INVALID';

4) check opatch version using
opatch -v
if opatch version is not compatible check the readme file and
download the latest version and uncompress
in $ORACLE_HOME.

5) check oraInst.loc file pointing to your current $ORACLE_HOME or not.
cat /etc/oraInst.loc

inventory_loc=/u01/app/oracle/10.2.0/GlobalOraInv
inst_group=dba

if your server have more then one $ORACLE_HOME then comment the other $ORACLE_HOME and
uncomment the current $ORACLE_HOME

inventory must point to the current $ORACLE_HOME which is getting patched.

6) check free space on $ORACLE_HOME
df -h $ORACLE_HOME

7) chek the utilities like
which ld
which ar
which make
etc as per readme file.

8) unzip the patch
unzip -d /loc_2_unzip p.zip

9) Go the patch directory
cd /loc_2_unzip/patch_number

10) Bring down the listner.
cd $ORACLE_HOME/bin
lsnrctl stop

11) Bring down the database
Shutdown immediate.

12) export opatch
export PATH=$PATH:$HOME:$ORACLE_HOME/OPatch:/bin

13) Start the patch

skip_duplicate
Skips patches to be applied that are duplicates of other patches installed in the Oracle home. Two patches are duplicates if they fix the same set of bugs.

skip_subset
Skips patches to be applied that are subsets of other patches installed in the Oracle home. One patch is a subset of another patch if the former fixes a subset of bugs fixed by the latter.

opatch napply -skip_subset -skip_duplicate

for RAC database then database can be up
as it may be having more then one instance
so you can bring down one instance and listener and apply the patch and open it
and then do the same on another node.
like this db will be up and no user will face issue in outage also.

to apply opatch in RAC instance (We can use -skip_subset -skip_duplicate with Opatch napply only? And is it right that if we are installing patch for the first time on a oracle software we will generally use opatch apply. consider I have applied october cpu patch and I am applying November patch. so while applting Oct patch I shall just use opatch apply and while applying Nov patch i shall use opatch napply -skip_subset -skip_duplicate)
///////////*************************************************888
The readme stats whether you should use opatch apply or opatch napply.

I am not sure whether november CPU patch is a bundle of multiple patch or single patch. If it is bundle of multiple patch then you should be using opatch napply irrespective of whether you applied any patch on your software previously. apply and naply does not depends on previous patches that are appled on your database. It just depend onl current opatch session. If you need to install just one patch currently use opatch apply. And if you want to install multiple patch in a single session use opatch napply.

And regarding -skip_subset and -skip_duplicate you are correct it shoudl be used only with opatch napply.

For more info use below command
opatch apply -help
opatch napply -help

**********************************************************///////////

opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME

when using -local parameter and
-oh $ORACLE_HOME this means this patch session will only apply patch to current ORACLE_HOME only.

--------------------------------------------------------
. All-Node Patch

. Shutdown all Oracle instances on all nodes
. Apply the patch to all nodes
. Bring all nodes up

. Minimum downtime

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. At this point, instances on nodes 1 and 2 can be brought up
. Apply the patch to the Oracle instance on node 3
. Startup the Oracle instance on node 3

. (no downtime)

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Start the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Start the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. Apply the patch to the Oracle instance on node 3
. Start the Oracle instance on node 3
-------------------------------------------------------------

14) Once patch installation is completed need to do post patching steps.

a) starup the instance
startup
b) Loading modified sqlfiles into the database.
@$ORACLE_HOME/rdbms/admin/catbundle.sql cpu apply

to check the logs generated
catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log

c) Recompiling Views in the Database
shutdown immediate
startup upgrade
@$ORACLE_HOME/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown immediate
startup

If it is a RAC instance.
shutdown
startup nomount
alter database set cluster database=false scope=spfile;
shutdown
startup upgrade
@?/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown
startup
alter database set cluster database=true scope=spfile;

restart the database.
cd $CRS_HOME/bin
srvctl start database -d
15) If any invalid objects were reported, run the utlrp.sql script as follows

user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;

count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';

if any new invalids seen then again take backup of invalid objects and compile it.

create table bk_inv_ as select * from dba_objects
where status='INVALID';

@?/rdbms/admin/utlrp.sql --- to compile the invalid objects.

16) Confirm that patch has been applied successfully or not at db level also.

post_patch.sql
--------------
col action_time for a40
col action for a15
col namespace for a15
col version for a15
col comments for a40
set pages 1000
set lines 170
select * from registry$history ;

2010-08-17T23:04:00.000-07:00

Transparent application failover

Is a client-side feature that allows for clients to reconnect to surviving databases in the event of a failure of a database instance. Notifications are used by the server to trigger TAF callbacks on the client-side. Your options could be: Basic, in which the application connects to a backup node only after the primary connection fails. This approach has low overhead, but the end user experiences a delay while the new connection is created, or Pre-Connect, where the application simultaneously connects to both a primary and a backup node. This offers faster failover, because a pre-spawned connection is ready to use. But the extra connection adds everyday overhead by duplicating connections.

Log switching history

2010-08-11T03:09:00.001-07:00

Log switching history

select to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from v$log_history group by to_char(first_time,'YYYY-MON-DD');

Track redo generation by day

2010-08-11T02:19:00.000-07:00

select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024)) “REDO PER DAY (MB)”
from v$archived_log
group by trunc(completion_time)
order by 1

Recover from loss of single current online redo log file

2010-08-11T02:01:00.000-07:00

Recover from loss of single current online redo log file

RMAN> list backupset summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
7 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
8 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
9 B 0 A DISK 11-AUG-10 1 1 NO EB64LEVEL0
10 B A A DISK 11-AUG-10 1 1 NO TAG20100811T141822
11 B A A DISK 11-AUG-10 1 1 NO TAG20100811T141822
RMAN> exit

Recovery Manager complete.
[oracle@crisreplication ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 11 14:19:01 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select member from v$Logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/trialrep/redo02.log
/u01/app/oracle/oradata/trialrep/redo01.log
/u01/app/oracle/oradata/trialrep/redo03.log
SQL> SQL>
SQL>
SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> select group#, member from v$logfile order by 1;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/trialrep/redo01.log
2
/u01/app/oracle/oradata/trialrep/redo02.log
3
/u01/app/oracle/oradata/trialrep/redo03.log

SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@crisreplication ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 11 14:21:18 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 ACTIVE
2 14 50 1 INACTIVE
3 16 50 1 CURRENT
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 1267164 bytes
Variable Size 318769700 bytes
Database Buffers 872415232 bytes
Redo Buffers 15507456 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/trialrep/redo03.log'

SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;
GROUP# SEQUENCE# Size (MB) MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 15 50 1 INACTIVE
3 16 50 1 CURRENT
2 14 50 1 INACTIVE
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@crisreplication ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 11 14:24:14 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TRIALREP (DBID=1985177645, not open)

RMAN> run {
2> set until sequence 16;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }

executing command: SET until clause
using target database control file instead of recovery catalog
Starting restore at 11-AUG-10

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/trialrep/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/trialrep/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/trialrep/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/rman0bll30b1_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rman/rman0bll30b1_1_1 tag=EB64LEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/trialrep/system01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/trialrep/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/trialrep/bv1to1.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/rman0all30b1_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rman/rman0all30b1_1_1 tag=EB64LEVEL0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46

Finished restore at 11-AUG-10

Starting recover at 11-AUG-10
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 15 is already on disk as file /arch/1_15_726755127.dbf
archive log filename=/arch/1_15_726755127.dbf thread=1 sequence=15
media recovery complete, elapsed time: 00:00:04

Finished recover at 11-AUG-10

database opened

Backup script for Linux using tar and find

2010-08-10T06:45:00.000-07:00

script.sh
#!/bin/bash
#
# creates backups of essential files
#
DATA="/home /root /usr/local/httpd"
CONFIG="/etc /var/lib /var/named"
LIST="/tmp/backlist_$$.txt"
#
#mount /mnt/backup
set $(date)
#
if test "$1" = "Tue" ; then
# weekly a full backup of all data and config. settings:
#
tar cfz "/mnt/backup/data/data_full_$6-$2-$3.tgz" $DATA
rm -f /mnt/backup/data/data_diff*
#
tar cfz "/mnt/backup/config/config_full_$6-$2-$3.tgz" $CONFIG
rm -f /mnt/backup/config/config_diff*
else
# incremental backup:
#
find $DATA -depth -type f $ -ctime -1 -o -mtime -1 $ -print > $LIST
tar cfzT "/mnt/backup/data/data_diff_$6-$2-$3.tgz" "$LIST"
rm -f "$LIST"
#
find $CONFIG -depth -type f $ -ctime -1 -o -mtime -1 $ -print > $LIST
tar cfzT "/mnt/backup/config/config_diff_$6-$2-$3.tgz" "$LIST"
rm -f "$LIST"
fi

Reading Text Files using Oracle PL/SQL and UTL_FILE

2010-07-08T00:39:00.000-07:00

Set ----alter system set utl_file_dir='/foo/bar/dir1','/foo/baz/dir2','/tmp' scope=spfile

The first step we need to perform is nothing but letting the Oracle database know on which directory we are working (reading or writing files only from that directory). This can be achieved using the following command:

CREATE DIRECTORY sampledata AS 'c:sampledata';

The above command creates a new directory entry into the Oracle database with a logical name to the directory, "SAMPLEDATA" (pointing to the path "c:sampledata"). To execute the above command, you need to have certain privileges. I suggest you log in using "SYSTEM" user (or contact your database administrator on this issue). If you wanted to list all the directories registered, you can use the following command:

SELECT * FROM ALL_DIRECTORIES;

The second step we need to perform is providing necessary permissions to the user to work with that directory. This can be achieved with something like the following:

GRANT read, write ON DIRECTORY sampledata TO PUBLIC;

The above step makes sure that the directory "sampledata" (or "c:sampledata") is accessible to every user (PUBLIC). It is not at all a good idea to provide grants to the PUBLIC. I suggest you provide the grants only to the required users (who really need them).

The third step we need to perform is simply creating a new text file within the folder "c:sampledata". I hope you can do it yourself.

The last and final step is trying to read the file using PL/SQL. Open your SQL*PLUS environment and copy the following code:

declare

f utl_file.file_type;

s varchar2(200);

begin

f := utl_file.fopen('SAMPLEDATA','sample1.txt','R');

utl_file.get_line(f,s);

utl_file.fclose(f);

dbms_output.put_line(s);

end;

Created by Suyash application dba HCL-----

Implemented this code.

*************************************************************

DECLARE

TYPE IDCurTyp IS REF CURSOR;

fo UTL_FILE.FILE_TYPE;

varRow VARCHAR2(4000);

cur_output IDCurTyp;

BEGIN

fo := UTL_FILE.FOPEN('C:\BACKUP','TEST.csv', 'W', 2000);

OPEN cur_output FOR

'SELECT ''"'' OID ''",'' DOC_PATH ''"'' FROM IREPS_PURGED_DOC_DETAILS';

LOOP

FETCH cur_output INTO varRow;

EXIT WHEN cur_output%NOTFOUND;

UTL_FILE.putf( fo, '%s\n', varRow );

END LOOP;

CLOSE cur_output;

UTL_FILE.FCLOSE( fo );

END;

****************************************************

Split-brain condition

2010-06-22T22:46:00.000-07:00

For a two-node cluster, split-brain occurs when nodes in a cluster cannot talk to each other (the internode links fail) and each node assumes it is the only surviving member of the cluster. If the nodes in the cluster have uncoordinated access to the shared storage area, they would end up overwriting each other's data, causing data corruption because each node assumes ownership of shared data. To prevent data corruption, one node must be asked to leave the cluster or should be forced out immediately.
In case of Split Brain in 2 Node cluster due to Private N/W failure between two nodes. The Node which has lowest Node number will continue to run while Node with high node number in Cluster will be evicted from cluster.

Move the OCR and Move the Voting disk

2010-05-05T00:26:00.000-07:00

Move the OCR
#
# The new raw storage devices for OCR should be owned by the
# root user, must be in the oinstall group, and must have
# permissions set to 640. Provide at least 280MB of disk
# space for each OCR file and verify the raw storage devices
# can be seen from all nodes in the cluster.
#
[root@racnode1 ~]# ls -l /dev/raw/raw[12]
crw-r----- 1 root oinstall 162, 1 Oct 8 21:55 /dev/raw/raw1
crw-r----- 1 root oinstall 162, 2 Oct 8 21:55 /dev/raw/raw2
[root@racnode2 ~]# ls -l /dev/raw/raw[12]
crw-r----- 1 root oinstall 162, 1 Oct 8 21:54 /dev/raw/raw1
crw-r----- 1 root oinstall 162, 2 Oct 8 21:54 /dev/raw/raw2
#
# Use the dd command to zero out the devices and make sure
# no data is written to the raw devices.
#
[root@racnode1 ~]# dd if=/dev/zero of=/dev/raw/raw1
[root@racnode1 ~]# dd if=/dev/zero of=/dev/raw/raw2
#
# Verify CRS is running on node 1.
#
[root@racnode1 ~]# crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
#
# Verify CRS is running on node 2.
#
[root@racnode2 ~]# crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
#
# Query the current location and number of OCR files on
# the OCFS2 file system.
#
[root@racnode1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 4676
Available space (kbytes) : 257444
ID : 1513888898
Device/File Name : /u02/oradata/racdb/OCRFile <-- OCR (primary)
Device/File integrity check succeeded
Device/File Name : /u02/oradata/racdb/OCRFile_mirror <-- OCR (mirror)
Device/File integrity check succeeded
Cluster registry integrity check succeeded
#
# Move OCR and OCR mirror to new storage location.
#
[root@racnode1 ~]# ocrconfig -replace ocr /dev/raw/raw1
[root@racnode1 ~]# ocrconfig -replace ocrmirror /dev/raw/raw2
#
# Verify OCR relocation from node 1.
#
[root@racnode1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 4676
Available space (kbytes) : 257444
ID : 1513888898
Device/File Name : /dev/raw/raw1
Device/File integrity check succeeded
Device/File Name : /dev/raw/raw2
Device/File integrity check succeeded
Cluster registry integrity check succeeded
#
# Verify OCR relocation from node 2.
#
[root@racnode2 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 4676
Available space (kbytes) : 257444
ID : 1513888898
Device/File Name : /dev/raw/raw1
Device/File integrity check succeeded
Device/File Name : /dev/raw/raw2
Device/File integrity check succeeded
Cluster registry integrity check succeeded
#
# Remove all deleted OCR files from the OCFS2 file system.
#
[root@racnode1 ~]# rm /u02/oradata/racdb/OCRFile
[root@racnode1 ~]# rm /u02/oradata/racdb/OCRFile_mirror
Move the Voting Disk
#
# The new raw storage devices for the voting disks should be
# owned by the oracle user, must be in the oinstall group,
# and and must have permissions set to 644. Provide at least
# 20MB of disk space for each voting disk and verify the raw
# storage devices can be seen from all nodes in the cluster.
#
[root@racnode1 ~]# ls -l /dev/raw/raw[345]
crw-r--r-- 1 oracle oinstall 162, 3 Oct 8 22:44 /dev/raw/raw3
crw-r--r-- 1 oracle oinstall 162, 4 Oct 8 22:45 /dev/raw/raw4
crw-r--r-- 1 oracle oinstall 162, 5 Oct 9 00:22 /dev/raw/raw5
[root@racnode2 ~]# ls -l /dev/raw/raw[345]
crw-r--r-- 1 oracle oinstall 162, 3 Oct 8 22:53 /dev/raw/raw3
crw-r--r-- 1 oracle oinstall 162, 4 Oct 8 22:54 /dev/raw/raw4
crw-r--r-- 1 oracle oinstall 162, 5 Oct 9 00:23 /dev/raw/raw5
#
# Use the dd command to zero out the devices and make sure
# no data is written to the raw devices.
#
[root@racnode1 ~]# dd if=/dev/zero of=/dev/raw/raw3
[root@racnode1 ~]# dd if=/dev/zero of=/dev/raw/raw4
[root@racnode1 ~]# dd if=/dev/zero of=/dev/raw/raw5
#
# Query the current location and number of voting disks on
# the OCFS2 file system. There needs to be at least two
# voting disks configured before attempting to perform the
# move.
#
[root@racnode1 ~]# crsctl query css votedisk
0. 0 /u02/oradata/racdb/CSSFile
1. 0 /u02/oradata/racdb/CSSFile_mirror1
2. 0 /u02/oradata/racdb/CSSFile_mirror2
located 3 votedisk(s).
#
# Stop all application processes.
#
[root@racnode1 ~]# srvctl stop database -d racdb
[root@racnode1 ~]# srvctl stop asm -n racnode1
[root@racnode1 ~]# srvctl stop asm -n racnode2
[root@racnode1 ~]# srvctl stop nodeapps -n racnode1
[root@racnode1 ~]# srvctl stop nodeapps -n racnode2
#
# Verify all application processes are OFFLINE.
#
[root@racnode1 ~]# crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.racdb.db application OFFLINE OFFLINE
ora....b1.inst application OFFLINE OFFLINE
ora....b2.inst application OFFLINE OFFLINE
ora....srvc.cs application OFFLINE OFFLINE
ora....db1.srv application OFFLINE OFFLINE
ora....db2.srv application OFFLINE OFFLINE
ora....SM1.asm application OFFLINE OFFLINE
ora....E1.lsnr application OFFLINE OFFLINE
ora....de1.gsd application OFFLINE OFFLINE
ora....de1.ons application OFFLINE OFFLINE
ora....de1.vip application OFFLINE OFFLINE
ora....SM2.asm application OFFLINE OFFLINE
ora....E2.lsnr application OFFLINE OFFLINE
ora....de2.gsd application OFFLINE OFFLINE
ora....de2.ons application OFFLINE OFFLINE
ora....de2.vip application OFFLINE OFFLINE
#
# Shut down CRS on node 1 and verify the CRS stack is not up.
#
[root@racnode1 ~]# crsctl stop crs
Stopping resources. This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
[root@racnode1 ~]# ps -ef grep d.bin grep -v grep
#
# Shut down CRS on node 2 and verify the CRS stack is not up.
#
[root@racnode2 ~]# crsctl stop crs
Stopping resources. This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
[root@racnode2 ~]# ps -ef grep d.bin grep -v grep
#
# Move all three voting disks to new storage location.
#
[root@racnode1 ~]# crsctl delete css votedisk /u02/oradata/racdb/CSSFile -force
successful deletion of votedisk /u02/oradata/racdb/CSSFile.
[root@racnode1 ~]# crsctl add css votedisk /dev/raw/raw3 -force
Now formatting voting disk: /dev/raw/raw3
successful addition of votedisk /dev/raw/raw3.
[root@racnode1 ~]# crsctl delete css votedisk /u02/oradata/racdb/CSSFile_mirror1 -force
successful deletion of votedisk /u02/oradata/racdb/CSSFile_mirror1.
[root@racnode1 ~]# crsctl add css votedisk /dev/raw/raw4 -force
Now formatting voting disk: /dev/raw/raw4
successful addition of votedisk /dev/raw/raw4.
[root@racnode1 ~]# crsctl delete css votedisk /u02/oradata/racdb/CSSFile_mirror2 -force
successful deletion of votedisk /u02/oradata/racdb/CSSFile_mirror2.
[root@racnode1 ~]# crsctl add css votedisk /dev/raw/raw5 -force
Now formatting voting disk: /dev/raw/raw5
successful addition of votedisk /dev/raw/raw5.
#
# Verify voting disk(s) relocation from node 1.
#
[root@racnode1 ~]# crsctl query css votedisk
0. 0 /dev/raw/raw3
1. 0 /dev/raw/raw4
2. 0 /dev/raw/raw5
located 3 votedisk(s).
#
# Verify voting disk(s) relocation from node 2.
#
[root@racnode2 ~]# crsctl query css votedisk
0. 0 /dev/raw/raw3
1. 0 /dev/raw/raw4
2. 0 /dev/raw/raw5
located 3 votedisk(s).
#
# Remove all deleted voting disk files from the OCFS2 file system.
#
[root@racnode1 ~]# rm /u02/oradata/racdb/CSSFile
[root@racnode1 ~]# rm /u02/oradata/racdb/CSSFile_mirror1
[root@racnode1 ~]# rm /u02/oradata/racdb/CSSFile_mirror2
#
# With all voting disks now located on raw storage devices,
# restart CRS on all Oracle RAC nodes.
#
[root@racnode1 ~]# crsctl start crs
[root@racnode2 ~]# crsctl start crs

Recover the Voting Disk

2010-05-05T00:22:00.000-07:00

The recommended way to recover from a lost or corrupt voting disk is to restore it from a previous good backup that was taken with the dd command.
There are actually very few steps required to restore the voting disks:
Shutdown CRS on all nodes in the cluster.
List the current location of the voting disks.
Restore each of the voting disks using the dd command from a previous good backup of the voting disks that was taken using the same dd command.
Re-start CRS on all nodes in the cluster.
For example:
[root@racnode1 ~]# crsctl stop crs
[root@racnode2 ~]# crsctl stop crs
[root@racnode1 ~]# crsctl query css votedisk
[root@racnode1 ~]# # Do this for all voting disks...
[root@racnode1 ~]# dd if= of= bs=4k
[root@racnode1 ~]# crsctl start crs
[root@racnode2 ~]# crsctl start crs

How to add two new voting disks to the current cluster

2010-05-05T00:02:00.000-07:00

The following example demonstrates how to add two new voting disks to the current cluster. The new voting disks will reside on the same OCFS2 file system in the same directory as the current voting disk. Please note that I am doing this for the sake brevity. Multiplexed voting disks should always be placed on a separate device than the current voting disk to guard against a single point of failure.
Stop all application processes, shut down CRS on all nodes, and Oracle10g R2 users should use the -force flag to the crsctl command when adding the new voting disk(s). For example:

# Query current voting disk configuration.
#
[root@racnode1 ~]# crsctl query css votedisk
0. 0 /u02/oradata/racdb/CSSFile
located 1 votedisk(s).
#
# Stop all application processes.
#
[root@racnode1 ~]# srvctl stop database -d racdb
[root@racnode1 ~]# srvctl stop asm -n racnode1
[root@racnode1 ~]# srvctl stop asm -n racnode2
[root@racnode1 ~]# srvctl stop nodeapps -n racnode1
[root@racnode1 ~]# srvctl stop nodeapps -n racnode2
#
# Verify all application processes are OFFLINE.
#
[root@racnode1 ~]# crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.racdb.db application OFFLINE OFFLINE
ora....b1.inst application OFFLINE OFFLINE
ora....b2.inst application OFFLINE OFFLINE
ora....srvc.cs application OFFLINE OFFLINE
ora....db1.srv application OFFLINE OFFLINE
ora....db2.srv application OFFLINE OFFLINE
ora....SM1.asm application OFFLINE OFFLINE
ora....E1.lsnr application OFFLINE OFFLINE
ora....de1.gsd application OFFLINE OFFLINE
ora....de1.ons application OFFLINE OFFLINE
ora....de1.vip application OFFLINE OFFLINE
ora....SM2.asm application OFFLINE OFFLINE
ora....E2.lsnr application OFFLINE OFFLINE
ora....de2.gsd application OFFLINE OFFLINE
ora....de2.ons application OFFLINE OFFLINE
ora....de2.vip application OFFLINE OFFLINE
#
# Shut down CRS on node 1 and verify the CRS stack is not up.
#
[root@racnode1 ~]# crsctl stop crs
Stopping resources. This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
[root@racnode1 ~]# ps -ef grep d.bin grep -v grep
#
# Shut down CRS on node 2 and verify the CRS stack is not up.
#
[root@racnode2 ~]# crsctl stop crs
Stopping resources. This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
[root@racnode2 ~]# ps -ef grep d.bin grep -v grep
#
# Take a backup of the current voting disk.
#
[oracle@racnode1 ~]$ dd if=/u02/oradata/racdb/CSSFile of=/home/oracle/VotingDiskBackup.dmp bs=4k
2500+0 records in
2500+0 records out
10240000 bytes (10 MB) copied, 0.272872 seconds, 37.5 MB/s
#
# Add two new voting disks.
#
[root@racnode1 ~]# crsctl add css votedisk /u02/oradata/racdb/CSSFile_mirror1 -force
Now formatting voting disk: /u02/oradata/racdb/CSSFile_mirror1
successful addition of votedisk /u02/oradata/racdb/CSSFile_mirror1.
[root@racnode1 ~]# crsctl add css votedisk /u02/oradata/racdb/CSSFile_mirror2 -force
Now formatting voting disk: /u02/oradata/racdb/CSSFile_mirror2
successful addition of votedisk /u02/oradata/racdb/CSSFile_mirror2.
#
# Set the appropriate permissions on the new voting disks.
#
[root@racnode1 ~]# chown oracle /u02/oradata/racdb/CSSFile_mirror1
[root@racnode1 ~]# chgrp oinstall /u02/oradata/racdb/CSSFile_mirror1
[root@racnode1 ~]# chmod 644 /u02/oradata/racdb/CSSFile_mirror1
[root@racnode1 ~]# chown oracle /u02/oradata/racdb/CSSFile_mirror2
[root@racnode1 ~]# chgrp oinstall /u02/oradata/racdb/CSSFile_mirror2
[root@racnode1 ~]# chmod 644 /u02/oradata/racdb/CSSFile_mirror2
If the new voting disks will be created on raw devices
#
# Clear out the contents from the new raw devices.
#
[root@racnode1 ~]# dd if=/dev/zero of=/dev/raw/raw3
[root@racnode1 ~]# dd if=/dev/zero of=/dev/raw/raw4
#
# Add two new voting disks.
#
[root@racnode1 ~]# crsctl add css votedisk /dev/raw/raw3 -force
Now formatting voting disk: /dev/raw/raw3
successful addition of votedisk /dev/raw/raw3.
[root@racnode1 ~]# crsctl add css votedisk /dev/raw/raw4 -force
Now formatting voting disk: /dev/raw/raw4
successful addition of votedisk /dev/raw/raw4.

----

# Verify new voting disk access from node 1.
#
[root@racnode1 ~]# crsctl query css votedisk
0. 0 /u02/oradata/racdb/CSSFile
1. 0 /u02/oradata/racdb/CSSFile_mirror1
2. 0 /u02/oradata/racdb/CSSFile_mirror2
located 3 votedisk(s).
#
# Verify new voting disk access from node 2.
#
[root@racnode2 ~]# crsctl query css votedisk
0. 0 /u02/oradata/racdb/CSSFile
1. 0 /u02/oradata/racdb/CSSFile_mirror1
2. 0 /u02/oradata/racdb/CSSFile_mirror2
located 3 votedisk(s).

After verifying the new voting disk(s) can be seen from all nodes in the cluster, restart CRS and the application processes.
Remove a Voting Disk

Oracle Clusterware must be shut down on all nodes in the cluster before adding or removing voting disks. Just as we were required to add the -force flag when adding a voting disk, the same holds true for Oracle10g R2 users attempting to remove a voting disk:
[root@racnode1 ~]# crsctl delete css votedisk /u02/oradata/racdb/CSSFile_mirror1
Cluster is not in a ready state for online disk removal
[root@racnode1 ~]# crsctl delete css votedisk /u02/oradata/racdb/CSSFile_mirror2
Cluster is not in a ready state for online disk removal

Recover OCR from Valid OCR Mirror

2010-05-04T22:51:00.000-07:00

The restore process will use the good OCR copy (whether its the primary OCR or the OCR mirror) to restore the missing/corrupt copy. Remember that if there is at least one copy of the OCR available, you can use that valid copy to restore the contents of the other copy of the OCR. The best part about this type of recovery is that it doesn't require any downtime! Oracle Clusterware and the applications can remain online during the recovery process.
For the purpose of this example, let's corrupt the primary OCR file:
[root@racnode1 ~]# dd if=/dev/zero of=/u02/oradata/racdb/OCRFile bs=4k count=100
100+0 records in
100+0 records out
409600 bytes (410 kB) copied, 0.00756842 seconds, 54.1 MB/s

Running ocrcheck picks up the now corrupted primary OCR file:
[root@racnode1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 4668
Available space (kbytes) : 257452
ID : 1331197
Device/File Name : /u02/oradata/racdb/OCRFile <-- Corrupt OCR
Device/File needs to be synchronized with the other device
Device/File Name : /u02/oradata/racdb/OCRFile_mirror
Device/File integrity check succeeded
Cluster registry integrity check succeeded
Note that after loosing the one OCR copy (in this case, the primary OCR file), Oracle Clusterware and the applications remain online:

While the applications and CRS remain online, perform the following steps to recover the primary OCR using the contents of the OCR mirror.
When using a clustered file system, remove the corrupt OCR file and re-initialize it:
[root@racnode1 ~]# rm /u02/oradata/racdb/OCRFile
[root@racnode1 ~]# cp /dev/null /u02/oradata/racdb/OCRFile
[root@racnode1 ~]# chown root /u02/oradata/racdb/OCRFile
[root@racnode1 ~]# chgrp oinstall /u02/oradata/racdb/OCRFile
[root@racnode1 ~]# chmod 640 /u02/oradata/racdb/OCRFile
NOTE: If the target OCR is located on a raw device, verify the permissions are applied correctly for an OCR file (owned by root:oinstall with 0640 permissions), that the device is being shared by all nodes in the cluster, and finally use the dd command from only one node in the cluster to zero out the device and make sure no data is written to the raw device.
[root@racnode1 ~]# ls -l /dev/raw/raw1
crw-r----- 1 root oinstall 162, 1 Oct 6 11:05 /dev/raw/raw1
[root@racnode2 ~]# ls -l /dev/raw/raw1
crw-r----- 1 root oinstall 162, 1 Oct 6 11:04 /dev/raw/raw1
[root@racnode1 ~]# dd if=/dev/zero of=/dev/raw/raw1

Restore the primary OCR using the contents of the OCR mirror. Note that this operation is the same process used when adding a new OCR location:
[root@racnode1 ~]# ocrconfig -replace ocr /u02/oradata/racdb/OCRFile
NOTE: If the target OCR is located on a raw device, substitute the path name above with that of the shared device name: (i.e. /dev/raw/raw1)
Verify the restore was successful by viewing the Clusterware alert log file.
[root@racnode1 ~]# tail $ORA_CRS_HOME/log/racnode1/alertracnode1.log
...
2009-10-06 17:46:51.118
[crsd(11054)]CRS-1007:The OCR/OCR mirror location was replaced by /u02/oradata/racdb/OCRFile.
Verify the OCR configuration by running the ocrcheck command:
[root@racnode1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 4668
Available space (kbytes) : 257452
ID : 1331197
Device/File Name : /u02/oradata/racdb/OCRFile
Device/File integrity check succeeded <-- Primary OCR Restored
Device/File Name : /u02/oradata/racdb/OCRFile_mirror
Device/File integrity check succeeded
Cluster registry integrity check succeeded

As the oracle user account with user equivalence enabled on all the nodes, run the cluvfy command to validate the OCR configuration:
[oracle@racnode1 ~]$ ssh racnode1 "hostname; date"
racnode1
Tue Oct 6 17:52:52 EDT 2009
[oracle@racnode1 ~]$ ssh racnode2 "hostname; date"
racnode2
Tue Oct 6 17:51:50 EDT 2009
[oracle@racnode1 ~]$ cluvfy comp ocr -n all
Verifying OCR integrity
Checking OCR integrity...
Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations.
Uniqueness check for OCR device passed.
Checking the version of OCR...
OCR of correct Version "2" exists.
Checking data integrity of OCR...
Data integrity check for OCR passed.
OCR integrity check passed.
Verification of OCR integrity was successful.
Recover OCR from Automatically Generated Physical Backup -
This section demonstrates how to recover the Oracle Cluster Registry from a lost or corrupt OCR file. This example assumes that both the primary OCR and the OCR mirror are lost from an accidental delete by a user and that the latest automatic OCR backup copy on the master node is accessible.
At this time, the second node in the cluster (racnode2) is the master node and currently available. We will be restoring the OCR using the latest OCR backup copy from racnode2 which is located at /u01/app/crs/cdata/crs/backup00.ocr.
Let's now corrupt the OCR by removing both the primary OCR and the OCR mirror:
[root@racnode1 ~]# rm /u02/oradata/racdb/OCRFile
[root@racnode1 ~]# rm /u02/oradata/racdb/OCRFile_mirror
Running ocrcheck fails to provide any useful information given that both OCR files are lost
[root@racnode1 ~]# ocrcheck
PROT-602: Failed to retrieve data from the cluster registry
Note that after loosing both OCR files, Oracle Clusterware and the applications remain online. Before restoring the OCR, the applications and CRS will need to be shutdown as described in the steps below.
Perform the following steps to recover the OCR from the latest automatically generated physical backup:
With CRS still online, identify the master node (which in this example is racnode2) and all OCR backups using the ocrconfig -showbackup command:
[root@racnode1 ~]# ocrconfig -showbackup
racnode2 2009/10/07 12:05:18 /u01/app/crs/cdata/crs
racnode2 2009/10/07 08:05:17 /u01/app/crs/cdata/crs
racnode2 2009/10/07 04:05:17 /u01/app/crs/cdata/crs
racnode2 2009/10/07 00:05:16 /u01/app/crs/cdata/crs
racnode1 2009/09/24 08:49:19 /u01/app/crs/cdata/crs
Note that ocrconfig -showbackup may result in a segmentation fault or simply not show any results if CRS is shutdown.
For documentation purposes, identify the number and location of all configured OCR files that will be recovered in this example.
[root@racnode2 ~]# cat /etc/oracle/ocr.loc
#Device/file /u02/oradata/racdb/OCRFile getting replaced by device /u02/oradata/racdb/OCRFile
ocrconfig_loc=/u02/oradata/racdb/OCRFile
ocrmirrorconfig_loc=/u02/oradata/racdb/OCRFile_mirror
Although all OCR files have been lost or corrupted, the Oracle Clusterware daemons as well as the clustered database remain running. In this scenario, Oracle Clusterware and all managed resources need to be shut down in order to recover the OCR. Attempting to stop CRS using crsctl stop crs will fail given it cannot write to the now lost/corrupt OCR file:
[root@racnode1 ~]# crsctl stop crs
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
With the environment in this unstable state, shutdown all database instances from all nodes in the cluster and then reboot each node:
[oracle@racnode1 ~]$ sqlplus / as sysdba
SQL> shutdown immediate
[root@racnode1 ~]# reboot
------------------------------------------------
[oracle@racnode2 ~]$ sqlplus / as sysdba
SQL> shutdown immediate
[root@racnode2 ~]# reboot
When the Oracle RAC nodes come back up, note that Oracle Clusterware will fail to start as a result of the lost/corrupt OCR file:
[root@racnode1 ~]# crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.
[root@racnode2 ~]# crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.
When using a clustered file system, re-initialize both the primary OCR and the OCR mirror target locations identified earlier in the /etc/oracle/ocr.loc file:
[root@racnode1 ~]# rm -f /u02/oradata/racdb/OCRFile
[root@racnode1 ~]# cp /dev/null /u02/oradata/racdb/OCRFile
[root@racnode1 ~]# chown root /u02/oradata/racdb/OCRFile
[root@racnode1 ~]# chgrp oinstall /u02/oradata/racdb/OCRFile
[root@racnode1 ~]# chmod 640 /u02/oradata/racdb/OCRFile
[root@racnode1 ~]# rm -f /u02/oradata/racdb/OCRFile_mirror
[root@racnode1 ~]# cp /dev/null /u02/oradata/racdb/OCRFile_mirror
[root@racnode1 ~]# chown root /u02/oradata/racdb/OCRFile_mirror
[root@racnode1 ~]# chgrp oinstall /u02/oradata/racdb/OCRFile_mirror
[root@racnode1 ~]# chmod 640 /u02/oradata/racdb/OCRFile_mirror
NOTE: If the target OCR is located on a raw device(s), verify the permissions are applied correctly for an OCR file (owned by root:oinstall with 0640 permissions), that the device is being shared by all nodes in the cluster, and finally use the dd command from only one node in the cluster to zero out the device(s) and make sure no data is written to the raw device(s).
[root@racnode1 ~]# ls -l /dev/raw/raw[12]
crw-r----- 1 root oinstall 162, 1 Oct 7 15:00 /dev/raw/raw1
crw-r----- 1 root oinstall 162, 2 Oct 7 15:00 /dev/raw/raw2
[root@racnode2 ~]# ls -l /dev/raw/raw[12]
crw-r----- 1 root oinstall 162, 1 Oct 7 14:59 /dev/raw/raw1
crw-r----- 1 root oinstall 162, 2 Oct 7 14:59 /dev/raw/raw2
[root@racnode1 ~]# dd if=/dev/zero of=/dev/raw/raw1 <-- OCR (primary)
[root@racnode1 ~]# dd if=/dev/zero of=/dev/raw/raw2 <-- OCR (mirror)
Before restoring the OCR, dump the contents of the physical backup you intend to recover from the master node (racnode2) to validate its availability as well as the accuracy of its contents:
[root@racnode2 ~]# ocrdump -backupfile /u01/app/crs/cdata/crs/backup00.ocr
[root@racnode2 ~]# less OCRDUMPFILE
With CRS down, perform the restore operation from the master node (racnode2) by applying the latest automatically generated physical backup:
[root@racnode2 ~]# ocrconfig -restore /u01/app/crs/cdata/crs/backup00.ocr
Restart Oracle Clusterware on all of the nodes in the cluster by rebooting each node or by running the crsctl start crs command:
[root@racnode1 ~]# crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly
[root@racnode2 ~]# crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly
Verify the OCR configuration by running the ocrcheck command:
[root@racnode1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262120
Used space (kbytes) : 4668
Available space (kbytes) : 257452
ID : 1331197
Device/File Name : /u02/oradata/racdb/OCRFile
Device/File integrity check succeeded <-- Primary OCR Restored
Device/File Name : /u02/oradata/racdb/OCRFile_mirror
Device/File integrity check succeeded <-- Mirror OCR Restored
Cluster registry integrity check succeeded
As the oracle user account with user equivalence enabled on all the nodes, run the cluvfy command to validate the OCR configuration:
[oracle@racnode1 ~]$ ssh racnode1 "hostname; date"
racnode1
Wed Oct 7 16:29:49 EDT 2009
[oracle@racnode1 ~]$ ssh racnode2 "hostname; date"
racnode2
Wed Oct 7 16:29:06 EDT 2009
[oracle@racnode1 ~]$ cluvfy comp ocr -n all
Verifying OCR integrity
Checking OCR integrity...
Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations.
Uniqueness check for OCR device passed.
Checking the version of OCR...
OCR of correct Version "2" exists.
Checking data integrity of OCR...
Data integrity check for OCR passed.
OCR integrity check passed.
Verification of OCR integrity was successful.
Finally, verify the applications are running:
[root@racnode1 ~]# crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.racdb.db application ONLINE ONLINE racnode1
ora....b1.inst application ONLINE ONLINE racnode1
ora....b2.inst application ONLINE ONLINE racnode2
ora....srvc.cs application ONLINE ONLINE racnode2
ora....db1.srv application ONLINE ONLINE racnode1
ora....db2.srv application ONLINE ONLINE racnode2
ora....SM1.asm application ONLINE ONLINE racnode1
ora....E1.lsnr application ONLINE ONLINE racnode1
ora....de1.gsd application ONLINE ONLINE racnode1
ora....de1.ons application ONLINE ONLINE racnode1
ora....de1.vip application ONLINE ONLINE racnode1
ora....SM2.asm application ONLINE ONLINE racnode2
ora....E2.lsnr application ONLINE ONLINE racnode2
ora....de2.gsd application ONLINE ONLINE racnode2
ora....de2.ons application ONLINE ONLINE racnode2
ora....de2.vip application ONLINE ONLINE racnode2

The Master Node determination in oracle RAC

2010-05-04T22:44:00.000-07:00

The CRSD process only creates automatic OCR physical backups on one node in the cluster, which is the OCR master node. It does not create automatic backup copies on the other nodes; only from the OCR master node. If the master node fails, the OCR backups will be created from the new master node. You can determine which node in the cluster is the master node by examining the $ORA_CRS_HOME/log//cssd/ocssd.log file on any node in the cluster. In this log file, check for reconfiguration information (reconfiguration successful) after which you will see which node is the master and how many nodes are active in the cluster:

Node 1 - (racnode1)
[ CSSD]CLSS-3000: reconfiguration successful, incarnation 1 with 2 nodes
[ CSSD]CLSS-3001: local node number 1, master node number 1
Node 2 - (racnode2)
[ CSSD]CLSS-3000: reconfiguration successful, incarnation 1 with 2 nodes
[ CSSD]CLSS-3001: local node number 2, master node number 1

Another quick approach is to use either of the following methods:

Node 1 - (racnode1)
# grep -i "master node" $ORA_CRS_HOME/log/racnode?/cssd/ocssd.log tail -1
[ CSSD]CLSS-3001: local node number 1, master node number 1
Node 2 - (racnode2)
# grep -i "master node" $ORA_CRS_HOME/log/racnode?/cssd/ocssd.log tail -1
[ CSSD]CLSS-3001: local node number 2, master node number 1
# If not found in the ocssd.log, then look through all
# of the ocssd archives:
Node 1 - (racnode1)
# for x in 'ls -tr $ORA_CRS_HOME/log/racnode?/cssd/ocssd.*'
do grep -i "master node" $x; done tail -1
[ CSSD]CLSS-3001: local node number 1, master node number 1
Node 2 - (racnode2)
# for x in 'ls -tr $ORA_CRS_HOME/log/racnode?/cssd/ocssd.*'
do grep -i "master node" $x; done tail -1
[ CSSD]CLSS-3001: local node number 2, master node number 1
# The master node information is confirmed by the
# ocrconfig -showbackup command:
# ocrconfig -showbackup
racnode1 2009/09/29 13:05:22 /u01/app/crs/cdata/crs
racnode1 2009/09/29 09:05:22 /u01/app/crs/cdata/crs
racnode1 2009/09/29 05:05:22 /u01/app/crs/cdata/crs
racnode1 2009/09/28 05:05:21 /u01/app/crs/cdata/crs
racnode1 2009/09/22 05:05:13 /u01/app/crs/cdata/crs

You can use any backup software to copy the automatically generated physical backup files to a stable backup location:

[root@racnode1 ~]# cp -p -v -f -R /u01/app/crs/cdata /u02/crs_backup/ocrbackup/RACNODE1
'/u01/app/crs/cdata/crs/day_.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE1/cdata/crs/day_.ocr'
'/u01/app/crs/cdata/crs/backup02.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE1/cdata/crs/backup02.ocr'
'/u01/app/crs/cdata/crs/backup01.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE1/cdata/crs/backup01.ocr'
'/u01/app/crs/cdata/crs/week_.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE1/cdata/crs/week_.ocr'
'/u01/app/crs/cdata/crs/day.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE1/cdata/crs/day.ocr'
'/u01/app/crs/cdata/crs/backup00.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE1/cdata/crs/backup00.ocr'
'/u01/app/crs/cdata/crs/week.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE1/cdata/crs/week.ocr'
[root@racnode2 ~]# cp -p -v -f -R /u01/app/crs/cdata /u02/crs_backup/ocrbackup/RACNODE2
'/u01/app/crs/cdata/crs/day_.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE2/cdata/crs/day_.ocr'
'/u01/app/crs/cdata/crs/backup02.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE2/cdata/crs/backup02.ocr'
'/u01/app/crs/cdata/crs/backup01.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE2/cdata/crs/backup01.ocr'
'/u01/app/crs/cdata/crs/week_.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE2/cdata/crs/week_.ocr'
'/u01/app/crs/cdata/crs/day.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE2/cdata/crs/day.ocr'
'/u01/app/crs/cdata/crs/backup00.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE2/cdata/crs/backup00.ocr'
'/u01/app/crs/cdata/crs/week.ocr' -> '/u02/crs_backup/ocrbackup/RACNODE2/cdata/crs/week.ocr'

Manual OCR Exports :

Performing a manual export of the OCR should be done before and after making significant configuration changes to the cluster, such as adding or deleting nodes from your environment, modifying Oracle Clusterware resources, or creating a database. This type of backup is often referred to as a logical backup.

ocrconfig -export
For example:
[root@racnode1 ~]# ocrconfig -export /u02/crs_backup/ocrbackup/RACNODE1/exports/OCRFileBackup.dmp
To restore the OCR from an export/logical backup, use the ocrconfig –import command-
To restore the OCR from an export/logical backup, use the ocrconfig –import command. Note that the CRS stack needs to be shutdown on all nodes in the cluster prior to running the restore operation. In addition, the total space required for the restored OCR location (typically 280MB) has to be pre-allocated. This is especially important when the OCR is located on a clustered file system like OCFS2.
ocrconfig –import

You cannot restore the OCR from a logical backup using the -restore option. The only method to restore the OCR from a logical export is to use the -import option.

clustering_factor

2010-05-04T02:55:00.000-07:00

The clustering_factor measures how synchronized an index is with the data in a table. A table with a high clustering factor is out-of-sequence with the rows and large index range scans will consume lots of I/O. Conversely, an index with a low clustering_factor is closely aligned with the table and related rows reside together of each data block, making indexes very desirable for optimal access.

ROHIT Blog

Upgrading the Protection Mode and Downgrading the Protection Mode

Different kind of protection modes Oracle offers in a Data Guard Environment

How to find log sequences used during the recovery

Oracle dba Interview Questions

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

Differential backup and Cumulative backup

Incremental backup and restore in oracle 10g

Oracle Clusterware Components

Latches

Unix for the DBA

Oracle Clusterware processes for 10g on Unix and Linux

ASM REBALANCING

Testing the Fail over in Real Application Clusters Oracle10gR2 using service

RAC Switchover / Switchback

OCR is automatically backed

Checkpoint Tuning

Duplicate Oracle Database with RMAN

Duplicate Oracle Database with RMAN

Memory Architecture of an Oracle instance

Moving, copying or cloning a database from one server to another with different directory structures can be easily accomplished with RMAN

Brief explanation of how assorted Oracle files can be renamed or moved to a new location

ORA-01555: snapshot too old during export EXP-00000: Export terminated unsuccessfully

Cost Based Optimizer (CBO) and Database Statistics

Using EXPLAIN PLAN and TKPROF To Tune Your Applications

Use EXPLAIN PLAN and TKPROF To Tune Your Applications

HOW TO REMOVE CRS AUTO START AND RESTART FOR A RAC INSTANCE

RMAN Terminology

Parsing SQL Statements in Oracle

Master node at Oracle Clusterware level

HOW TO APPLY ORACLE PATCH (OPATCH)

Log switching history

Track redo generation by day

Recover from loss of single current online redo log file

Backup script for Linux using tar and find

Reading Text Files using Oracle PL/SQL and UTL_FILE

Split-brain condition

Move the OCR and Move the Voting disk

Recover the Voting Disk

How to add two new voting disks to the current cluster

Recover OCR from Valid OCR Mirror

The Master Node determination in oracle RAC

clustering_factor

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