Friday, 22 January 2016

Recover Datafile from Hot Backup
==========================


[oracle@test ~]$ cd /u01/app/oracle/oradata/proddb/

[oracle@test proddb]$ ls -l
total 1035664
-rw-r----- 1 oracle oinstall   7061504 Jul 27 10:00 control01.ctl
-rw-r----- 1 oracle oinstall   7061504 Jul 27 10:00 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 Jul 27 10:00 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Jul 27 09:56 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jul 27 09:59 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jul 27 09:49 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul 27 09:49 redo03.log
-rw-r----- 1 oracle oinstall 241180672 Jul 27 09:56 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Jul 27 09:56 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jul 25 15:11 temp01.dbf
-rw-r----- 1 oracle oinstall  26222592 Jul 27 09:56 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Jul 27 09:56 users01.dbf


SQL> startup

SQL> alter database begin backup;

Database altered.

Let's Copy take backup of datafiles.

[oracle@test proddb]$ cp *.dbf /u01/coldbkp/
[oracle@test proddb]$

Now we can check which files is been in backup state:

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                 485758 06-JAN-16
         2 ACTIVE                 485758 06-JAN-16
         3 ACTIVE                 485758 06-JAN-16
         4 ACTIVE                 485758 06-JAN-16
         5 ACTIVE                 485758 06-JAN-16


Let's close the backup state.

SQL> alter database end backup;

Database altered.

Well now I will shutdown my database end drop one datafile.

Remember in this case didn't  happens any log switch.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

I will remove USERS01.DBF :

[oracle@test proddb]$ rm users01.dbf

And let's Start.

SQL> startup

Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/proddb/users01.dbf'

As expect  error, ok in this case my database will be in MOUNT state so I just need to restore the file manually.

[oracle@test coldbkp]$ cp users01.dbf /u01/app/oracle/oradata/proddb/

If you try to open directly.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/proddb/users01.dbf'

SQL> recover datafile 4;
Media recovery complete.

SQL> alter database open;

Database altered.

Done.

Posted by at No comments:

FIND Concurrent request details from OS PID or Oracle SID

For the particular Day:
=================
select * from fnd_concurrent_requests
where 1=1
and oracle_process_id=&OS_PID
and trunc(request_date)=trunc(
sysdate);

For all Days:
=========
select * from fnd_concurrent_requests
where 1=1
and oracle_process_id=&OS_PID

FULL Deatils from OS PID
====================
select * FROM v$process p, v$session s, fnd_concurrent_requests f
 WHERE s.paddr = p.addr /*and s.status = 'ACTIVE'*/
   AND s.username NOT LIKE '%SYS%'
   AND p.spid IN (SELECT oracle_process_id
                    FROM fnd_concurrent_requests
                   WHERE 1 = 1 AND oracle_process_id = &os_pid);

Complete Detials with filtering for the OS PID
===================================
SELECT trunc(f.ACTUAL_START_DATE) "Actual Start Date", s.LOGON_TIME,p.spid "OS PID", s.SID, s.serial#, s.action, s.username, s.status, s.program,
       p.program, s.module, s.lockwait, s.state, s.sql_hash_value,
       s.schemaname, s.osuser, s.machine, s.last_call_et, p.program,
       p.terminal, logon_time, module, s.osuser , f.request_id,  f.request_date,
        f.completion_text, f.outcome_product,
        f.logfile_node_name, f.outfile_name, argument_text,
       f.outfile_node_name, f.oracle_id, f.concurrent_program_id,
       f.responsibility_application_id, f.responsibility_id,
       f.last_update_login, f.nls_language, f.controlling_manager, f.actual_start_date,f.actual_completion_date
  FROM v$process p, v$session s, fnd_concurrent_requests f
  WHERE s.paddr = p.addr /*and s.status = 'ACTIVE'*/
   AND s.username NOT LIKE '%SYS%'
   AND p.spid=f.oracle_process_id
   order by actual_start_date desc;


Details for a specific OS PID
=======================

SELECT p.spid, s.SID, s.serial#, s.action, s.username, s.status, s.program "Session Program",
       p.program "Process Program, s.module, s.lockwait, s.state, s.sql_hash_value,
       s.schemaname, s.osuser, s.machine, s.last_call_et, p.program,
       p.terminal, logon_time, module, s.osuser , f.request_id,  f.request_date,
        f.completion_text, f.outcome_product,
        f.logfile_node_name, f.outfile_name, argument_text,
       f.outfile_node_name, f.oracle_id, f.concurrent_program_id,
       f.responsibility_application_id, f.responsibility_id,
       f.last_update_login, f.nls_language, f.controlling_manager, f.actual_start_date,f.actual_completion_date
  FROM v$process p, v$session s, fnd_concurrent_requests f
 WHERE s.paddr = p.addr /*and s.status = 'ACTIVE'*/
   AND s.username NOT LIKE '%SYS%'
   AND p.spid IN (SELECT oracle_process_id
                    FROM fnd_concurrent_requests
                   WHERE 1 = 1 AND oracle_process_id = &os_pid);
Posted by at No comments:

Steps to recover data file block corruption using data file from standby
===================================================


Scenario :
========

Block corruption occurs in primary datafile , we dont have recent  archivelogs and  backups


Error :
=====

We receive the below error in alert log stating the file 52 block 192655 is corrupted


ORA-01578: ORACLE data block corrupted (file # 52, block # 192655)
Reread of blocknum=192655, file=xx /xbol_data5.dbf. found same corrupt data
Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data
Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data
Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data
Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data
Reading datafile 'xx/xbol_data5.dbf' for corruption at rdba: 0x0d02f08f (file 52, block 192655)
Reread (file 52, block 192655) found same corrupt data (no logical check)

Solution :
========

Please follow the below steps as per given order to recover the datafile.

1. PRIMARY SIDE

Step 1 :   switch logfile in primary node

alter system switch logfile;

Step 2:

select status,instance_name,database_role,open_mode from v$database,v$instance;

Step 3 :

RMAN > validate check logical database;
SQL>      select * from v$database_block_corruption;

Step 4 :

select file_id,file_name,tablespace_name from dba_data_files where file_id=57;

Step 5 :

select max(sequence#) from v$archived_log;

2. ON STANDBY DATABASE

Step 1 :  

select status,instance_name,database_role,open_mode from v$database,v$instance;

Step 2:

select max(sequence#) from v$archived_log where applied='YES';

3. ON PRIMARY DATABASE

Step 1 :  

shu immediate;

Step 2:

mv xx/a_media07.dbf xx/a_media07.dbf_06Octbkp


4. ON STANDBY DATABASE

Step 1 :  

backup as copy datafile 57 format 'xx/a_media07.dbf';

Step 2:

scp xx/ a_media07.dbf oraprod@144.11.7.15:xx/a_media07.dbf

5. ON PRIMARY DATABASE

Step 1 :  

startup mount

Step 2:

catalog start with 'xx/a_media07.dbf';

Step 3 :

restore datafile 57;

Step 4 :

recover datafile 57;

Step 5 :

alter database open;

Step 6 :

backup validate check logical database;
           OR
validate check logical datafile 57;

Step 7 :

select * from v$database_block_corruption;

Step 8 :

select max(sequence#) from v$archived_log;

6. ON PRIMARY DATABASE

Step 1 :

select status,instance_name,database_role,open_mode from v$database,v$instance;

Step 2 :

select max(sequence#) from v$archived_log where applied='YES';


Posted by at No comments:
Subscribe to: Comments (Atom)