备份与恢复（contorlfile恢复篇）

最新推荐文章于 2024-02-07 22:09:49 发布
最新推荐文章于 2024-02-07 22:09:49 发布
阅读量497 收藏
点赞数
分类专栏： 备份与恢复 文章标签： database archive filenames character sql file
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dbadoudou/article/details/7775742
版权

备份与恢复（之contorlfile篇）

环境：oracle 10.2.0.1

系统：contos 5.6

 

备份：

 

         备份控制文件只能在open或mount状态下经行备份，备份有4种方式：

 

SQL> select status from v$instance;

 

STATUS

------------

MOUNTED

 

1、备份控制文件（一）：trace文件

SQL> alter database backup controlfile to trace;

 

Database altered.

 

2、备份控制文件（二）：二进制文件

SQL> alter database backup controlfile to '/home/ora/controlfile.sql';

 

Database altered.

 

3、备份控制文件（三）：文本文件

SQL> alter database backup controlfile to trace as '/home/ora/controlfile.bak';

 

Database altered.

 

4、备份控制文件（四）：snapshot controlfile

RMAN> show all;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/10.2.0/db_1/dbs/snapcf_research.f'; # default

 

产生控制文件快照条件：

1）  backup database

2）  backup  current       controlfile

控制文件快照恢复控制文件，如下链接

http://space.itpub.net/?uid-26442936-action-viewspace-itemid-736402

 

 

恢复：

1、  trace文件恢复：

附表中有如何找到dump文件，及如何查看dump文件

 

1-1）开启数据库以及报错

SQL> startup

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              96470416 bytes

Database Buffers          213909504 bytes

Redo Buffers                2973696 bytes

ORA-00205: error in identifying control file, check alert log for more info

 

1-2）尝试在nomount下，备份控制文件这样是不可行的。

SQL> alter database backup controlfile to trace;

alter database backup controlfile to trace

*

ERROR at line 1:

ORA-01507: database not mounted

 

1-3）根据dump文件内容创建控制文件，创建好，数据库变成mount状态。

SQL> CREATE CONTROLFILE REUSE DATABASE "RESEARCH" RESETLOGS  NOARCHIVELOG

  2      MAXLOGFILES 10

  3      MAXLOGMEMBERS 5

  4      MAXDATAFILES 200

  5      MAXINSTANCES 1

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 '/opt/oracle/oradata/research/redo1.dbf'  SIZE 10M,

  9    GROUP 2 '/opt/oracle/oradata/research/redo2.dbf'  SIZE 10M,

 10    GROUP 3 '/opt/oracle/oradata/research/redo3.dbf'  SIZE 10M

 11  -- STANDBY LOGFILE

 12  DATAFILE

 13    '/opt/oracle/oradata/research/system01.dbf',

 14    '/opt/oracle/oradata/research/undo01.dbf',

 15    '/opt/oracle/oradata/research/sysaux01.dbf',

 16    '/opt/oracle/oradata/research/users01.dbf'

 17  CHARACTER SET ZHS16GBK

 18  ;

 

Control file created.

 

SQL> select status from v$instance;

 

STATUS

------------

MOUNTED

 

[ora@dg-pp research]$ ls

control01.ctl  control02.sql  redo1.dbf  sysaux01.dbf  undo01.dbf

control01.sql  control03.ctl  redo2.dbf  system01.dbf  users01.dbf

control02.ctl  control03.sql  redo3.dbf  temp01.dbf

 

创建成功控制文件。物理的控制文件也被创建了。

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

SQL> alter database open resetlogs;

 

Database altered.

 

OK 恢复成功！

 

 

 

2、  二进制文件恢复

2-1）直接把二进制文件copy到指定位置，修改控制文件名。就OK了。

[ora@dg-pp ~]$ cp controlfile.sql /opt/oracle/oradata/research/control01.ctl

[ora@dg-pp ~]$ cd /opt/oracle/oradata/research

[ora@dg-pp research]$ ls

control01.ctl  redo2.dbf  sysaux01.dbf  temp01.dbf  users01.dbf

redo1.dbf      redo3.dbf  system01.dbf  undo01.dbf

[ora@dg-pp research]$ cp control01.ctl control02.ctl

[ora@dg-pp research]$ cp control01.ctl control03.ctl

 

         2-2）修改完控制文件，还不让我直接open ,必须先mount

SQL> alter database open ;  

alter database open

*

ERROR at line 1:

ORA-01507: database not mounted

 

 

SQL> alter database mount;

 

Database altered.

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

 

SQL> alter database open resetlogs;

 

Database altered.

 

OK 恢复成功！

 

3、  文本文件恢复：

这里不展示了，跟DUMP文件恢复一样，就是重建建立控制文件，然后resetlogs开启数据库。

 

总结：备份控制文件必须在open或mount状态下，备份为二进制文件可以直接使用。其余备份需在SQL执行创建。

 

 

 

附表：

 

1）查看DUMP文件

SQL> SELECT SPID FROM V$PROCESS

  2  WHERE ADDR =

  3  (

  4  SELECT PADDR FROM V$SESSION

  5  WHERE SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1)

  6  )

  7  ;

 

SPID

------------

19277

2）查看DUMP文件内容

[ora@dg-pp udump]$ cat research_ora_19277.trc

Dump file /opt/oracle/admin/research/udump/research_ora_19277.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

ORACLE_HOME = /opt/oracle/product/10.2.0/db_1

System name:    Linux

Node name:      dg-pp

Release:        2.6.18-238.el5

Version:        #1 SMP Thu Jan 13 16:24:47 EST 2011

Machine:        i686

Instance name: research

Redo thread mounted by this instance: 0 <none>

Oracle process number: 13

Unix process pid: 19277, image: oracle@dg-pp (TNS V1-V3)

 

*** 2012-04-02 18:45:05.219

*** SERVICE NAME:() 2012-04-02 18:45:05.219

*** SESSION ID:(49.1) 2012-04-02 18:45:05.219

kccsga_update_ckpt: num_1 = 1, num_2 = 0, num_3 = 0, lbn_2 = 0, lbn_3 = 0

*** 2012-04-02 18:45:15.691

-- The following are current System-scope REDO Log Archival related

-- parameters and can be included in the database initialization file.

--

-- LOG_ARCHIVE_DEST=''

-- LOG_ARCHIVE_DUPLEX_DEST=''

--

-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

--

-- DB_UNIQUE_NAME="research"

--

-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'

-- LOG_ARCHIVE_MAX_PROCESSES=2

-- STANDBY_FILE_MANAGEMENT=MANUAL

-- STANDBY_ARCHIVE_DEST=?/dbs/arch

-- FAL_CLIENT=''

-- FAL_SERVER=''

--

-- LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/product/10.2.0/db_1/dbs/arch'

-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--

-- Below are two sets of SQL statements, each of which creates a new

-- control file and uses it to open the database. The first set opens

-- the database with the NORESETLOGS option and should be used only if

-- the current versions of all online logs are available. The second

-- set opens the database with the RESETLOGS option and should be used

-- if online logs are unavailable.

-- The appropriate set of statements can be copied from the trace into

-- a script file, edited as necessary, and executed when there is a

-- need to re-create the control file.

--

--     Set #1. NORESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline

-- Use this only if the current versions of all online logs are

-- available.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "RESEARCH" NORESETLOGS  NOARCHIVELOG

    MAXLOGFILES 10

    MAXLOGMEMBERS 5

    MAXDATAFILES 200

    MAXINSTANCES 1

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/opt/oracle/oradata/research/redo1.dbf'  SIZE 10M,

  GROUP 2 '/opt/oracle/oradata/research/redo2.dbf'  SIZE 10M,

  GROUP 3 '/opt/oracle/oradata/research/redo3.dbf'  SIZE 10M

-- STANDBY LOGFILE

DATAFILE

  '/opt/oracle/oradata/research/system01.dbf',

  '/opt/oracle/oradata/research/undo01.dbf',

  '/opt/oracle/oradata/research/sysaux01.dbf',

  '/opt/oracle/oradata/research/users01.dbf'

CHARACTER SET ZHS16GBK

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/product/10.2.0/db_1/dbs/arch1_1_779555912.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/product/10.2.0/db_1/dbs/arch1_1_779562711.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/product/10.2.0/db_1/dbs/arch1_1_779564338.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/product/10.2.0/db_1/dbs/arch1_1_779564616.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/product/10.2.0/db_1/dbs/arch1_1_779565733.dbf';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/research/temp01.dbf' REUSE;

-- End of tempfile additions.

--

--     Set #2. RESETLOGS case

--

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "RESEARCH" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 10

    MAXLOGMEMBERS 5

    MAXDATAFILES 200

    MAXINSTANCES 1

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/opt/oracle/oradata/research/redo1.dbf'  SIZE 10M,

  GROUP 2 '/opt/oracle/oradata/research/redo2.dbf'  SIZE 10M,

  GROUP 3 '/opt/oracle/oradata/research/redo3.dbf'  SIZE 10M

-- STANDBY LOGFILE

DATAFILE

  '/opt/oracle/oradata/research/system01.dbf',

  '/opt/oracle/oradata/research/undo01.dbf',

  '/opt/oracle/oradata/research/sysaux01.dbf',

  '/opt/oracle/oradata/research/users01.dbf'

CHARACTER SET ZHS16GBK

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/product/10.2.0/db_1/dbs/arch1_1_779555912.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/product/10.2.0/db_1/dbs/arch1_1_779562711.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/product/10.2.0/db_1/dbs/arch1_1_779564338.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/product/10.2.0/db_1/dbs/arch1_1_779564616.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/product/10.2.0/db_1/dbs/arch1_1_779565733.dbf';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/research/temp01.dbf' REUSE;

-- End of tempfile additions.

--

 

lovehewenyu
关注
专栏目录
Oracle备份standby,Controlfile备份恢复关于Standby的理解
weixin_29603829的博客
04-12 1701
standby在standby状态下的备份方式如下,生成一个控制文件备份control_1，一个数据文件备份full_dataRMAN> backup database format '/iso/backup/XX_%U';primary的备份方式如下，并拷贝到standby的对应目录RMAN> backup current controlfile format '/iso/backu...
备份与恢复系列 十一 控制文件的备份与恢复
cuibaodun6837的博客
10-06 173
看看当前数据库的控制文件位置。一个在/u01/oradata/practice/下，另一个在快速恢复区，彼此互为镜像。 SYS@practice >show parameter control_files; N...
【RMAN】Oracle中如何备份控制文件？备份控制文件的方式有哪几种？
小麦苗DBA宝典
03-22 2380
【RMAN】Oracle中如何备份控制文件？备份控制文件的方式有哪几种？ 真题1、如何备份控制文件？备份控制文件的方式有哪几种？ 答案：备份控制文...
3、控制文件的备份与恢复
Multis
03-12 774
使用rman对控制文件进行备份与恢复
oracle dg备库恢复
袁子建
06-22 3120
standby在standby状态下的备份方式如下,生成一个控制文件备份control_1，一个数据文件备份full_data RMAN> backup database format '/iso/backup/XX_%U'; primary的备份方式如下，并拷贝到standby的对应目录 RMAN> backup current controlfile format '/iso/backup/control_2'; RMAN> backup current controlfile .
实验4 数据库备份与恢复实验 (2).docx
11-19
实验4 数据库备份与恢复实验 数据库备份与恢复是数据库管理系统中非常重要的一部分。实验4 数据库备份与恢复实验旨在让学生掌握数据库备份与恢复的操作方法。 知识点1: 数据库备份 数据库备份是指将数据库中的...
顶级DBA漫谈Oracle Rman备份与恢复
09-01
Oracle RMAN 备份与恢复概述 Oracle RMAN（Recovery Manager）是一种强大的备份和恢复工具，旨在帮助DBA管理员更好地管理和保护Oracle数据库。以下是Oracle RMAN备份与恢复的相关知识点： 备份恢复概述 备份恢复...
论灾难备份与恢复策略 论灾难备份与恢复策略
01-10
论灾难备份与恢复策略
《Oracle Database 11g RMAN备份与恢复》PDF版本下载.txt
07-17
《Oracle Database 11g RMAN备份与恢复》PDF版本下载
数据库的备份与恢复方法.pdf
07-13
本篇文章将深入探讨数据库备份的三种主要方法：完全备份、差异备份和增量备份，以及如何进行数据库的增量备份与恢复。 1. **数据库备份分类** - **物理备份**：物理备份直接复制数据库的物理文件，包括数据文件和...
Controlfile备份恢复关于Standby的理解
袁子建
07-01 2306
standby在standby状态下的备份方式如下,生成一个控制文件备份control_1，一个数据文件备份full_data RMAN> backup database format '/iso/backup/XX_%U'; primary的备份方式如下，并拷贝到standby的对应目录 RMAN> backup current controlfile format '/iso/backup/control_2'; RMAN> backup current controlfile .
controlfile文件的备份与恢复
cqcq2013的博客
01-23 372
Oracle的控制文件controlfile有以下4种备份恢复方法。注意：本文模拟controlfile故障均是直接删除控制文件。1. 使用RMAN工具备份恢复controlfileRMAN参数选项”CONFIGURE C...
linux-bash 归档数据文件
cyx441984694的博客
07-17 303
Source: > 如何使用shell备份linux系统上的数据 归档数据文件 1. 你需要归档的文件或目录 Files_To_Backup /home/winnie/Downloads /home/winnie/Documents /home/winnie/script /home/winnie/Does_not_exist 2.创建按日归档的脚本 Dai
oracle的rac控制文件备份,RAC控制文件恢复（三种不同情况）
weixin_42143806的博客
04-12 744
RAC控制文件恢复(三种不同情况)测试环境：系统：LINUX-64数据库：10.2.0.1二节点的RAC(RACDB1，RACDB2)，存储用的ASM有备份情况下，丢失控制文件，我们改如何恢复？一：使用备份的控制文件恢复，最后需要使用resetlogs打开。(1)备份控制文件RMAN> backup current controlfile;(2)进行日志切换和数据修改RACDB1>...
Oracle 基于备份控制文件的恢复(unsing backup controlfile)
热门推荐
乐沙弥的世界
09-10 1万+
通常在当前控制文件丢失，或者当前的控制文件与需要恢复的控制文件不一致的情况下，我们需要重新创建一个控制文件或者使用 unsingbackup controlfile方式来恢复控制文件。说简单点，只要是备份的控制文件与当前的控制文件不一致进行恢复数据库，就需要使用到 unsingbackup controlfile方式，而一旦使用了该方式，则需使用resetlgos选项来打开数据库。      有关
using backup controlfile 和 until cancel区别
Data & Analysis
11-18 355
  1. recover database using backup controlfile 2. recover database until cancel 3. recover database using backup controlfile until cancel; 4. recover database until cancel using backup controlfile...
RMAN备份和恢复命令总结
一片叶子飞舞
12-20 6250
RMAN备份和恢复命令总结： 备份： Backup 有增量,copy 没有增量。 0，所有的使用的数据块，是基石 1—4 增量级别,备份 1c---4c 累积增量,备份 通过增量的级别来指定完备的备份策略 一、report:列出需要备份的所有物理文件--应该做什么 report schema;--数据库的结构是什么 report need backup days 3;
Shell 学习（七）：简单实用脚本
漂亮姐姐的博客
06-21 230
归档 ```bash #!/bin/bash # Daily_Archive - Archive designated files & directories ###################################################### # Gather Current Date DATE=$(date +%y%m%d) # Set Archive File Name FILE=archive$DATE.tar.gz # Set Configuration and D
常用的文件系统、存储类型小整理
最新发布
Hehuyi_In的博客
02-07 3462
最近接触到了五花八门的文件系统、存储类型，名词听得头大，趁假期整理学习一番~
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值