rman备库恢复主数据库到某一时间点案例(改进版)

最新推荐文章于 2023-03-02 17:15:26 发布

congkuang7197

最新推荐文章于 2023-03-02 17:15:26 发布

阅读量191

点赞数

文章标签：数据库操作系统

本文详细阐述了在数据库管理中遇到的问题与解决方案，包括如何利用RMAN进行定期备份、处理报表缺失数据、恢复数据库到特定时间点以及解决备库控制文件相关问题。通过实例分析，介绍了从主库备份到备库、恢复数据库、重建控制文件等关键步骤，旨在帮助数据库管理员高效解决问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

由于主库空间有限,standby 空间较大，备库与主库之间是实时同步，所以每周在备库上做
两次rman备份
30 5 * * 0,3 sh /data/run/rmanfull.sh 1>>/data/run/log/rmanfull.log 2>>/data/run/log/rmanfull.bad
30 6 * * 1,4 sh /data/run/auto_bak.sh 1>>/data/run/log/auto_bak.log 2>>/data/run/log/auto_bak.bad

由于报表17号有个别代理商没有对应数据生成，每天的报表数据由sched.job在第二天凌晨3:30运行生成，
当发现问题的时候已经是18号10点左右，有对报表及数据表作闪回查询及日志挖掘查询，均未发现有不满足
条件及生成数据后有删除现象，由此需要将数据库恢复到18号凌晨3:30状态查询相关表数据及重新运行job
以诊断具体原因

注：恢复后验证报表存储过程，发现各代理商均满足统计条件，只是由于统计当天有个代理商增加后没有交易数据
在实时余额表中没有记录，导致存储过程bug，运行时退出，后续代理商没有统计，具体原因已查出，相关bug修复
如下：
            SELECT   COUNT (GROUP_ID)
              INTO   V_EXIST
              FROM   TAB_BASE_MONEYLIST_ACCOUNT
             WHERE   GROUP_ID = v_group_id;

            IF V_EXIST > 0
            THEN
               --代理商当前余额
               SELECT   money_account * 100
                 INTO   v_balance_now
                 FROM   tab_base_moneylist_account a
                WHERE   a.GROUP_ID = v_group_id;

               IF v_balance_now IS NULL
               THEN
                  v_balance_now := 0;
               END IF;
            ELSE
               v_balance_now := 0;
            END IF;

新找一台服务器参照主库相关目录结构安装操作系统并安装oracle,从备库拷贝rman 备份到新机器恢复
(拷贝密码文件/tnsnames.ora/listener.ora/spfile)，由于两台机器内存不一样，所以需要从spfile
生成pfile,并修改*.memory_target=2684354560这个参数为对应的内存分配即可，其他内存参数不需要
修改，用新pfile启动后重新生成spfile，则其他内存参数会自动调整
sqlplus / as sysdba
SQL> create pfile from spfile;
编辑pfile并重新生成spfile
SQL> create spfile from pfile='/data/backup/pfile.ora';
SQL> startup nomount;
SQL> alter database mount standy database;

通过以下命令转储数据文件，由于数据库需要恢复到某个指定的时间点，可以转储归档日志，也可以不
转储归档日志，需要的时候再上拷贝
$rman target/
Rman>restore database;

当时创建stanby 的时候有创建standby logfile,这时候需要注意做以下动作(待验证)，由于我在此没有作
以下操作，恢复后激活时报其中的ORA-00312/ORA-00338,其中有涉及某个standby logfile有问题,查询相关
文档后，觉得需要重新创建控制文件，后来重建控制文件后不用激活则直接切换到read write主库模式,这是
后话，这里继续以下操作

ALTER DATABASE DROP STANDBY LOGFILE group 11;

ALTER DATABASE DROP STANDBY LOGFILE group 12;
SQL> ALTER DATABASE DROP STANDBY LOGFILE group 12;

/*
ALTER DATABASE DROP STANDBY LOGFILE group 12
*
ERROR at line 1:
ORA-00338: log 12 of thread 1 is more recent than control file
ORA-00312: online log 12 thread 1: '/ctrl/szxonline/onlinelog/dgredo12b.log'
ORA-00338: log 12 of thread 1 is more recent than control file
ORA-00312: online log 12 thread 1: '/redo/szxonline/onlinelog/dgredo12a.log'

针对以上报错，处理时先运用一部分归档(是否可以先不运用，待验证)，再作如下处理
SQL> alter database clear logfile group 12;
Database altered.
SQL> alter database drop standby logfile group 12;
Database altered.

清除日志说明：
--非当前联机日志
alter database clear logfile group 12;
alter database clear logfile '/redo/szxonline/onlinelog/dgredo12a.log';
alter database clear logfile '/ctrl/szxonline/onlinelog/dgredo12b.log';

--当前联机日志
alter database clear unarchived logfile group 12;
alter database clear unarchived logfile '/redo/szxonline/onlinelog/dgredo12a.log';
alter database clear unarchived logfile '/ctrl/szxonline/onlinelog/dgredo12b.log';
*/

ALTER DATABASE DROP STANDBY LOGFILE group 13;

ALTER DATABASE DROP STANDBY LOGFILE group 14;

ALTER DATABASE DROP STANDBY LOGFILE group 15;

ALTER DATABASE DROP STANDBY LOGFILE group 16;

/* 如果此部分成功删除，则不用执行此部分

ALTER DATABASE ADD STANDBY LOGFILE group 11 ('/redo/szxonline/onlinelog/dgredo11a.log','/ctrl/szxonline/onlinelog/dgredo11b.log') SIZE 500M;

ALTER DATABASE ADD STANDBY LOGFILE group 12 ('/redo/szxonline/onlinelog/dgredo12a.log','/ctrl/szxonline/onlinelog/dgredo12b.log') SIZE 500M;

ALTER DATABASE ADD STANDBY LOGFILE group 13 ('/redo/szxonline/onlinelog/dgredo13a.log','/ctrl/szxonline/onlinelog/dgredo13b.log') SIZE 500M;

ALTER DATABASE ADD STANDBY LOGFILE group 14 ('/redo/szxonline/onlinelog/dgredo14a.log','/ctrl/szxonline/onlinelog/dgredo14b.log') SIZE 500M;

ALTER DATABASE ADD STANDBY LOGFILE group 15 ('/redo/szxonline/onlinelog/dgredo15a.log','/ctrl/szxonline/onlinelog/dgredo15b.log') SIZE 500M;

ALTER DATABASE ADD STANDBY LOGFILE group 16 ('/redo/szxonline/onlinelog/dgredo16a.log','/ctrl/szxonline/onlinelog/dgredo16b.log') SIZE 500M;

recover database using backup controlfile until cancel;

如果以上关于standby logfile 的报错能解决，则归档运用到指定时间点后直接激活standby
就可以，无需重新创建控制文件,如果激活有问题，也可以read only 的模式下导出数据，导入到别的库，此方式较耗时耗力
SQL> recover standby database until time '2012-10-18 03:30:00'
Log applied.
Media recovery complete.
SQL> alter database activate standby database;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

正常情况下,备库归档运用采取以下步骤
SQL> shut immediate;

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2672361472 bytes
Fixed Size                  2229480 bytes
Variable Size            1040190232 bytes
Database Buffers         1610612736 bytes
Redo Buffers               19329024 bytes
SQL> alter database mount standby database;
alter database mount standby database

SQL> recover standby database 或 recover automatic standby database
运用完成归档后激活
alter database activate standby database;
前面有提到需要恢复到指定的时间点，则不能用以上方式恢复，需要用以下方式
在有备份及归档的情况下，主库和备库都可以恢复到任何时间点
1. 主库
recover database until time '2012-10-18 02:30:00'
2. 备库
recover standby database until time '2012-10-18 03:30:00'

正常情况下，恢复到指定时间点后，只需要激活standby 的read only 模式为主库
的read write 模式，由于前面有提到激活报错，则采取以下重建控件文件的方式
激活

根据如下命令创建控制文件内容
SQL> alter database backup controlfile to trace;

根据trace文件生成创建控制文件脚本create_controlfile.sh,去掉其中关于standby
logfile 定义部分,如下：

CREATE CONTROLFILE REUSE DATABASE "SZXONLIN" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 40
    MAXLOGMEMBERS 3
    MAXDATAFILES 1000
    MAXINSTANCES 8
    MAXLOGHISTORY 584
LOGFILE
GROUP 1 (
    '/redo/szxonline/onlinelog/redo01a.log',
    '/ctrl/szxonline/onlinelog/redo01b.log'
) SIZE 500M BLOCKSIZE 512,
GROUP 2 (
    '/redo/szxonline/onlinelog/redo02a.log',
    '/ctrl/szxonline/onlinelog/redo02b.log'
) SIZE 500M BLOCKSIZE 512,
GROUP 3 (
    '/redo/szxonline/onlinelog/redo03a.log',
    '/ctrl/szxonline/onlinelog/redo03b.log'
) SIZE 500M BLOCKSIZE 512,
GROUP 4 (
    '/redo/szxonline/onlinelog/redo04a.log',
    '/ctrl/szxonline/onlinelog/redo04b.log'
) SIZE 500M BLOCKSIZE 512,
GROUP 5 (
    '/redo/szxonline/onlinelog/redo05a.log',
    '/ctrl/szxonline/onlinelog/redo05b.log'
) SIZE 500M BLOCKSIZE 512
DATAFILE
'/u01/product/oracle/oradata/szxonline/system01.dbf',
'/u01/product/oracle/oradata/szxonline/sysaux01.dbf',
'/u01/product/oracle/oradata/szxonline/undotbs01.dbf',
'/u01/product/oracle/oradata/szxonline/users01.dbf',
'/u01/product/oracle/oradata/szxonline/undotbs02.dbf',
'/data/szxonline/datafile/order_data01.dbf',
'/data/szxonline/datafile/order_data02.dbf',
'/data/szxonline/datafile/order_data03.dbf',
'/data/szxonline/datafile/order_data04.dbf',
'/data/szxonline/datafile/order_indx01.dbf',
'/data/szxonline/datafile/order_indx02.dbf',
'/data/szxonline/datafile/order_indx03.dbf',
'/data/szxonline/datafile/card_data01.dbf',
'/data/szxonline/datafile/card_data02.dbf',
'/data/szxonline/datafile/card_data03.dbf',
'/data/szxonline/datafile/card_indx01.dbf',
'/data/szxonline/datafile/card_indx02.dbf',
'/data/szxonline/datafile/base_data01.dbf',
'/data/szxonline/datafile/base_data02.dbf',
'/data/szxonline/datafile/base_indx01.dbf',
'/data/szxonline/datafile/base_indx02.dbf',
'/data/szxonline/datafile/base_data03.dbf',
'/data/szxonline/datafile/base_indx03.dbf',
'/data/szxonline/datafile/card_indx03.dbf',
'/data/szxonline/datafile/order_data05.dbf',
'/data/szxonline/datafile/base_data04.dbf',
'/data/szxonline/datafile/order_indx04.dbf',
'/data/szxonline/datafile/base_indx04.dbf',
'/data/szxonline/datafile/card_indx04.dbf',
'/data/szxonline/datafile/card_data04.dbf',
'/data/szxonline/datafile/order_indx05.dbf',
'/data/szxonline/datafile/part_card_data01.dbf',
'/data/szxonline/datafile/part_card_data02.dbf',
'/data/szxonline/datafile/part_card_indx01.dbf',
'/data/szxonline/datafile/part_card_indx02.dbf',
'/data/szxonline/datafile/part_base_data01.dbf',
'/data/szxonline/datafile/part_base_data02.dbf',
'/data/szxonline/datafile/part_base_indx01.dbf',
'/data/szxonline/datafile/part_base_indx02.dbf',
'/data/szxonline/datafile/part_base_indx03.dbf',
'/data/szxonline/datafile/part_card_indx03.dbf',
'/data/szxonline/datafile/part_card_indx04.dbf',
'/data/szxonline/datafile/part_card_data03.dbf',
'/data/szxonline/datafile/site_data01.dbf',
'/data/szxonline/datafile/site_indx01.dbf',
'/data/szxonline/datafile/site_indx02.dbf',
'/data/szxonline/datafile/part_card_indx05.dbf',
'/data/szxonline/datafile/part_card_indx06.dbf'
CHARACTER SET AL32UTF8
;

关闭备库并备份删除原控制文件
SQL> shut immediate;

webbase$ll
total 26147104
-rw-r----- 1 oracle oinstall 19185664 Oct 22 11:57 control01.ctl
-rw-r----- 1 oracle oinstall 6442459136 Oct 22 11:47 sysaux01.dbf
-rw-r----- 1 oracle oinstall 6442459136 Oct 22 11:47 system01.dbf
-rw-r----- 1 oracle oinstall 4294975488 Oct 20 16:02 temp01.dbf
-rw-r----- 1 oracle oinstall 4294975488 Oct 20 16:57 temp02.dbf
-rw-r----- 1 oracle oinstall 5368717312 Oct 22 11:47 undotbs01.dbf
-rw-r----- 1 oracle oinstall 7516200960 Oct 22 11:47 undotbs02.dbf
-rw-r----- 1 oracle oinstall 524296192 Oct 22 11:47 users01.dbf
webbase$mv control01.ctl control01.ctl.bak

启动备库到nomount状态并创建控件文件

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2672361472 bytes
Fixed Size                  2229480 bytes
Variable Size            1040190232 bytes
Database Buffers         1610612736 bytes
Redo Buffers               19329024 bytes
SQL> @/backup/create_controlfile.sh;

Control file created.

注意:控制文件重建后数据库状态由nomount 进入mount,standby 控制文件变为主库控制文件
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database mount standby database;
alter database mount standby database
*
ERROR at line 1:
ORA-01100: database already mounted

SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

控制文件创建完成后关闭数据库并重启数据库到open状态

SQL> shut immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 2672361472 bytes
Fixed Size                  2229480 bytes
Variable Size            1040190232 bytes
Database Buffers         1610612736 bytes
Redo Buffers               19329024 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open NORESETLOGS;
alter database open NORESETLOGS
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
webbase$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 22 13:00:15 2012

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

SQL> alter database open RESETLOGS;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

由于时间仓促,在用standby rman 备份恢复后没有创建temp 文件，需要重新创建,由于之前已经有创建过,
所以只能通过如下方式增加

alter tablespace temp add tempfile '/u01/product/oracle/oradata/szxonline/temp01.dbf' size 4G;

由于spfile有配置standby 的相关参数，数据库会不断报以下错误
Mon Oct 22 15:57:40 2012
PING[ARC1]: Heartbeat failed to connect to standby 'szxonline'. Error is 16009.

需要取消如下参数配置
SQL> alter system set log_archive_dest_2='';

System altered.

SQL>
SQL> alter system set log_archive_config='';

System altered.

SQL> alter system switch logfile;

System altered.
后台报警日志显示如下
Mon Oct 22 16:00:44 2012
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
Mon Oct 22 16:01:38 2012
ALTER SYSTEM SET log_archive_config='' SCOPE=BOTH;
Mon Oct 22 16:02:08 2012
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 3 seq# 3 mem# 0: /redo/szxonline/onlinelog/redo03a.log
Current log# 3 seq# 3 mem# 1: /ctrl/szxonline/onlinelog/redo03b.log
Mon Oct 22 16:02:12 2012
Archived Log entry 2 added for thread 1 sequence 2 ID 0x496172b8 dest 1:

至此主库恢复到某一时间点，并可以正式使用

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

转载于:http://blog.itpub.net/25198367/viewspace-747026/