Recovery - Media recovery vs Corrupted Recovery

Today I faced a recovery issue with one of my database.
Media Recovery:
Database Name: xxxxxx
OS : SunOs
Alertlog Error Message:
CJQ0: terminating instance due to error 472
Instance terminated by CJQ0, pid = 9963
Step 1 :When manually tried to start the database below error occured.
Cause:
SQL> startup open
ORACLE instance started.
Total System Global Area 338657376 bytes
Fixed Size 729184 bytes
Variable Size 234881024 bytes
Database Buffers 100663296 bytes
Redo Buffers 2383872 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/u02/oradata/xxxxxx/system01.dbf'
Step 2: Verified all the archivelogs are in place and recovered the database.
SQL> recover database;
Media recovery complete.
SQL> alter database open
2 /
NAME OPEN_MODE LOG_MODE
--------- ---------- ------------
xxxxxx READ WRITE ARCHIVELOG

dbms_jobs execution.

Condition to be checked :
1) Check for job_queue_processess is more than 0 ( if it set to zero , no job will be running at database level).
2) Run the job as the job owner.
3) Check for the database instance is in restricted mode.

apex installation in oracle 10.2.0.4

Introduction about Apex :
http://www.oracle.com/pls/ebn/swf_viewer.loadp_shows_id=6392594&p_referred=0&p_width=800&p_height=600

will be continued......

creating user using shell script on particular server

Scenario : Need to create a shell script to create user in the database which is running on that following server.

Script1 : dbcreate.sh

#!/bin/ksh
#script name dbcreate.sh

for SID in `cat /etc/oratab | grep -v "#"| grep ":"| awk -F: '{print $1}'`
do
#export ORACLE_SID=${SID}
. setdb ${SID} auto #env variable
sqlplus /nolog <
connect / as sysdba;
@usercreation.sql;
EOF
done

Script 2 : users.sql

SET ECHO OFF;
SET NEWPAGE 0;
SET SPACE 0;
SET PAGESIZE 0;
SET FEEDBACK OFF;
SET TRIMSPOOL ON;
SET TAB OFF;
SET LINESIZE 300;
CREATE USER IDENTIFIED BY
ACCOUNT LOCK;
GRANT DBA TO ;
spool on;
select name from v$database;
select username,account_status from dba_users where username in 'xxx';
spool off;
host cat on.lst >> created_user_list.lst
host rm on.lst
exit;

Archive Mount Point 100% percent filled up

If your archive mount is 100% filled up.

what are the action we need to take?

Step 1 : Move some of the archive sequence to another mount point

Ex : /opt/oracle/arch/demo ---> is my archive log destination
/u01/oracle/backup ----> is the temporary destination where I m moving my archivelog files

Note : Be careful while moving the archive log files to other mount because it might
got corrupted. So do not try to move the archive log file which is in "ACTIVE"
for safer try to leave 4 to 5 archive log files from

consider you have the current log sequence is 134.so the previous archive log file sequence is 133

$ ls -ltr /opt/oracle/arch/demo/1_13*
(It will display the current archive logfile. Dont try to move this file seq starts with 131,132,133 )
$ cd /opt/oracle/arch/demo;ls -ltr 1_1*
$ mv 1_12* /u01/oracle/backup/.

Note: After moving the archive files your Database Starts works Normally

Step 2 : Now catalog the Archivelog files moved to new location

RMAN> CATALOG STARTS WITH '/u01/oracle/backup';
RMAN> CROSSCHECK ARCHIVELOG ALL;

Step 3 : Now Kick the archivelog backup (if you have script just execute that script)

RMAN> run
{
backup archivelog all delete input;
}

How to grep a particular string and list all the files

$cd $ORACLE_HOME/dbs; grep user_dump_dest initxxxdev.ora
Output:
user_dump_dest=/u01/app/oracle/admin/initxxxdev/udump
$cd /u01/app/oracle/admin/initxxxdev/udump; grep 'CREATE CONTROLFILE' *\ awk -F: '{print $1}'xargs ls -ltr
Output:
-rwx-rwx--- Oracle Oinstall 4499 Nov 12:30 xxxdev_ora_4515.trc

Undo tablespace recovery

Follow the below steps, If the database is up and running

You can see this error in your alert log file:

ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []
SMON: mark undo segment 29 as needs recovery

ORACLE Instance test2 (pid = 11) - Error 600 encountered while recovering
transaction (29, 42) on object 36.

Step 1 : Create a new undo tablespace

create undo tablespace undotbs2 datafile
‘/opt/oracle/oradata/prod003/undotbs2.dbf’ size 300m;

Step 2: Find the problematic undo segment using the below command

sql>select SEGMENT_NAME, STATUS from dba_rollback_segs;

Step 3: Alter the spfile to use the currently created undotbs2.

sql>alter system set undo_tablespace=undotbs2 scope=spfile;

Step 4 : create a pfile from the spfile

sql> create pfile from spfile;

Step 5 : Append the problematic rollback segements in the pfile.

_offline_rollback_segments=” segment_name from dba_rollback_segs"
_corrupt_rollback_segments=”segment_name from dba_rollback_segs”


Step 6 : shutdown the database using immediate or abort.

Step 7 : startup using pfile.

Step 8 : bring the tablespace offline and drop the corrupted undo tablespace .

sql> alter tablespace undotbs1 offline;
sql> drop tablespace undotbs1 including contents and datafile;


More Info Refer :

http://www.dba-oracle.com/oracle_tips_fix_corrupt_undo_segments.htm

ORA-19625: error identifying file

Archive Sweep got failed :

Found many archive backups got failed during the timeStamp [arch_09012010_18 hours*( 6.00pm PST) - 18:30(6.20pm PST)]
and results in the following error

RMAN-03002: failure of backup command at 01/09/2010 18:22:41
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u01/oracle/xxxxxx/10.2.0/dbs/arch/1_7631_677955387.dbf
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

Cause :
Recovery Catalog Database : rcvcat
Target Database Name : xxxxxx

Here two backups for the database (xxxxxx) got kicked automatically.
first one started at 18:16:46sec and the second archive backup started at 18:16:49sec.
Archive backup which ran first, successfully archived the following file 1_7631_677955387.dbf and
in a fraction of second the second archive backup tried to backup the same file,
which leads to this error.
ORA-19625: error identifying file /u01/oracle/ascpconv/db/tech_st/10.2.0/dbs/arch/1_7631_677955387.dbf
ORA-27037: unable to obtain file status.
(this is the same file backed up and deleted by the first running archive backup(started at 46sec)).

Its Purely, crontab timing problem.

History :

oracle@
rcvcat:/opt/oracle/rcvcat/10.2.0/admin/rcvcat/log/xxxxxx> ls -ltr arch_09012010_18*
-rw-r----- 1 oracle oinstall 2204 Jan 9 18:22 arch_09012010_18:16:49.log
-rw-r----- 1 oracle oinstall 2542 Jan 9 18:24 arch_09012010_18:16:46.log

OUTPUT OF arch_09012010_18:16:46.log :

archive log filename=/u01/oracle/xxxxx/db/10.2.0/dbs/arch/1_7631_677955387.dbf recid=8604 stamp=707854752
Finished backup at 09:01:2010 18:21:33

OUTPUT OF arch_09012010_18:16:49.log :

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u01/oracle/xxxxxx/dbs/10.2.0/dbs/arch/1_7631_677955387.dbf
ORA-27037: unable to obtain file status

Impact :

No impact on database as the archivelog file is already backed up successfully.