How to recover a corrupted/damaged/lost/canceled NONSYSTEM datafile

How to recover a corrupted/damaged/lost/canceled NONSYSTEM datafile (database in ARCHIVELOG mode)

Tweet 

The following scenario describes what happens when a nonsystem datafile is lost. Before proceeding be sure to have a complete backup of your test database and be sure your database is in ARCHIVELOG mode. 

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL> startup

SQL> archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

 rman target /
RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               YES     /u01/app/oracle/oradata/orcl/system01.dbf
3    620      SYSAUX               NO      /u01/app/oracle/oradata/orcl/sysaux01.dbf
4    75       UNDOTBS1             YES     /u01/app/oracle/oradata/orcl/undotbs01.dbf
5    1243     EXAMPLE              NO      /u01/app/oracle/oradata/orcl/example01.dbf
6    5        USERS                NO      /u01/app/oracle/oradata/orcl/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/app/oracle/oradata/orcl/temp01.dbf

SQL> set pagesize 999
SQL> set linesize 180
SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME from dba_tables
2  where owner = 'HR';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ -----------------------
HR                             REGIONS                    EXAMPLE
HR                             LOCATIONS                     EXAMPLE
HR                             DEPARTMENTS                    EXAMPLE
HR                             JOBS                          EXAMPLE
HR                             EMPLOYEES                   EXAMPLE
HR                             JOB_HISTORY                    EXAMPLE
HR                             COUNTRIES                      EXAMPLE

What does it happens when I delete the datafile where EXAMPLE tablespace is based on.All the tables which is belong to EXAMPLE  tablespace is also deleted.

 I am moving my EXAMPLE datafile in different name.

[oracle@localhost orcl]$ mv /home/oracle/app/oracle/oradata/orcl/users01.dbf /home/oracle/app/oracle/oradata/orcl/users01_damaged.dbf

I'm still able to query the dictionary tables for example but...

SQL> select count(*) from dba_tables;                 

COUNT(*)
----------
3013

I obtain an error when I try to select some rows from the HR.EMPLOYEES table.

SQL> select count(*) from hr.EMPLOYEES;
select count(*) from hr.EMPLOYEES  *
                       
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Now Check Alert Log file..

Looking at the  same clear error: the systen is not able to obtain status information on file '/u01/app/oracle/oradata/orcl/example01.dbf'

[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log 
...
Thu Jul 19 07:07:49 2012
Errors in file
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_7476.trc:
ORA-01116: error in opening database file 5
ORA-01110:data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
...

If you try to connect with RMAN the REPORT SCHEMA command is now unable to correctly size the EXAMPLE tablespace. RMAN says its size is 0.

 rman target /
RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    1325        USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5  0      EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

Because theEXAMPLE  tablespace is not a system tablespace, we can recover it just putting it in 

offline mode.

RMAN> sql 'alter database datafile 5 offline';

sql statement: alter database datafile 5 offline

RMAN> restore datafile 5;

Starting restore at 27-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=265 device type=DISK

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 /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2017_12_27/o1_mf_nnndf_TAG20171227T122203_f46jrmy0_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2017_12_27/o1_mf_nnndf_TAG20171227T122203_f46jrmy0_.bkp tag=TAG20171227T122203
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 27-DEC-17


RMAN> recover datafile 5;

Starting recover at 27-DEC-17
using channel ORA_DISK_1

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

Finished recover at 27-DEC-17


RMAN> sql 'alter database datafile 5 online';

sql statement: alter database datafile 5 online

Now Again Check Alert log file

In the alert log you can see how the recovery process proceeded

tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
...
Thu Jul 19 07:30:01 2012
alter database datafile 5 offline
Completed: alter database datafile 5 offline
Thu Jul 19 07:30:01 2012
Starting background process SMCO
Thu Jul 19 07:30:02 2012
SMCO started with pid=46, OS id=8257 
Thu Jul 19 07:31:03 2012
Full restore complete of datafile 5
/home/oracle/app/oracle/oradata/orcl/users01.dbf.  Elapsed time: 0:00:47 
checkpoint is 13569948
last deallocation scn is 13511135
Thu Jul 19 07:31:21 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 5
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
datafile 5
Thu Jul 19 07:31:35 2012
alter database datafile 5 online
Completed: alter database datafile 5 online
...

 Now I'm able to query again my hr.employees table

SQL> select count(*) from hr.employees;

COUNT(*)
----------
107

and even RMAN is able to size correctly the EXAMPLE tablespace

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

=====================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               YES     /u01/app/oracle/oradata/orcl/system01.dbf
3    620      SYSAUX               NO      /u01/app/oracle/oradata/orcl/sysaux01.dbf
4    75       UNDOTBS1             YES     /u01/app/oracle/oradata/orcl/undotbs01.dbf
5    1243     EXAMPLE              NO      /u01/app/oracle/oradata/orcl/example01.dbf
6    5        USERS                NO      /u01/app/oracle/oradata/orcl/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/app/oracle/oradata/orcl/temp01.dbf

How To Open Standby Database When Primary Database Is Lost Step By Step

How To Open Standby Database When Primary Database Is Lost

    If your primary database is not accessible or crashed for any issue, then we can use open the standby database for use.

Below are the steps for opening standby database:

1. Start the standby database in mount stage: [ STANDBY]

shut immediate; startup mount; 2 . Check the protection_mode and database_role SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE; OPEN_MODE     PROTECTION_MODE            DATABASE_ROLE ----------   --------------------      ---------------- MOUNTED       MAXIMUM PERFORMANCE      PHYSICAL STANDBY NOTE: If protection_mode is other than maximum performance, then change it . SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; 3. Recover standby database:[ STANDBY] SQL> RECOVER STANDBY DATABASE; Make sure all archives has been applied in this process. 4. Finish the recovery process: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; Database altered 5. Activate the standby database: SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; Database altered. 6. Check the status: SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE; OPEN_MODE     PROTECTION_MODE            DATABASE_ROLE ----------   --------------------      ---------------- MOUNTED       MAXIMUM PERFORMANCE        PRIMARY 7. Open the database: SQL> ALTER DATABASE OPEN;

How to check Rman backup Job

How to check Rman backup Job

 In below script we will see  RMAN backup status along with start and stop timing.

SQL> select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;  

SESSION_KEY INPUT_TYPE    STATUS    START_TIME     END_TIME           HRS
----------- ------------- --------- -------------- -------------- -------
      10476 DB INCR       COMPLETED 11/21/17 13:30 11/21/17 13:57     .44
      10491 DB INCR       COMPLETED 11/21/17 21:55 11/21/17 23:07    1.20
      10496 DB INCR       COMPLETED 11/22/17 06:00 11/22/17 06:07     .12
      10504 DB INCR       COMPLETED 11/22/17 21:55 11/22/17 23:02    1.13
      10509 DB INCR       COMPLETED 11/23/17 06:00 11/23/17 06:16     .26
      10517 DB INCR       COMPLETED 11/23/17 21:56 11/23/17 22:59    1.05
      10522 DB INCR       COMPLETED 11/24/17 06:00 11/24/17 06:04     .07
      10530 DB INCR       COMPLETED 11/24/17 22:06 11/24/17 23:10    1.06
      10535 DB INCR       COMPLETED 11/25/17 06:00 11/25/17 06:07     .11
      10543 DB INCR       COMPLETED 11/26/17 06:00 11/26/17 12:14    6.23
      10551 DB INCR       COMPLETED 11/27/17 06:00 11/27/17 07:20    1.34
      10559 DB INCR       COMPLETED 11/27/17 21:57 11/28/17 04:32    6.58
      10564 DB INCR       FAILED    11/28/17 06:00 11/28/17 12:49    6.81
      10568 DB INCR       COMPLETED 11/28/17 21:55 11/29/17 01:03    3.13
      10573 DB INCR       COMPLETED 11/29/17 06:00 11/29/17 06:25     .42
      10581 DB INCR       COMPLETED 11/29/17 21:48 11/29/17 23:34    1.77
      10586 DB INCR       COMPLETED 11/30/17 06:00 11/30/17 08:49    2.82
      10594 ARCHIVELOG    RUNNING   11/30/17 11:12 11/30/17 12:28    1.27

      10597 ARCHIVELOG    RUNNING   11/30/17 11:51 11/30/17 12:28     .61


In below script we will see  SID, Total Work, Sofar & % of completion.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK; 

       SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK % COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
       111      22620          1     428855    3637560      11.79
       116        891          1     465662    1846176      25.22

        47        430          1   15747871   23353264      67.43

In this Script we will  check historical backup status with the help of following script:

 set linesize 500 pagesize 2000

 col Hours format 9999.99

 col STATUS format a10

 select SESSION_KEY, INPUT_TYPE, STATUS,

 to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,

 to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,

 elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS

 order by session_key;

SESSION_KEY INPUT_TYPE    STATUS     RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME     HOURS

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

      10314 CONTROLFILE   COMPLETED  11-08-2017 06:05:22 11-08-2017 06:05:44      .01

      10322 DB INCR       COMPLETED  11-08-2017 21:59:32 11-08-2017 23:03:35     1.07

      10327 DB INCR       COMPLETED  11-09-2017 06:00:20 11-09-2017 06:04:58      .08

      10335 DB INCR       COMPLETED  11-09-2017 21:54:54 11-09-2017 22:58:09     1.05

      10340 DB INCR       COMPLETED  11-10-2017 06:00:21 11-10-2017 06:04:52      .08

      10348 DB INCR       COMPLETED  11-10-2017 21:56:20 11-10-2017 23:01:05     1.08

      10353 DB INCR       COMPLETED  11-11-2017 06:00:28 11-11-2017 06:06:38      .10

      10361 DB INCR       COMPLETED  11-12-2017 06:00:20 11-12-2017 06:33:49      .56

      10369 DB INCR       COMPLETED  11-13-2017 06:00:28 11-13-2017 06:06:47      .11

      10377 DB INCR       COMPLETED  11-13-2017 21:54:08 11-13-2017 23:10:10     1.27

      10382 DB INCR       COMPLETED  11-14-2017 06:00:20 11-14-2017 06:04:59      .08

      10390 DB INCR       COMPLETED  11-14-2017 22:01:09 11-14-2017 23:09:16     1.14

      10395 DB INCR       COMPLETED  11-15-2017 06:00:20 11-15-2017 06:07:13      .11

      10403 DB INCR       COMPLETED  11-15-2017 21:56:38 11-15-2017 23:00:32     1.07

      10408 DB INCR       COMPLETED  11-16-2017 06:00:20 11-16-2017 06:04:38      .07

      10416 DB INCR       COMPLETED  11-16-2017 21:55:53 11-16-2017 23:00:55     1.08

      10421 DB INCR       COMPLETED  11-17-2017 06:00:20 11-17-2017 06:05:43      .09

      10429 DB INCR       COMPLETED  11-17-2017 21:56:44 11-17-2017 23:01:21     1.08

      10434 DB INCR       COMPLETED  11-18-2017 06:00:29 11-18-2017 06:06:42      .10

      10442 DB INCR       COMPLETED  11-19-2017 06:00:20 11-19-2017 06:28:17      .47

Sql loader in oracle

Sql loader in oracle

If you are using Oracle database, at some point you might have to deal with uploading data to the tables from a text file. Actually, Sql loader is the utility to use for high performance data loads. It is used for moving data from external files into the Oracle database.

The Control File

The SQL Loader control file, is a flat file or text file, contains information that describes how the data will be loaded.

 It contain the following information…

§  infile – Indicates the location of the input data file

§  into table – Indicates the table name where this data should be inserted

§  fields terminated by – Indicates the delimiter that is used in the input file to separate the fields

§  ( id, name, dept, salary ) – Lists the name of the column names in the table into which the data should be uploaded.

Options in SQL*Loader while loading the data.

      a) INSERT: Specifies that you are loading into an empty table.

(b) APPEND: If we want to load the data into a table which is already containing some rows.

(c) REPLACE: Specifies that, we want to replace the data in the table before loading. Will 'DELETE' all the existing records and replace them with new.

(d) TRUNCATE: This is same as 'REPLACE', but SQL*Loader will use the 'TRUNCATE' command instead of 'DELETE' command.

Example

Firstly, We create the table as shown below.

SQL> create table employee

(

  id integer,

  name varchar2(10),

  dept varchar2(15),

  salary integer)

Now We create the control file to upload data.

Define the SQL*Loader control file, called .ctl, that will allow us to load the data.

load data

 infile '/home/xyz/employee.txt'

 into table employee

 fields terminated by ","

 ( id, name, dept, salary )

 Now we execute the sqlldr command to upload these new record to the empty table .

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-new.ctl

Commit point reached - logical record count 5

Now verify...

SQL> select * from employee;

        ID NAME       DEPT                SALARY 

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

       100 Ram      Sales                 5000

       200 Ravi     Technology            5500

       300 Raja     Technology            7000

       400 Mohan      Marketing             9500

       500 Shyam    Technology            6000

 Data inside the Control File using BEGINDATA

We can also specify the data directly inside the control file itself using BEGINDATA keyword. i.e Anything that comes after BEGINDATA will be treated as data to be uploaded to the table .

$ Cat sqlldr-add-new-with-data.ctl

Load data

 infile *

 into table employee

 fields terminated by ","

 ( id, name, dept, salary )

begindata

100,Ram,Sales,5000

200,Ravi,Technology,5500

300,Raj,Technology,7000

400,Shyam,Marketing,9500

 Now We Execute sqlldr to upload the data from the control file.

$ sqlldr scott/tiger control=/home/xyz/sqlldr-add-new-with-data.ctl

Loading fixed length (positional) data

The control file can also specify that records are in fixed format. A file is in fixed record format when all records in a datafile are the same length.

LOAD DATA

INFILE *

INTO TABLE positional_data

(data1 POSITION(1:5),

data2 POSITION(6:15)

)

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB

Database Shutdown

Database Shutdown

Different Modes in Database Shutdown

There are different modes to bring down the database:
1. Shutdown immediate
2. Shutdown transactional
3. Shutdown normal
4. Shutdown abort

Shutdown Immediate

1) No new connections are allowed.

2) Active transactions are terminated and uncommitted changes are rolled back.

3) Changes made by an active transaction are lost in this option.

$ sqlplus / as sysdba

SQL> shutdown immediate;

Shutdown Transactional

1) No new connections are allowed.

2) Oracle waits until all active transactions are completed.

3) Oracle then performs a checkpoint and then close the online data files.

$ sqlplus / as sysdba

SQL> shutdown transactional

Shutdown Normal

1) The database waits for all connected users to disconnect before shutting down.

2) It waits till all the current transactions end.

3) Oracle then performs a checkpoint and then close the online data files.

$ sqlplus / as sysdba

SQL> shutdown normal.

Shutdown Abort

1) Oracle closes the data files without any checkpoint.

2) This is the fastest shutdown mode.

Note-

 1) This happens immediately however the database is not shutdown cleanly.

 2) Database will have to perform instance recovery next time it is started.

3) This option should not be used in regular activities.