Skip to main content

Special Scenario - Loss of Datafile (non-critical) without any backup and in Noarchivelog mode

 We faced below interesting issue in one of our test environments, I call it SPICE19 here. One of the datafile was deleted with the intent to recreate it. Unfortunately, database was up and running at that moment when the datafile was deleted at OS level.

Observations -

1. Database SPICE19 was in noarchivelog mode.

2. No backups were available for restoration.

3. Datafile '/u02/oracle/oradata/SPICE19/users06' was created accidentally with a wrong name (should have been /u02/oracle/oradata/SPICE19/users06.dbf)

4. It was then removed using (rm) at OS level.

5. A new datafile was added with the name - /u02/oracle/oradata/SPICE19/users06.dbf

6. Database got crashed searching for datafile - /u02/oracle/oradata/SPICE19/users06

7. Database complained for missing datafile - /u02/oracle/oradata/SPICE19/users06 as the information was in controlfile.

 

Troubleshooting Steps -

1. Missing datafile id was found below -

SQL>  select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                              CHANGE# TIME          CON_ID

---------- ------- ------- ------------------------------------------ --------- ----------

        18 ONLINE  ONLINE  FILE NOT FOUND                           0                    0

 

2. Second step was to recreate missing datafile with a different name-

SQL> alter database create datafile '/u02/oracle/oradata/SPICE19/users06' as '/u02/oracle/oradata/SPICE19/users06_new.dbf' reuse;

Database altered.

 

3. As the database was in norachivelog mode, recovery is not possible UNLESS complete information is present in the REDO logs and they are not overwritten.

 

4. We have 3 redo logs here,

/u02/oracle/oradata/SPICE19/redo03.log

/u02/oracle/oradata/SPICE19/redo02.log

/u02/oracle/oradata/SPICE19/redo01.log

 

5. Tried applying them in the following sequence with incomplete recovery -

SQL> recover database using backup controlfile until cancel;

/u02/oracle/oradata/SPICE19/redo01.log

/u02/oracle/oradata/SPICE19/redo02.log

Log applied.

Media recovery complete.

 

6. Opened the database with resetlogs option

 

Correct Approach -

We must rename datafile with below 2 steps.

1. Tablespace must be offline (no users can access the data during this period)

alter tablespace USERS offline immediate;

 

2. Rename the datafile at OS level using mv command for Linux OS.

mv /u02/oracle/oradata/SPICE19/users06 /u02/oracle/oradata/SPICE19/users06.dbf

 

2. Rename the datafile using below command to update the controlfile at database level -

ALTER TABLESPACE users rename datafile '/u02/oracle/oradata/SPICE19/users06'

to '/u02/oracle/oradata/SPICE19/users06.dbf';

 

Conclusion-

We were lucky to have the redo information in the redo logs and they were not overwritten. This was a rare case where recovery of a datafile was possible with database in noarchivelog mode and with no backups available.


Comments

Post a Comment

Popular posts from this blog

Logfile locations in EBS r12.1 and EBS r12.2

Startup/shutdown Apps tier services are started and stopped frequently and we must know logfiles when troubleshooting startup/shutdown issues. $INST_TOP/logs/appl/admin/log $INST_TOP/logs/appl/admin/log Apache OHS being part of opmn in r12.1 has continued in r12.2. Logfile locations for troubleshooting have been changed $INST_TOP/logs/ora/10.1.3/Apache/error_log[timestamp] $INST_TOP/logs/ora/10.1.3/opmn/HTTP_Server~1.log $IAS_ORACLE_HOME/instances/*/diagnostics/logs/OHS/*/*log*   OPMN Logfile locations for r12.1 and r12.2 have been changed $INST_TOP/logs/ora/10.1.3/opmn/opmn* $IAS_ORACLE_HOME/instances/*/diagnostics/logs/OPMN/opmn/* Oacore oacore in r12.1 is oc4j component and part of 10gAS. However, in r12.2, oacore is now a managed server for weblogic server $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore*/ $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore*/ $LOG_HOME/ora/10.1.3/opmn/oacore*/oacor...
1 comment
Read more

Query to Check AD and TXK code levels in your EBS environment

Below query can be very handy in finding out current AD and TXK code levels. col ABBREVIATION for a10 set lines 1000 col NAME for a50 col CODELEVEL for a20 SELECT ABBREVIATION,NAME,codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad'); ABBREVIATI NAME                                                CODELEVEL ---------- -------------------------------------------------- ------------ ad           Oracle Applications DBA                             C.11 txk         Oracle Applications Technology Stack    ...
1 comment
Read more

Compile all JSP files in Oracle ebs r12.2

Before you start compiling jsps and following below steps, I recommend understanding some key differences between 11i, r12.1 and r12.2 when it comes to compiling jsps. Please follow below link and then proceed further - One-stop shop to Compile JSPs in 11i, r12.1 and r12.2 1. Take a backup of _pages directory that will be modified due to jsp compilation - $ cd $EBS_APPS_DEPLOYMENT_DIR/oacore/html/WEB-INF/classes/ $ cp -R _pages _pages29dec2019 $ ls -ld _pages* drwxr-xr-x 5 applmgr oinstall 249856 Dec 29 16:36 _pages drwxr-xr-x 5 applmgr oinstall 249856 Dec 29 16:56 _pages29dec2019 2. Stop apache, oacore and oafm services - adapcctl.sh stop admanagedsrvctl.sh stop oacore_server1 admanagedsrvctl.sh stop oafm_server1 3. Compile the jsps manually using the below command - $ cd $FND_TOP/patch/115/bin/ $ perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p              4. Check class file last mo...
Post a Comment
Read more