Loss of a Non system Data file recovery scenarios

By -


ERROR:

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u03/apps/oracle/oradata/PROD/users01.dbf'

Scenario 1:

Step 1: Start the database if necessary

Step 2: Query the V$RECOVER_FILE

SQL> select * from v$recover_file;

Step 3: Taking the Missing datafile offline mode

SQL> alter database datafile '/u03/apps/oracle/oradata/PROD/users01.dbf' offline;

Step 4: Restore datafile

RMAN> restore datafile 4;
RMAN> recover datafile 4;

Step5: Bring datafile online

SQL> alter database datafile '/u03/apps/oracle/oradata/PROD/users01.dbf' Online;

Step 6: Open database



ERROR:

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u03/app/oradba/oradata/VIRRAN/data01.dbf'


Scenario 2:

Solution:

[oradba@vtebz1 VIRRAN]$ rman target /


RMAN> list failure;


using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
121022     HIGH     OPEN      04-APR-18     One or more non-system datafiles are                                           corrupt

RMAN> advise failure;


List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
121022     HIGH     OPEN      04-APR-18     One or more non-system datafiles are                                           corrupt

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 5
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u03/app/oradba/diag/rdbms/virran/VIRRAN/hm/reco_306628433.hm

RMAN> repair failure;


Strategy: The repair includes complete media recovery with no data loss
Repair script: /u03/app/oradba/diag/rdbms/virran/VIRRAN/hm/reco_306628433.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 5;
   recover datafile 5;
   sql 'alter database datafile 5 online';

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 04-APR-18
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u03/app/oradba/oradata/VIRRAN/d                                          ata01.dbf
channel ORA_DISK_1: reading from backup piece /u03/archive/VIRRAN/backupset/2018                                          _04_04/o1_mf_nnndf_TAG20180404T020134_fd8tkz52_.bkp
channel ORA_DISK_1: piece handle=/u03/archive/VIRRAN/backupset/2018_04_04/o1_mf_                                          nnndf_TAG20180404T020134_fd8tkz52_.bkp tag=TAG20180404T020134
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 04-APR-18

Starting recover at 04-APR-18
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 04-APR-18

sql statement: alter database datafile 5 online
repair failure complete

Do you want to open the database (enter YES or NO)? yes
database opened

RMAN> exit

Recovery Manager complete.

[oradba@vtebz1 VIRRAN]$ sqlplus / as sysdba

SQL> shu immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1.0122E+10 bytes
Fixed Size                  2237088 bytes
Variable Size            1644170592 bytes
Database Buffers         8455716864 bytes
Redo Buffers               19468288 bytes
Database mounted.
Database opened.


Comments

Post a Comment

Popular posts from this blog

How to troubleshoot long running concurrent request in R12.2

By -
Normally we are getting concurrent program running longer and concurrent request not picking jobs, etc. those are very frequent issue we expected from development team. Collect below basis level of information to development team 1 Oracle seeded program/Custom program? 2 How much time it used earlier? 3 Is there any recent code change done in concurrent program? 4 Is this program fetching higher data compare to last run? 5 Does this job running any specific time/ It can be run any time? 6 Does this job fetching data using DB link? 7 Does the problem happen on both the test and production instance? Once asked above questions to development team in meanwhile we need to start basic sanity check in our environment. 1 Verify the status of the concurrent program Copy select REQUEST_ID,phase_code,status_code,ORACLE_SESSION_ID from apps.fnd_concurrent_requests where request_id=’1234567’;
Read more

How to run Gather Schema Statistics in R12.2

By -
Image
What is Gather Schema Statistics? Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions. The cost-based optimization (CBO)  uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan. Why Run the Gather Schema Statistics? When Data is update, insert, delete in table of user end Like (Technical User ,Finance User ,Ect) It become necessary to run the Gather Schema Statistics It’s recommended to Run GSS in Weekly Once or twice. How to run Gather Schema Statistics? Login application with sysadmin user Submit Request Window Navigate to: Concurrent > Requests Enter the parameters This can be run for specific schemas by specifying the schema name or enter ‘ALL’ to gather statistics for every schema in the database Submit the Gather Schema Statistics program Parameters: Schem
Read more

How to compile forms in R12.2

By -
STEP 1--> Login to app server STEP 2--> Go to  directory $AU_TOP/forms?US STEP 3--> Move to ".fmb" file STEP 4--> Execute the below command to generate ".fmx" frmcmp_batch userid=apps/<apps_pwd> module=<form_name>.fmb output_file=<form_name>.fmx module_type=form batch=no compile_all=special (EX):-   frmcmp_batch userid=apps/apps module=JAINTAXT.fmb output_file=$JA_TOP/forms/US/JAINTAXT.fmx module_type=form batch=no compile_all= special (O/p):   Created form file /oracle/CLONE/apps/fs2/EBSapps/appl/ja/12.0.0/forms/US/ JAINTAXT.fmx Refer: R12: How to Compile a Form in Release 12 (Doc ID 1085928.1)
Read more