Oracle uses the backup control file for recovery, and then creates a tablespace for recovery.

Oracle uses the backup control file for recovery, and then creates a tablespace for recovery.

1. Create a tablespace after the backup control file, and all the control files are lost. How can I restore the table using the backup control file?

The procedure is as follows:
1. Back up the database

Rman target/catalog RC_ADMIN/RC_ADMIN @ prod3

Backup database plus archivelog delete all input;

 

2. Create a tablespace
SYS @ PROD2> create tablespace indx
2 datafile '/u01/app/oracle/oradata/PROD2/indx01.dbf'
3 size 10 m;
3. Create a table and specify the tablespace as indx.
SYS @ PROD2> create table test tablespace indx as select * from scott. emp;

Table created.

SYS @ PROD2> select * from test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------------------------------------------------------------------
7369 smith clerk 7902 17-DEC-80 800 20
7499 allen salesman 7698 20-FEB-81 1600 300 30
7521 ward salesman 7698 22-FEB-81 1250 500 30
7566 jones manager 7839 02-APR-81 2975 20
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7698 blake manager 7839 01-MAY-81 2850 30
7782 clark manager 7839 09-JUN-81 2450 10
7788 scott analyst 7566 19-APR-87 3000 20
7839 king president 17-NOV-81 5000 10
7844 turner salesman 7698 08-SEP-81 1500 0 30
7876 adams clerk 7788 23-MAY-87 1100 20
7900 james clerk 7698 03-DEC-81 950 30
7902 ford analyst 7566 03-DEC-81 3000 20
7934 miller clerk 7782 23-JAN-82 1300 10

14 rows selected.

SYS @ PROD2> alter system switch logfile;

System altered.

-- Delete some records for testing
SYS @ PROD2> delete test where deptno = 30;

6 rows deleted.

SYS @ PROD2> commit;

Commit complete.

SYS @ PROD2> select * from test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------------------------------------------------------------------
7369 smith clerk 7902 17-DEC-80 800 20
7566 jones manager 7839 02-APR-81 2975 20
7782 clark manager 7839 09-JUN-81 2450 10
7788 scott analyst 7566 19-APR-87 3000 20
7839 king president 17-NOV-81 5000 10
7876 adams clerk 7788 23-MAY-87 1100 20
7902 ford analyst 7566 03-DEC-81 3000 20
7934 miller clerk 7782 23-JAN-82 1300 10

8 rows selected.

4. Delete all control files
Oracle@node102.cuug.com:/u01/app/oracle/oradata/PROD2> rm-rf control *

The alert Log reports that the control file cannot be found.
Tue May 12 02:15:47 2015
Errors in file/u01/app/oracle/diag/rdbms/prod2/PROD2/trace/PROD2_m000_4995.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD2/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory

5. Shut down database startup to nomount
SYS @ PROD2> shutdown abort;
ORACLE instance shut down.
SYS @ PROD2> startup nomount;
ORACLE instance started.

Total System Global Area 630501376 bytes
Fixed Size 2215984 bytes
Variable Size 255856592 bytes
Database Buffers 364904448 bytes
Redo Buffers 7524352 bytes

6. log on to rman to restore the backup control file.
Oracle@node102.cuug.com:/u01/app/oracle/oradata/PROD2> rman target/catalog RC_ADMIN/RC_ADMIN @ prod3

Recovery Manager: Release 11.2.0.1.0-Production on Tue May 12 02:19:25 2015

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

Connected to target database: PROD2 (not mounted)
Connected to recovery catalog database

RMAN> restore controlfile;

Starting restore at 12-MAY-15
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 10 device type = DISK
Allocated channel: ORA_DISK_2
Channel ORA_DISK_2: SID = 180 device type = DISK
Allocated channel: ORA_DISK_3
Channel ORA_DISK_3: SID = 11 device type = DISK

Channel ORA_DISK_1: starting datafile backup set restore
Channel ORA_DISK_1: restoring control file
Channel ORA_DISK_1: reading from backup piece/home/oracle/rman_bk/control/c-1529226433-20150512-02
Channel ORA_DISK_1: piece handle =/home/oracle/rman_bk/control/c-1529226433-20150512-02 tag = TAG20150512T020556
Channel ORA_DISK_1: restored backup piece 1
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Output file name =/u01/app/oracle/oradata/PROD2/control01.ctl
Output file name =/u01/app/oracle/oradata/PROD2/control02.ctl
Finished restore at 12-MAY-15

RMAN> alter database mount;

RMAN> recover database;

ORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error below
ORA-01245: offline file 6 will be lost if RESETLOGS is done
ORA-01111: name for data file 6 is unknown-rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006'

Rename data file 6
Alter database create datafile 6 as '/u01/app/oracle/oradata/PROD2/indx01.dbf ';

SYS @ PROD2> alter database datafile 6 online;

Database altered.

RMAN> recover database;

Starting recover at 12-MAY-15
Using channel ORA_DISK_1
Using channel ORA_DISK_2
Using channel ORA_DISK_3

Starting media recovery

Archived log for thread 1 with sequence 5 is already on disk as file/u01/app/oracle/oradata/PROD2/redo02.log
Archived log file name =/u01/app/oracle/oradata/PROD2/redo02.log thread = 1 sequence = 5
Oracle Error:
ORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error below
ORA-01245: offline file 6 will be lost if RESETLOGS is done
ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD2/indx01.dbf'

Media recovery complete, elapsed time: 00:00:00
Finished recover at 12-MAY-15

Do not use rman to recover when using backup controlfile uses sqlplus

Sqlplus/as sysdba
SYS @ PROD2> recover database using backup controlfile;
ORA-00279: change 1212936 generated at 05/12/2015 02:15:52 needed for thread 1
ORA-00289: suggestion:/u01/app/oracle/archive/4105_879466155.dbf
ORA-00280: change 1212936 for thread 1 is in sequence #5

Specify log :{ = Suggested | filename | AUTO | CANCEL}
Auto

SYS @ PROD2> select group #, sequence #, status from v $ log;

GROUP # SEQUENCE # STATUS
------------------------------------
1 4 CURRENT
3 3 ACTIVE
2 2 INACTIVE

If the sequence is 5, the current redo log directory is specified.

SYS @ PROD2> recover database using backup controlfile;
ORA-00279: change 1212936 generated at 05/12/2015 02:15:52 needed for thread 1
ORA-00289: suggestion:/u01/app/oracle/archive/4105_879466155.dbf
ORA-00280: change 1212936 for thread 1 is in sequence #5

Specify log :{ = Suggested | filename | AUTO | CANCEL}
/U01/app/oracle/oradata/PROD2/redo02.log
Log applied.
Media recovery complete.

SYS @ PROD2> select * from v $ recover_file;

No rows selected

SYS @ PROD2> alter database open resetlogs;

SYS @ PROD2> select * from test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------------------------------------------------------------------
7369 smith clerk 7902 17-DEC-80 800 20
7566 jones manager 7839 02-APR-81 2975 20
7782 clark manager 7839 09-JUN-81 2450 10
7788 scott analyst 7566 19-APR-87 3000 20
7839 king president 17-NOV-81 5000 10
7876 adams clerk 7788 23-MAY-87 1100 20
7902 ford analyst 7566 03-DEC-81 3000 20
7934 miller clerk 7782 23-JAN-82 1300 10

8 rows selected.

 

Check that the record is normal. Recovery successful!

Note:

1. Data File needs to be redirected

Alter database create datafile 6 as '/u01/app/oracle/oradata/PROD2/indx01.dbf ';

2. Change Data File to online

Alter database datafile 6 online;

3. Rman cannot be used for recovery when using backup controlfile