ORA-01110 data file 1: system01.dbf recovery

Hello DBA-Mates,

We are back after a long time with ORA-01110: data file 1: '/u01/oracle/oradata/ORA-DATA3/system01.dbf'. Got so many messages and comments as why stopped writing but we didn’t stopped , there was some bad time which slowly passing away and we came back.

Thank you all for your supporting and loving.

Recently, one of our friend faced this below issue and they were not able to recover the database. So, friends before playing with database take the backup always either online or offline.

Here, we have just shown as if we missed system datafile or deleted, how can we recover it and start the database.

1.

Take the offline backup.

SQL> select name,open_mode,log_mode,flashback_on from v$database;

NAME      OPEN_MODE  LOG_MODE     FLASHBACK_ON

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

ORA-DATA3      READ WRITE ARCHIVELOG   NO

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence           4

******************************************

Make some transactions inorder to generate archive logs

(if account is locked then):

SQL> alter user scott identified by tiger account unlock;

SQL> conn scott/tiger

Connected.

SQL> create table student(name varchar2(50)) tablespace users;

Table created.

SQL> begin

  2  for i in 1..300000 loop

  3  insert into student values('10g DBA Training and R12 Apps DBA Training student');

  4  end loop;

  5  commit;

  6  end;

  7  /

*************************************************************

sql>conn /as sysdba;

SQL>  select * from v$recover_file;

no rows selected

SQL> column name format a40

SQL> select name,file#,status from v$datafile;

NAME                                          FILE# STATUS

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

/u01/oracle/oradata/ORA-DATA3/system01.dbf             1 SYSTEM

/u01/oracle/oradata/ORA-DATA3/undotbs01.dbf            2 ONLINE

/u01/oracle/oradata/ORA-DATA3/sysaux01.dbf             3 ONLINE

/u01/oracle/oradata/ORA-DATA3/users01.dbf              4 ONLINE

SIMULATE THE FAILURE OF SYSTEM DATAFILE LOSS

[oracle@sku ORA-DATA3]$ clear

[oracle@sku ORA-DATA3]$ pwd

/u01/oracle/oradata/ORA-DATA3

[oracle@sku ORA-DATA3]$ ls -l syst*

-rw-r----- 1 oracle oinstall 503324672 Sep 14 23:37 system01.dbf

[oracle@sku ORA-DATA3]$ ls

control01.ctl      control04.ctl      redo01.log   redo05a.log  sysaux01.dbf

control01.ctl_old  control04.ctl_old  redo02a      redo05.log   system01.dbf

control02.ctl      example01.dbf      redo02a.log  redo06a.log  temp01.dbf

control02.ctl_old  indx01.dbf         redo02.log   redo06.log   undotbs01.dbf

control03.ctl      redo01a            redo03a.log  redo07a.log  users01.dbf

control03.ctl_old  redo01a.log        redo03.log   redo07.log

[oracle@sku ORA-DATA3]$ mv system01.dbf system01.dbf_old

[oracle@sku ORA-DATA3]$

*********************************************

SQL> shutdown immediate

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/u01/oracle/oradata/ORA-DATA3/system01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size                  2020128 bytes

Variable Size             301993184 bytes

Database Buffers          771751936 bytes

Redo Buffers               14753792 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/oracle/oradata/ORA-DATA3/system01.dbf'

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size                  2020128 bytes

Variable Size             301993184 bytes

Database Buffers          771751936 bytes

Redo Buffers               14753792 bytes

Database mounted.

SQL>

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_

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

ERROR                                                                CHANGE#

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

TIME

---------

         1 ONLINE  ONLINE

                                                                      535300

15-SEP-11

Restore the system01.dbf from Offline Backup

[oracle@sku ORA-DATA3]$ pwd

/u01/oracle/oradata/ORA-DATA3

$cp /u01/oracle/offline_backup/system01.dbf .   ##(This will also work if you restore system01.dbf from online backup)

[oracle@sku ORA-DATA3]$ ls -l *dbf

-rw-r----- 1 oracle oinstall 104865792 Sep 14 23:50 example01.dbf

-rw-r----- 1 oracle oinstall   4202496 Sep 14 23:50 indx01.dbf

-rw-r----- 1 oracle oinstall 251666432 Sep 14 23:50 sysaux01.dbf

-rw-r----- 1 oracle oinstall 503324672 Sep 14 23:56 system01.dbf

-rw-r----- 1 oracle oinstall  20979712 Sep 14 20:12 temp01.dbf

-rw-r----- 1 oracle oinstall  26222592 Sep 14 23:50 undotbs01.dbf

-rw-r----- 1 oracle oinstall  10493952 Sep 14 23:50 users01.dbf

******************

[oracle@sku 2011_09_14]$ pwd

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15

[oracle@sku 2011_09_15]$ ls -alrt

total 33708

drwxr-x--- 3 oracle oinstall     4096 Sep 15 02:47 ..

-rw-r----- 1 oracle oinstall 34460160 Sep 15 02:47 o1_mf_1_1_7726hlvy_.arc

-rw-r----- 1 oracle oinstall     1024 Sep 15 02:48 o1_mf_1_2_7726j9bo_.arc

drwxr-x--- 2 oracle oinstall     4096 Sep 15 02:48 .

*************************************

   

SQL> recover database;

ORA-00279: change 680758 generated at 09/14/2011 20:31:38 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_9_%u_.arc

ORA-00280: change 680758 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 681408 generated at 09/14/2011 20:45:01 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_10_%u_.arc

ORA-00280: change 681408 for thread 1 is in sequence #10

ORA-00278: log file

'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_9_771kpqbq_.

arc' no longer needed for this recovery

ORA-00279: change 681427 generated at 09/14/2011 20:53:03 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_11_%u_.arc

ORA-00280: change 681427 for thread 1 is in sequence #11

ORA-00278: log file

'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_10_771kpqrc_

.arc' no longer needed for this recovery

ORA-00279: change 684925 generated at 09/14/2011 22:00:07 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_12_%u_.arc

ORA-00280: change 684925 for thread 1 is in sequence #12

ORA-00278: log file

'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_11_771onh9m_

.arc' no longer needed for this recovery

Log applied.

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from v$recover_file;

no rows selected

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                     STATUS

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

         1 /u01/oracle/oradata/ORA-DATA3/system01.dbf    SYSTEM

         2 /u01/oracle/oradata/ORA-DATA3/undotbs01.dbf   ONLINE

         3 /u01/oracle/oradata/ORA-DATA3/sysaux01.dbf    ONLINE

         4 /u01/oracle/oradata/ORA-DATA3/users01.dbf     ONLINE

===============================================

IF

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/opt/oracle/oradata/orcl/system01.dbf'

SQL> recover datafile 1;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL>

Some more useful links:

Sharing: A Company's Experience

How to check and get details about patch applied

Important DBA Scripts

R12.2 ADOP Patching Phases Finalize and Cutover

TKPROF Concepts with Syntax and Real time example

Why We are not Rich

How to recover database from rman command with example

Hi DBA-Mates,

We would like give a simple demo for how to recover database from rman backup with example.

It is very simple steps. As we all know these steps but for Quick check we can check it.

Please find the below details.

Backup details:

[oracle@localhost backup]$ ls -ltr

total 816752

-rwxrwxrwx 1 oracle oracle 794894336 Dec 12 05:09 ORCL_20181212_5_1_FULL

-rwxrwxrwx 1 oracle oracle   1114112 Dec 12 05:09 ORCL_20181212_6_1_FULL

-rwxrwxrwx 1 oracle oracle   9797632 Dec 12 05:31 ORCL_20181212_7_1_CONTROL

-rwxrwxrwx 1 oracle oracle  29705728 Dec 12 05:46 ORCL_20181212_8_1_ARCHIVE

 [oracle@localhost backup]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Dec 12 05:58:31 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)

1. RMAN> startup nomount;

database is already started

2. RMAN> restore controlfile from '/home/oracle/backup/ORCL_20181212_7_1_CONTROL';

Starting restore at 12-DEC-18

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl

output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

Finished restore at 12-DEC-18

3. RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

4. RMAN> restore database;

Starting restore at 12-DEC-18

Starting implicit crosscheck backup at 12-DEC-18

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

Crosschecked 3 objects

Finished implicit crosscheck backup at 12-DEC-18

Starting implicit crosscheck copy at 12-DEC-18

using channel ORA_DISK_1

Finished implicit crosscheck copy at 12-DEC-18

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2018_12_12/o1_mf_1_565_g1249wpd_.arc

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 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ORCL_20181212_5_1_FULL

channel ORA_DISK_1: piece handle=/home/oracle/backup/ORCL_20181212_5_1_FULL tag=DATABASE_BKP121218

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:03:37

Finished restore at 12-DEC-18

5. RMAN> recover database;

Starting recover at 12-DEC-18

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 565 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2018_12_12/o1_mf_1_565_g1249wpd_.arc

archived log for thread 1 with sequence 566 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo02.log

archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2018_12_12/o1_mf_1_565_g1249wpd_.arc thread=1 sequence=565

archived log file name=/home/oracle/app/oracle/oradata/orcl/redo02.log thread=1 sequence=566

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

Finished recover at 12-DEC-18

6. RMAN> alter database open resetlogs;

database opened

RMAN>

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

ORCL      READ WRITE

SQL>

Some more useful links:

Performance Issue and Solution

How to increase Java heap size in Hyperion

How to start MRP process on Dataguard -- standbydatabase

Difference between fnd_conc_clone.setup_clean and cmclean

Regards,

Real time Oracle DBA Interview Questions and Answers

Dear DBA-Mates,
Hope you all are doing Good!!! As per time constraint, not able to provide all the Answers but these are the some important Questions which are asked in Ci... MNC company... We will try to close all the answers slowly-slowly...
But for this time please go through the Questions and test yourself...

1) Rac Complete Configuration with prereq steps?

2)What is Voting disk and OCR File?

3) How u will take ocr Backup if u lost?

4)How to check the node connectivity? what is the script used for?

5)What are the advantages of ASM?

6)Why we use raw devices in ASM? can we use any other storage?

7)How to add redo logs in ASM?

8) How many Undo required in RAC?

9)Benefits of RMAN Backups

10)Have u clone RAC to Non RAC?

11)Clone of RAC to RAC..Tell me the difference?

12)Patching on Distributed AD?

13) Tell me some workflow background process?

14)How to generate DBC file?

15)For Discoverer What is required to set the profile?

16)SSO,OID ..u know?

17)U know Weblogic?

18)U know SOA, Application server?

19)what all the ad utilities u used ?

20)What are the different Status of Worker?

21)U know about events?

22)Let me different Ad options?

23)Flow of apps when u login ?

24)What is the use of DBC File and Tnsnames.ora

what is the difference here...both used for connectivity?

25)PCP Concepts ..how to implement?

26)where do you increase the process for a concurrent Manager

for standard manager?

27)Let the Major difference for 11i and R12?

28)Upgrade process 11.5.9 to R12?

29)How to you licence a product? what is the purpose of adsplice? what is the difference of the both?

30)Have u applied HRMS or Legislative Patches?

31)RAC node ,How do you patch?

32)RAC to Non RAC Clone? Explain and what are the major difference?

33)What exactly happens when any user connect to database?

34)How many undo's required in RAC?

35)What is $TWO_TASK

36)You do Rman Clones?

37)How to do save customizations, What u run autoconfig?

38)How to configure JVM?

39)What is JVM?

40)Have u faced any performance issues After upgrade?

41)what are the services running in R12(1st node - CM and DB

and 2nd node - web and forms

42)Custom_code Creation Steps

43) What are the things that can be done on OAM?

44) Mention some Workflow Tables?

45)why u rebuild workflow tables ?and How?

46)10g Discoverer can be configured on 11?
Hope this may useful...
Regards,

How to Restore DR database from RMAN

Dear DBA-Mates,

We would like to share some rough work for "How to Restore DR database" which can be helpful here for quick reference.

First we need to set the value for log_archive_dest_state_2 paramater as shown below in Primary Node.

In Primary Seerver:

SQL>alter system set log_archive_dest_state_2=DEFER sid='*';

Now, we should Physically check all the archive, once the full backup completed, as is available either in primary or in standby.

Then disable the archive purge cronjob in standby.

Also, Stop if any 3rd party snapsync jobs is running. In our case we don't have any.

Now, we will login to Standby and will start restore:

1. We will restore controlfile from production backup controlfile.

Login rman as:

$rman target /

rman>

restore standby controlfile from  '/rmanbk01/ora-data/backup/PRD_20180621_1919_1_CONTROL';

rman>

2. Start database in mount stage:

rman>alter database mount;

3. Start recovering from catalogs.

rman>catalog start with '/rmanbk01/PRD/backup/';

4. Restore the database.

rman>RUN

{

ALLOCATE CHANNEL c1 DEVICE TYPE disk;

ALLOCATE CHANNEL c2 DEVICE TYPE disk;

ALLOCATE CHANNEL c3 DEVICE TYPE disk;

ALLOCATE CHANNEL c4 DEVICE TYPE disk;

ALLOCATE CHANNEL c5 DEVICE TYPE disk;

ALLOCATE CHANNEL c6 DEVICE TYPE disk;

ALLOCATE CHANNEL c7 DEVICE TYPE disk;

restore database;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

release channel c5;

release channel c6;

release channel c7;

}

5. Once restore completed, we can check archive and start the catalog.

rman>catalog start with  'path/archivelog';

6. Now, we can start the MRP process as shown below:

rman>alter database recover managed standby database disconnect;

7. From below Query, we can monitor the restore process.

select SID,OPEN_TIME, (sysdate-open_time)*24*60 ela_time,close_time,filename,

(TOTAL_BYTES/1024/1024/1024) GBTOBEREAD,(bytes)/1024/1024/1024 GB_sofar  from V$BACKUP_ASYNC_IO where filename like '/path/datafile%';

Some more useful links:

How to run SQLT for a sqlid in Oracle

ORA-07391:sftopn: fopen error, unable to open text file

RMAN Issues And Solutions Troubleshooting

Database Prechecks Script before clone
Regards,

How to start MRP process on Dataguard -- standby database

Hi DBA-Mates, Hope you all are doing Great!!!

Usually, we have faced this issue Archive logs not applied on physical standby and this is most important error/issue in our DBA task.

Also, 99% people ask this question in the interviews.

We would like to share our real time scenario here which may useful and helpful to everyone.

Whenever we faced this type of issue, we need to check some important below points:

1. We should check the status of database, which should be mounted status.

SQL> select name, open_mode from v$database;

It may also show in Open (Read-only) mode because it may open for any backup or reporting purpose.

2. Then we need to check the MRP process is running or not as shown below command:

1. Check the MRP process is running or not?

$ ps -ef|grep mrp

$

2. In our case MRP is down. If the MRP process is not running then we need to START the MRP to sync from below command.

As per DBA guidance, we should always cross verify the correct instance and database.

$hostname; id

$echo $ORACLE_SID

PRD02

$sqlplus '/as sysdba'

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL>

Once you start the MRP then archive log will start to sync.

Alert log error details: Providing below alert details , so we can see how the above command is working...

$ cd /p01/app/oracle/diag/rdbms/ora-data/ORA-DATA/trace

$ view alert_ORA-DATA.log

Fri Nov 02 08:41:14 2018

Archived Log entry 1014432 added for thread 1 sequence 608497 rlc 810827692 ID 0x8c1e6f64 dest 2:

RFS[2]: No standby redo logfiles available for thread 1

RFS[2]: Opened log for thread 1 sequence 608499 dbid -1944187801 branch 810827692

Fri Nov 02 12:42:47 2018

alter database recover managed standby database disconnect

Attempt to start background Managed Standby Recovery process (ORA-DATA)

Fri Nov 02 08:42:47 2018

MRP0 started with pid=54, OS id=7948

MRP0: Background Managed Standby Recovery process started (ORA-DATA)

 started logmerger process

Fri Nov 02 08:42:53 2018

Managed Standby Recovery not using Real Time Apply

Fri Nov 02 08:42:53 2018

Reconfiguration started (old inc 4, new inc 6)

List of instances:

 1 2 (myinst: 1)

 Global Resource Directory frozen

 Communication channels reestablished

 Master broadcasted resource hash value bitmaps

 Non-local Process blocks cleaned out

Fri Nov 02 08:42:53 2018

 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

Fri Nov 02 08:42:53 2018

 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

Fri Nov 02 08:42:53 2018

 LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived

 Set master node info

 Submitted all remote-enqueue requests

 Dwn-cvts replayed, VALBLKs dubious

 All grantable enqueues granted

 Submitted all GCS remote-cache requests

 Fix write in gcs resources

Reconfiguration complete

Parallel Media Recovery started with 32 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Completed: alter database recover managed standby database disconnect

Fri Nov 02 08:42:57 2018

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608453.131634.991106577

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_2_seq_423209.63201.991105597

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_2_seq_423210.119301.991106405

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608454.137049.991106667

Fri Nov 02 08:43:12 2018

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608455.141399.991107219

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608456.122183.991107343

Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_2_seq_423211.111046.991107213

Fri Nov 02 08:43:24 2018

To Stop the MRP process:

$sqlplus '/as sysdba'

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>

Next – How to check Archive Gap and details…(soon)

Some more useful links:

Difference between fnd_conc_clone.setup_clean and cmclean

How to deploy RPD in OBIEE 11g

Concurrent Managers are down error insert_fcp failed due to ORA-01403 no data found, ORA-06512

Oracle Apps DBA Interview Questions and Answers for experienced asked Top MNC company

Regards,