20160331数据文件offline与open resetlogs

最新推荐文章于 2021-04-08 16:11:50 发布
最新推荐文章于 2021-04-08 16:11:50 发布
阅读量102 收藏
点赞数

[20160331]数据文件offline与open resetlogs.txt

--昨天不小心导致一个数据文件offline，而归档日志已经删除，无法在通过recover恢复到online，通过手工修改online数据文件。
--链接http://blog.itpub.net/267265/viewspace-2071731/

--这让我想起来节前跟别人聊天提到一个恢复操作，数据库无法open，安全起见，通过dg来恢复。设置只读模式，停止日志应用，拷贝文
--件到新机器，建立新的控制文件，打开使用open resetlogs，悲剧发生，有一个数据文件在做恢复时，已经处在offline状态。结果
--resetlog时，没有改变数据文件的RESETLOGS_CHANGE#.提示：

ORA-01190: control file or data file XX is from before the last RESETLOGS

--我当时问出现这种情况如何恢复，我记得对方讲通过提升scn来恢复，我当时觉得很奇怪，提到scn能解决这个问题吗？数据文件里面记
--录RESETLOGS_CHANGE#就能改变吗？当时由于时间关系，这个测试我一直做，今天比较有空做一些测试：

1.环境：

SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                             TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ----------------
    1        13227498083 2016-03-31 08:34:53                7       13227286650 ONLINE              1000 YES /mnt/ramdisk/book/system01.dbf   SYSTEM
    2        13227498083 2016-03-31 08:34:53             1834       13227286650 ONLINE               996 YES /mnt/ramdisk/book/sysaux01.dbf   SYSAUX
    3        13227498083 2016-03-31 08:34:53           923328       13227286650 ONLINE               916 YES /mnt/ramdisk/book/undotbs01.dbf  UNDOTBS1
    4        13227498083 2016-03-31 08:34:53            16143       13227286650 ONLINE              1000 YES /mnt/ramdisk/book/users01.dbf    USERS
    5        13227498083 2016-03-31 08:34:53           952916       13227286650 ONLINE               913 YES /mnt/ramdisk/book/example01.dbf  EXAMPLE
    6        13227498083 2016-03-31 08:34:53          1314508       13227286650 ONLINE               929 YES /mnt/ramdisk/book/sugar01.dbf    SUGAR
    7        13227498083 2016-03-31 08:34:53      13227207527       13227286650 ONLINE                22 YES /mnt/ramdisk/book/tea01.dbf      TEA
7 rows selected.

--每一次open resetlog都会改变RESETLOGS_CHANGE#，我这里当前是13227286650。也可以通过这个视图查询v$database_incarnation ：
SYS@book> select * from v$database_incarnation ;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIM STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------------------- ----------------------- ------------------- ------- ------------ ------------------ --------------------------
           1                 1 2013-08-24 11:37:30                       0                     PARENT     824297850                  0 NO
           2            925702 2015-11-24 09:11:12                       1 2013-08-24 11:37:30 PARENT     896605872                  1 NO
           3       13227285139 2016-03-25 16:45:42                  925702 2015-11-24 09:11:12 PARENT     907433142                  2 NO
           4       13227286650 2016-03-25 17:06:01             13227285139 2016-03-25 16:45:42 CURRENT    907434361                  3 NO

--我的测试库经历了过几次1->925702->13227285139->13227286650.感觉这个就是当时切换的scn号。注意后面的RESETLOGS_ID跟
--log_archive_format参数的%r有关.
--还可以通过rman的list incarnation;查看

RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BOOK     1337401710       PARENT  1          2013-08-24 11:37:30
2       2       BOOK     1337401710       PARENT  925702     2015-11-24 09:11:12
3       3       BOOK     1337401710       PARENT  13227285139 2016-03-25 16:45:42
4       4       BOOK     1337401710       CURRENT 13227286650 2016-03-25 17:06:01

--另外查看参数log_archive_format，里面的%r表示的就是v$database_incarnation的RESETLOGS_ID,这个参数必须包括%r参数。如果你
--open resetlogs的日志的seq又从1开始。
SCOTT@book> show parameter log_archive_format
NAME                TYPE    VALUE
------------------- ------- ---------------
log_archive_format  string  %t_%s_%r.dbf

$ ll -l /u01/app/oracle/archivelog/book/
total 19952
-rw-r----- 1 oracle oinstall 20403712 2016-03-31 08:43:00 1_18_907434361.dbf

--这些信息应该保存在控制文件中，如果新建控制文件list incarnation;就看不到了。
--从这里看出如果open resetlogs 前，最好查询视图v$datafile_header确定是否存在表空间或者数据文件offline.

2.测试：
CREATE TABLESPACE TEA DATAFILE
  '/mnt/ramdisk/book/tea01.dbf' SIZE 1536K AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SCOTT@book> create table t tablespace tea as select rownum id ,lpad('text',4) name from dual connect by level<=4;
Table created.

SCOTT@book> select rowid,t.* from scott.t ;

ROWID                      ID NAME
------------------ ---------- --------------------
AAAWgeAAHAAAACLAAA          1 text
AAAWgeAAHAAAACLAAB          2 text
AAAWgeAAHAAAACLAAC          3 text
AAAWgeAAHAAAACLAAD          4 text

SCOTT@book> @ &r/rowid AAAWgeAAHAAAACLAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
     92190          7        139          0 7,139                alter system dump datafile 7 block 139 ;

--关机做一个冷备份,便于重复测试.
--session 1:
update t set name='aaaa' where id=1;
commit ;

--session 2:
update t set name='bbbb' where id=2;
--不提交。

--session 3:
SYS@book> alter database datafile 7 offline ;
Database altered.

--session 2:
commit ;

alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;

--切换出全部redo到归档。

SYS@book> shutdown abort ;
ORACLE instance shut down.

3.重新打开数据库看看：
SYS@book> startup mount
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.


SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                             TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ----------------
    1        13227501337 2016-03-31 09:59:13                7       13227286650 ONLINE              1006 YES /mnt/ramdisk/book/system01.dbf   SYSTEM
    2        13227501337 2016-03-31 09:59:13             1834       13227286650 ONLINE              1002 YES /mnt/ramdisk/book/sysaux01.dbf   SYSAUX
    3        13227501337 2016-03-31 09:59:13           923328       13227286650 ONLINE               922 YES /mnt/ramdisk/book/undotbs01.dbf  UNDOTBS1
    4        13227501337 2016-03-31 09:59:13            16143       13227286650 ONLINE              1006 YES /mnt/ramdisk/book/users01.dbf    USERS
    5        13227501337 2016-03-31 09:59:13           952916       13227286650 ONLINE               919 YES /mnt/ramdisk/book/example01.dbf  EXAMPLE
    6        13227501337 2016-03-31 09:59:13          1314508       13227286650 ONLINE               935 YES /mnt/ramdisk/book/sugar01.dbf    SUGAR
    7        13227500869 2016-03-31 09:53:40      13227207527       13227286650 OFFLINE               26 YES /mnt/ramdisk/book/tea01.dbf      TEA
7 rows selected.

--如果我使用 recover database using backup controlfile until cancel;恢复后，不能在使用open打开，必须加入open resetlogs参数。

SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227501224 generated at 03/31/2016 09:10:29 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf
ORA-00280: change 13227501224 for thread 1 is in sequence #24
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_24_907434361.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_24_907434361.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'

SYS@book> @ &r/logfile
GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           GROUP# STATUS     TYPE       MEMBER                                             IS_
------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ ------------------- ------ ---------- ---------- -------------------------------------------------- ---
     1       1        22    52428800       512       1 YES INACTIVE     13227501329 2016-03-31 09:59:09  13227501333 2016-03-31 09:59:12      1            ONLINE     /mnt/ramdisk/book/redo01.log                       NO
     2       1        23    52428800       512       1 YES INACTIVE     13227501333 2016-03-31 09:59:12  13227501337 2016-03-31 09:59:13      2            ONLINE     /mnt/ramdisk/book/redo02.log                       NO
     3       1        24    52428800       512       1 NO  CURRENT      13227501337 2016-03-31 09:59:13 2.814750E+14                          3            ONLINE     /mnt/ramdisk/book/redo03.log                       NO

--还需要 /mnt/ramdisk/book/redo03.log.

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS     CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- -------------------------------------------------- ------------------------------
    1        13227501337 2016-03-31 09:59:13                7       13227286650 ONLINE                 1006 YES /mnt/ramdisk/book/system01.dbf                     SYSTEM
    7        13227500869 2016-03-31 09:53:40      13227207527       13227286650 ONLINE                   26 YES /mnt/ramdisk/book/tea01.dbf                        TEA

--另外可以发现数据文件7在recover时已经设置为online.

SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227500869 generated at 03/31/2016 09:53:40 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_20_907434361.dbf
ORA-00280: change 13227500869 for thread 1 is in sequence #20

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 13227501325 generated at 03/31/2016 09:59:08 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_21_907434361.dbf
ORA-00280: change 13227501325 for thread 1 is in sequence #21
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_20_907434361.dbf' no longer needed for this recovery


ORA-00279: change 13227501329 generated at 03/31/2016 09:59:09 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_22_907434361.dbf
ORA-00280: change 13227501329 for thread 1 is in sequence #22
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_21_907434361.dbf' no longer needed for this recovery


ORA-00279: change 13227501333 generated at 03/31/2016 09:59:12 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_23_907434361.dbf
ORA-00280: change 13227501333 for thread 1 is in sequence #23
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_22_907434361.dbf' no longer needed for this recovery


ORA-00279: change 13227501337 generated at 03/31/2016 09:59:13 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf
ORA-00280: change 13227501337 for thread 1 is in sequence #24
ORA-00278: log file '/u01/app/oracle/archivelog/book/1_23_907434361.dbf' no longer needed for this recovery


ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_24_907434361.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'

--我觉得这个地方是执行恢复数据文件7.因为前面提示是差seq=24.继续恢复:

SYS@book> recover database using backup controlfile until cancel;
ORA-00279: change 13227501337 generated at 03/31/2016 09:59:13 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_24_907434361.dbf
ORA-00280: change 13227501337 for thread 1 is in sequence #24
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo03.log
Log applied.
Media recovery complete.

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS     CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- -------------------------------------------------- ------------------------------
    1        13227501343 2016-03-31 09:59:24                7       13227286650 ONLINE                 1007 NO  /mnt/ramdisk/book/system01.dbf                     SYSTEM
    7        13227501343 2016-03-31 09:59:24      13227207527       13227286650 ONLINE                   27 NO  /mnt/ramdisk/book/tea01.dbf                        TEA

SYS@book> alter database datafile 7 offline ;
Database altered.

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS     CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- -------------------------------------------------- ------------------------------
    1        13227501343 2016-03-31 09:59:24                7       13227286650 ONLINE                 1007 NO  /mnt/ramdisk/book/system01.dbf                     SYSTEM
    7        13227501343 2016-03-31 09:59:24      13227207527       13227286650 OFFLINE                  27 NO  /mnt/ramdisk/book/tea01.dbf                        TEA

-- 数据文件7已经offline.

SYS@book> alter database read ;
alter database read
                   *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

SYS@book> alter database open resetlogs;
Database altered.

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file# in (1,7);
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS     CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- -------------------------------------------------- ------------------------------
    1        13227501347 2016-03-31 10:08:00                7       13227501344 ONLINE                 1009 YES /mnt/ramdisk/book/system01.dbf                     SYSTEM
    7        13227501343 2016-03-31 09:59:24      13227207527       13227286650 OFFLINE                  27 NO  /mnt/ramdisk/book/tea01.dbf                        TEA

--数据文件7的RESETLOGS_CHANGE#与数据文件1的RESETLOGS_CHANGE#不一致.

SYS@book> alter database datafile 7 online ;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01190: control file or data file 7 is from before the last RESETLOGS
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

--不要被ORA-01190提示蒙骗,实际上10g以后可以跨resetlogs恢复,只要归档日志全部都在.

RMAN> list incarnation;
using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BOOK     1337401710       PARENT  1          2013-08-24 11:37:30
2       2       BOOK     1337401710       PARENT  925702     2015-11-24 09:11:12
3       3       BOOK     1337401710       PARENT  13227285139 2016-03-25 16:45:42
4       4       BOOK     1337401710       PARENT  13227286650 2016-03-25 17:06:01
5       5       BOOK     1337401710       CURRENT 13227501344 2016-03-31 10:07:59

SYS@book> recover datafile 7;
Media recovery complete.

SYS@book> alter database datafile 7 online ;
Database altered.

SYS@book> select rowid,t.* from scott.t ;
ROWID                        ID NAME
------------------ ------------ ---------
AAAWgeAAHAAAACLAAA            1 aaaa
AAAWgeAAHAAAACLAAB            2 bbbb
AAAWgeAAHAAAACLAAC            3 text
AAAWgeAAHAAAACLAAD            4 text

--没有任何丢失.

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS     CHECKPOINT_COUNT FUZ NAME                                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- -------------------------------------------------- ------------------------------
    1        13227501347 2016-03-31 10:08:00                7       13227501344 ONLINE                 1009 YES /mnt/ramdisk/book/system01.dbf                     SYSTEM
    2        13227501347 2016-03-31 10:08:00             1834       13227501344 ONLINE                 1005 YES /mnt/ramdisk/book/sysaux01.dbf                     SYSAUX
    3        13227501347 2016-03-31 10:08:00           923328       13227501344 ONLINE                  925 YES /mnt/ramdisk/book/undotbs01.dbf                    UNDOTBS1
    4        13227501347 2016-03-31 10:08:00            16143       13227501344 ONLINE                 1009 YES /mnt/ramdisk/book/users01.dbf                      USERS
    5        13227501347 2016-03-31 10:08:00           952916       13227501344 ONLINE                  922 YES /mnt/ramdisk/book/example01.dbf                    EXAMPLE
    6        13227501347 2016-03-31 10:08:00          1314508       13227501344 ONLINE                  938 YES /mnt/ramdisk/book/sugar01.dbf                      SUGAR
    7        13227501818 2016-03-31 10:12:53      13227207527       13227501344 ONLINE                   29 YES /mnt/ramdisk/book/tea01.dbf                        TEA
7 rows selected.

--注意一个细节,我的recover database using backup controlfile until cancel;数据文件7已经恢复到CHECKPOINT_CHANGE#=13227501343,而open resetlogs的scn=13227501344,仅仅相差1.
--如果存在一定差距,能恢复吗? 看一篇帖子.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2073141/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2073141/

cuixun7780
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle数据库学习之第三篇(权限及一些常用命令的演示)
天才王撕葱的技术博客
02-25 296
接上篇我们创建了新的数据库-test 由于中间出了很多的问题 所以这次的演示均在原来的数据库orcl上进行演示  连接好数据库后 select name from v$database; v$database视图是当前数据库实例的各种信息，其中包括Name --数据库名称created --创建日期RESETLOGS_CHANGE --重置日志的次数",RESETLOGS_TIME
RMAN备份FORMAT格式中%a的含义
java开发指南博客 【转载】
08-09 99
oracle文档对%a的描述是： %a Specifies the activation ID of the database. rman备份并保存，查询%a RMAN&gt; backup tablespace users format '/home/oracle/think_%a'; 由此，可知%a为：790774120 查询x$kccdi sys@ORCL&gt; S...
oracle v$database 视图
haiross的专栏
03-15 8272
oracle v$database        Posted on 2012-10-30 15:59 bingfeng 阅读(278) 评论(0)  编辑 收藏 Select db.dbid "数据库标识",--数据库的标识，当数据库在所有文件的头部创建和存储时计算出来的标记编号        db.Name "数据库名称",--数据库名称       
DG动态性能视图详解
weixin_34273046的博客
08-02 213
V$LOG 显示CONTROLFILE记录的LOG FILE信息。 列名描述 GROUP#日志组号 THREAD#日志线程号 SEQUENCE#日志序列号 BYTES日志文件大小(单位bytes) BLOCKSIZE 11.2新增字段,logfile的块大小 MEMBERS 日...
测试8——oracle数据恢复的研究
changyanmanman的专栏
10-27 1420
写
Oracle回滚表空间数据文件误删除处理
03-03
Oracle回滚表空间数据文件误删除处理是一个严重的问题，因为它涉及到数据库的核心组件——回滚段。回滚段在Oracle数据库中扮演着至关重要的角色，它们记录了事务的修改历史，确保了数据库的一致性，同时也为数据库...
Oracle7.X 回滚表空间数据文件误删除处理方法
09-11
svrmgrl > alter database open resetlogs; ``` 之后，需要删除旧的回滚段和回滚表空间，以确保数据库的干净状态： ```sql svrmgrl > alter rollback_segment r01 offline drop; svrmgrl > alter rollback_segment...
ORACLE之FAQ -- 备份与恢复
07-13
- 将丢失的数据文件设置为离线：`alter database datafile 'filename' offline;` - 在归档模式下，跳过此步骤；在非归档模式下，执行：`alter database datafile 'filename' offline drop;` - 恢复数据库：`alter ...
oracle恢复手册
11-04
- 然后，离线删除数据文件（`alter database datafile 3 offline drop`）。 - 从备份中恢复丢失的数据文件和相关归档日志。 - 通过`alter database rename file`命令重命名恢复的数据文件，然后将其在线（`alter ...
RMAN-20208: UNTIL CHANGE is before RESETLOGS change 问题小计
cqrw65623的博客
07-30 497
RMAN-20208: UNTIL CHANGE is before RESETLOGS change 问题小计 RMAN> restore database until scn 1045382; ...
Oracle 控制文件 说明
热门推荐
David Dai -- Focus on Oracle
12-09 2万+
一.  Oracle 控制文件内容 我们可以通过v$controlfile_record_section 视图查看控制文件里包含的内容。 SQL> select type from v$controlfile_record_section; TYPE--------------------------------------------------------DATABASECKPT PROGRE
oracle resetlogs 误删,Oracle数据库恢复教程之resetlogs操作
weixin_42151305的博客
04-07 392
实验环境：RHEL 5.4 + Oracle 11.2.0.3如果是一名合格的Oracle DBA，对resetlogs这种关键字都应该是极其敏感的，当确认需要这种操作时一定要三思而后行，如果自己不是特别确认，哪怕多花些时间申请去让高级DBA人员协助你一起确认，也不要擅自去尝试执行，避免误操作造成既定损失后追悔莫及。1.哪些场景可以resetlogs首先要明确resetlogs操作非常危险的，也只...
有关resetlogs和incarnation关系的理解
cqis22460的博客
08-05 208
一、实验环境： Sqldeveloper+linux in virtual machine； [oracle@lzc ~]$ lsb_release -a LSB Version: :core-3.1-ia32:...
oracle的resetlogs机制浅析
冰刀(skate)
09-07 1万+
oracle的resetlogs机制浅析
oracle备库 恢复主库,主库resetlogs对物理备库的影响 - Oracle数据库管理 - Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀...
weixin_42665725的博客
04-08 88
ODM DATA:12.5.1 Flashing Back a Physical Standby Database to a Specific Point-in-TimeThe following steps describe how to avoid re-creating a physical standby database after you issued the OPEN RESETLO...
ORA-01190: 控制文件或数据文件 6 来自最后一个 RESETLOGS 之前
u010882595的专栏
03-24 4187
SQL> select tablespace_name, file_name from dba_data_files; select tablespace_name, file_name from dba_data_files                                        * 第 1 行出现错误: ORA-01219: 数据库未打开: 仅允许在固定表/视图中
（伟大的实验 ）RESETLOGS后没有备份情况下的数据恢复
shenghuiping2001的专栏
01-08 850
一点说明： 媒体恢复分完全恢复和不完全恢复。不完全恢复可以恢复到指定的时刻或系统更改号，但不完全恢复之后剩余日志文件就不可用，必须重置日志序列号，用RESETLOGS选项打开数据库，此后数据库变成一个新形体，为了将来的恢复，必须重做一致备份。而且，RESETLOGS之前的备份已不可用。但是，很可能在RESETLOGS后没有做数据库的一致备份，而数据库又不认识RESETLOGS之前的备份，此时该如
oracle数据文件offline
最新发布
06-11
如果你想将Oracle数据库中的数据文件设置为“offline”状态，则可以使用以下步骤： 1. 连接到Oracle数据库并以管理员身份登录。 2. 执行以下命令将数据文件设置为“offline”状态： ALTER DATABASE DATAFILE '<datafile_path>' OFFLINE; 其中，<datafile_path> 是数据文件的路径。 3. 如果数据文件当前正在使用，则可能需要强制它们“offline”： ALTER DATABASE DATAFILE '<datafile_path>' OFFLINE FOR DROP; 这将强制关闭使用该数据文件的任何事务，并允许你删除该文件。 请注意，将数据文件设置为“offline”状态会阻止Oracle数据库使用该文件。如果你需要恢复该文件的使用，可以使用以下命令将其设置为“online”状态： ALTER DATABASE DATAFILE '<datafile_path>' ONLINE; 希望这可以帮助你！
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值