Monday, June 25, 2012

Issue during database upgrade from 11.2.0.1 to 11.2.0.3 on VM

Today, We came across the different issue while upgrade. Database was successfully opened in 11.2.0.1 but while " Startup Upgrade", it was throwing following errors-

ORA-01157: cannot identify/lock data file 359 - see DBWR trace file

ORA-01110: data file 359: 'apps_undots.dbf'
ORA-27048: skgfifi: file header information is invalid

In alert log we found:

ALTER DATABASE OPEN MIGRATE

Direct NFS: attempting to mount /vol/ORADATA_FS01 on filer defined in mtab
Direct NFS: channel config is:
channel id [0] local [] path ]
Direct NFS: mount complete dir /ORADATA_FS01 Errors in file /oracle/admin//diag/rdbms/ts2ecp/dbw0_20560.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/oracle/oradata/fs01/system_01.dbf'
ORA-17503: ksfdopn:7 Failed to open file /oracle/oradata/fs01/system_01.dbf
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 11174: Unknown system error
Additional information: 7
Additional information: 1795
Additional information: 0
Direct NFS: attempting to mount /vol/ORADATA_FS02 on filer DB-generic10-vfiler112 defined in mtab
Direct NFS: mount complete dir /vol/ORADATA_FS02 on --vfiler112 mntport 4046 nfsport 2049


To resolve this issue we generated STRACE :
strace -fo sqlplus /nolog


SQL> conn / as sysdba

SQL> startup migrate

Strace o/p:

 3314 stat("/oracle/oradata/fs01/system_01.dbf", {st_mode=S_IFREG

0644, st_size=9018802176, ...}) = 0


3314 stat("/oracle/oradata//fs01/system_01.dbf", {st_mode=S_IFREG
0644, st_size=9018802176, ...}) = 0


3314 open("/oracle/oradata//fs01/system_01.dbf", O_RDONLY) = 18


3314 ioctl(18, BLKSSZGET, 0x7fffdb90e398) = -1 ENOTTY (Inappropriate ioctl for device)


3314 close(18)

 

Solution:
While upgrade, oracle may update each datafile. But as observed in alert log and Strace, data in datafile reached it's brim, eventually not able to write in the datafiles. In our case, 7 datafiles were listed in alert log for which it was unable to update header.

As a solution, resized all the datafiles listed in the log in order to make some place available for updating header using following query:

alter database datafile 'datafile_name' resize 4800M;









Wednesday, June 13, 2012

ORA error :

ORA-25228: timeout or end-of-fetch during message dequeue from AQADM.CMRS_INCR_ITEM_DQ


ORA-06512: at "SYS.DBMS_AQ", line 335

Solution:
 
Try to restart the jobs if it doesnt fix the issue, then bounce job_queue_processes and aq_tm_processes.

Monday, March 26, 2012

Oracle database vault

command to check if database vault is enabled the database

GO to $ORACLE_HOME/rdbms/lib and execute following:
ar -t libknlopt.a | grep -c kzvidv.o

If it gives output as 0, database vault is disabled and vice versa.


To Enable:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_on
make -f ins_rdbms.mk ioracle

To Disable:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off
make -f ins_rdbms.mk ioracle

Tuesday, January 31, 2012

Oracle DB Error:ORA-29702: error occurred in Cluster Group Service operation

Cause:
In case of standalone database,RAC has tobe made off.If its not made off,one can see this error while starting up the database.

Simple Solution:
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk rac_off
make -f ins_rdbms.mk ioracle

Sunday, October 9, 2011

Oracle RMAN -Simple commands

Recovery Manager :RMAN is a oracle utility used for backup and recovery purpose, following are simple commands to initiate with RMAN

connecting to RMAN
===========================

$ rman target sys/
If you’re accessing your target database remotely via Oracle Net, then you will need to
specify a connection string as follows:
$ rman target sys/@
You can also invoke RMAN and then connect to your target database as a second step,
from the RMAN prompt:
$ rman
RMAN> connect target /

Backing Up Your Database
===========================
You’re new to RMAN, and you want to back up your database. You just need to get a backup
created, and you want to take the simplest possible approach.
Solution
Start the rman utility, connect to your target database, and use the backup command to back up
your entire database:
$ rman target /
RMAN> backup database;


Simulating a Failure
================================
You want to simulate a failure as a prelude to testing RMAN’s restore and recovery capabilities.
Solution
To simulate a failure, perform the following steps:
1. Ensure you have a backup.
2. Determine the location and name of a datafile to rename. You will simulate failure by
renaming a datafile so that it appears to have been lost.
3. Stop the database.
4. Rename a datafile at the OS level (simulates media failure).
5. Attempt to start the database.

Before simulating a media failure, ensure that you’re in a noncritical test database environment
and that you have a good RMAN backup of your database. Run the following
command in your target database, and ensure that you have a good backup:
RMAN> connect target /
RMAN> list backup;

Caution If no backup information is listed, then stop here. You need to ensure that you have a good
backup of your database before you simulate media failure.
Determine the location of a target database datafile so that you can rename it to simulate
media failure:

RMAN> report schema;

Shown next is an abbreviated portion of the output of the previous command. This shows
the name of the file that you’re going to rename.

File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 22 USERS *** C:\ORA01\BRDSTN\USERS01.DBF

RMAN> shutdown immediate;
RMAN> exit

then rename the datafile to simulate

RMAN> connect target /
RMAN> startup

RMAN-03002: failure of startup command
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORA01\BRDSTN\USERS01.DBF'



Restoring and Recovering Your Database
==========================================
You’ve experienced a failure and want to use RMAN to restore and recover your database. You
have a current and good backup in the default location, and all needed control files, archived
redo log files, and online redo log files are available.
Solution
Connect to RMAN, and use the following commands to restore and recover your database. In
this recipe you’ll perform the following steps:
1. Connect to the target database.
2. Mount the database.
3. Restore the database.
4. Recover the database.
5. Open the database.


RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
database opened

Thursday, June 23, 2011

Cloning oracle database using RMAN Duplicate-(ASM to ASM) steps:

Precloning activities:










Source database:



-parameter changes at source datbase-pfile-remote_login_passwordfile= exclusive



-make source database archive log enabled.



-change the sys password to known value and create the passwordfile



-Make sure, you are able to connect to source database from target server(remote connection)



-Keep source database in mount or open state.









Target database:









-create the password file. Password should be same as source############



-parameter remote_login_passwordfile= exclusive



-add foolowing paramters db_file_name_convert = ('+DG_source/oradata','+DG_target/oradata') ###### DATA DISKGROUP.



log_file_name_convert = ('+DG_source_DB_RC/oraredo','+DG_target_DB_RC/oraredo') ####### REDO CONTROL DISKGROUP









Cloning Activity:















Target database:









start the Database in nomount state









Start the Listener.















Check the rman connectting on the Target.



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



rman target sys/***@source_db









connect auxiliary sys/***@target_db









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









rman_duplicate.sh ######### I kept the Parallelism as 32 only. May be we can increase to 100. It may put load on source server



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



rman target sys/***@source_db auxiliary sys/***@target_db trace=debug.txt LOG duplicate_${ORACLE_SID}_${DATE}.log
run {

duplicate target database to target_DB FROM ACTIVE DATABASE;

}

exit

EOF



Intiate the Script

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

nohup /oracle/admin//clone/rman_duplicate.sh > rman_duplicate.out 2>&1 &







Monitor the Log file

tail -f duplicate_target_DB_.log



It will take care of complete cloning.









Tuesday, May 10, 2011

Error while installing CRS ...

Error in cluvfy checks:


Check: User equivalence for user "oracle"
Node Name Comment
------------------------------------ ------------------------
node1 passed
node2 failed
Result: PRVF-4007 : User equivalence check failed for user "oracle"



Solution:
Check for the ssh trust between nodes.
There should not be any banner after ssh.
check the user equivalence as a oracle user