OMF flashback database new added Datafiles

jnrjian

已于 2023-08-30 19:50:46 修改

阅读量195

点赞数

文章标签： ffmpeg 数据库

于 2023-08-30 17:59:46 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/132585536

版权

Flashback Recovery With Added Datafiles (Doc ID 1538357.1)

I have two guaranteed restore points with a datafile added to the tablespace between. Once I flashback to first restore point, the first datafile gets removed from the database and disk.(flashback自动删除没用到的data文件的) This is expected behaviour. Once that flashback is complete, and I open the database with resetlogs. 再次flashback（falshback 期间加数据文档可能导致了ADG文件丢失？？） I get errors trying to flash this same database back to the second guaranteed restore point which should bring back the added datafile.

What is the procedure for running this second flashback?

SOLUTION

Therefore, before running the second flashback (to a new restore point), you must either run 'flashback database to before resetlogs' OR reset the database incarnation.

The key is bringing the database back to the resetlogs for which the newly added datafile will belong.

Consider the following testcase:

1. Take guarantee restore point as b4user

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> select * from v$restore_point;

no rows selected

SQL> CREATE RESTORE POINT b4user GUARANTEE FLASHBACK DATABASE;

Restore point created.

2. add tablespace and create user with default tablespace as the new tablespace created.

SQL> alter system switch logfile;

System altered.

SQL> create tablespace RP_TEST datafile '/u01/V112_oradata/rp_test.dbf'
2 size 500M;

Tablespace created.

SQL> create user rp_user identified by test default tablespace rp_test;

User created.

3. grant dba privs to new user created.

SQL> grant dba to rp_user;

Grant succeeded.

4. create test table as select * from dba_objects in new user.

SQL> create table rp_user.test as select * from dba_objects;

Table created.

SQL> select count(*) from rp_user.test;

COUNT(*)
----------
14003

5. take guarantee restore point as afteruser.

SQL> alter system switch logfile;

System altered.

SQL> CREATE RESTORE POINT afteruser GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> alter system switch logfile;

System altered.

6. Now, rollback to restore point b4user

$ ls -l /u01/V112_oradata/rp_test.dbf
-rw-r----- 1 oracle dba 524296192 Mar 17 11:33 /u01/V112_oradata/rp_test.dbf -还在
$

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 17 11:33:58 2013

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

connected to target database: V112 (DBID=2313187069)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name V112

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1000 SYSTEM *** /u01/V112_oradata/system01.dbf
2 970 SYSAUX *** /u01/V112_oradata/sysaux01.dbf
3 6144 UNDOTBS1 *** /u01/V112_oradata/undotbs01.dbf
4 1024 USERS *** /u01/V112_oradata/users01.dbf
5 500 RP_TEST *** /u01/V112_oradata/rp_test.dbf

RMAN> list restore point all;

SCN RSP Time Type Time Name
---------------- --------- ---------- --------- ----
44205249 GUARANTEED 17-MAR-13 B4USER
44205810 GUARANTEED 17-MAR-13 AFTERUSER

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 631914496 bytes

Fixed Size 1346784 bytes
Variable Size 423625504 bytes
Database Buffers 201326592 bytes
Redo Buffers 5615616 bytes

RMAN> flashback database to restore point B4USER;

Starting flashback at 17-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/temp/flash_areas/V112/archivelog/2013_03_17/o1_mf_1_1_8ncrcwql_.arc
media recovery complete, elapsed time: 00:00:01
Finished flashback at 17-MAR-13

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.

$ ls -l /u01/V112_oradata/rp_test.dbf
ls: /u01/V112_oradata/rp_test.dbf: No such file or directory
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 17 11:37:31 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from rp_user.test;
select * from rp_user.test
*
ERROR at line 1:
ORA-00942: table or view does not exist

7. After successful rollback, roll-forward to restore point afteruser, using the steps given in the SR.

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 631914496 bytes

Fixed Size 1346784 bytes
Variable Size 423625504 bytes
Database Buffers 201326592 bytes
Redo Buffers 5615616 bytes

RMAN> list restore point all;

SCN RSP Time Type Time Name
---------------- --------- ---------- --------- ----
44204679 GUARANTEED 17-MAR-13 B4USER
44205757 GUARANTEED 17-MAR-13 AFTERUSER

RMAN> report schema;

Report of database schema for database with db_unique_name V112

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1000 SYSTEM *** /u01/V112_oradata/system01.dbf
2 970 SYSAUX *** /u01/V112_oradata/sysaux01.dbf
3 6144 UNDOTBS1 *** /u01/V112_oradata/undotbs01.dbf
4 1024 USERS *** /u01/V112_oradata/users01.dbf

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 V112 2313187069 PARENT 39833795 28-JAN-13
2 2 V112 2313187069 PARENT 41283854 25-FEB-13
3 3 V112 2313187069 ORPHAN 44200518 17-MAR-13
4 4 V112 2313187069 PARENT 44202630 17-MAR-13
5 5 V112 2313187069 CURRENT 44204681 17-MAR-13

RMAN> flashback database to before resetlogs;

Starting flashback at 17-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

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

Finished flashback at 17-MAR-13

RMAN> flashback database to restore point AFTERUSER;

Starting flashback at 17-MAR-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/temp/flash_areas/V112/archivelog/2013_03_17/o1_mf_1_1_8ncrcwql_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/temp/flash_areas/V112/archivelog/2013_03_17/o1_mf_1_2_8ncrkcvy_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/temp/flash_areas/V112/archivelog/2013_03_17/o1_mf_1_3_8ncrqmx1_.arc
media recovery complete, elapsed time: 00:00:03
Finished flashback at 17-MAR-13

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name V112

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1000 SYSTEM *** /u01/V112_oradata/system01.dbf
2 970 SYSAUX *** /u01/V112_oradata/sysaux01.dbf
3 6144 UNDOTBS1 *** /u01/V112_oradata/undotbs01.dbf
4 1024 USERS *** /u01/V112_oradata/users01.dbf
5 0 RP_TEST *** /u02/app/oracle/11.2.3/dbs/UNNAMED00005

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database create datafile '/u02/app/oracle/11.2.3/dbs/UNNAMED00005' as
2 '/u01/V112_oradata/rp_test.dbf';

Database altered.

SQL> select status, resetlogs_change# from v$datafile_header;

STATUS RESETLOGS_CHANGE#
------- -----------------
ONLINE 44204681
ONLINE 44204681
ONLINE 44204681
ONLINE 44204681
OFFLINE 44204681

SQL> alter database datafile 5 online;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database required recovery
ORA-01152: file 5 was not restored from a sufficiently old backup
ORA-01110: data file 5: '/u01/V112_oradata/rp_test.dbf'

SQL> flashback database to restore point AFTERUSER; --再搞一次就好了？

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from rp_user.test;

COUNT(*)
----------
14003

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 631914496 bytes
Fixed Size 1346784 bytes
Variable Size 423625504 bytes
Database Buffers 201326592 bytes
Redo Buffers 5615616 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from rp_user.test;

COUNT(*)
----------
14003

---------------------------alter database create datafile '/u02/app/oracle/11.2.3/dbs/UNNAMED00005' as '/u01/V112_oradata/rp_test.dbf'; 搞不定的话就需要restore datafile，按道理由于有一个datafile从0到1的全过程，不需要restore datafile的！！

This document shows how to recover from a DROP TABLESPACE statement using Flashback Database.

SCOPE

To recover from DROP TABLESPACE using Flashback Database, the following conditions must be met:

A backup of the tablespace must exist prior to when the DROP TABLESPACE was issued.
All archived log files must exist from when the backup was created to when the DROP TABLESPACE was issued.

DETAILS

1. In the alert log, Oracle records the time that a DROP TABLESPACE statement is issued and the time it completes. Find the message indicating the DROP TABLESPACE statement was issued. For example:

Tue Feb 17 10:02:02 2009
drop tablespace ts1 including contents and datafiles

2. Ensure that the database can be flashed back to before the DROP TABLESPACE command. If the time returned by the query below is later than when the DROP TABLESPACE statement was issued, then flashback database cannot be used to recover the tablespace.

SQL> select to_char(oldest_flashback_time,'Dy Mon DD HH24:MI:SS YYYY')
from v$flashback_database_log;

TO_CHAR(OLDEST_FLASHBACK
------------------------
Wed Feb 10 19:34:12 2009

3. Flashback the database to before the DROP TABLESPACE command. Use the date stamp taken from the alert log.

RMAN> run {

# Flashback database requires a mounted database
shutdown immediate;
startup mount;

# Flashback the database to just before the DROP occurred
flashback database to before time
"to_date('Tue Feb 17 10:02:02 2009','Dy Mon DD HH24:MI:SS YYYY')";
}

Note that during the flashback database operation the datafiles that were part of the dropped tablespace are added back into the control file as UNNAMED datafiles. The UNNAMED datafile name and the name the datafile was originally created as are required to properly recover the dropped tablespace.

Recovery deleting file #6:'/u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00006' from controlfile.
Recovery deleting file #7:'/u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00007' from controlfile.
Recovery dropped tablespace 'TS1'
Flashback recovery: Added file #6 to control file as OFFLINE and 'UNNAMED00006'
because it was dropped during the flashback interval
or it was added during flashback media recovery.
File was originally created as:
'+ENG/<path>/datafile/ts1.14360.679053609'
File will have to be restored from a backup or
recreated using ALTER DATABASE CREATE DATAFILE command,
and the file has to be onlined and recovered.
Flashback recovery: Added file #7 to control file as OFFLINE and 'UNNAMED00007'
because it was dropped during the flashback interval
or it was added during flashback media recovery.
File was originally created as:
'+ENG/<path>/datafile/ts1.17165.679053609'
File will have to be restored from a backup or
recreated using ALTER DATABASE CREATE DATAFILE command,
and the file has to be onlined and recovered.

Note: It is possible to open the database READ ONLY to query data that exists outside the dropped tablespace to determine whether or not the flashback operation should proceed (data within the dropped tablespace is not available until it is recovered in a subsequent step). If you wish to continue with the flashback database operation after opening the database READ ONLY, then SHUTDOWN and STARTUP MOUNT the database before proceeding to the next step.

4. Ensure a backup exists of the tablespace prior to it being dropped. Use the date stamp taken from the alert log. Crosscheck the backup to verify status.

RMAN> list backup of tablespace ts1 completed before
"to_date('Tue Feb 17 10:02:02 2009','Dy Mon DD HH24:MI:SS YYYY')";

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
5       Full    184.00K    DISK        00:00:01     17-FEB-09 10:00:42
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20090217T100041
        Piece Name: +ENG/<path>/backupset/2009_02_17/nnndf0_tag20090217t100041_0.9705.679053641
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time           Name
  ---- -- ---- ---------- ------------------ ----
  6       Full 1039902    17-FEB-09 10:00:41 /u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00006
  7       Full 1039902    17-FEB-09 10:00:41 /u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00007

Crosscheck the backup to verify status.

RMAN> crosscheck backup of tablespace ts1 completed before
"to_date('Tue Feb 17 10:02:02 2009','Dy Mon DD HH24:MI:SS YYYY')";

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=+ENG/<path>/backupset/2009_02_17/nnndf0_tag20090217t100041_0.9705.679053641 RECID=5 STAMP=679053641
Crosschecked 1 objects

If there is no valid backup of the tablespace, then the tablespace cannot be recovered. The flashback database command should be abandoned by running RECOVER DATABASE. Refer to Oracle Database Backup and Recovery User's Guide for details.

上面的文档实现了没有这个datafile的backup也可以！！毕竟有所有的archived log

5. If there exists a valid backup for the dropped tablespace, then continue with recovery of the dropped tablespace and opening the database. During this step, all UNNAMED datafiles must be renamed back to their proper name with SET NEWNAME. This information is taken from the alert log output identified above.

RMAN> run {

# Rename the UNNAMED datafiles back to their original names.
# There will be one SET NEWNAME command for each UNNAMED datafile.
set newname for
datafile '/u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00007'
to '+DATA/<path>/datafile/ts1.17165.679053609';

set newname for
datafile '/u01/app/oracle/product/11.1.0/11.1.0.7/db/dbs/UNNAMED00006'
to '+DATA/<path>/datafile/ts1.14360.679053609';

# Restore the files from the backup
# and switch the controlfile to point to them
restore tablespace ts1;
switch datafile all;

# Open resetlogs - required for flashback database
alter database open resetlogs;

# Recover and online the tablespace that was dropped
recover tablespace ts1;
sql "alter tablespace ts1 online";
}

There exists a hidden initialization parameter _OMF that can be either enabled or disabled

col Parameter format a40
col "Instance Value" format a20
select KSPPINM "Parameter",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm like '%_omf%';

Parameter                                Instance Value
---------------------------------------- --------------------
_omf                                     enabled

col name format a25
col value format a10
select x.ksppinm name, y.kspftctxvl value, y.kspftctxdf isdefault, decode(bitand(y.kspftctxvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.kspftctxvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv2 y where x.inst_id = userenv('Instance')
and y.inst_id = userenv('Instance') and x.indx+1 = y.kspftctxpn
and x.ksppinm like '%omf%' ;

NAME                      VALUE      ISDEFA ISMOD      ISADJ
------------------------- ---------- ------ ---------- -----
_omf                      enabled    TRUE   FALSE      FALSE

To disable the Oracle Managed Files (OMF) feature you have to :
1. add the CONTROL_FILES parameter in the pfile/ spfile;

And

2. remove DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameter from pfile/ spfile;

3. Then restart the database.

Don't modify "_omf" until it is asked by Oracle Support

Users of Oracle Managed Files.

DETAILS

OMF (Oracle Managed Files) Usage for Datafiles:
===============================================

You decide to manage your datafiles with OMF.

*** *************************************************
*** Homogeneous Test : tablespace with OMF files only
*** *************************************************

1. In init.ora:

DB_CREATE_FILE_DEST=/oradata/<SID>

2. Create a tablespace using the OMF syntax:

SQL> create tablespace omf_domi datafile size 100k;

   Tablespace created.

   SQL> select name from v$datafile;

   NAME
   ------------------------------------------
   ...
   /oradata/<SID>/ora_omf_domi_xcpsj3tv.dbf

3. Add a datafile to the tablespace using the OMF syntax:

   SQL> select name from v$datafile;

   NAME
   ------------------------------------------
   ...
   /oradata/<SID>/ora_omf_domi_xcpsj3tv.dbf
   /oradata/<SID>/ora_omf_domi_xcpspo63.dbf

   SQL> alter tablespace omf_domi add datafile '/oradata/<SID>/ora_do03.dbf' size 100k;

   alter tablespace omf_domi add datafile '/oradata/<SID>/ora_do03.dbf'
   *
   ERROR at line 1:
   ORA-01276: Cannot add a file with an Oracle Managed Files file name.

4. Add another datafile naming it explicitly:

   SQL> alter tablespace omf_domi add datafile '/oradata/<SID>/do03.dbf'; 可以命名不能ORA开头

   Tablespace altered.

NOTE : Be cautious: it is strongly recommended NOT to explicitly name the OMF files. It is likely not to work in the future.

5. Drop the tablespace and check that all datafiles are removed on the operating system:

   SQL> drop tablespace omf_domi;

   Tablespace dropped.

   SQL> select name from v$datafile where name like 'ora_omf%';

   no rows selected.

   In alert.log file:

   Deleted Oracle managed file /oradata/<SID>/ora_omf_domi_xcpsj3tv.dbf
   Deleted Oracle managed file /oradata/<SID>/ora_omf_domi_xcpspo63.dbf
   Fri Mar 23 16:09:36 2001

   Completed: drop tablespace omf_domi

   $ ls -l /oradata/<SID>/ora_omf*
   /oradata/<SID>/ora_omf*: No such file or directory

   $ ls -l /oradata/<SID>
   -rw-r-----   1 ora901   dba       114688 juin 15 11:30 do03.dbf

*** ***********************************************************************
*** Homogeneous test : tablespace with OMF located in different directories
*** ***********************************************************************

   SQL> create tablespace omf_domi;

   Tablespace created.

   SQL> alter system set db_create_file_dest='/oradata/<SID>/TEST';

   System altered.

   SQL> alter tablespace omf_domi add datafile;

   Tablespace altered.

   SQL> select name from v$datafile;

   NAME
   -----------------------------------------------
   ...
   /oradata/<SID>/ora_omf_domi_xcpvst5q.dbf
   /oradata/<SID>/TEST/ora_omf_domi_xcpvxk0l.dbf

   SQL> drop tablespace omf_domi;

   Tablespace dropped.

   In alert log file:

   Fri Mar 23 16:46:29 2001
   Deleted Oracle managed file /oradata/<SID>/ora_omf_domi_xcpvst5q.dbf
   Deleted Oracle managed file /oradata/<SID>/TEST/ora_omf_domi_xcpvxk0l.dbf

   $ ls -l /oradata/<SID>/ora_omf*
   /oradata/<SID>/ora_omf*: No such file or directory
   $ ls -l /oradata/<SID>/TEST/ora_omf*
   /oradata/<SID>/TEST/ora_omf*: No such file or directory

   All OMF have been correctly removed on the OS.

*** **********************************************************************
*** Heterogeneous test: OMF and non-OMF datafiles within the same database
*** **********************************************************************

1. You leave in the init.ora the DB_CREATE_FILE_DEST parameter.
   You create a new tablespace with datafiles located in another directory than the OMF directory:

   SQL> create tablespace omf_domi datafile '/ora/ora9i/admin/<SID>/bdump/omf_domi01.dbf' size 100k;

   Tablespace created.

2. Drop the tablespace and check that the datafile is still remaining on the operating system.

   SQL> drop tablespace omf_domi;

   Tablespace dropped.

   $ ls /ora/ora9i/admin/<SID>/bdump
   omf_domi01.dbf

*** *************************************************************
*** Heterogeneous test: tablespace with OMF and non-OMF datafiles
*** *************************************************************

   SQL> create tablespace omf_domi datafile size 100k;

   Tablespace created.

   SQL> alter tablespace omf_domi add datafile '/ora/ora9i/admin/<SID>/bdump/omf_domi02.dbf' size 100k;

   Tablespace altered.

   SQL> drop tablespace omf_domi;
   Tablespace dropped.

   $ ls /oradata/<SID>
   => no more files

   $ ls /ora/ora9i/admin/<SID>/bdump/omf*
   /ora/ora9i/admin/<SID>/bdump/omf_domi02.dbf

   It is preferrable in this case to use the following command that drops the tablespace and related datafiles (OMF and non-OMF):

   SQL> drop tablespace omf_domi including contents and datafiles;

   Tablespace dropped.

   In the alert.log you will see:

   drop tablespace omf_domi including contents and datafiles
   Thu Mar 29 17:09:22 2001
   Deleted Oracle managed file /ora/ora9i/admin/<SID>/bdump/omf_domi01.dbf
   Deleted file /ora/ora9i/admin/<SID>/bdump/omf_domi02.dbf
   Completed: drop tablespace omf_domi including contents and datafiles

Explanation:
============

In RDBMS version 9.0.1, we identify OMF files when they are located in the DB_CREATE_FILE_DEST directory and if the name respects the 'ora_' prefix and the '.dbf' extension.

Only datafiles created in the DB_CREATE_FILE_DEST directory are automatically removed from the operating system when dropping a tablespace without the INCLUDING CONTENTS AND DATAFILES clause.
Even if the value of DB_CREATE_FILE_DEST has changed, the different locations of OMF files are taken into account.

NOTE: Be cautious: it is strongly recommended not to explicitly name the OMF files. It will probably not work successfully in the future.

IMPORTANT NOTE: