Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We can recover a datafile that is corrupted or deleted in the Primary Database, or from a datafile in the Standby Database, which is briefly damaged.
Since our topic is Standby, in this section, we will restore a damaged Datafile using the Standby database.
Let’s Test
1. To set up the test environment, we first create a Tablespace on the Primary side.
1 2 3 | [Primary] SQL> create tablespace SAMPLE datafile '/u01/app/oracle/oradata/primary/sample01.dbf' size 5M; Tablespace created. |
2. We check if Tablespace is formed in Primary and Physical Standby.
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary] SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/primary/system01.dbf /u01/app/oracle/oradata/primary/sample01.dbf /u01/app/oracle/oradata/primary/sysaux01.dbf /u01/app/oracle/oradata/primary/undotbs01.dbf /u01/app/oracle/oradata/primary/example01.dbf /u01/app/oracle/oradata/primary/users01.dbf 6 rows selected. |
1 2 3 4 5 6 7 8 9 10 11 12 | [Physical] SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/physical/system01.dbf /u01/app/oracle/oradata/physical/sample01.dbf /u01/app/oracle/oradata/physical/sysaux01.dbf /u01/app/oracle/oradata/physical/undotbs01.dbf /u01/app/oracle/oradata/physical/example01.dbf /u01/app/oracle/oradata/physical/users01.dbf 6 rows selected. |
The reason for adding Datafile to Physical Standby automatically is because STANDBY_FILE_MANAGEMENT parameter is in AUTO and DB_FILE _NAME_CONVERT parameter on Standby side is (Primary,Physical).
3. A table is created as the tablespace that creates the tablespace.
1 2 3 | [Primary] SQL> create table hr.employees2 tablespace sample as select * from hr.employees; Table created. |
4. We check if the table is formed.
1 2 3 4 5 | [Primary] SQL> select count(*) from hr.employees2; COUNT(*) ---------- 107 |
5. A disaster situation is created by changing the datafile.
1 | [Primary] SQL> !mv /u01/app/oracle/oradata/primary/sample01.dbf /u01/app/oracle/oradata/primary/sample01.sav |
6. We close the primary database with Abort.
1 2 | [Primary] SQL> shu abort; ORACLE instance shut down. |
7. Primary database is requested to be opened.
1 2 3 4 5 6 7 8 9 10 11 | [Primary] SQL> startup; ORACLE instance started. Total System Global Area 3472883712 bytes Fixed Size 2930272 bytes Variable Size 822086048 bytes Database Buffers 2634022912 bytes Redo Buffers 13844480 bytes Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/u01/app/oracle/oradata/primary/sample01.dbf' |
8. The database gave the expected error and did not open. We question his status.
1 2 3 4 5 | [Primary] SQL> select status from v$instance; STATUS ------------ MOUNTED |
When switching to OPEN mode, the database could not switch to OPEN mode because it opened Datafile and remained in MOUNT mode.
9. Datafile is taken offline.
1 2 3 | [Primary] SQL> alter tablespace sample datafile offline; Tablespace altered. |
10. The database opens.
1 2 3 | [Primary] SQL> alter database open; Database altered. |
11. The status of the problematic Datafile is questioned.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Primary] SQL> set linesize 9000 [Primary] SQL> column name format a45 [Primary] SQL> select NAME, STATUS from v$datafile; NAME STATUS --------------------------------------------- ------- /u01/app/oracle/oradata/primary/system01.dbf SYSTEM /u01/app/oracle/oradata/primary/sample01.dbf RECOVER /u01/app/oracle/oradata/primary/sysaux01.dbf ONLINE /u01/app/oracle/oradata/primary/undotbs01.dbf ONLINE /u01/app/oracle/oradata/primary/example01.dbf ONLINE /u01/app/oracle/oradata/primary/users01.dbf ONLINE 6 rows selected. |
12. From RMAN, it is connected to Physical Standby as TARGET and to Primary as Auxiliary. The reason for connecting like this is taking backup of Datafile in Physical Standby.
1 2 3 4 5 6 7 8 9 10 11 12 13 | [oracle@primary primary]$ rman Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 14 16:29:40 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. RMAN> connect target sys/Passw0rd4@physical connected to target database: PRIMARY (DBID=1772883609) RMAN> connect auxiliary sys/Passw0rd4@primary connected to auxiliary database: PRIMARY (DBID=1772883609) |
13. Backup of problematic Datafile is taken.
1 2 3 4 5 6 7 8 9 10 11 | RMAN> backup as copy datafile 2 auxiliary format '/u01/app/oracle/oradata/primary/sample01.bkp'; Starting backup at 14-FEB-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=253 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/physical/sample01.dbf output file name=/u01/app/oracle/oradata/primary/sample01.bkp tag=TAG20170214T164229 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 14-FEB-17 |
14. Connects from RMAN to Primary as TARGET and to Catalog.
1 2 3 4 5 6 7 8 | [oracle@primary primary]$ rman target sys/Passw0rd4@primary catalog rcowner/rcowner@broker; Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 14 16:44:46 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMARY (DBID=1772883609) connected to recovery catalog database |
15. I saved the received backup to the catalog.
1 2 3 4 5 6 7 | RMAN> catalog datafilecopy '/u01/app/oracle/oradata/primary/sample01.bkp'; new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete cataloged datafile copy datafile copy file name=/u01/app/oracle/oradata/primary/sample01.bkp RECID=3 STAMP=935945136 |
16. I change the name of the Datafile and recover it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | RMAN> run { 2> set newname for datafile 2 to '/u01/app/oracle/oradata/primary/sample01.bkp'; 3> switch datafile 2; 4> recover datafile 2; 5> } executing command: SET NEWNAME RMAN-06169: could not read file header for datafile 2 error reason 4 datafile 2 switched to datafile copy input datafile copy RECID=3 STAMP=935945136 file name=/u01/app/oracle/oradata/primary/sample01.bkp starting full resync of recovery catalog full resync complete Starting recover at 14-FEB-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=243 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 14-FEB-17 |
17. I’m taking Tablespace online.
1 2 3 | [Primary] SQL> alter tablespace sample datafile online; Tablespace altered. |
18. Checking the functionality of Tablespace.
1 2 3 4 5 | [Primary] SQL> select count(*) from hr.employees2; COUNT(*) ---------- 107 |
19. I am converting the name of Datafile to standard format.
1 2 3 | [Primary] SQL> alter database move datafile '/u01/app/oracle/oradata/primary/sample01.bkp' to '/u01/app/oracle/oradata/primary/sample01.dbf'; Database altered. |
20. I’m querying the status of the configuration from the broker.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DGMGRL> show configuration Configuration - DG_Solution Protection Mode: MaxAvailability Members: primary - Primary database prmyFS - Far sync instance physical - Physical standby database Members Not Receiving Redo: physclFS - Far sync instance Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 12 seconds ago) |
21. Tests are passed.
a. Log Switch operation is tested. Previously, the existing archive sequence numbers are queried.
1 2 3 4 5 | [Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('14/02/2017 16:11:11', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 12 1 |
1 2 3 4 5 | [Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('14/02/2017 16:11:11', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 12 1 |
b. The archive sequence numbers are checked by performing the Log Switch operation.
1 2 3 4 5 6 7 8 9 | [Primary] SQL> alter system switch logfile; System altered. [Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('14/02/2017 16:11:11', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 13 1 |
1 2 3 4 5 | [Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('14/02/2017 16:11:11', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 13 1 |
