关于RMAN备份时,控制文件位置

最新推荐文章于 2022-12-19 16:36:36 发布

haohaodeshijie123

最新推荐文章于 2022-12-19 16:36:36 发布

阅读量1.3k

点赞数

分类专栏： oracle 11g 052 体系结构

本文链接：https://blog.csdn.net/u011209497/article/details/11010225

版权

oracle 11g 052 体系结构专栏收录该内容

16 篇文章 0 订阅

订阅专栏

转储测试语句：
alter session set events 'immediate trace name controlf level 8';
select value from v$diag_info where name ='Default Trace File';
select value from v$diag_info where name='Default Alert File';
-------控制文件所在位置
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/controlfile/current.260.820441479
+FRA/orcl/controlfile/current.256.820441489

--------备份片所在的位置
V$RMAN_STATUS: recid=3215756204, stamp=3215756200
Flags: <concurrent access>
Device: DISK
Handle: +FRA/orcl/backupset/2013_08_01/nnndf0_eygle070301_0.261.822333897
Media-Handle:
Comment:
Tag: EYGLE070301
Completion time 08/01/13 18:09:50
-------自动备份所在的位置，oracle自动备份的控制文件，发生灾难时，可以使用这个自动备份恢复spfile文件及控制文件
RECID #2 Recno 2 Record timestamp 08/01/13 18:07:54 piece #1 copy #1 pool 0
Backup set key: stamp=822333976, count=5
V$RMAN_STATUS: recid=3215756204, stamp=3215756200
Flags: <concurrent access>
Device: DISK
Handle: +FRA/orcl/autobackup/2013_08_01/s_822333975.262.822334075
Media-Handle:
Comment:
Tag: TAG20130801T180556
Completion time 08/01/13 18:08:01

-----恢复spfile文件
restore spfile to '/tmp/spfile.ora' from autobackup;
-----恢复控制文件
restore controlfile to '/tmp/control01.ctl' from autobackup;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/controlfile/current.260.820441479
+FRA/orcl/controlfile/current.256.820441489

-----恢复spfile文件
restore spfile to '/tmp/spfile.ora' from autobackup;
-----恢复控制文件
restore controlfile to '/tmp/control01.ctl' from autobackup;

------查看跟踪日志文件位置
SQL> show parameter background_dump;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/orc
                                                 l/orcl/trace

   ---静态参数文件pfile和动态参数文件spfile位置，其中pfile为文本文件，spfile为二进制文件，通过strings spfileorcl.ora来查看
   $cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs
-bash-4.1$ ls
hc_DBUA0.dat init.ora      lkORCL     peshm_DBUA0_0 snapcf_orcl.f
hc_orcl.dat   initorcl.ora orapworcl peshm_orcl_0   spfileorcl.ora
---参数文件pfile丢失后，无法startup mount
-bash-4.1$ mv initorcl.ora initorcl.ora.bak
SQL> startup mount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
---创建spfile，从pfile创建,默认创建在dbs目录下，后面加ORACLE_SID的名字,可以再数据库启动之前创建,也可以再启动之后创建

create spfile from pfile;
修改spfile内容

#修改

Alter system set 参数名=值 <comment='text'><deferred><scope=memory|spfile|both><sid='sid|*'>

#缺省

Alter system reset 参数名 sid='sid|*' scope=memory|spfile|both <comment='text'><deferred>

Oracle启动使用参数文件的顺序

①指定的PFILE文件

SQL>STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora

STARTUP 后面不可指定SPFILE，如果想指定SPFILE则可以将SPFILE配置在PFILE文件中

②spfileSID.ora

③默认spfile：spfile.ora

④initSID.ora

如果以上参数文件都不存在,则数据库就无法启动

数据库启动后，如果想知道启动数据库到底使用的是哪个参数文件，可以查看参数spfile的值

SQL>show parameter spfile

当使用pfile启动数据时，则show parameter spfile 的value值就为空
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfileorcl.ora
------一个正常运行的数据库，通常控制文件都存在多份镜像，这些镜像的内容是完全相同的，oracle默认
就创建多份控制文件。
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/controlfile/current.260.820441479
+FRA/orcl/controlfile/current.256.820441489
跟踪控制文件的创建
SQL> alter database backup controlfile to trace;

Database altered.

SQL> select value from v$diag_info where name ='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10500.trc
打开跟踪文件：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
    '+DATA/orcl/onlinelog/group_1.261.820441507',
    '+FRA/orcl/onlinelog/group_1.257.820441517'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
    '+DATA/orcl/onlinelog/group_2.262.820441519',
    '+FRA/orcl/onlinelog/group_2.258.820441525'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
    '+DATA/orcl/onlinelog/group_3.263.820441527',
    '+FRA/orcl/onlinelog/group_3.259.820441531'
) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA/orcl/datafile/system.256.820439857',
'+DATA/orcl/datafile/sysaux.257.820439909',
'+DATA/orcl/datafile/undotbs1.258.820439933',
'+DATA/orcl/datafile/users.259.820439937',
'+DATA/orcl/datafile/example.265.820441561',
'+DATA/orcl/datafile/eygle.267.822320029',
'+DATA/orcl/datafile/eygle1.268.822333495'
CHARACTER SET AL32UTF8
;
当在数据库nomount状态下，可以通过运行这段脚本创建控制文件，控制文件会自动创建到参数文件记录中
的控制文件的位置。其中的noresetlog参数用来重建控制文件
模拟断电
shutdown abort
startup mount
重建控制文件：
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
    '+DATA/orcl/onlinelog/group_1.261.820441507',
    '+FRA/orcl/onlinelog/group_1.257.820441517'
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
    '+DATA/orcl/onlinelog/group_2.262.820441519',
    '+FRA/orcl/onlinelog/group_2.258.820441525'
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
    '+DATA/orcl/onlinelog/group_3.263.820441527',
    '+FRA/orcl/onlinelog/group_3.259.820441531'
) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA/orcl/datafile/system.256.820439857',
'+DATA/orcl/datafile/sysaux.257.820439909',
'+DATA/orcl/datafile/undotbs1.258.820439933',
'+DATA/orcl/datafile/users.259.820439937',
'+DATA/orcl/datafile/example.265.820441561',
'+DATA/orcl/datafile/eygle.267.822320029',
'+DATA/orcl/datafile/eygle1.268.822333495'
CHARACTER SET AL32UTF8
;
重建的控制文件可以从日志文件中获取正确的scn，及时间点等信息，查看一下控制文件中的记录信息：
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 9, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
name #5: +DATA/orcl/onlinelog/group_1.261.820441507
name #6: +FRA/orcl/onlinelog/group_1.257.820441517
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000016 hws: 0xe bsz: 512 nab: 0x12e59 flg: 0x8 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.0011b04a
Low scn: 0x0000.001208fe 08/01/2013 17:20:41
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #2:
name #3: +DATA/orcl/onlinelog/group_2.262.820441519
name #4: +FRA/orcl/onlinelog/group_2.258.820441525
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000014 hws: 0x6 bsz: 512 nab: 0x378c flg: 0x1 dup: 2
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000016 hws: 0xe bsz: 512 nab: 0x12e59 flg: 0x8 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.0011b04a
Low scn: 0x0000.001208fe 08/01/2013 17:20:41
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #2:
name #3: +DATA/orcl/onlinelog/group_2.262.820441519
name #4: +FRA/orcl/onlinelog/group_2.258.820441525
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000014 hws: 0x6 bsz: 512 nab: 0x378c flg: 0x1 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.001138b7
Low scn: 0x0000.00119cf0 08/01/2013 10:02:10
Next scn: 0x0000.0011b04a 08/01/2013 11:14:02
LOG FILE #3:
name #1: +DATA/orcl/onlinelog/group_3.263.820441527
name #2: +FRA/orcl/onlinelog/group_3.259.820441531
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x00000015 hws: 0x6 bsz: 512 nab: 0x13910 flg: 0x1 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00119cf0
Low scn: 0x0000.0011b04a 08/01/2013 11:14:02
Next scn: 0x0000.001208fe 08/01/2013 17:20:41
其中next scn 0ffffff表示正在写的日志组。
由于数据库是异常关闭，所以stop scn为无穷大。接下来就是执行数据库恢复，先查看下数据文件的记录
DATA FILE #7:
name #14: +DATA/orcl/datafile/eygle1.268.822333495
creation size=12800 block size=8192 status=0xe head=14 tail=14 dup=1
tablespace 8, index=8 krfil=7 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:10 scn: 0x0000.00126120 08/01/2013 22:16:38
Stop scn:offffff
如果执行的控制文件时resetlogs,则此时控制文件中的日志信息都是空的，oracle认为resetlogs方式下，当前的日志已经丢失。把账本都撕掉，恢复将是不完全的介质恢复。使用备份的控制文件进行恢复
此时执行恢复必须使用backup controlfile选项，否则报错
recover database using backup controlfile until cancel;

haohaodeshijie123

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关于RMAN备份时,控制文件位置

转储测试语句：alter session set events 'immediate trace name controlf level 8';select value from v$diag_info where name ='Default Trace File';select value from v$diag_info where name='Default Alert Fil
复制链接

扫一扫

专栏目录