The process is as follows:
- Ensure that the database is in archivelog mode.
- Determine where to copy the backup files.
- Identify which files need to be backed up.
- Note the maximum sequence number of the online redo logs.
- Alter the database/tablespace into backup mode.
- Copy the data files with an OS utility to the location determined in step 2.
- Alter the database/tablespace out of backup mode.
- Archive the current online redo log, and note the maximum sequence number of the
online redo logs. - Back up the control file.
- Back up any archive redo logs generated during the backup.
Confirm that the database is in Archive Mode
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
Confirm backup target
The backup target directory is / u01/backup/ORCLCDB. Then look at the required space of 1.4 GH:
SQL> select sum(bytes) from dba_data_files; SUM(BYTES) ---------- 1415577600
Don't worry, it's only CDB$ROOT, and ORCLPDB1:
SQL> alter session set container=orclpdb1; Session altered. SQL> select sum(bytes) from dba_data_files; SUM(BYTES) ---------- 739246080
Confirm the files to be backed up
Confirm the files to be backed up and their association with tablespace:
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/ORCLCDB/system01.dbf /opt/oracle/oradata/ORCLCDB/sysaux01.dbf /opt/oracle/oradata/ORCLCDB/undotbs01.dbf /opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf /opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf /opt/oracle/oradata/ORCLCDB/users01.dbf /opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf 11 rows selected. SQL> col file_name format a60 SQL> select tablespace_name, file_name from dba_data_files order by 1,2; TABLESPACE_NAME FILE_NAME ------------------------------ ------------------------------------------------ SYSAUX /opt/oracle/oradata/ORCLCDB/sysaux01.dbf SYSTEM /opt/oracle/oradata/ORCLCDB/system01.dbf UNDOTBS1 /opt/oracle/oradata/ORCLCDB/undotbs01.dbf USERS /opt/oracle/oradata/ORCLCDB/users01.dbf SQL> alter session set container=orclpdb1; Session altered. SQL> set linesize 100 SQL> select tablespace_name, file_name from dba_data_files order by 1,2; TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------- SYSAUX /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf SYSTEM /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf UNDOTBS1 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf USERS /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
Determine the maximum sequence number of online redo log
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select thread#, max(sequence#) from v$log group by thread# order by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 5
Because CDB and PDB share redo log s, it's OK to execute in cdb$root.
Place the database in backup mode
There's a question here about whether it's necessary to set up backup mode for each PDB. The answer is No. We can see the output of V$backup later.
SQL> alter database begin backup;
Database altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME CON_ID
---------- ------------------ ---------- --------- ----------
1 ACTIVE 1585088 09-APR-19 1
3 ACTIVE 1585088 09-APR-19 1
4 ACTIVE 1585088 09-APR-19 1
5 NOT ACTIVE 0 2
6 NOT ACTIVE 0 2
7 ACTIVE 1585088 09-APR-19 1
8 NOT ACTIVE 0 2
9 ACTIVE 1585088 09-APR-19 3
10 ACTIVE 1585088 09-APR-19 3
11 ACTIVE 1585088 09-APR-19 3
12 ACTIVE 1585088 09-APR-19 3
11 rows selected.
In the above output, status is an Active indicating that it is in the backup state. CON_ID=1 denotes orclcdb and CON_ID=3 denotes orclpdb1.
SQL> select pdb_id, pdb_name from dba_pdbs;
3 ORCLPDB1
2 PDB$SEED
2 rows selected.
SQL> show con_name;
CDB$ROOT
SQL> show con_id;
1
con_id is cdb$root for 1, pdb$seed for 2, and orclpdb1 for 3.
SQL> show con_id
CON_ID
------------------------------
1
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
Temporary insertion experiments verify that changes are still written to data files in backup mode
Write data:
SQL> show con_name;
CDB$ROOT
SQL> create table cc(cc varchar2(20)) tablespace users;
Table created.
SQL> insert into cc values('DBWR does write');
1 row created.
SQL> alter system checkpoint;
System altered.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Verify that the changes have been written to the data file:
$ strings /opt/oracle/oradata/ORCLCDB/users01.dbf | grep "DBWR does write" DBWR does write
Backup file
Including cdb$root and orclpdb1:
[oracle@oracle-12201-vagrant ~]$ cp $ORACLE_BASE/oradata/ORCLCDB/*.dbf /u01/backup/ORCLCDB [oracle@oracle-12201-vagrant ~]$ cp $ORACLE_BASE/oradata/ORCLCDB/ORCLPDB1/*.dbf /u01/backup/ORCLCDB/ORCLPDB1 cp: target '/u01/backup/ORCLCDB/ORCLPDB1' is not a directory [oracle@oracle-12201-vagrant ~]$ mkdir /u01/backup/ORCLCDB/ORCLPDB1 [oracle@oracle-12201-vagrant ~]$ cp $ORACLE_BASE/oradata/ORCLCDB/ORCLPDB1/*.dbf /u01/backup/ORCLCDB/ORCLPDB1 [oracle@oracle-12201-vagrant ~]$ du -sh /u01/backup/ORCLCDB 2.1G /u01/backup/ORCLCDB
Exit backup mode
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter database end backup;
Database altered.
SQL> alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
Session altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME CON_ID
---------- ------------------ ---------- -------------------- ----------
1 NOT ACTIVE 1600206 22-APR-2019 18:04:46 1
3 NOT ACTIVE 1600206 22-APR-2019 18:04:46 1
4 NOT ACTIVE 1600206 22-APR-2019 18:04:46 1
5 NOT ACTIVE 0 2
6 NOT ACTIVE 0 2
7 NOT ACTIVE 1600206 22-APR-2019 18:04:46 1
8 NOT ACTIVE 0 2
9 NOT ACTIVE 1600206 22-APR-2019 18:04:46 3
10 NOT ACTIVE 1600206 22-APR-2019 18:04:46 3
11 NOT ACTIVE 1600206 22-APR-2019 18:04:46 3
12 NOT ACTIVE 1600206 22-APR-2019 18:04:46 3
11 rows selected.
File online redo log and record the maximum serial number
[oracle@oracle-12201-vagrant ~]$ tree /u01/fra
/u01/fra
└── ORCLCDB
└── archivelog
├── 2019_04_08
│ ├── control.sql
│ ├── o1_mf_1_1_gbps0c30_.arc
│ ├── o1_mf_1_2_gbps0c3y_.arc
│ └── o1_mf_1_3_gbps2ps1_.arc
└── 2019_04_09
4 directories, 4 files
SQL> alter system archive log current; System altered.
[oracle@oracle-12201-vagrant ~]$ tree /u01/fra
/u01/fra
└── ORCLCDB
└── archivelog
├── 2019_04_08
│ ├── control.sql
│ ├── o1_mf_1_1_gbps0c30_.arc
│ ├── o1_mf_1_2_gbps0c3y_.arc
│ └── o1_mf_1_3_gbps2ps1_.arc
├── 2019_04_09
│ └── o1_mf_1_4_gbrtc78t_.arc
└── 2019_04_22
└── o1_mf_1_5_gcv4t94v_.arc
5 directories, 6 files
The format description of the filename is OMF.
Take a look at the file format of the archive:
SQL> show parameter log_archive_format NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.dbf
Record serial number:
SQL> select thread#, max(sequence#) from v$log group by thread# order by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 6
Backup control file
SQL> alter database backup controlfile to '/u01/backup/ORCLCDB/controlbk.ctl' reuse; Database altered. SQL> !ls -l /u01/backup/ORCLCDB/controlbk.ctl -rw-r-----. 1 oracle oinstall 19054592 Apr 9 17:48 /u01/backup/ORCLCDB/controlbk.ctl
Backup archive logs generated during backup
Below is my own thinking, anyway, not much, we have a full backup.
$ cp -r /u01/fra/ORCLCDB/archivelog/ /u01/backup/ORCLCDB/ $ du -sh /u01/backup/ORCLCDB/archivelog/ 368M /u01/backup/ORCLCDB/archivelog/
Or maybe backing up the following files is enough:
SQL> select name from V$ARCHIVED_LOG; NAME -------------------------------------------------------------------------------- /u01/fra/ORCLCDB/archivelog/2019_04_08/o1_mf_1_2_gbps0c3y_.arc /u01/fra/ORCLCDB/archivelog/2019_04_08/o1_mf_1_1_gbps0c30_.arc /u01/fra/ORCLCDB/archivelog/2019_04_08/o1_mf_1_3_gbps2ps1_.arc /u01/fra/ORCLCDB/archivelog/2019_04_09/o1_mf_1_4_gbrtc78t_.arc /u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_5_gcv4t94v_.arc
Off-line recovery experiment
SQL> alter session set container=orclpdb1; Session altered. SQL> create table foo(foo number) tablespace users; Table created. SQL> insert into foo values(1); 1 row created. SQL> commit; Commit complete. SQL> alter system switch logfile; alter system switch logfile * ERROR at line 1: ORA-65040: operation not allowed from within a pluggable database SQL> alter session set container=cdb$root; Session altered. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> alter session set container=orclpdb1; Session altered. SQL> select file_name from dba_data_files where tablespace_name='USERS'; FILE_NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
Under the operating system, renamed file emulation error:
$ mv /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf.old
Close the database and open it to mount state:
SQL> shutdown immediate; ORA-01116: error in opening database file 12 ORA-01110: data file 12: '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.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> startup mount; ORACLE instance started. Total System Global Area 1207959552 bytes Fixed Size 8792152 bytes Variable Size 436209576 bytes Database Buffers 754974720 bytes Redo Buffers 7983104 bytes Database mounted.
Restore files from backup:
$ cp /u01/backup/ORCLCDB/ORCLPDB1/users01.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
Record SCN:
SQL> select checkpoint_change# from v$datafile where file#=4;
CHECKPOINT_CHANGE#
------------------
1602881
Check the SCN recorded in the data file:
select file#, fuzzy, checkpoint_change#
from v$datafile_header
3 where file#=4;
FILE# FUZ CHECKPOINT_CHANGE#
---------- --- ------------------
4 YES 1602881
Open the database:
SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 12 needs media recovery ORA-01110: data file 12: '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf'
The recovery process is as follows:
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 ORCLPDB1 MOUNTED
SQL> alter session set container=orclpdb1;
Session altered.
SQL> recover tablespace users;
ORA-00279: change 1600206 generated at 04/22/2019 18:04:46 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_5_gcv4t94v_.arc
ORA-00280: change 1600206 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1601454 generated at 04/22/2019 18:11:21 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_6_gcv5d0m7_.arc
ORA-00280: change 1601454 for thread 1 is in sequence #6
ORA-00279: change 1602872 generated at 04/22/2019 18:20:48 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_7_gcv5d2vh_.arc
ORA-00280: change 1602872 for thread 1 is in sequence #7
Log applied.
Media recovery complete.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCLPDB1 MOUNTED
SQL> alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-65054: Cannot open a pluggable database in the desired mode.
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter database open;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;
NAME Multitenant Option ? OPEN_MODE CON_ID
--------- -------------------------- -------------------- ----------
ORCLCDB Multitenant Option enabled READ WRITE 0
Verification data has been restored:
SQL> alter session set container=orclpdb1;
Session altered.
SQL> select * from foo;
FOO
----------
1
On-line recovery experiment
First simulate the error:
mv /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf.old
Insert data:
SQL> alter session set container=orclpdb1;
Session altered.
SQL> insert into foo values(2);
insert into foo values(2)
*
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Place data files offline:
SQL> alter database datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf' offline; Database altered. SQL> show con_name; CON_NAME ------------------------------ ORCLPDB1 SQL>
Restore data files from backup:
$ cp /u01/backup/ORCLCDB/ORCLPDB1/users01.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
Restore data files:
SQL> recover datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf';
ORA-00279: change 1600206 generated at 04/22/2019 18:04:46 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_5_gcv4t94v_.arc
ORA-00280: change 1600206 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1601454 generated at 04/22/2019 18:11:21 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_6_gcv5d0m7_.arc
ORA-00280: change 1601454 for thread 1 is in sequence #6
ORA-00279: change 1602872 generated at 04/22/2019 18:20:48 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_7_gcv5d2vh_.arc
ORA-00280: change 1602872 for thread 1 is in sequence #7
ORA-00279: change 1602875 generated at 04/22/2019 18:20:50 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_8_gcv5d504_.arc
ORA-00280: change 1602875 for thread 1 is in sequence #8
ORA-00279: change 1602878 generated at 04/22/2019 18:20:52 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_9_gcv5d639_.arc
ORA-00280: change 1602878 for thread 1 is in sequence #9
Log applied.
Media recovery complete.
Place data files online:
SQL> alter database datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf' online;
Database altered.
SQL> select * from foo;
FOO
----------
1
Recovery in case of partial control file corruption
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /opt/oracle/oradata/ORCLCDB/co
ntrol01.ctl, /opt/oracle/orada
ta/ORCLCDB/control02.ctl
Analog Control File 1 is corrupted:
$ > /opt/oracle/oradata/ORCLCDB/control01.ctl
The recovery process is simple, that is, to copy an undamaged document.
[oracle@oracle-12201-vagrant ~]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 22 20:41:15 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> alter session set container=orclpdb1
2 ;
alter session set container=orclpdb1
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 8792152 bytes
Variable Size 436209576 bytes
Database Buffers 754974720 bytes
Redo Buffers 7983104 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> shutdown abort
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oracle-12201-vagrant ~]$ cp /opt/oracle/oradata/ORCLCDB/control02.ctl /opt/oracle/oradata/ORCLCDB/control01.ctl
[oracle@oracle-12201-vagrant ~]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 22 20:42:41 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 8792152 bytes
Variable Size 436209576 bytes
Database Buffers 754974720 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
Recovery of all control files in case of damage
Firstly, all control files are simulated to be corrupted:
> /opt/oracle/oradata/ORCLCDB/control01.ctl > /opt/oracle/oradata/ORCLCDB/control02.ctl
Query data error:
SQL> select * from v$database; select * from v$database * ERROR at line 1: ORA-03135: connection lost contact Process ID: 7571 Session ID: 17 Serial number: 63926
shutdown example:
SQL> shutdown abort; ORACLE instance shut down.
Restore all control files from backup:
cp /u01/backup/ORCLCDB/controlbk.ctl /opt/oracle/oradata/ORCLCDB/control01.ctl cp /u01/backup/ORCLCDB/controlbk.ctl /opt/oracle/oradata/ORCLCDB/control02.ctl
Start the database to mount state:
SQL> startup mount ORACLE instance started. Total System Global Area 1207959552 bytes Fixed Size 8792152 bytes Variable Size 436209576 bytes Database Buffers 754974720 bytes Redo Buffers 7983104 bytes Database mounted.
Then resume:
SQL> recover database using backup controlfile;
ORA-00279: change 1601481 generated at 04/22/2019 18:11:21 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_6_gcv5d0m7_.arc
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1
ORA-27048: skgfifi: file header information is invalid
Additional information: 2
ORA-27048: skgfifi: file header information is invalid
Additional information: 2
ORA-00280: change 1601481 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1602872 generated at 04/22/2019 18:20:48 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_7_gcv5d2vh_.arc
ORA-00280: change 1602872 for thread 1 is in sequence #7
ORA-00278: log file
'/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_6_gcv5d0m7_.arc' no longer
needed for this recovery
ORA-00279: change 1602875 generated at 04/22/2019 18:20:50 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_8_gcv5d504_.arc
ORA-00280: change 1602875 for thread 1 is in sequence #8
ORA-00278: log file
'/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_7_gcv5d2vh_.arc' no longer
needed for this recovery
ORA-00279: change 1602878 generated at 04/22/2019 18:20:52 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_9_gcv5d639_.arc
ORA-00280: change 1602878 for thread 1 is in sequence #9
ORA-00278: log file
'/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_8_gcv5d504_.arc' no longer
needed for this recovery
ORA-00279: change 1602881 generated at 04/22/2019 18:20:54 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_10_gcv5xbm0_.arc
ORA-00280: change 1602881 for thread 1 is in sequence #10
ORA-00278: log file
'/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_9_gcv5d639_.arc' no longer
needed for this recovery
ORA-00279: change 1802961 generated at 04/22/2019 18:30:02 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_11_gcvdtyp1_.arc
ORA-00280: change 1802961 for thread 1 is in sequence #11
ORA-00278: log file
'/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_10_gcv5xbm0_.arc' no longer
needed for this recovery
ORA-00279: change 1904184 generated at 04/22/2019 20:28:14 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_12_gcvfpcs0_.arc
ORA-00280: change 1904184 for thread 1 is in sequence #12
ORA-00278: log file
'/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_11_gcvdtyp1_.arc' no longer
needed for this recovery
ORA-00279: change 2005927 generated at 04/22/2019 20:42:51 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_13_%u_.arc
ORA-00280: change 2005927 for thread 1 is in sequence #13
ORA-00278: log file
'/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_12_gcvfpcs0_.arc' no longer
needed for this recovery
ORA-00308: cannot open archived log
'/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_13_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Continue to recover:
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/opt/oracle/oradata/ORCLCDB/system01.dbf'
Query redo log:
select a.sequence#, a.status, a.first_change#, b.member
from v$log a, v$logfile b
where a.group# = b.group#
4 order by a.sequence#;
SEQUENCE# STATUS FIRST_CHANGE#
---------- ---------------- -------------
MEMBER
--------------------------------------------------------------------------------
4 INACTIVE 1576056
/opt/oracle/oradata/ORCLCDB/redo01.log
5 ACTIVE 1586001
/opt/oracle/oradata/ORCLCDB/redo02.log
6 CURRENT 1601454
/opt/oracle/oradata/ORCLCDB/redo03.log
Then try to restore with online redo log, one by one:
SQL> recover database using backup controlfile;
ORA-00279: change 2005927 generated at 04/22/2019 20:42:51 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_13_%u_.arc
ORA-00280: change 2005927 for thread 1 is in sequence #13
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/ORCLCDB/redo03.log
ORA-00310: archived log contains sequence 12; sequence 13 required
ORA-00334: archived log: '/opt/oracle/oradata/ORCLCDB/redo03.log'
SQL> recover database using backup controlfile;
ORA-00279: change 2005927 generated at 04/22/2019 20:42:51 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_13_%u_.arc
ORA-00280: change 2005927 for thread 1 is in sequence #13
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/ORCLCDB/redo02.log
ORA-00310: archived log contains sequence 11; sequence 13 required
ORA-00334: archived log: '/opt/oracle/oradata/ORCLCDB/redo02.log'
SQL> recover database using backup controlfile;
ORA-00279: change 2005927 generated at 04/22/2019 20:42:51 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_13_%u_.arc
ORA-00280: change 2005927 for thread 1 is in sequence #13
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/ORCLCDB/redo01.log
Log applied.
Media recovery complete.
Finally, open the database, this step takes a long time, about tens of seconds:
SQL> alter database open resetlogs; Database altered.
Successful recovery:
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
Imcomplete recovery experiment
First insert some data:
SQL> alter session set container=orclpdb1;
Session altered.
SQL> select * from foo;
FOO
----------
1
SQL> insert into foo values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------------------
22-APR-2019 21:13:51
Close the database:
SQL> shutdown abort; ORACLE instance shut down.
Restore data from backups (orclpdb1 is restored only here):
$ cp /u01/backup/ORCLCDB/ORCLPDB1/*.dbf /opt/oracle/oradata/ORCLCDB/ORCLPDB1
Start the database and restore:
SQL> recover database until cancel;
ORA-00279: change 1600206 generated at 04/22/2019 18:04:46 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_5_gcv4t94v_.arc
ORA-00280: change 1600206 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/opt/oracle/oradata/ORCLCDB/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/opt/oracle/oradata/ORCLCDB/system01.dbf'
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oracle-12201-vagrant ~]$ cp /u01/backup/ORCLCDB/system01.dbf /opt/oracle/oradata/ORCLCDB/system01.dbf
[oracle@oracle-12201-vagrant ~]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 22 21:22:33 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 8792152 bytes
Variable Size 436209576 bytes
Database Buffers 754974720 bytes
Redo Buffers 7983104 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 1600206 generated at 04/22/2019 18:04:46 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_5_gcv4t94v_.arc
ORA-00280: change 1600206 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/ORCLCDB/system01.dbf'
ORA-01112: media recovery not started
SQL> recover /opt/oracle/oradata/ORCLCDB/system01.dbf;
ORA-00905: missing keyword
SQL> recover data file 1;
ORA-00905: missing keyword
SQL> recover database;
ORA-00279: change 1600206 generated at 04/22/2019 18:04:46 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_5_gcv4t94v_.arc
ORA-00280: change 1600206 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> recover database;
ORA-00279: change 1600206 generated at 04/22/2019 18:04:46 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_5_gcv4t94v_.arc
ORA-00280: change 1600206 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1601454 generated at 04/22/2019 18:11:21 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_6_gcv5d0m7_.arc
ORA-00280: change 1601454 for thread 1 is in sequence #6
ORA-00279: change 1602872 generated at 04/22/2019 18:20:48 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_7_gcv5d2vh_.arc
ORA-00280: change 1602872 for thread 1 is in sequence #7
ORA-00279: change 1602875 generated at 04/22/2019 18:20:50 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_8_gcv5d504_.arc
ORA-00280: change 1602875 for thread 1 is in sequence #8
ORA-00279: change 1602878 generated at 04/22/2019 18:20:52 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_9_gcv5d639_.arc
ORA-00280: change 1602878 for thread 1 is in sequence #9
ORA-00279: change 1602881 generated at 04/22/2019 18:20:54 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_10_gcv5xbm0_.arc
ORA-00280: change 1602881 for thread 1 is in sequence #10
ORA-00279: change 1802961 generated at 04/22/2019 18:30:02 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_11_gcvgpr1s_.arc
ORA-00280: change 1802961 for thread 1 is in sequence #11
ORA-00279: change 1904184 generated at 04/22/2019 20:28:14 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_12_gcvgpr2n_.arc
ORA-00280: change 1904184 for thread 1 is in sequence #12
ORA-00279: change 2005927 generated at 04/22/2019 20:42:51 needed for thread 1
ORA-00289: suggestion :
/u01/fra/ORCLCDB/archivelog/2019_04_22/o1_mf_1_13_gcvgpr0g_.arc
ORA-00280: change 2005927 for thread 1 is in sequence #13
ORA-00283: recovery session canceled due to errors
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ORCLCDB/redo01.log'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 3 needs more recovery to be consistent
ORA-01110: data file 3: '/opt/oracle/oradata/ORCLCDB/sysaux01.dbf'
SQL> recover datafile 3;
Media recovery complete.
The experiment was unsuccessful and had to be continued.