Friday, February 6, 2009

RAC : SPFILE configuration

Query : consider RAC setup having 2 nodes. first node showing the value of spfile parameter while on the second node it display as null value. spfile is on shared location.
Solution : although this config. is working without any error upto restart both the nodes individually but recommended is : both nodes are to provides the value for spfile parameter shoulb be same,because as this spfile is at shared location.
Edit this value on node2 as below:
(login to : 192.175.18.16)
SQL> !hostname
node1
SQL> show parameter spfile
NAME TYPE VALUE
------- ------- -------------------------------------------------------------------
spfile string /u001/DB_Config/spfile/testdb/spfiletestdb.ora
*NOTE : this /u001 is mount point for shared location...
(login to : 192.175.18.17)
SQL> !hostname
node2
SQL> show parameter spfile
NAME TYPE VALUE
--------- ------ ------------------------------------------------------------
spfile string
how to enable spfile value on both the nodes?
check the below config.
From node1:
1)check the value of init file:
$cat $ORACLE_HOME/dbs/initnode1.ora
SPFILE='/u001/DB_Config/spfile/testdb/spfiletestdb.ora'
From node2:
1)check the value of init file:
$cat $ORACLE_HOME/dbs/initnode2.ora
node2.__db_cache_size=1493172224
node2.__java_pool_size=16777216
node2.__large_pool_size=16777216
node2.__shared_pool_size=419430400
node2.__streams_pool_size=0
*.cluster_database_instances=2
*.cluster_database=true......
Needs to create new init file for node2 which is having the same value of spfile as node1 have it:
1)create new initfile for node2:
$vi initnode2.ora
SPFILE='/u001/DB_Config/spfile/testdb/spfiletestdb.ora'
2)shutdown node2 instance
SQL>shut immediate
3)startup this node2 and check the spfile parameter
SQL> show parameter spfile
NAME TYPE VALUE
------- ------- -----------------------------------------------------------
spfile string /u001/DB_Config/spfile/testdb/spfiletestdb.ora

Thursday, February 5, 2009

Recovery : Redolog group (Current) :- without having backup

How to recover of currently dropped or crashed redo log without having any backup...
1) check the status through v$log
SQL> select group#,sequence#,bytes,archived,status from v$log;
2) check the log mode of database
SQL> select name,open_mode,log_mode from v$database;
3) create new controlfile and loss of data thats having within that corrupted group
SQL> alter database backup controlfile to trace as 'path for new ctl file';
4) use this above created controlfile and open the database with resetlog option

Recovry : Redolog group (Current) :- having backup

Current group ie. on which LGWR is currently writing and it gets corrupted or dropeed by mistake then do recover as mentioned below:
1) check the status of redo group by v$log.
2) shut immediate
3) copy that missing redo from backup location
4) startup mount
5) recover database until cancel
6) alter database open resetlogs
7) check the sequence it started from zero.

Recovery : Redolog group (Inactive)

1) check the status through v$log, if its inacitve then
2) alter database clear logfile group 5;
---particular redo group: this clear and again create the redo group automatically.
3) alter database open
--- check the sequence from v$log, no need to doing any startup/shutdown in between....