In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Thursday, December 31, 2009

Recover Catalog RMAN


This chapter show how to create the Rman catalog, how to register a database with it and how to review some of the information contained in the catalog.

The catalog database is usually a small database it contains and maintains the metadata of all rman backups performed using the catalog.

1.Creating and Register a database with Recovery Catalog

step1: create a tablespace for storing recovery catalog information in recovery catalog database
here my recovery catalog database is demo1

[oracle@rac2 bin]$ . oraenv
ORACLE_SID = [oracle] ? demo1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
[oracle@rac2 bin]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Dec 31 10:28:22 2009

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


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

SQL> startup
ORACLE instance started.

Total System Global Area 481267712 bytes
Fixed Size 1300716 bytes
Variable Size 226494228 bytes
Database Buffers 247463936 bytes
Redo Buffers 6008832 bytes
Database mounted.
Database opened.

SQL> CREATE TABLESPACE RMAN DATAFILE '/u01/app/oracle/oradata/demo1/rman01.dbf' size 1000m;

step 2: create a user for recovery catalog and assign a tablespace and resources to that user

SQL> create user sai identified by sai default tablespace rman quota unlimited on rman;

SQL> grant connect,resource, recovery_catalog_owner to sai;

step 3: Connect to recovery catalog and register the database with recovery catalog:
[oracle@rac2 bin]$ . oraenv
ORACLE_SID = [oracle] ? demo1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle


[oracle@rac2 bin]$ rman target /

RMAN> connect catalog sai/sai@demo1;

RMAN> create catalog;

RMAN> register database;

RMAN> report schema;

2.How to register a new database with RMAN recovery catalog

Replace username/password with the actual username and password for recovery catalog; and
DEMO1 with the name of the recovery catalog database and new database name ANTO

1. Change SID to the database you want to register

. oraenv
ORACLE_SID

2. Connect to RMAN catalog database

rman target / catalog username/password@DEMO1

3. Register database

RMAN> register database;


example:

[oracle@rac2 bin]$ . oraenv
ORACLE_SID = [anto] ? anto
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
[oracle@rac2 bin]$ rman target / catalog sai/sai@demo1;

Recovery Manager: Release 11.1.0.6.0 - Production on Thu Dec 31 10:32:15 2009

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

connected to target database: ANTO (DBID=2484479252)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

verification:

connect to the recovery catalog database demo1 and connect as recovery catalog user sai;
SQL> conn sai/sai;
Connected.

SQL> select * from db;

    DB_KEY      DB_ID CURR_DBINC_KEY

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

         1 3710360247              2

       141 2484479252            142


3. Unregister the database from recovery catalog:

Login as rman catalog owner in sql*plus prompt
SQL> select * from rc_database where dbid = DBID;



SQL> exec dbms_rcvcat.unregisterdatabase(DBKEY, DBID);

example:

SQL> select * from rc_database where dbid = DBID;


DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1          2 3710360247 DEMO1               594567 29-DEC-09
141        142 2484479252 ANTO                522753 30-DEC-09

SQL>exec dbms_rcvcat.unregisterdatabase(141, 2484479252);

SQL> select * from rc_database where dbid = DBID;

DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1          2 3710360247 DEMO1               594567 29-DEC-09

SUCCESSFULLY, REMOVED THE DATABASE ANTO FROM THE RECOVER CATALOG

regards,
rajeshkumar g, database administrator

for more information about recovery catalog and rman commands:
http://www.tiplib.com/kb/25/1/rman

Monday, December 28, 2009

Recovering a Standby database from a missing archivelog

Hi friends,
today i came across one issue recovering a standby database from a missing archivelog files.

on primary database

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/archive
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18


on standby database
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/archive
Oldest online log sequence 13
Next log sequence to archive 0
Current log sequence 18


i tried to solve the problem using shutdownabort.com document
Register a missing log file
alter database register physical logfile '';
If FAL doesn't work and it says the log is already registered
alter database register or replace physical logfile '';


If that doesn't work, try this...

shutdown immediate
startup nomount
alter database mount standby database;
alter database recover automatic standby database;
wait for the recovery to finish - then cancel
shutdown immediate
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;
Check which logs are missing
Run this on the standby...

select local.thread#
, local.sequence# from
(select thread#
, sequence#
from v$archived_log
where dest_id=1) local
where local.sequence# not in
(select sequence#
from v$archived_log
where dest_id=2 and
thread# = local.thread#)
/
THREAD# SEQUENCE#
---------- ----------
1 9
1 10
1 11
1 12
1 13
1 14
1 15


still i the archive logs are not applied to the standby database.

finally i tried recovering a standby database using rman , el-caro blog document
i got a solution, now my primary and standby database has equal archives.

A Physical Standby database relies on continuous application of
archivelogs from a Primary Database to be in synch with it. In Oracle
Database versions prior to 10g in the event of an archivelog gone
missing or corrupt you had to rebuild the standby database from scratch.

In
10g you can use an incremental backup and recover the standby using the
same to compensate for the missing archivelogs as shown below

In
the case below archivelogs with sequence numbers 137 and 138 which are
required on the standby are deleted to simulate this problem.

Step 1: On the standby database check the current scn.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
548283

Step 2: On the primary database create the needed incremental backup from the above SCN

login to primary database rman target /

RMAN> backup device type disk incremental from scn 548283 database format '/u01/backup/bkup_%U';

Starting backup at 28-DEC-09

using channel ORA_DISK_1
backup will be obsolete on date 04-JAN-10
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/demo1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/demo1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/demo1/rman01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/demo1/rman02.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/demo1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/demo1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 28-DEC-09
channel ORA_DISK_1: finished piece 1 at 28-DEC-09
piece handle=/u01/backup/bkup_07l21ukv_1_1 tag=TAG20091228T143302 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:24:19

using channel ORA_DISK_1
backup will be obsolete on date 04-JAN-10
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 28-DEC-09
channel ORA_DISK_1: finished piece 1 at 28-DEC-09
piece handle=/u01/backup/bkup_08l2202v_1_1 tag=TAG20091228T143302 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 28-DEC-09

RMAN>


Step 3: Cancel managed recovery at the standby database

SQL>recover managed standby database cancel;
Media recovery complete.

Move the backup files to a new folder called new_incr so that they are the only files in that folder.

Step 4: Catalog the Incremental Backup Files at the Standby Database

[oracle@rac1 bin]$ . oraenv
ORACLE_SID = [RAC1] ? stby
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
[oracle@rac1 bin]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Mon Dec 28 15:01:33 2009

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

connected to target database: DEMO1 (DBID=3710229940, not open)


RMAN> catalog start with '/u01/backup/new_incr';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/backup/new_incr

List of Files Unknown to the Database
=====================================
File Name: /u01/backup/new_incr/bkup_08l2202v_1_1
File Name: /u01/backup/new_incr/bkup_07l21ukv_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/backup/new_incr/bkup_08l2202v_1_1
File Name: /u01/backup/new_incr/bkup_07l21ukv_1_1


Step 5: Apply the Incremental Backup to the Standby Database

RMAN> recover database noredo;

Starting recover at 28-DEC-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/stby/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/stby/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/stby/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/stby/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/stby/rman01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/stby/rman02.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/new_incr/bkup_07l21ukv_1_1
channel ORA_DISK_1: piece handle=/u01/backup/new_incr/bkup_07l21ukv_1_1 tag=TAG20091228T143302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished recover at 28-DEC-09

RMAN>



Step 6: Put the standby database back to managed recovery mode.

SQL> recover managed standby database nodelay disconnect;
Media recovery complete.

From the alert.log you will notice that the standby database is still looking for the old log files

*************************************************
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 137-137
DBID 768471617 branch 600609988
**************************************************

This is because the controlfile has not been updated.
Hence the standby controlfile has to be recreated

On the primary DATABASE

SQL> alter database create standby controlfile as
2 '/u01/control01.ctl';

Copy the standby control file to the standby site and restart the standby database in managed recovery mode...

NOW CHECK THE ARCHIVE LOG LIST ON BOTH PRIMARY AND STANDBY DATABASE,
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/archive
Oldest online log sequence 20
Next log sequence to archive 22
Current log sequence 22


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/archive
Oldest online log sequence 20
Next log sequence to archive 0
Current log sequence 22
SQL>

regards,
rajeshkumar g

Saturday, December 26, 2009

CRS-0215: Could not start resource 'ora.rac2.LISTENER_RAC2.lsnr'.

problem:
[root@rac1 bin]# ./srvctl start listener -n rac2
CRS-0215: Could not start resource 'ora.rac2.LISTENER_RAC2.lsnr'.

solution:
check /etc/hosts on both nodes
check /etc/sysconfig/network on both nodes

correct it if necessary if u found any mismatch between both nodes
i didnt mention the default gateway ip address for rac2 machine
i corrected the gateway address for node 2 machine
[oracle@rac2 ~]$ su -
Password:
[root@rac2 ~]# vi /etc/sysconfig/network
[root@rac2 ~]# service network restart
Shutting down interface eth0: [ OK ]
Shutting down interface eth1: [ OK ]
Shutting down loopback interface: [ OK ]
Bringing up loopback interface: [ OK ]
Bringing up interface eth0: [ OK ]
Bringing up interface eth1: [ OK ]
[root@rac2 ~]# cd /u01/crs/oracle/product/11.1.0/crs/bin/
[root@rac2 bin]# ./crsctl start crs
Attempting to start Oracle Clusterware stack
The CRS stack will be started shortly
[root@rac2 bin]# ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr 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 OFFLINE
ora....C2.lsnr application ONLINE OFFLINE
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac1
[root@rac2 bin]# ./srvctl start nodeapps -n rac2
[root@rac2 bin]# ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr 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....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
[root@rac2 bin]#

problem solved,

if got error,
pls follow the steps mentioned in metalink

How to Configure Virtual IPs for 10g RAC [ID 264847.1]
NOTE:264847.1

regards,
rajeshkumar g

How to ADD/REMOVE/REPLACE/MOVE Oracle Cluster Registry (OCR) and Voting Disk

Purpose
To provide step to add, remove, replace or move an OCR or voting disk in Oracle
Real Application Clusters (RAC) 10g Realese 2 (10.2.0.1 and later)
environments.
SCOPE & APPLICATION
This article is intended for DBAs and Support Engineers who need to modify, or
move OCR and voting disk locations. Customers, who have an existing clustered
environment deployed on a storage array, might want to migrate to a new storage
array, with minimal downtime. Typically, one would simply cp or dd the files
once the new storage has been presented to the hosts. In this case, it is a
little more difficult because,
1. The clusterware has the OCR and voting disks open and is actively using
them. (Both the primary and the mirrors)
2. There is a “cluster api” provided for this function (ocrconfig, and
crsctl), which is a better interface than typical cp and/or dd commands.
It is highly recommended to take a backup of the voting disk, and OCR device
before making any changes.
Examples are included at the end of OCR and voting disk command.
ADD/REMOVE/REPLACE OCR Device
Note: You must be logged in as the root user, because root own the OCR files.
Make sure there is a recent copy of the OCR file before making any changes:
ocrconfig –showbackup
If there is not a recent backup copy of the OCR file, an export can be taken
for the current OCR file. Use the following command to generate a an export of
the online OCR file:
ocrconfig –export -s online
If you need to recover using this file, the following command can be used:
ocrconfig import
1. To add an OCR device:
To add an OCR device, provide the full path including file name.
ocrconfig -replace ocr
To add an OCR mirror device, provide the full path including file name.
ocrconfig -replace ocrmirror
2. To remove an OCR device:
To remove an OCR device:
ocrconfig -replace ocr
To remove an OCR mirror device
ocrconfig -replace ocrmirror
3. To replace or move the location of an OCR device:
To replace the OCR device with , provide the full path including file
name.
ocrconfig -replace ocr
To replace the OCR mirror device with , provide the full path
including file name.
ocrconfig -replace ocrmirror
Example moving OCR file from OCFS to raw devices
The OCR disk must be owned by root, must be in the oinstall group, and must have permissions set
to 640. Provide at least 100 MB disk space for the OCR.
In this example the OCR file are located in the ocfs2 file system:
/ocfs2/ocr1
/ocfs2/ocr2
Create raw device files of at least 100 MB. In this example the new OCR file
will be on the following devices:
/dev/raw/raw1
/dev/raw/raw2
Once the raw devices are created, use the dd command to zero out the device and
make sure no data is written to the raw devices:
dd if=/dev/zero of=/dev/raw/raw1
dd if=/dev/zero of=/dev/raw/raw2
Note: Use UNIX man pages for additional information on the dd command.
Now you are ready to move/replace the OCR file to the new storage location.
Move/Replace the OCR device
ocrconfig -replace ocr /dev/raw/raw1
Add /dev/raw/raw2 as OCR mirror device
ocrconfig -replace ocr /dev/raw/raw2
Example of adding an OCR device file
If you have upgraded your environment from a previous version, where you only
had 1 OCR device file, you can use the following step to add an additional OCR
file.
In this example a second OCR device file is added:
Add /dev/raw/raw2 as OCR mirror device
ocrconfig -replace ocr /dev/raw/raw2
ADD/DELETE/MOVE Voting Disk
Note: Shutdown the Oracle Clusterware (crsctl stop crs as root) on all nodes
before making any modification to the voting disk. Determine the current
voting disk location using:
crsctl query css votedisk
Take a backup of all voting disk:
dd if=voting_disk_name of=backup_file_name
Note: Use UNIX man pages for additional information on the dd command.
The following can be used to restore the voting disk from the backup file
created.
dd if=backup_file_name of=voting_disk_name
1. To add a Voting Disk, provide the full path including file name.:
crsctl add votedisk css -force
2. To delete a Voting Disk, provide the full path including file name.:
crsctl delete votedisk css -force
3. To move a Voting Disk, provide the full path including file name.:
crsctl delete votedisk css –force
crsctl add votedisk css –force
After modifying the voting disk, start the Oracle Clusterware stack on all
nodes
crsctl start crs
Verify the voting disk location using
crsctl query css votedisk
Example moving Voting Disk from OCFS to raw devices
The voting disk is a partition that Oracle Clusterware uses to verify cluster
node membership and status.
The voting disk must be owned by the oracle user, must be in the dba
group, and must have permissions set to 644. Provide at least 20 MB disk
space for the voting disk.
In this example the Voting Disks are located in the ocfs2 file system:
/ocfs2/voting1
/ocfs2/voting2
/ocfs2/voting3
Create raw device files of at least 20 MB. In this example the new voting
disks will be on the following devices:
/dev/raw/raw3
/dev/raw/raw4
/dev/raw/raw5
Once the raw devices are created, use the dd command to zero out the device and
make sure no data is written to the raw devices:
dd if=/dev/zero of=/dev/raw/raw3
dd if=/dev/zero of=/dev/raw/raw4
dd if=/dev/zero of=/dev/raw/raw5
Note: Use UNIX man pages for additional information on the dd command.
Now you are ready to move/replace the voting disks to the new storage location.
To move a Voting Disk to new storage location:
crsctl delete votedisk css /ocfs2/voting1 –force
crsctl add votedisk css /dev/raw/raw3 –force
crsctl delete votedisk css /ocfs2/voting2 –force
crsctl add votedisk css /dev/raw/raw4 –force
crsctl delete votedisk css /ocfs2/voting3 –force
crsctl add votedisk css /dev/raw/raw5 –force
Example of adding Voting Disks
If you have upgraded your environment from a previous version, where you only
had 1 voting disk, you can use the following steps to add additional voting
disk.
In this example 2 additional Voting Disks are added:
crsctl add votedisk css /dev/raw/raw4 –force
crsctl add votedisk css /dev/raw/raw5 –force
After modifying the voting disk, start the Oracle Clusterware stack on all
nodes
crsctl start crs
Verify the voting disk location using
crsctl query css votedisk

Administering OCR in RAC

What is a voting disk?
A voting disk is a file that manages information about node membership.
What are the administrative tasks involved with voting disk?
Following administrative tasks are performed with the voting disk :
1) Backing up voting disks
2) Recovering Voting disks
3) Adding voting disks
4) Deleting voting disks
5) Moving voting disks
How do we backup voting disks?
1) Oracle recommends that you back up your voting disk after the initial cluster creation and after we complete any node addition or deletion procedures.
2) First, as root user, stop Oracle Clusterware (with the crsctl stop crs command) on all nodes. Then, determine the current voting disk by issuing the following command:
crsctl query css votedisk
3) Then, issue the dd or ocopy command to back up a voting disk, as appropriate.
Give the syntax of backing up voting disks:-
On Linux or UNIX systems:
dd if=voting_disk_name of=backup_file_name
where,
voting_disk_name is the name of the active voting disk
backup_file_name is the name of the file to which we want to back up the voting disk contents
On Windows systems, use the ocopy command:
ocopy voting_disk_name backup_file_name
What is the Oracle Recommendation for backing up voting disk?
Oracle recommends us to use the dd command to backup the voting disk with aminimum block size of 4KB.
How do you restore a voting disk?
To restore the backup of your voting disk, issue the dd or ocopy command for Linux and UNIX systems or ocopy for Windows systems respectively.
On Linux or UNIX systems:
dd if=backup_file_name of=voting_disk_name
On Windows systems, use the ocopy command:
ocopy backup_file_name voting_disk_name
where,
backup_file_name is the name of the voting disk backup file
voting_disk_name is the name of the active voting disk
How can we add and remove multiple voting disks?
If we have multiple voting disks, then we can remove the voting disks and add them back into our environment using the following commands, where path is the complete path of the location where the voting disk resides:
crsctl delete css votedisk path
crsctl add css votedisk path
How do we stop Oracle Clusterware?When do we stop it?
Before making any modification to the voting disk, as root user, stop Oracle Clusterware using the crsctl stop crs command on all nodes.
How do we add voting disk?
To add a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to add:
crsctl add css votedisk path -force
How do we move voting disks?
To move a voting disk, issue the following commands as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to move:
crsctl delete css votedisk path -force
crsctl add css votedisk path -force
How do we remove voting disks?
To remove a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to remove:
crsctl delete css votedisk path -force
What should we do after modifying voting disks?
After modifying the voting disk, restart Oracle Clusterware using the crsctl start crs command on all nodes, and verify the voting disk location using the following command:
crsctl query css votedisk
When can we use -force option?
If our cluster is down, then we can include the -force option to modify the voting disk configuration, without interacting with active Oracle Clusterware daemons. However, using the -force option while any cluster node is active may corrupt our configuration.

OCRCHECK in CLUSTERWARE, RAC

What is an OCRCHECK utility?
An ocrcheck utility is a diagnostic tool used for diagnosing OC(Oracle Cluster Registry) Problems.This is used to verify the Oracle Cluster Registry(OCR) integrity.
What does an ocrcheck display?
The OCRCHECK utility displays the version of the OCR’s block format, total space available and used space, OCRID, and the OCR locations that we have configured.
How does ocrcheck perform integrity check?
OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that we have configured. It also returns an individual status for each file as well as a result for the overall OCR integrity check.
Give a sample output of ocrcheck utility:-
Sample of the OCRCHECK utility output:
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 16256
Available space (kbytes) : 245888
ID : 1918913332
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

Where does an ocrcheck utility create a log file?
OCRCHECK creates a log file in the directory CRS_home/log/hostname/client.
How can we change the amount of logging?
To change amount of logging, edit the file CRS_home/srvm/admin/ocrlog.ini.


Administering Voting Disks in Oracle Real Application Clusters

The Oracle Clusterware is comprised primarily of two components: the voting disk and the OCR (Oracle Cluster Registry). The voting disk is nothing but a file that contains and manages information of all the node memberships and the OCR is a file that manages the cluster and RAC configuration. Let's take a quick look at administering the voting disks

Administering voting disks

Backup and Recovery:

First, let's look at backing up the voting disks by running the following command:

dd if=voting_disk_name of=backup_file_name
example:
dd if=/dev/sdc1 of=/tmp/votingdisk_bkp
2088387+0 records in
2088387+0 records out
1069254144 bytes (1.1 GB) copied, 889.716 seconds, 1.2 MB/s

This operation needs to be performed on all voting disks. Here, clearly you see that the if (input file) is the source file (replace the voting_disk_name with your voting disk) and the of (output file) is the destination backup file containing all information of the voting disk contents. Type dd –help for more information. Running the command with the names of the files reversed will help you recover your voting disk file(s).

dd if=backup_file_name of=voting_disk_name

note:
before restore, stop the crs with the following command:
[root@rac1 bin]# /etc/init.d/init.crs stop

You can use the ocopy command in Windows environments or use the crsctl commands to copy and administer the files. Also, note that if you have multiple voting disks, which are not abnormal to have, you can use the crsctl command to add and delete the voting disks. For instance:

crsctl delete css votedisk path

Here you delete the disk and the path, which is the complete path of the location of the file, and below you add your new or backup files by doing the following:

crsctl add css votedisk path

This way you can either statically or dynamically add or remove your voting disks in your RAC.

You must, however, note that if your cluster is down, then you can use the -force option

crsctl add css votedisk path -force

to modify the voting disk configuration. This way you don’t end up interfering with other Clusterware daemons. Using it in your active configuration may corrupt your configuration.

regards,

rajeshkumar g


Thursday, December 24, 2009

changing database dbid

SQL> startup mount
ORACLE instance started.

Total System Global Area 481267712 bytes
Fixed Size 1300716 bytes
Variable Size 226494228 bytes
Database Buffers 247463936 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [demo2] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
[oracle@rac1 ~]$ nid target = /

DBNEWID: Release 11.1.0.6.0 - Production on Thu Dec 24 20:05:44 2009

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

Connected to database DEMO2 (DBID=3682169720)

Connected to server version 11.1.0

Control Files in database:
/u01/app/oracle/oradata/demo2/control01.ctl
/u01/app/oracle/oradata/demo2/control02.ctl
/u01/app/oracle/oradata/demo2/control03.ctl

Change database ID of database DEMO2? (Y/[N]) => y

Proceeding with operation
Changing database ID from 3682169720 to 3682222232
Control File /u01/app/oracle/oradata/demo2/control01.ctl - modified
Control File /u01/app/oracle/oradata/demo2/control02.ctl - modified
Control File /u01/app/oracle/oradata/demo2/control03.ctl - modified
Datafile /u01/app/oracle/oradata/demo2/system01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/demo2/sysaux01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/demo2/undotbs01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/demo2/users01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/demo2/temp01.dbf - dbid changed
Control File /u01/app/oracle/oradata/demo2/control01.ctl - dbid changed
Control File /u01/app/oracle/oradata/demo2/control02.ctl - dbid changed
Control File /u01/app/oracle/oradata/demo2/control03.ctl - dbid changed
Instance shut down

Database ID for database DEMO2 changed to 3682222232.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

[oracle@rac1 ~]$
SQL> alter database open resetlogs;

Database altered.

SQL> select dbid from v$database;

DBID
----------
3682222232

regards,
rajeshkumar g

Friday, December 18, 2009

INTERNAL OPERATION OF HOT BACKUP

What Happens When A Tablespace/Database Is Kept In Begin Backup Mode

This document explains in detail about what happens when a tablespace/datafile is kept in hot backup/begin backup mode.

To perform online/hot backup we have to put the tablespace in begin backup mode followed by copying the datafiles and then putting the tablespace to end backup.

In 8i, 9i we have to put each tablespace individually in begin/end backup mode to perform the online backup. From 10g onwards the entire database can be put in begin/end backup mode.

Make sure that the database is in archivelog mode

Example :

Performing a single tablespace backup

+ sql>alter tablespace system begin backup;

+ Copy the corresponding datafiles using appropriate O/S commands.

+ sql>alter tablespace system end backup;


Performing a full database backup (starting from 10g)

+ sql> alter database begin backup;

+ Copy all the datafiles using appropriate O/S commands.

+ sql> alter database end backup;


One danger in making online backups is the possibility of inconsistent data within a block. For example, assume that you are backing up block 100 in datafile users.dbf. Also, assume that the copy utility reads the entire block while DBWR is in the middle of updating the block. In this case, the copy utility may read the old data in the top half of the block and the new data in the bottom top half of the block. The result is called a fractured block, meaning that the data contained in this block is not consistent. at a given SCN.


Therefore oracle internally manages the consistency as below :

1. The first time a block is changed in a datafile that is in hot backup mode, the entire block is written to the redo log files, not just the changed bytes. Normally only the changed bytes (a redo vector) is written. In hot backup mode, the entire block is logged the first time. This is because you can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously.

Lets say they are and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time). The backup program goes to read an 8k Oracle block. The OS gives it 4k. Meanwhile -- DBWR has asked to rewrite this block. the OS schedules the DBWR write to occur right now. The entire 8k block is rewritten. The backup program starts running again (multi-tasking OS here) and reads the last 4k of the block. The backup program has now gotten an fractured block -- the head and tail are from two points in time.

We cannot deal with that during recovery. Hence, we log the entire block image so that during recovery, this block is totally rewritten from redo and is consistent with itself atleast. We can recover it from there.


2. The datafile headers which contain the SCN of the last completed checkpoint are not updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file.

To limit the effect of this additional logging, you should ensure you only place one tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon as you have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible.

Try to take the hot/online backups when there is less / no load on the database, so that less redo will be generated.

v$ASM view, Automatic Storage Management views

The following v$ASM views describe the structure and components of ASM:

v$ASM_ALIAS
This view displays all system and user-defined aliases. There is one row for every alias present in every diskgroup mounted by the ASM instance. The RDBMS instance displays no rows in this view.

V$ASM_ATTRIBUTE
This Oracle Database 11g view displays one row for each ASM attribute defined. Theseattributes are listed when they are defined in CREATE DISKGROUP or ALTER DISKGROUP statements. DISK_REPAIR_TIMER is an example of an attribute.

V$ASM_CLIENT
This view displays one row for each RDBMS instance that has an opened ASM diskgroup.

V$ASM_DISK
This view contains specifics about all disks discovered by the ASM isntance, including mount status, disk state, and size. There is one row for every disk discovered by the ASM instance.

V$ASM_DISK_IOSTAT
This displays information about disk I/O statistics for each ASM Client. If this view is queried from the database instance, only the rows for that instance are shown.

V$ASM_DISK_STAT
This view contains similar content as the v$ASM_DISK, except v$ASM_DISK_STAT reads disk information from cache and thus performs no disk discovery. Thsi view is primarily used form quick acces to the disk information without the overhead of disk discovery.

V$ASM_DISKGROUP
This view displays one row for every ASM diskgroup discovered by the ASM instance on the node.

V$ASM_DISKGROUP_STAT
This view contains all the similar view contents as the v$ASM_DISKGROUP, except that v$ASM_DISK_STAT reads disk information from the cache and thus performs no disk discovery. This view is primarily used for quick access to the diskgroup information without the overhead of disk discovery.

V$ASM_FILE
This view displays information about ASM files. There is one row for every ASM file in every diskgroup mounted by the ASM instance. In a RDBMS instance, V$ASM_FILE displays no row.

V$ASM_OPERATION
This view describes the progress of an influx ASM rebalance operation. In a RDBMS instance, v$ASM_OPERATION displays no rows.

V$ASM_TEMPLATE
This view contains information on user and system-defined templated. v$ASM_TEMPLATE displays one row for every template present in every diskgroup mounted by the ASM instance. In a RDBMS instance, v$ASM_TEMPLATE displays one row for every template present in every diskgroup mounted by the ASM instance with which the RDBMS instance communicates.

thats it,

regards,
rajeshkumar g

Wednesday, December 16, 2009

oracle DBA Tips (PART-II)

ORACLE DBA TIPS:- PART-2
----------------------------------
26.Retrieving Threshold Information
SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';

27.Viewing Alert Data
The following dictionary views provide information about server alerts:
DBA_THRESHOLDS lists the threshold settings defined for the instance.

DBA_OUTSTANDING_ALERTS describes the outstanding alerts in the database.

DBA_ALERT_HISTORY lists a history of alerts that have been cleared.

V$ALERT_TYPES provides information such as group and type for each alert.

V$METRICNAME contains the names, identifiers, and other information about the
system metrics.

V$METRIC and V$METRIC_HISTORY views contain system-level metric values in
memory.

28.The following views can help you to monitor locks:

for getting information about locks, we have to run two scripts
utllockt.sql and catblock.sql
Lists the locks currently held by Oracle Database and outstanding
V$LOCK
requests for a lock or latch

Displays a session if it is holding a lock on an object for which
DBA_BLOCKERS
another session is waiting

Displays a session if it is waiting for a locked object
DBA_WAITERS

Lists all DDL locks held in the database and all outstanding
DBA_DDL_LOCKS
requests for a DDL lock

Lists all DML locks held in the database and all outstanding
DBA_DML_LOCKS
requests for a DML lock

Lists all locks or latches held in the database and all outstanding
DBA_LOCK
requests for a lock or latch

Displays a row for each lock or latch that is being held, and one
DBA_LOCK_INTERNAL
row for each outstanding request for a lock or latch

Lists all locks acquired by every transaction on the system
V$LOCKED_OBJECT


29.Process and Session Views
v$process
v$locked_object
v$session
v$sess_io
v$session_longops
v$session_wait
v$sysstat
v$resource_limit
v$sqlarea
v$latch

30.What Is a Control File?
Every Oracle Database has a control file, which is a small binary file that records the
physical structure of the database. The control file includes:
The database name

Names and locations of associated datafiles and redo log files

The timestamp of the database creation

The current log sequence number

Checkpoint information

31.The following views display information about control files:
V$DATABASE Displays database information from the control file

V$CONTROLFILE Lists the names of control files


V$CONTROLFILE_RECORD_SECTION Displays information about control file record sections

V$PARAMETER Displays the names of control files as specified in the CONTROL_FILES initialization parameter

32.Redo Log Contents
Redo log files are filled with redo records. A redo record, also called a redo entry, is
made up of a group of change vectors, each of which is a description of a change made
to a single block in the database.

Redo entries record data that you can use to reconstruct all changes made to the
database, including the undo segments. Therefore, the redo log also protects rollback
data. When you recover the database using redo data, the database reads the change
vectors in the redo records and applies the changes to the relevant blocks.

33.Log Switches and Log Sequence Numbers
A log switch is the point at which the database stops writing to one redo log file and
begins writing to another. Normally, a log switch occurs when the current redo log file
is completely filled and writing must continue to the next redo log file.
You can also force log switches manually.

Oracle Database assigns each redo log file a new log sequence number every time a
log switch occurs and LGWR begins writing to it. When the database archives redo log
files, the archived log retains its log sequence number. A redo log file that is cycled
back for use is given the next available log sequence number.


34. Setting the Size of Redo Log Members
The minimum size permitted for a redo log file is 4 MB.

35.Setting the ARCHIVE_LAG_TARGET Initialization Parameter
The ARCHIVE_LAG_TARGET initialization parameter specifies the target of how many
seconds of redo the standby could lose in the event of a primary shutdown or failure if
the Oracle Data Guard environment is not configured in a no-data-loss mode. It also
provides an upper limit of how long (in seconds) the current log of the primary
database can span. Because the estimated archival time is also considered, this is not
the exact log switch time.
The following initialization parameter setting sets the log switch interval to 30 minutes
(a typical value).
ARCHIVE_LAG_TARGET = 1800
A value of 0 disables this time-based log switching functionality. This is the default
setting.
You can set the ARCHIVE_LAG_TARGET initialization parameter even if there is no
standby database. For example, the ARCHIVE_LAG_TARGET parameter can be set
specifically to force logs to be switched and archived.

36.Verifying Blocks in Redo Log Files
If you set the initialization parameter DB_BLOCK_CHECKSUM to TRUE, the database
computes a checksum for each database block when it is written to disk, including
each redo log block as it is being written to the current log. The checksum is stored the header of the block.

Oracle Database uses the checksum to detect corruption in a redo log block. The
database verifies the redo log block when the block is read from an archived log
during recovery and when it writes the block to an archive log file. An error is raised
and written to the alert log if corruption is detected.

37.Clearing a Redo Log File
A redo log file might become corrupted while the database is open, and ultimately
stop database activity because archiving cannot continue. In this situation the ALTER
DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without
shutting down the database.
The following statement clears the log files in redo log group number 3:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
This statement overcomes two situations where dropping redo logs is not possible:
If there are only two log groups

The corrupt redo log file belongs to the current group

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the
statement.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
This statement clears the corrupted redo logs and avoids archiving them. The cleared
redo logs are available for use even though they were not archived.
If you clear a log file that is needed for recovery of a backup, then you can no longer
recover from that backup. The database writes a message in the alert log describing the
backups from which you cannot recover.
Note:
If you clear an unarchived redo log file, you should make
another backup of the database.
If you want to clear an unarchived redo log that is needed to bring an offline
tablespace online, use the UNRECOVERABLE DATAFILE clause in the ALTER
DATABASE CLEAR LOGFILE statement.

38.Viewing Redo Log Information
Displays the redo log file information from the control file
V$LOG
Identifies redo log groups and members and member status
V$LOGFILE
Contains log history information
V$LOG_HISTORY

39.You can use archived redo logs to:
Recover a database

Update a standby database

Get information about the history of a database using the LogMiner utility

40.Changing the database ARCHIVING mode:
(1) shutdown
(2) startup mount
(3) alter database archivelog;
(4) alter database open;

41.Performing Manual Archiving
ALTER DATABASE ARCHIVELOG MANUAL;
ALTER SYSTEM ARCHIVE LOG ALL;

note:When you use manual archiving mode, you cannot specify any standby databases in
the archiving destinations.

42.Understanding Archive Destination Status
Each archive destination has the following variable characteristics that determine its
status:
Valid/Invalid: indicates whether the disk location or service name information is

specified and valid
Enabled/Disabled: indicates the availability state of the location and whether the

database can use the destination
Active/Inactive: indicates whether there was a problem accessing the destination

Several combinations of these characteristics are possible. To obtain the current status
and other information about each destination for an instance, query the
V$ARCHIVE_DEST view.

The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 10) initialization
parameter lets you control the availability state of the specified destination (n).
ENABLE indicates that the database can use the destination.

DEFER indicates that the location is temporarily disabled.

ALTERNATE indicates that the destination is an alternate.

The availability state of the destination is DEFER, unless there is a failure of its parent destination, in which case its state becomes ENABLE.

43.Viewing Information About the Archived Redo Log
You can display information about the archived redo logs using the following sources:
(1)Dynamic Performance Views

(2)The ARCHIVE LOG LIST Command

Dynamic Performance Views
-------------------------
Shows if the database is in ARCHIVELOG or NOARCHIVELOG
V$DATABASE
mode and if MANUAL (archiving mode) has been specified.

Displays historical archived log information from the control
V$ARCHIVED_LOG
file. If you use a recovery catalog, the RC_ARCHIVED_LOG
view contains similar information.

Describes the current instance, all archive destinations, and
V$ARCHIVE_DEST
the current value, mode, and status of these destinations.

Displays information about the state of the various archive
V$ARCHIVE_PROCESSES
processes for an instance.

Contains information about any backups of archived logs. If
V$BACKUP_REDOLOG
you use a recovery catalog, the RC_BACKUP_REDOLOG
contains similar information.

Displays all redo log groups for the database and indicates
V$LOG
which need to be archived.

Contains log history information such as which logs have
V$LOG_HISTORY
been archived and the SCN range for each archived log.

44.Bigfile Tablespaces
A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks)
datafile. Traditional smallfile tablespaces, in contrast, can contain multiple datafiles,
but the files cannot be as large. The benefits of bigfile tablespaces are the following:
A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile
tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum
number of datafiles in an Oracle Database is limited (usually to 64K files).
Therefore, bigfile tablespaces can significantly enhance the storage capacity of an
Oracle Database.

45.Altering a Bigfile Tablespace
Two clauses of the ALTER TABLESPACE statement support datafile transparency
when you are using bigfile tablespaces:

RESIZE: The RESIZE clause lets you resize the single datafile in a bigfile
tablespace to an absolute size, without referring to the datafile. For example:
ALTER TABLESPACE bigtbs RESIZE 80G;

AUTOEXTEND (used outside of the ADD DATAFILE clause):
With a bigfile tablespace, you can use the AUTOEXTEND clause outside of the ADD
DATAFILE clause. For example:
ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;

An error is raised if you specify an ADD DATAFILE clause for a bigfile tablespace.

46.Identifying a Bigfile Tablespace
The following views contain a BIGFILE column that identifies a tablespace as a bigfile
tablespace:
DBA_TABLESPACES

USER_TABLESPACES

V$TABLESPACE

47.Temporary Tablespaces
You can view the allocation and deallocation of space in a temporary tablespace sort
segment using the V$SORT_SEGMENT view. The V$TEMPSEG_USAGE view identifies
the current sort users in those segments.

You also use different views for viewing information about tempfiles than you would
for datafiles. The V$TEMPFILE and DBA_TEMP_FILES views are analogous to the
V$DATAFILE and DBA_DATA_FILES views.

48.Creating a Locally Managed Temporary Tablespace
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Altering a Locally Managed Temporary Tablespace
-----------------------------------------------
ALTER TABLESPACE lmtemp
ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;
(or)
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;
ALTER TABLESPACE lmtemp TEMPFILE ONLINE;

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;

Assigning Default Temporary Tablespace:
--------------------------------------
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE lmttemp;

49.Multiple Temporary Tablespaces: Using Tablespace Groups:
A tablespace group enables a user to consume temporary space from multiple
tablespaces. A tablespace group has the following characteristics:

It contains at least one tablespace. There is no explicit limit on the maximum
number of tablespaces that are contained in a group.

It shares the namespace of tablespaces, so its name cannot be the same as any
tablespace.

You can specify a tablespace group name wherever a tablespace name would
appear when you assign a default temporary tablespace for the database or a
temporary tablespace for a user.

You do not explicitly create a tablespace group. Rather, it is created implicitly when
you assign the first temporary tablespace to the group. The group is deleted when the
last temporary tablespace it contains is removed from it.

The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member
tablespaces.

50.Creating a Tablespace Group
------------------------------
CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'
SIZE 50M
TABLESPACE GROUP group1;

ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;

Changing Members of a Tablespace Group
--------------------------------------
You can add a tablespace to an existing tablespace group by specifying the existing
group name in the TABLESPACE GROUP clause of the CREATE TEMPORARY
TABLESPACE or ALTER TABLESPACE statement.
The following statement adds a tablespace to an existing group. It creates and adds
tablespace lmtemp3 to group1, so that group1 contains tablespaces lmtemp2 and
lmtemp3.
CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf'
SIZE 25M
TABLESPACE GROUP group1;
The following statement also adds a tablespace to an existing group, but in this case
because tablespace lmtemp2 already belongs to group1, it is in effect moved from
group1 to group2:

ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;

Now group2 contains both lmtemp and lmtemp2, while group1 consists of only
tmtemp3.
You can remove a tablespace from a group as shown in the following statement:

ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';

Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer
any members of group1, this results in the implicit deletion of group1.

Assigning a Tablespace Group as the Default Temporary Tablespace
----------------------------------------------------------------
ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;

regards,
rajeshkumar g

Related topics:
http://oracleinstance.blogspot.com/2009/12/oracle-dba-tips-for-day.html

oracle DBA tips (PART-I)

ORACLE DBA TIPS:- PART-1
---------------------------------
1.To dynamically change the default tablespace type after database creation, use the SET
DEFAULT TABLESPACE clause of the ALTER DATABASE statement:
ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

2.You can determine the current default tablespace type for the database by querying the
DATABASE_PROPERTIES data dictionary view as follows:
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TBS_TYPE';


3.To view the time zone names in the file being used by your database, use the following
query:
SELECT * FROM V$TIMEZONE_NAMES;


4.You can cancel FORCE LOGGING mode using the following SQL statement:
ALTER DATABASE NO FORCE LOGGING;

5.The V$SGA_TARGET_ADVICE view provides information that helps you decide on a
value for SGA_TARGET.

6.The fixed views V$SGA_DYNAMIC_COMPONENTS and V$SGAINFO display the current
actual size of each SGA component.

7.Checking Your Current Release Number
SELECT * FROM PRODUCT_COMPONENT_VERSION;

SELECT * FROM v$VERSION;

8.Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files).

9.Specifying a Flash Recovery Area with the following initialization parameters:
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
In a RAC environment, the settings for these two parameters must be the same on all
instances.

10.DB_BLOCK_SIZE Initialization Parameter
You cannot change the block size after database creation except by re-creating the
database.

11.Nonstandard Block Sizes
Tablespaces of nonstandard block sizes can be created using the CREATE
TABLESPACE statement and specifying the BLOCKSIZE clause. These nonstandard
block sizes can have any of the following power-of-two values: 2K, 4K, 8K, 16K or 32K.

12.All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.

13.Viewing Information about the SGA:
v$SGA
v$SGAINFO
v$SGASTAT
v$SGA_DYNAMIC_COMPONENTS
v$SGA_DYNAMIC_FREE_MEMORY
v$SGA_RESIZE_OPS
v$SGA_CURRENT_RESIZE_OPS
v$SGA_TARGET_ADVICE

14.An optional COMMENT clause lets you associate a text string with the parameter
update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the
server parameter file.
example:ALTER SYSTEM
SET LOG_ARCHIVE_DEST_4='LOCATION=/u02/oracle/rbdb1/',MANDATORY,'REOPEN=2'
COMMENT='Add new destimation on Nov 29'
SCOPE=SPFILE;

15.Viewing Parameter Settings
show parameters, v$parameter, v$parameter2, v$spparameter.

16.You can find service information in the following service-specific views:

DBA_SERVICES

ALL_SERVICES or V$SERVICES

V$ACTIVE_SERVICES

V$SERVICE_STATS

V$SERVICE_EVENTS

V$SERVICE_WAIT_CLASSES

V$SERV_MOD_ACT_STATS

V$SERVICE_METRICS

V$SERVICE_METRICS_HISTORY

The following additional views also contain some information about services:

V$SESSION

V$ACTIVE_SESSION_HISTORY

DBA_RSRC_GROUP_MAPPINGS

DBA_SCHEDULER_JOB_CLASSES

DBA_THRESHOLDS

17.Viewing Information About the Database

DATABASE_PROPERTIES

GLOBAL_NAME

V$DATABASE

18.Starting an Instance, Mounting a Database, and Starting Complete Media Recovery
If you know that media recovery is required, you can start an instance, mount a
database to the instance, and have the recovery process automatically start by using
the STARTUP command with the RECOVER clause:
STARTUP OPEN RECOVER
If you attempt to perform recovery when no recovery is required, Oracle Database
issues an error message.

19.Placing a Database into a Quiesced State
To place a database into a quiesced state, issue the following statement:
ALTER SYSTEM QUIESCE RESTRICTED;
Non-DBA active sessions will continue until they become inactive.

20. You can determine the sessions that are blocking the quiesce operation by querying the V$BLOCKING_QUIESCE view:

select bl.sid, user, osuser, type, program
from v$blocking_quiesce bl, v$session se
where bl.sid = se.sid;

21.You cannot perform a cold backup when the database is in
the quiesced state, because Oracle Database background processes
may still perform updates for internal purposes even while the
database is quiesced. In addition, the file headers of online datafiles
continue to appear to be accessible. They do not look the same as if
a clean shutdown had been performed. However, you can still take
online backups while the database is in a quiesced state.

22.Restoring the System to Normal Operation
The following statement restores the database to normal operation:

ALTER SYSTEM UNQUIESCE;

23.Viewing the Quiesce State of an Instance
You can query the ACTIVE_STATE column of the V$INSTANCE view to see the current
state of an instance. The column values has one of these values:

NORMAL: Normal unquiesced state.
QUIESCING: Being quiesced, but some non-DBA sessions are still active.
QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

24.Suspending and Resuming a Database
The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files. The suspended state lets you back up a database without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state.

The following statements illustrate ALTER SYSTEM SUSPEND/RESUME usage. The
V$INSTANCE view is queried to confirm database status.
SQL> ALTER SYSTEM SUSPEND;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
SUSPENDED
SQL> ALTER SYSTEM RESUME;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
ACTIVE

25.The DB_WRITER_PROCESSES initialization parameter specifies the number of DBWn processes.
Oracle Database allows a maximum of 20 database writer processes
(DBW0-DBW9 and DBWa-DBWj).

hope, this will help you,
regards,
rajeshkumar g

Related Topics:
http://oracleinstance.blogspot.com/2009/12/oracle-dba-tips-part-ii.html

Monday, December 14, 2009

obsolete and/or deprecated parameter(s) specified

ORA-32004 obsolete and/or deprecated parameter(s) specified


Cause

One or more obsolete and/or parameters were specified in the SPFILE or the PFILE on the server side.

Action

See alert log for a list of parameters that are obsolete. or deprecated. Remove them from the SPFILE or the server side PFILE.

So somebody, somewhere has put obsolete and/or deprecated parameter(s) in my initDB.ora file. To find out which you could from SQL*PLUS, issue the following statement, to find the sinner.

SQL> select name, isspecified from v$obsolete_parameter where isspecified='TRUE';

Or if you are the one, who has made the changes to initDB.ora, you might know which one. In my case somebody had been messing around with the parameter log_archive_start;

In order to remove this, you should create a pfile from spfile and back to spfile? Thats the way to do it.

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL>
SQL>
alter system reset log_archive_start scope=spfile sid='*' ;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

Using srvctl to Manage your 10g RAC Database

Using srvctl to Manage your 10g RAC Database

Oracle recommends that RAC databases be managed with srvctl, an Oracle-supplied tool that was first introduced with 9i RAC. The 10g version of srvctl is slightly different from the 9i implementation. In this article, we will look at how -- and why -- to manage your 10g databases with srvctl.

RAC Architecture Overview

Let's begin with a brief overview of RAC architecture.

  • A cluster is a set of 2 or more machines (nodes) that share or coordinate resources to perform the same task.
  • A RAC database is 2 or more instances running on a set of clustered nodes, with all instances accessing a shared set of database files.
  • Depending on the O/S platform, a RAC database may be deployed on a cluster that uses vendor clusterware plus Oracle's own clusterware (Cluster Ready Services), or on a cluster that solely uses Oracle's own clusterware.

Thus, every RAC sits on a cluster that is running Cluster Ready Services. srvctl is the primary tool DBAs use to configure CRS for their RAC database and processes.

Cluster Ready Services and the OCR

Cluster Ready Services, or CRS, is a new feature for 10g RAC. Essentially, it is Oracle's own clusterware. On most platforms, Oracle supports vendor clusterware; in these cases, CRS interoperates with the vendor clusterware, providing high availability support and service and workload management. On Linux and Windows clusters, CRS serves as the sole clusterware. In all cases, CRS provides a standard cluster interface that is consistent across all
platforms.

CRS consists of four processes (crsd, occsd, evmd, and evmlogger) and two disks: the Oracle Cluster Registry (OCR), and the voting disk.

CRS manages the following resources:

  • The ASM instances on each node
  • Databases
  • The instances on each node
  • Oracle Services on each node
  • The cluster nodes themselves, including the following processes, or "nodeapps":
    • VIP
    • GSD
    • The listener
    • The ONS daemon

CRS stores information about these resources in the OCR. If the information in the OCR for one of these resources becomes damaged or inconsistent, then CRS is no longer able to manage that resource. Fortunately, the OCR automatically backs itself up regularly and frequently.

Interacting with CRS and the OCR: srvctl

srvctl is the tool Oracle recommends that DBAs use to interact with CRS and the cluster registry. Oracle does provide several tools to interface with the cluster registry and CRS more directly, at a lower level, but these tools are deliberately undocumented and intended only for use by Oracle Support. srvctl, in contrast, is well documented and easy to use. Using other tools to modify the OCR or manage CRS without the assistance of Oracle Support runs the risk of damaging the OCR.

Using srvctl

Even if you are experienced with 9i srvctl, it's worth taking a look at this section; 9i and 10g srvctl commands are slightly different.

srvctl must be run from the $ORACLE_HOME of the RAC you are administering. The basic format of a srvctl command is

srvctl [options]

where command is one of

enable|disable|start|stop|relocate|status|add|remove|modify|getenv|setenv|unsetenv|config

and the target, or object, can be a database, instance, service, ASM instance, or the nodeapps.

The srvctl commands are summarized in this table:

Table 1. Summary of srvctl commands.
CommandTargetsDescription
srvctl add
srvctl modify
srvctl remove
database
instance
service
nodeapps
srvctl add / remove adds/removes target's configuration information to/from the OCR.

srvctl modify allows you to change some of target's configuration information in the OCR without wiping out the rest.

srvctl relocate service Allows you to reallocate a service from one named instance to another named instance.
srvctl config database
service
nodeapps
asm
Lists configuration information for target from the OCR.
srvctl disable
srvctl enable
database
instance
service
asm
srvctl disable disables target, meaning CRS will not consider it for automatic startup, failover, or restart. This option is useful to ensure an object that is down for maintenance is not accidentally automatically restarted.

srvctl enable reenables the specified object.

srvctl getenv
srvctl setenv
srvctl unsetenv
database
instance
service
nodeapps
srvctl getenv displays the environment variables stored in the OCR for target.

srvctl setenv allows these variables to be set, and unsetenv unsets them.

srvctl start
srvctl status
srvctl stop
database
instance
service
nodeapps
asm
Start, stop, or display status (started or stopped) of target.

As you can see, srvctl is a powerful utility with a lot of syntax to remember. Fortunately, there are only really two commands to memorize: srvctl -help displays a basic usage message, and srvctl -h displays full usage information for every possible srvctl command.

Examples

Example 1. Bring up the MYSID1 instance of the MYSID database.

[oracle@myserver oracle]$ srvctl start instance -d MYSID -i MYSID1

Example 2. Stop the MYSID database: all its instances and all its services, on all nodes.

[oracle@myserver oracle]$ srvctl stop database -d MYSID

Example 3. Stop the nodeapps on the myserver node. NB: Instances and services also stop.

[oracle@myserver oracle]$ srvctl stop nodeapps -n myserver

Example 4. Add the MYSID3 instance, which runs on the myserver node, to the MYSID
clustered database.

[oracle@myserver oracle]$ srvctl add instance -d MYSID -i MYSID3 -n myserver

Example 4. Add a new node, the mynewserver node, to a cluster.

[oracle@myserver oracle]$ srvctl add nodeapps -n mynewserver -o $ORACLE_HOME -A
149.181.201.1/255.255.255.0/eth1

(The -A flag precedes an address specification.)

Example 5. To change the VIP (virtual IP) on a RAC node, use the command

[oracle@myserver oracle]$ srvctl modify nodeapps -A new_address

Example 6. Find out whether the nodeapps on mynewserver are up.

[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserver
VIP is running on node: mynewserver
GSD is running on node: mynewserver
Listener is not running on node: mynewserver
ONS daemon is running on node: mynewserver

Example 7. Disable the ASM instance on myserver for maintenance.

[oracle@myserver oracle]$ srvctl disable asm -n myserver

Debugging srvctl

Debugging srvctl in 10g couldn't be easier. Simply set the SRVM_TRACE environment variable.

[oracle@myserver bin]$ export SRVM_TRACE=true

Let's repeat Example 6 with SRVM_TRACE set to true:

[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserver
/u01/app/oracle/product/10.1.0/jdk/jre//bin/java -classpath
/u01/app/oracle/product/10.1.0/jlib/netcfg.jar:/u01/app/oracle/product/10.1.0/jdk/jre//lib/rt.jar:
/u01/app/oracle/product/10.1.0/jdk/jre//lib/i18n.jar:/u01/app/oracle/product/10.1.0/jlib/srvm.jar:
/u01/app/oracle/product/10.1.0/jlib/srvmhas.jar:/u01/app/oracle/product/10.1.0/jlib/srvmasm.jar:
/u01/app/oracle/product/10.1.0/srvm/jlib/srvctl.jar
-DTRACING.ENABLED=true -DTRACING.LEVEL=2 oracle.ops.opsctl.OPSCTLDriver status nodeapps -n
mynewserver
[main] [19:53:31:778] [OPSCTLDriver.setInternalDebugLevel:165] tracing is true at level 2 to
file null
[main] [19:53:31:825] [OPSCTLDriver.:94] Security manager is set
[main] [19:53:31:843] [CommandLineParser.parse:157] parsing cmdline args
[main] [19:53:31:844] [CommandLineParser.parse2WordCommandOptions:900] parsing 2-word
cmdline
[main] [19:53:31:866] [GetActiveNodes.create:212] Going into GetActiveNodes constructor...
[main] [19:53:31:875] [HASContext.getInstance:191] Module init : 16
[main] [19:53:31:875] [HASContext.getInstance:216] Local Module init : 19
...
[main] [19:53:32:285] [ONS.isRunning:186] Status of ora.ganges.ons on mynewserver is true
ONS daemon is running on node: mynewserver
[oracle@myserver oracle]$

Pitfalls

A little impatience when dealing with srvctl can corrupt your OCR, ie, put it into a state where the information for a given object is inconsistent or partially missing. Specifically, the srvctl remove command provides the -f option, to allow you to force removal of an object from the OCR. Use this option judiciously, as it can easily put the OCR into an inconsistent state.

Restoring the OCR from an inconsistent state is best done with the assistance of Oracle Support, who will guide you in using the undocumented $CRS_HOME/bin/crs_* tools to repair it. The OCR can also be restored from backup.

Error messages

srvctl errors are PRK% errors, which are not documented in the 10gR1 error messages manual. However, for those with a Metalink account, they are documented on Metalink here.

Conclusion

srvctl is a powerful tool that will allow you to administer your RAC easily and effectively. In addition, it provides a valuable buffer between the DBA and the OCR, making it more difficult to corrupt the OCR.

Saturday, December 12, 2009

BDUMP, UDUMP, ALERT LOG FILES IN ORACLE 11G

The 11g New Features Guide notes important OFA changes, namely the removal of $ORACLE_HOME as an anchor for diagnostic and alert files:

"The database installation process has been redesigned to be based on the ORACLE_BASE environment variable. Until now, setting this variable has been optional and the only required variable has been ORACLE_HOME.

With this feature, ORACLE_BASE is the only required input, and the ORACLE_HOME setting will be derived from ORACLE_BASE."

Let's take a look at changes to the Oracle11g OFA standard.

Enter new admin subdirectories

New in Oracle 11g we see the new ADR (Automatic Diagnostic Repository) and Incident Packaging System, all designed to allow quick access to alert and diagnostic information.

The new $ADR_HOME directory is located by default at $ORACLE_BASE/diag, with the directories for each instance at $ORACLE_HOME/diag/$ORACLE_SID, at the same level as the traditional bdump, udump and cdump directories and the initialization parameters background_dump_dest and user_dump_dest are deprecated in 11g.

You can use the new initialization parameter diagnostic_dest to specify an alternative location for the diag directory contents.

In 11g, each $ORACLE_HOME/diag/$ORACLE_SID directory may contain these new directories:

*

alert - A new alert directory for the plain text and XML versions of the alert log.

*

incident - A new directory for the incident packaging software.

*

incpkg - A directory for packaging an incident into a bundle.

*

trace - A replacement for the ancient background dump (bdump) and user dump (udump) destinations.

*

cdump - The old core dump directory retains it's 10g name.

Let's see how the 11g alert log has changed.

Alert log changes in 11g

Oracle now writes two alert logs, the traditional alert log in plain text plus a new XML formatted alert.log which is named as log.xml.

"Prior to Oracle 11g, the alert log resided in $ORACLE_HOME/admin/$ORACLE_SID/bdump directory, but it now resides in the $ORACLE_BASE/diag/$ORACLE_SID directory".

Fortunately, you can re-set it to the 10g and previous location by specifying the BDUMP location for the diagnostic_dest parameter.

But best of all, you no longer require server access to see your alert log since it is now accessible via standard SQL using the new v$diag_info view:

select name, value from v$diag_info;

For complete details, see MetaLink Note:438148.1 - "Finding alert.log file in 11g".

regards,

rajeshkumar g

ENABLE ARCHIVELOG AND FLASHBACK IN RAC DATABASE

Step by step process of putting a RAC database in archive log mode and then enabling the flashback Database option.

Enabling archive log in RAC Database:

A database must be in archivelog mode before enabling flashback.

In this example database name is test and instances name are test1 and test2.

step 1:

creating recovery_file_dest in asm disk

SQL> alter system set db_recovery_file_dest_size=200m sid='*';

System altered.

SQL> alter system set db_recovery_file_dest='+DATA' sid='*';

System altered.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Current log sequence 14
SQL>

step 2:

set the LOG_ARCHIVE_DEST_1 parameter. since these parameters will be identical for all nodes, we will use sid='*'. However, you may need to modify this for your situation if the directories are different on each node.

SQL> alter system set log_archive_dest_1='LOCATION=USE-DB_RECOVERY_FILE_DEST';

System altered.

step 3:

set LOG_ARCHIVE_START to TRUE for all instances to enable automatic archiving.

SQL> alter system set log_archive_start=true scope=spfile sid='*';

System altered.
Note that we illustrate the command for backward compatibility purposes, but in oracle database 10g onwards, the parameter is actually deprecated. Automatic archiving will be enabled by default whenever an oracle database is placed in archivelog mode.

step 4:

Set CLUSTER_DATABASE to FALSE for the local instance, which you will then mount to put the database into archivelog mode. By having CLUSTER_DATABASE=FALSE, the subsequent shutdown and startup mount will actually do a Mount Exclusive by default, which is necessary to put the database in archivelog mode, and also to enable the flashback database feature:

SQL> alter system set cluster_database=false scope=spfile sid='test1';

System altered.

step 5;
Shut down all instances. Ensure that all instances are shut down cleanly:

SQL> shutdown immediate

step 6:
Mount the database from instance test1 (where CLUSTER_DATABASE was set to FALSE) and then put the database into archivelog mode.

SQL> startup mount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Database mounted.

SQL> alter database archivelog;

Database altered.

NOTE:
If you did not shut down all instances cleanly in step 5,
putting the database in archivelog mode will fail
with an ORA-265 Error.

SQL> alter database archivelog;
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

step 7:
Confirm that the database is in archivelog mode, with the appropriate parameters, by issuing the ARCHIVE LOG LIST command:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE-DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15

step 8
Confirm the location of the RECOVERY_FILE_DEST via a SHOW PARAMETER.


SQL> show parameter recovery_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 200M

Step 9:
Once the database is in archivelog mode, you can enable flashback while the database is still mounted in Exclusive mode (CLUSTER_DATABASE=FALSE).

SQL> alter database flashback on;

Database altered.

Step 10:
Confirm that Flashback is enabled and verify the retention target:

SQL> select flashback_on,current_scn from v$database;

FLASHBACK_ON CURRENT_SCN
------------------ -----------
YES 0

SQL> show parameter flash

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440

step 11:
Reset the CLUSTER_DATABASE parameter back to TRUE for all instances:

SQL> alter system set cluster_database=true scope=spfile sid=' * ';

System altered.

step 12:
shutdown the instance and then restart all cluster database instances.
All instances will now be archiving their redo threads.

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

start the database, using srvctl command or normal startup

[root@rac1 bin]# ./srvctl status database -d test
Instance test1 is not running on node rac1
Instance test2 is not running on node rac2

[root@rac1 bin]# ./srvctl start database -d test

[root@rac1 bin]# ./srvctl status database -d test
Instance test1 is running on node rac1
Instance test2 is running on node rac2
[root@rac1 bin]#

on test1 instance:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE-DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL>

on test2 instance:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE-DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>

wow, both are in archive log mode

hope, this document will help you .

regards,
rajeshkumar g
free counters
 
Share/Bookmark