rman如何在備庫執行一致性備份
rman如何在備庫執行一致性備份
先說下碰到的情況：
如果在dg的備庫進行rman備份，而拿不到最新的歸檔日誌，那麼這些備份很可能不能正常恢復一個新庫。因為，資料庫處於不一致的狀態，即使強制啟庫，也不可行。
Howto make a consistent RMAN backup in an Standby database in Active DataGuard mode (文件 ID 1419923.1)
In this Document
| Goal |
| Solution |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Enterprise Manager for Oracle Database - Version 12.1.0.2.0 to 12.1.0.2.0 [Release 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
GOAL
Howto create a consistent RMAN backup on a Standby database in Active Dataguard Mode.
Consistent means, that the backup is having the datafiles and the related archived redologs, so that it can be opened with this backup. This done on the Primary database by using :
This will execute the following :
- Backup the existing archived redologs
- Backup the datafiles
- Switch a logfile
- Backup all the archives again, but this time it will include all the archived redologs created during and just after the datafile backup. This are the archives which are required to make the datafile backup consistent.
The goal of this document it to accomplish this in an Active DataGuard database.
An Active DataGuard database is a standby database which is open in READ ONLY, but with an ongoing recovery.
This ongoing recovery is updating the datablocks, even while the database is in READ ONLY mode.
From an RMAN perspective the datafiles will behave the same as in a Primary database, meaning that they are FUZZY and will need some recovery to become consistent.
As the standby database is open in READ ONLY, RMAN will NOT be able to force a switch. This is only done when a database is in READ WRITE. The archives are only created when the Primary is switching a logfile. So the above step(4) will NOT backup any new archived redologs and therefor this total backup will not be consistent as it requires additional archives which are not backed up (yet).
SOLUTION
The missing logswitch on the Active DataGuard database can be force, by making an explicit logswitch on the
Primary.
shell script : /usr/local/bin/logswitch.sh
#
# Change <passwd>
# <primary_db>
#
sqlplus -s "sys/<passwd>@<primary_db> as sysdba" <<EOF
alter system archive log current;
exit
EOF
Example of RMAN backup script on the Active Dataguard Standby database
Rman> Configure controlfile autobackup on ;--------------> This would ensure Controlfile auto backup
RMAN> backup database plus archivelog delete input;
host "/usr/local/bin/logswitch.sh";
backup archivelog all delete input;
Additional Comments
The missing 'Log Switch' by making an explicit 'Log Switch' on the Primary.
also does apply to the BASIC DATA GUARD case as well where the database is MOUNTED and in
managed recovery mode (MRP).
A backup taken of a MOUNTED standby database in recovery mode will also not get all of the
archived logs required unless a switch is performed on the primary and a second archived log backup is taken.
For taking consistent RMAN Backups in a basic Standby
"not" in Active DataGuard mode and not running recovery mode (MRP)
.
Please see:
( Doc ID 1292126.1) How to take consistent backups at standby site
.
The logswitch on primary is NOT required in 11.2.0.4 and above anymore.
Reference:
( Doc ID 1616074.1) RMAN-06820 ORA-17629 During Backup at Standby Site
Bug 17580082 : ACTIVE STANDBY - RMAN-6820: WARNING: FAILED TO ARCHIVE CURRENT LOG AT PRIMARY
Status: 32 - Not a Bug
How to take consistent backups at standby site (文件 ID 1292126.1)
In this Document
| Goal |
| Solution |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 08-Jan-2013***
GOAL
How to take an offline, consistent, self-contained backup at the standby site. The aim of this exercise is to have a self-contained backup that can be restored on its own without needing recovery. I.e., since recovery of standby is stopped, the backup is a cold backup.
This process will work for both RAC and non-RAC systems.
SOLUTION
1) stop managed recovery:
2) backup the database and controlfile
If you have a recovery catalog configured you can connect to it and take a backup.
Both the Primary and Standby database should use the same recovery catalog. Even though these database share the same DBID,Rman is able to differentiate the standby database from Primary .
Note you do not need to register the standby database in the catalog if the primary is already registered. Simply connect to the standby as target and then connect to recovery catalog for taking backup.
RMAN> backup current controlfile;
The above will result in a consistent, self-contained backup.
3) restart managed recovery:
To restore from this backup:
RMAN> restore controlfile from 'controlfile backuppiece name and location';
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database noredo;
Please see:
( Doc ID 1419923.1) Howto make a consistent RMAN backup in an Standby database in Active DataGuard mode
REFERENCES
NOTE:1419923.1
- Howto make a consistent RMAN backup in an Standby database in Active DataGuard mode
NOTE:735106.1
- How to Recreate a Controlfile
Step by Step method to create Primary/Standby Database from Standby Backup (文件 ID 1604251.1)
In this Document
| Goal |
| Solution |
| References |
APPLIES TO:
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
GOAL
Case (a): How to create
STANDALONE database from standby backup
Case (b): How to create
STANDBY database from standby backup
SOLUTION
For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:
Database Name:
PRIM
First Standby Unique Name: STBY_1
Second Standby Unique Name: STBY_2
******************
Method of Restoring controlfile makes trick here.
From 11gr2 onwards,
For 11.2.0.2 to 11.2.0.3
For case (a) RMAN> restore controlfile from '<backuppiece>'
eg: RMAN> restore controlfile from 'E:\<path>\PRIM_34_12NEVQQH_1_1.BKP';
Note:
From 11.2.0.4 , behavior is changed due to fix of bug 7553431.
In 11.2.0.4, the following:
RMAN> restore controlfile from '<backuppiece>';
will restore a controlfile with contolfile_type as "STANDBY" instead of "BACKUP". The controlfile type can be found with:
SQL> select controlfile_type from v$database;
Due to controlfile_type=STANDBY, alter database open resetlogs will fail with error... at step (a4)
This issue is fixed with patch 18455956.
Once patch is applied, you should use below command to restore the controlfile as 'BACKUP':
RMAN> restore PRIMARY controlfile from '<backuppiece>'
RMAN> restore standby controlfile from '<Backup piece > ';
eg: RMAN> restore standby controlfile from 'E:\<path>\PRIM_38_16NF0NI4_1_1.BKP';
Note:
Only from 11g, backup of controlfile taken with 'backup current controlfile' can be restored at standby database as:
(a) current controlfile: (restore controlfile from '<backuppiece>'; )
(b) standby controlfile: (restore standby controlfile from '<backuppiece>';)
Example: Case (a) Creating Primary/Normal database from Standby backup.
Sequence of steps:
++ Assuming you have taken backup from standby database. Now you need to create stand alone database using standby backup
Refer below notes for assistance :
How to take consistent backups at standby site (Doc ID 1292126.1)
How to make a consistent RMAN backup in an Standby database in Active DataGuard mode (Doc ID 1419923.1)
Step a1: Create an init file with the following
minimum required parameters:
DB_NAME=PRIM # Database name of standby/primary
DB_UNIQUE_NAME="STBY_1" # can be set for differentiation
control_files='/<path>/<controlfile name>.CTL'
log_file_name_convert='<current standby REDO location>,<new host REDO location>' (#needed only if path is different
)
Step a2:
$ export ORACLE_SID=STBY_1
$ sqlplus / as sysdba
SQL> startup nomount;
Step a3:
In another session
$ export ORACLE_SID=STBY_1
$ rman target /
RMAN> restore controlfile from '<backup location>/<controlfile backuppiece>'; for ( case a)
RMAN> alter database mount;
RMAN> catalog start with '<backuppiece location>';
RMAN
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
set newname for datafile 1 to '<new location/datafile name>';
set newname for datafile 2 to '<new location/datafile name>';
...
set until sequence 212; # this is to set as per your requirement
restore database;
switch datafile all;
recover database;
}
Output:
RMAN> run {
2> allocate channel t1 type disk;
3> allocate channel t2 type disk;
4> set newname for datafile 1 to 'E:\ORADATA\STBY_1\DATA\SYSTEM01.DBF';
5> set newname for datafile 2 to 'E:\ORADATA\STBY_1\DATA\SYSAUX01.DB';
6> set newname for datafile 3 to 'E:\ORADATA\STBY_1\DATA\UNDOTBS01.DBF';
7> set newname for datafile 4 to 'E:\ORADATA\STBY_1\DATA\USERS01.DBF';
8> set until sequence 181;
9> restore database;
10> switch datafile all;
11> recover database;
12> }
released channel: ORA_DISK_1
allocated channel: t1
channel t1: SID=10 device type=DISK
allocated channel: t2
channel t2: SID=97 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET until clause
Starting restore at 01-JUL-12
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to E:\ORADATA\STBY_1\DATA\SYSTEM01.DBF
channel t1: restoring datafile 00002 to E:\ORADATA\STBY_1\DATA\SYSAUX01.DB
channel t1: restoring datafile 00003 to E:\ORADATA\STBY_1\DATA\UNDOTBS01.DBF
channel t1: restoring datafile 00004 to E:\ORADATA\STBY_1\DATA\USERS01.DBF
channel t1: reading from backup piece E:\<path>\PRIM_31_0VNEVQM2_1_1.BKP
channel t1: piece handle=E:\<path>\PRIM_31_0VNEVQM2_1_1.BKP tag=TAG20120701T070538
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:01:05
Finished restore at 01-JUL-12
datafile 1 switched to datafile copy
input datafile copy RECID=19 STAMP=787476336 file name=E:\ORADATA\STBY_1\DATA\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=20 STAMP=787476336 file name=E:\ORADATA\STBY_1\DATA\SYSAUX01.DB
datafile 3 switched to datafile copy
input datafile copy RECID=21 STAMP=787476336 file name=E:\ORADATA\STBY_1\DATA\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=22 STAMP=787476336 file name=E:\ORADATA\STBY_1\DATA\USERS01.DBF
Starting recover at 01-JUL-12
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-JUL-12
released channel: t1
released channel: t2
RMAN>
Step a4: Open the database:
SQL> alter database open resetlogs.
++ if log_file_name_convert is set at init , during database open, ORL will be created as per its value.
example: Init.ora
log_file_name_convert = 'D:\ORADATA\STBY_1\REDO\','E:\ORADATA\STBY_1\REDO\'
else, at mount stage, you need to rename the online redo log location as below otherwise the 'alter database open resetlogs' will throw a 'file not found' error.
To do this, at mount stage:
SQL> alter database rename file '<old file location and name>' to '<new location and name>';
SQL> alter database open resetlogs;
For 11.2.0.4 :
Prior to applying the patch controlfile restore as 'STANDBY'
SQL> select controlfile_type from v$database;
CONTROL
-------
STANDBY <========
After applying the patch 18455956 controlfile restored as 'BACKUP'
Example in 11.2.0.4
Step a2:
$ export ORACLE_SID=aux
$ sqlplus / as sysdba
SQL> startup nomount;
Step a3:
In another session
$ export ORACLE_SID=aux
$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 31 17:23:17 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (not mounted)
RMAN> restore primary controlfile from '/home/oracle/PRIM_176_5gr1kmhv_1_1.bkp';
RMAN> exit
In sql session
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 31 17:23:42 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> alter database mount;
Database altered.
SQL> select controlfile_type from v$database;
CONTROL
-------
BACKUP <======
Since controlfile type would be 'STANDBY step (a4) will fail at 'Alter database open resetlogs'. With any of below option you can open the database as primary role:
Option (a) Create trace file of controlfile
SQL> alter database backup controlfile to trace resetlogs;
Use trace file generated by command to recreate the controlfile.
SQL> recover database using backup controlfile until cancel; # Cancel the recovery
SQL> alter database open resetlogs;
Option (b) Activate the Standby
SQL> Alter database activate physical standby database;
SQL> Alter database open;
Note: Prior to activate need to clear standby logfile if we get error ORA-00314
Example : Case (b) Creating standby database from standby backup;
# This is not cascaded standby database.
# Below example is to create another standby database from existing standby backup and to add as second standby database to existing primary database
You need to change syntax on step (b3) of case (a) to case (b) like below
RMAN> restore standby controlfile from '<backup location/<backuppiece containing controlfile>';
Step b1: Create an init file with the following minimum required parameters:
DB_NAME=PRIM # Database name of standby/primary
DB_UNIQUE_NAME="STBY_2" # can be set for differentiation
control_files='/<path>/<controlfile name>.CTL'
FAL_SERVER=<your existing primary service>
log_file_name_convert = '<current standby REDO location >,<new host REDO location>' (#needed only if path is different)
Step b2:
$ export ORACLE_SID=STBY_2
$ sqlplus / as sysdba
SQL> startup nomount;
Step b3:
In another session
$ export ORACLE_SID=STBY_2
$ rman target /
RMAN> restore standby controlfile from '<backup location/backuppiece with controlfile>'; # Restoring controlfile as STANDBY type
RMAN> alter database mount;
RMAN> catalog start with '< backup piece location>';
RMAN>
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
set newname for datafile 1 to '<new location/datafile name>';
set newname for datafile 2 to '<new location/datafile name>';
...
set until sequence 212; # this is to set as per your requirement
restore database;
switch datafile all;
recover database;
}
Step b4: Once new standby database is recovered, mount and start MRP:
SQL> recover managed standby database disconnect;
Step b5: Configure and enable redo shipping from existing primary to new standby database.
NOTE : From 11.2 onward use RMAN DUPLICATE from ACTIVE DATABASE by connecting to standby as target to create standby.
How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (文件 ID 1354256.1)
In this Document
| Goal |
| Solution |
| Check 1: Checkpoint Time and Fuzziness |
| Check 2: Datafile Status |
| Check 3: Absolute Fuzzy |
| Check 4: Archive Logs Required |
|
Check 5: Post OPEN RESETLOGS Activities |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.0.1.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 05-Nov-2014***
GOAL
What is the LEAST amount of recovery that has to be done before a database restored from a backup can be opened?
After performing a RESTORE / RECOVER, we need to perform a quick validation to ensure database is consistent and ready for OPEN RESETLOGS.
This proactive check helps to prevent several issues which may appear during or after OPEN RESETLOGS.
Through out this note we assume that you are restoring from a valid backup.
There can be more scenarios than discussed here. Please consult Oracle Support when in doubt.
SOLUTION
For cold/offline backups, no archivelogs/recovery is necessary. You can simply open the database with resetlogs.
However for HOT/ONLINE backups, ALL archivelogs from backup start to backup end must be applied before the database can be opened - this is the MINIMUM amount of recovery needed.
To determine which log was current at the time the backup completed, note the COMPLETION time of the database backup - take this from the backup log.
If this is an RMAN backup you can also query the RMAN metadata. Ensure that the environment variable NLS_DATE_FORMAT is set before invoking rman so that timestamps as well as date are returned:
For unix:
% rman target /
For windows:
> rman target /
To find your backup:
RMAN> LIST BACKUP OF DATABASE COMPLETED AFTER '<date>';
or
RMAN> LIST BACKUP OF DATABASE COMPLETED AFTER 'sysdate-n';
Set <date> to limit the output to the backups that you are interested and note the completion time - for a multi-piece backup, note the completion time of the LAST backuppiece created.
Through out this note, when running SQL queries you should set NLS_DATE_FORMAT at the session level as follows:
Check 1: Checkpoint Time and Fuzziness
Objective:
Verify that the datafiles are recovered to the intended point in time (PIT) and they are consistent (FUZZY=NO)
Query the current status and PIT (
Point
In
Time up to which the datafiles have been recovered) of datafiles by reading datafile headers directly from the physical datafiles:
FUZ STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
--- ------- --------------- --- ------------------ -------------------- ----------
NO ONLINE 5311260 31-AUG-2011 23:10:14 6
YES ONLINE 5311260 31-AUG-2011 23:10:14 1
a) Verify that the checkpoint_time / checkpoint_change# is in line with your intended UNTIL TIME/SCN. If not, recover the database further if you have more archived logs available.
b) If FUZZY=YES for some datafiles, it means more recovery is required. If these archived logs are lost, identify such datafiles and determine if we can take them offline.
Warning: we will lose data in those datafiles if they are taken offline!
If the datafiles belong to SYSTEM or UNDO tablespace, we MUST not bring such datafile offline without proper analysis. Please consult Oracle Support for further actions.
FILE# SUBSTR(NAME,1,50) SUBSTR(TABLESPA UNDO_OPT_CURRENT_CHANGE#
---------- -------------------------------------------------- --------------- ------------------------
3 /<path>/undotbs01.dbf UNDOTBS1 5117431
Occasionally, if the tablespace name doesn't indicate it to be UNDO tablespace, if we see non-zero value in column UNDO_OPT_CURRENT_CHANGE#, it indicates that the datafile contains undo segments.
To bring a datafile offline :
Check 1 can be considered PASSED when :
a) Verified that all the datafiles are at the some checkpoint_time, and this is your intended Point in time.
b) Fuzzy=NO for SYSTEM, UNDO and all intended datafiles. For datafiles with Fuzzy=YES, either recover them further or bring them OFFLINE if no further archived logs are available.
Check 2: Datafile Status
Objective:
Verify that the files with status=RECOVER are not OFFLINE unintentionally
STATUS ENABLED COUNT(*)
------- ---------- ----------
SYSTEM DISABLED 1
ONLINE READ WRITE 4
RECOVER DISABLED 2
If the files are in RECOVER status, verify if they are OFFLINE :
If you want the data for these files to be accessible, then bring them ONLINE :
Check 2 can be considered Passed when:
All the intended datafiles are not OFFLINE
Check 3: Absolute Fuzzy
Objective:
Additional Fuzzy check (Absolute Fuzzy check)
Occasionally, it is possible to see Fuzzy=NO and same checkpoint_change# for all the intended datafiles but OPEN RESETLOGS still fails
eg:
FUZ STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
--- ------- --------------- --- ------------------ -------------------- ----------
NO ONLINE 5311260 31-AUG-2011 23:10:14 7
SQL> ALTER DATABASE OPEN RESETLOGS ;
ORA-01194: file 4 needs more recovery to be consistent
ORA-01110: data file 3: '/<path>/undotbs02.dbf'
Hence, we should perform additional fuzzy check known as Absolute Fuzzy Check:
FILE# NAME CHECKPOINT_CHANG ABSOLUTE_FUZZY_S MIN_PIT_SCN
---------- -------------------------------------------------- ---------------- ---------------- ----------------
4 /<path>/undotbs01.dbf 5311260 5311524 5311524
6 /<path>/system01.dbf 5311260 5311379 5311524
Above query indicates that the recovery must be performed at least UNTIL SCN 5311524 to make datafiles consistent and ready to OPEN. Since the checkpoint_change# is smaller than Min_PIT_SCN, the datafiles will ask for more recovery.
Check 3 can be considered PASSED when:
a) No rows selected from above query (i.e. Min_PIT_SCN is 0 (Zero) for all the datafiles)
b) Min_PIT_SCN is returned less than Checkpoint_Change#
Check 4: Archive Logs Required
Query the controlfile to find the latest archivelog required fore recovery. Lets say the backup completed at 31-AUG-2011 23:20:14:
SQL> --
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG
WHERE '31-AUG-11 23:20:14' BETWEEN FIRST_TIME AND NEXT_TIME;
If the above query does not return any rows, it may be that the information has aged out of the controlfile - run the following query against v$log_history.
SQL> --
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> select a.THREAD#, a.SEQUENCE#, a.FIRST_TIME
from V$LOG_HISTORY a
where FIRST_TIME =
( SELECT MAX(b.FIRST_TIME)
FROM V$LOG_HISTORY b
WHERE b.FIRST_TIME < to_date( '31-AUG-11 23:20:14', 'DD-MON-RR HH24:MI:SS')
) ;
SQL>
The sequence# returned by the above query is the log sequence current at the time the backup ended - let say 530 thread 1.
For minimum recovery use: (Sequence# as returned +1 )
{
SET UNTIL SEQUENCE 531 THREAD 1;
RECOVER DATABASE;
}
If this is a RAC implementation the use this SQL instead to query the controlfile:
For minimum recovery use the log sequence and thread that has the lowest NEXT_CHANGE# returned by the above query.
Check 4 can be considered PASSED when:
All archivelogs from the time of the backup to the end of the backup is available for use during recovery
Check 5: Post OPEN RESETLOGS Activities
During OPEN RESETLOGS, monitor the alert.log for additonal errors/messages. You might see some messages like below during dictionary check:
Tablespace 'TEMP' #3 found in data dictionary, <(============================== (1)
but not in the controlfile. Adding to controlfile.
Tablespace 'USERS' #4 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #4 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00004' in the controlfile. <(==================== (2)
File #5 is online, but is part of an offline tablespace. <(==================== (3)
data file 5: '/<path>/example01.dbf'
File #7 found in data dictionary but not in controlfile. <(==================== (2)
Creating OFFLINE file 'MISSING00007' in the controlfile.
File #8 is offline, but is part of an online tablespace. <(==================== (4)
data file 8: '/<path>/mydata02.dbf'
File #9 is online, but is part of an offline tablespace. <(==================== (3)
data file 9: '/<path>/example02.dbf'
Dictionary check complete
We discuss below the points highlighted :
(1) Check if the temp files exist. If not, add them as per your preference:
no rows selected
SQL> select file#, name from dba_temp_files ;
no rows selected
SQL> select tablespace_name, status, contents from dba_tablespaces where contents='TEMPORARY' ;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
TEMP ONLINE TEMPORARY
SQL> alter tablespace temp add tempfile '/<path>/temp01.dbf' size 10m ;
Tablespace altered.
SQL> select file#, substr(name, 1, 50), status, enabled from v$tempfile
FILE# SUBSTR(NAME,1,50) STATUS ENABLED
-------- -------------------------------------------------- ------- ----------
1 /<path>/temp01.dbf ONLINE READ WRITE
(2) It appears that the tablespace was brought offline using "ALTER TABLESPACE USERS OFFLINE" command. So, verify if the missing files really exist with original name. You may need to consult your pear DBAs, or refer alert.log / RMAN backup log or any such information which may provide clue about the actual file name.
If you find the file, try to rename them. If not, we can offline the datafile or drop associated tablespace:
FILE# STATUS ENABLED SUBSTR(NAME,1,50)
-------- ------- ---------- --------------------------------------------------
4 OFFLINE DISABLED /<path>/MISSING000
7 OFFLINE DISABLED /<path>/MISSING000
SQL> alter database datafile 4 online ;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: '/<oracle_home path>/dbs/MISSING00004'
SQL> alter database rename file 'MISSING00004' to '/<path>/users01.dbf' ;
Database altered.
SQL> alter database rename file 'MISSING00007' to '/<path>/users02.dbf' ;
Database altered.
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name in (select tablespace_name from dba_data_files where file_id in (4, 7)) ;
TABLESPACE_NAME STATUS
------------------------------ ---------
USERS OFFLINE
SQL> ALTER TABLESPACE USERS ONLINE ;
Tablespace altered.
Before proceeding, let's query the status for these files in alert.log:
SQL> select a.file#, substr(a.name, 1, 50) file_name, a.status file_status, a.error, substr(a.tablespace_name, 1, 10) tablespace_name, b.status tablespace_status from v$datafile_header a, dba_tablespaces b
where a.tablespace_name=b.tablespace_name /* and a.file# in (4, 5, 7, 8, 9) */ ;
FILE# FILE_NAME FILE_STATUS ERROR TABLESPA TABLESPACE_STATUS
----- --------------------------------------------- ----------- --------------- -------- ------------------
1 /<path>/system01.dbf ONLINE SYSTEM ONLINE
2 /<path>/sysaux01.dbf ONLINE SYSAUX ONLINE
3 /<path>/undotbs01.dbf ONLINE UNDOTBS1 ONLINE
4 /<path>/users01.dbf OFFLINE OFFLINE NORMAL USERS OFFLINE <(== related to (2) in alert.log excerpt above
5 /<path>/example01.dbf ONLINE EXAMPLE OFFLINE <(== related to (3) in alert.log excerpt above
6 /<path>/mydata01.dbf ONLINE MYDATA ONLINE
7 /<path>/users02.dbf OFFLINE OFFLINE NORMAL USERS OFFLINE <(== related to (2) in alert.log excerpt above
8 /<path>1/mydata02.dbf OFFLINE WRONG RESETLOGS MYDATA ONLINE <(=== related to (4) in alert.log excerpt above
9 /<path>/example02.dbf ONLINE EXAMPLE OFFLINE <(== related to (3) in alert.log excerpt above
9 rows selected.
So, we can attempt to correct the "ERROR" as displayed in above query depending on the availability of file / archived logs and other possible factors.
Let's continue,
(3) It seems that tablespace was brought offline inconsistently ( ALTER TABLESPACE EXAMPLE OFFLINE IMMEDIATE ). If the archived log generated at that time has got applied, the file may be back online :
Tablespace altered.
(4) This tablespace MYDATA has 2 datafiles File# 6 & 8. It appears that File# 8 was brought offline ( using ALTER DATABASE DATAFILE 8 OFFLINE ) and it was OFFLINE before OPEN RESETLOGS. If the archived log generated at that time has got applied during recovery or all the archived logs are available for recovery since that time, the file may be back online :
alter database datafile 8 online
*
ERROR at line 1:
ORA-01190: control file or data file 8 is from before the last RESETLOGS
ORA-01110: data file 8: '/<path>/mydata02.dbf'
SQL> alter tablespace mydata online ;
alter tablespace mydata online
*
ERROR at line 1:
ORA-01190: control file or data file 8 is from before the last RESETLOGS
ORA-01110: data file 8: '/<path>/mydata02.dbf'
SQL> recover datafile 8 ;
Media recovery complete.
SQL> alter database datafile 8 online ;
Database altered.
SQL> alter tablespace mydata online ;
Tablespace altered.
Please note that it is not always possible to recover and bring the file online which is failing with error " ORA-01190: control file or data file x is from before the last RESETLOGS".
(5) There can be a scenario where the tablespace was in READ ONLY mode before OPEN RESETLOGS. Please check below Article on that:
Note 266991.1 Recovering READONLY tablespace backups made before a RESETLOGS Open
REFERENCES
NOTE:238422.1
- RMAN recover database fails RMAN-6025 - v$archived_log.next_change# is 281474976710655
NOTE:266991.1
- Recovering READONLY tablespace backups made before a RESETLOGS Open
About Me
|
........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在個人微 信公眾號（ DB寶）上有同步更新 ● QQ群號： 230161599 、618766405，微信群私聊 ● 個人QQ號（646634621），微 訊號（db_bao），註明新增緣由 ● 於 2020年9月 在西安完成 ● 最新修改時間：2020年9月 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用、DBA學習班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ........................................................................................................................ 請掃描下面的二維碼來關注小麥苗的微 信公眾號（ DB寶）及QQ群（230161599、618766405）、新增小麥苗微 信（db_bao）， 學習最實用的資料庫技術。
........................................................................................................................ |
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2723840/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【RMAN】在備庫執行rman備份時報錯RMAN-06820 ORA-17629
- rman 備份 全庫
- 備份之全庫一致性備份
- RMAN說，我能備份(3)--RMAN全庫備份和表空間備份
- 整庫(whole)備份-一致性整庫備份
- RMAN備份之備份多個備份集到帶庫（三）
- RMAN備份之備份多個備份集到帶庫（二）
- RMAN備份之備份多個備份集到帶庫（一）
- 整庫(whole)備份-非一致性整庫備份
- RMAN備份的並行並行
- 【RMAN】使用增量備份更新資料庫備份映象資料庫
- RMAN備份恢復——備份到帶庫的效能
- Backup And Recovery User's Guide-備份RMAN備份-使用RMAN備份備份集GUIIDE
- 使用RMAN備份資料庫資料庫
- RMAN全庫備份指令碼指令碼
- Backup And Recovery User's Guide-備份資料庫-RMAN備份概覽-RMAN備份的目的GUIIDE資料庫
- rman全庫備份備份歸檔日誌檔案
- RMAN備份恢復——備份到帶庫的效能（二）
- RMAN 全庫備份和 0級備份的區別
- 【原】Oracle學習系列—資料庫備份—RMAN備份Oracle資料庫
- RMAN說，我能備份(9)--RMAN增量備份與備份保留策略
- Backup And Recovery User's Guide-備份資料庫-RMAN備份概覽-進行壓縮備份GUIIDE資料庫
- Backup And Recovery User's Guide-備份RMAN備份-用RMAN備份映象拷貝備份GUIIDE
- Backup And Recovery User's Guide-備份資料庫-RMAN備份概覽-為RMAN備份指定TAGGUIIDE資料庫
- Backup And Recovery User's Guide-RMAN備份概念-RMAN何時執行控制檔案自動備份GUIIDE
- 獲取rman備份/恢復執行進度資訊
- 什麼是備份資料庫？什麼是資料庫一致性備份和非一致性備份?資料庫
- RMAN說，我能備份(14)--實戰RMAN備份
- RMAN備份多個備份集到帶庫的小bug
- Backup And Recovery User's Guide-備份資料庫-RMAN備份概覽-為RMAN備份指定格式GUIIDE資料庫
- rman備份和增量備份指令碼指令碼
- 利用RMAN備份重建資料庫資料庫
- RMAN備份恢復整個庫
- RMAN備份到帶庫被阻塞
- rman 備份策略
- RMAN備份原理
- 備份策略 增量備份中的差異備份：週日和週三執行0級備份，其他每天執行1級備份
- 如何利用Rman對Oracle資料庫進行備份Oracle資料庫