【DB宝22】使用DG环境的物理备库进行备份还原的备份一致性问题

AiDBA宝典

发布于 2021-05-06 14:55:32

1.2K0

发布于 2021-05-06 14:55:32

在部署完ADG（Active Data Guard）后，不但可以将只读的查询交给备库执行，还可以把日常的数据库备份工作放在备库上执行，从而减轻主库的压力，充分的发挥服务器资源。本文演示的是利用Oracle DG环境的备库执行备份，然后异机还原恢复成一个新的主库的过程。

※使用物理备库备份数据库的一致性说明

之前发过一篇类似的文章，请参考：【DB宝15】生产环境中，如何利用DG的备库来异机还原一个新库？连接地址为： https://mp.weixin.qq.com/s/ptB9D3sDzwNyHyHujTwKbQ

这几天还原了一个将近2T的库，碰到了一个坑。情况是这样的，我在物理备库进行的备份，但是当我把备份拿到新主机进行还原后，死活就是不能正常启动数据库，报错“数据库不一致，需要相关的归档日志进行恢复”，即使加上隐含参数“_allow_resetlogs_corruption”强制启库也不行。无奈之下，找了找相关资料，发现在物理备库进行的备份其实是存在备份一致性的问题的。

请看下图，来自于Benefits and Usage of RMAN with Standby Databases (文档 ID 602299.1)：

在备库进行备份，主要关注备份的一致性问题，分为2种情况： 1、备库可以连接到主库： 方法a、对数据文件备份完成后，需要在主库切换归档后，等最新的归档传递到备库后，再在备库备份归档，最后备份控制文件。需要注意的是，如果是11.2.0.4以上，可以不用单独在主库执行切换归档操作，因为在备库执行备份的过程中，会自动去连接主库执行一个切换归档的操作，但是若主库本身拒绝备库的连接，会报RMAN警告（RMAN-06820: WARNING: failed to archive current log at primary database），那么依然需要手工在主库进行切换。 方法b、灾备端可以在mount状态或read only状态进行备份，但是不能启用实时应用功能（mrp进程）。因为，即使数据库处于read only或mount状态，mrp进程也会持续更新数据块。 2、备库不能连接到主库：灾备端可以在mount状态或read only状态进行备份，但是不能启用实时应用功能（mrp进程）。因为，即使数据库处于read only或mount状态，mrp进程也会持续更新数据块。

如果备库还原完成后，还需要归档才能打开数据库，那么我们也可以在原主库上拷贝相关的归档日志到我们的新主库进行恢复（recover），即可打开数据库，只是有的情况下，环境不允许我们连接主库，主库一般都是生产库。

其它更多内容请参考：http://blog.itpub.net/26736162/viewspace-2723840/

参考文档：

( Doc ID 1616074.1) RMAN-06820 ORA-17629 During Backup at Standby Site
How to take consistent backups at standby site (文档 ID 1292126.1)
( Doc ID 1419923.1) Howto make a consistent RMAN backup in an Standby database in Active DataGuard mode
Step by Step method to create Primary/Standby Database from Standby Backup (文档 ID 1604251.1)
How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (文档 ID 1354256.1)
Benefits and Usage of RMAN with Standby Databases (文档 ID 602299.1)

本环境中，我们选择第2种办法在灾备端进行备份数据库，并拿到新环境进行还原。

※DG环境介绍

项目	primary db	physical standby db	新库
数据库类型（rac或单实例）	单实例	单实例	单实例
数据库版本	11.2.0.3.0	11.2.0.3.0	11.2.0.3.0
platform_name	Linux x86 64-bit	Linux x86 64-bit	Linux x86 64-bit
ORACLE_SID	oradg11g	oradgphy	TEST
db_name/GLOBAL_DBNAME	oradg11g	oradg11g	oradg11g
db_unique_name	oradg11g	oradgphy	TEST
ORACLE_HOME	/u01/app/oracle/product/11.2.0/dbhome_1	/u01/app/oracle/product/11.2.0/dbhome_1	/u01/app/oracle/product/11.2.0/dbhome_1

一、备库执行备份

1.1、备库取消恢复

为了能在备库达到一致性备份，需要取消实时应用功能：

alter database recover managed standby database cancel;

检查是否处于一致性状态：

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set line 1000
set numwidth 16
col name format a55
select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ;

如果fuzzy为NO，且checkpoint_change#列的值都一致，那么就代表数据库处于一致性状态，可以进行rman备份。

SYS@oradgphy > alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SYS@oradgphy > set line 1000
SYS@oradgphy > set numwidth 16
SYS@oradgphy > col name format a55
SYS@oradgphy > select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ;

FUZ STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME                FILE# NAME
--- ------- ------------------ ------------------- ---------------- -------------------------------------------------------
NO  ONLINE             3500675 2020-09-25 14:44:17                1 /u01/app/oracle/oradata/oradgphy/system01.dbf
NO  ONLINE             3500675 2020-09-25 14:44:17                2 /u01/app/oracle/oradata/oradgphy/sysaux01.dbf
NO  ONLINE             3500675 2020-09-25 14:44:17                3 /u01/app/oracle/oradata/oradgphy/undotbs01.dbf
NO  ONLINE             3500675 2020-09-25 14:44:17                4 /u01/app/oracle/oradata/oradgphy/users01.dbf
NO  ONLINE             3500675 2020-09-25 14:44:17                5 /u01/app/oracle/oradata/oradgphy/example01.dbf
NO  ONLINE             3500675 2020-09-25 14:44:17                6 /u01/app/oracle/oradata/oradgphy/logmnrtbs1.dbf

SYS@oradgphy > select open_mode , database_role, flashback_on from v$database;

OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
-------------------- ---------------- ------------------
READ ONLY            PHYSICAL STANDBY NO

1.2、备库执行备份

export NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss'
ORACLE_SID=oradgphy
rman target /
run{
backup as compressed backupset  database format '/home/oracle/oracle_bk/FULL_%d_%U.full' section size 100G;
backup as compressed backupset archivelog from time 'sysdate-2/24' format '/home/oracle/oracle_bk/ARC_%d_%U.arc' section size 100G;
backup current controlfile  format '/home/oracle/oracle_bk/standby_1.ctl';
backup spfile format '/home/oracle/oracle_bk/spfile_%d_%U.ora';
}

执行过程：

[oracle@rhel6lhr oracle_bk]$ export NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss'
[oracle@rhel6lhr oracle_bk]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 14:48:52 2020

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

connected to target database: ORADG11G (DBID=1403587593)

RMAN> run{
2> backup as compressed backupset  database format '/home/oracle/oracle_bk/FULL_%d_%U.full' section size 100G;
3> backup as compressed backupset archivelog from time 'sysdate-2/24' format '/home/oracle/oracle_bk/ARC_%d_%U.arc' section size 100G;
4> backup current controlfile  format '/home/oracle/oracle_bk/standby_1.ctl';
5> backup spfile format '/home/oracle/oracle_bk/spfile_%d_%U.ora';
6> }

Starting backup at 2020-09-25 14:48:55
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/oradgphy/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/oradgphy/sysaux01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/oradgphy/logmnrtbs1.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/oradgphy/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/oradgphy/users01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/oradgphy/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 2020-09-25 14:48:55
channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:30
piece handle=/home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full tag=TAG20200925T144855 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2020-09-25 14:49:31
channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:32
piece handle=/home/oracle/oracle_bk/FULL_ORADG11G_37vbaa3q_1_1.full tag=TAG20200925T144855 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2020-09-25 14:49:32

Starting backup at 2020-09-25 14:49:32
using channel ORA_DISK_1
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 2020-09-25 14:49:32

Starting backup at 2020-09-25 14:49:32
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2020-09-25 14:49:33
channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:34
piece handle=/home/oracle/oracle_bk/standby_1.ctl tag=TAG20200925T144932 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2020-09-25 14:49:35

Starting backup at 2020-09-25 14:49:35
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2020-09-25 14:49:35
channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:36
piece handle=/home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora tag=TAG20200925T144935 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2020-09-25 14:49:36

1.3、备库启用日志应用进程

alter database recover managed standby database using current logfile disconnect from session;

------------物理dg日志应用情况(主备库都可以)
alter session set NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss';
COL NAME FOR A100
SET LINESIZE 9999  PAGESIZE 9999
SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME
  FROM V$ARCHIVED_LOG A,
       (SELECT NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE#
          FROM V$ARCHIVED_LOG NB
         WHERE NB.APPLIED = 'YES'
     and NB.DEST_ID in (SELECT NB.DEST_ID FROM V$ARCHIVE_DEST_STATUS NB where STATUS <>'INACTIVE')
      GROUP BY NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID) B
 WHERE B.THREAD# = A.THREAD#
   AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#
   AND A.DEST_ID=B.DEST_ID
   AND A.SEQUENCE# >= MAX_SEQUENCE#
   AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO'  ELSE 'YES'  END FROM V$DATABASE NB)
 ORDER BY A.THREAD#, A.SEQUENCE#;

执行过程：

SYS@oradgphy > alter session set NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss';

Session altered.

SYS@oradgphy > COL NAME FOR A100
SYS@oradgphy > SET LINESIZE 9999  PAGESIZE 9999
SYS@oradgphy > SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME
  2    FROM V$ARCHIVED_LOG A,
  3         (SELECT NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE#
  4            FROM V$ARCHIVED_LOG NB
  5           WHERE NB.APPLIED = 'YES'
  6   and NB.DEST_ID in (SELECT NB.DEST_ID FROM V$ARCHIVE_DEST_STATUS NB where STATUS <>'INACTIVE')
  7        GROUP BY NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID) B
  8   WHERE B.THREAD# = A.THREAD#
  9     AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#
 10     AND A.DEST_ID=B.DEST_ID
 11     AND A.SEQUENCE# >= MAX_SEQUENCE#
 12     AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO'  ELSE 'YES'  END FROM V$DATABASE NB)
 13   ORDER BY A.THREAD#, A.SEQUENCE#;

   THREAD# NAME                                                                                                  SEQUENCE# APPLIED   FIRST_TIME
---------- ---------------------------------------------------------------------------------------------------- ---------- --------- -------------------
         1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_242_hpv5042k_.arc                242 YES       2020-09-25 14:53:53
         1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_243_hpv5050f_.arc                243 YES       2020-09-25 14:53:56
         1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_244_hpv505oh_.arc                244 IN-MEMORY 2020-09-25 14:53:57

确保DG环境恢复正常运行。

二、将备份文件传递到新主库

接下来就是把/home/oracle/oracle_bk/备份目录下的内容都拷贝到新主机上，方法很多，例如scp、磁盘卸载重新挂载等。

[oracle@rhel6lhr oracle_bk]$ scp /home/oracle/oracle_bk/* oracle@192.168.1.35:/home/oracle/oracle_bk/
oracle@192.168.1.35's password: 
FULL_ORADG11G_36vbaa2n_1_1.full         100%  333MB  19.6MB/s   00:17    
FULL_ORADG11G_37vbaa3q_1_1.full         100% 1120KB   1.1MB/s   00:00    
spfile_ORADG11G_39vbaa3v_1_1.ora        100%   96KB  96.0KB/s   00:00    
standby_1.ctl                           100%   10MB   9.8MB/s   00:00    
[oracle@rhel6lhr oracle_bk]$

三、在新主机执行恢复操作

3.1、恢复spfile

export ORACLE_SID=TEST
rman target /
startup nomount;
restore spfile to pfile '?/dbs/initTEST.ora' from '/home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora';

-- 修改pfile，去除dg相关参数
vi $ORACLE_HOME/dbs/initTEST.ora

需要根据情况对pfile做相关的修改，最终的参数文件内容：

建议加上log_file_name_convert和db_recovery_file_dest参数，可以避免很多后续的日志转换操作。

根据pfile文件内容创建相关目录

根据pfile创建spfile，并启动到nomout

整个执行过程：

[oracle@lhrora11203 oracle_bk]$ export ORACLE_SID=TEST
[oracle@lhrora11203 oracle_bk]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:07:43 2020

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area     242208768 bytes

Fixed Size                     2227176 bytes
Variable Size                184550424 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5099520 bytes

RMAN> restore spfile to pfile '?/dbs/initTEST.ora' from '/home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora';

Starting restore at 2020-09-25 15:15:08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2020-09-25 15:15:10

RMAN> exit


Recovery Manager complete.
[oracle@lhrora11203 ~]$ 

[oracle@lhrora11203 ~]$ more /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initTEST.ora 
oradgphy.__db_cache_size=37748736
oradgphy.__java_pool_size=4194304
oradgphy.__large_pool_size=4194304
oradgphy.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oradgphy.__pga_aggregate_target=12582912
oradgphy.__sga_target=197132288
oradgphy.__shared_io_pool_size=0
oradgphy.__shared_pool_size=142606336
oradgphy.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/oradgphy/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/oradgphy/crontal01.ctl','/u01/app/oracle/oradata/oradgphy/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='oradg11g','oradgphy'
*.db_name='oradg11g'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='oradgphy'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oradg11gXDB)'
*.fal_client='tns_oradg11g'
*.fal_server='tns_oradg11g'
*.log_archive_config='dg_config=(oradgphy,oradg11g,oradglg)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=oradgphy'
*.log_archive_dest_2='SERVICE=tns_oradg11g LGWR ASYNC db_unique_name=oradg11g valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
oradgphy.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
oradgphy.log_archive_trace=0
*.log_file_name_convert='oradg11g','oradgphy'
*.memory_max_target=209715200
*.memory_target=209715200
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@lhrora11203 ~]$ 
[oracle@lhrora11203 ~]$ 
[oracle@lhrora11203 ~]$ cat > /home/oracle/a.txt <<"EOF"
> *.audit_file_dest='/home/oracle/oradata/TEST/adump'
> *.audit_trail='db'
> *.compatible='11.2.0.0.0'
> *.control_files='/home/oracle/oradata/TEST/control01.ctl'
> *.db_block_size=8192
> *.db_domain=''
> *.db_name='oradg11g'
> *.db_recovery_file_dest='/home/oracle/oradata/flash_recovery_area'
> *.db_recovery_file_dest_size=4322230272
> *.db_unique_name='TEST'
> *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradg11gXDB)'
> *.memory_max_target=229715200
> *.open_cursors=300
> *.processes=150
> *.remote_login_passwordfile='EXCLUSIVE'
> *.standby_file_management='AUTO'
> *.undo_tablespace='UNDOTBS1'
> *.log_file_name_convert='/u01/app/oracle/oradata/oradg11g/','/home/oracle/oradata/'
> *.db_recovery_file_dest='/home/oracle/oradata/'
> EOF
[oracle@lhrora11203 ~]$ sas

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 25 15:18:00 2020

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


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

SYS@TEST> create spfile from pfile='/home/oracle/a.txt';

File created.

SYS@TEST> startup force nomount
ORACLE instance started.

Total System Global Area  242208768 bytes
Fixed Size                  2227176 bytes
Variable Size             184550424 bytes
Database Buffers           50331648 bytes
Redo Buffers                5099520 bytes

3.2、恢复控制文件

-- 因为要恢复为主库，所以需要加上primary关键字
restore primary controlfile from '/home/oracle/oracle_bk/standby_1.ctl';

执行过程：

[oracle@lhrora11203 ~]$ rman target / 

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:24:08 2020

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

connected to target database: ORADG11G (not mounted)

RMAN> restore primary controlfile from '/home/oracle/oracle_bk/standby_1.ctl';

Starting restore at 2020-09-25 15:24:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/oradata/TEST/control01.ctl
Finished restore at 2020-09-25 15:24:32

3.3、注册备份信息

-- 启动到mout阶段
alter database mount;

-- 清除之前的备份信息
EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(11); /** CLEAR V$ARCHIVED_LOG    */
EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(12); /** CLEAR V$BACKUP_SET    */  
EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13); /** CLEAR V$BACKUP_PIECE    */

--重新注册,注意路径最后一定需要加上/
catalog start with '/home/oracle/oracle_bk/';

执行过程：

SYS@TEST> alter database mount;

Database altered.

SYS@TEST> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(11);

PL/SQL procedure successfully completed.

SYS@TEST>  
SYS@TEST> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(12);

PL/SQL procedure successfully completed.

SYS@TEST> 
SYS@TEST> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13);

PL/SQL procedure successfully completed.

SYS@TEST> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lhrora11203 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:31:44 2020

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

connected to target database: ORADG11G (DBID=1403587593, not open)

RMAN> catalog start with '/home/oracle/oracle_bk/';

Starting implicit crosscheck backup at 2020-09-25 15:31:47
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK
Finished implicit crosscheck backup at 2020-09-25 15:31:48

Starting implicit crosscheck copy at 2020-09-25 15:31:48
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 2020-09-25 15:31:48

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /home/oracle/oracle_bk/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/oracle_bk/standby_1.ctl
File Name: /home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full
File Name: /home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora
File Name: /home/oracle/oracle_bk/FULL_ORADG11G_37vbaa3q_1_1.full

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/oracle_bk/standby_1.ctl
File Name: /home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full
File Name: /home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora
File Name: /home/oracle/oracle_bk/FULL_ORADG11G_37vbaa3q_1_1.full

RMAN> 
RMAN> list backupset summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
1       B  F  A DISK        2020-09-25 14:49:32 1       1       NO         TAG20200925T144932
2       B  F  A DISK        2020-09-25 14:48:55 1       1       YES        TAG20200925T144855
3       B  F  A DISK        2020-09-25 14:49:35 1       1       NO         TAG20200925T144935
4       B  F  A DISK        2020-09-25 14:49:30 1       1       YES        TAG20200925T144855

3.4、还原数据文件

run{
SET NEWNAME FOR DATABASE TO '/home/oracle/oradata/%b';
restore database;
switch datafile all;
}

执行过程：

[oracle@lhrora11203 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:31:44 2020

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

connected to target database: ORADG11G (DBID=1403587593, not open)


RMAN> run{
2> SET NEWNAME FOR DATABASE TO '/home/oracle/oradata/%b';
3> restore database;
4> switch datafile all;
5> }

executing command: SET NEWNAME

Starting restore at 2020-09-25 15:32:54
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/oradata/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oradata/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/oradata/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oradata/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oradata/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/oradata/logmnrtbs1.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full
channel ORA_DISK_1: piece handle=/home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full tag=TAG20200925T144855
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 2020-09-25 15:34:20

datafile 1 switched to datafile copy
input datafile copy RECID=36 STAMP=1052062460 file name=/home/oracle/oradata/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=37 STAMP=1052062460 file name=/home/oracle/oradata/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=38 STAMP=1052062460 file name=/home/oracle/oradata/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=39 STAMP=1052062460 file name=/home/oracle/oradata/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=40 STAMP=1052062460 file name=/home/oracle/oradata/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=41 STAMP=1052062460 file name=/home/oracle/oradata/logmnrtbs1.dbf

RMAN> exit


Recovery Manager complete.

3.5、激活备库为主库，并启动数据库

-- 1、查询数据库状态
select open_mode , database_role, flashback_on from v$database;

-- 2、激活备库为主库，如果已经是主库了，那就跳过该步骤
alter database activate standby database;
-- ALTER DATABASE CLEAR LOGFILE GROUP 4;
-- alter database drop logfile group 4;

-- 3、启动数据库
alter database open resetlogs;

结果：

[oracle@lhrora11203 ~]$ sas

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 25 15:34:58 2020

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


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

SYS@TEST> select open_mode , database_role, flashback_on from v$database;

OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
-------------------- ---------------- ------------------
MOUNTED              PRIMARY          NO

SYS@TEST> set line 1000
SYS@TEST> set numwidth 16
SYS@TEST> col name format a55
SYS@TEST> select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ;

FUZ STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME                FILE# NAME
--- ------- ------------------ ------------------- ---------------- -------------------------------------------------------
NO  ONLINE             3500675 2020-09-25 14:44:17                1 /home/oracle/oradata/system01.dbf
NO  ONLINE             3500675 2020-09-25 14:44:17                2 /home/oracle/oradata/sysaux01.dbf
NO  ONLINE             3500675 2020-09-25 14:44:17                3 /home/oracle/oradata/undotbs01.dbf
NO  ONLINE             3500675 2020-09-25 14:44:17                4 /home/oracle/oradata/users01.dbf
NO  ONLINE             3500675 2020-09-25 14:44:17                5 /home/oracle/oradata/example01.dbf
NO  ONLINE             3500675 2020-09-25 14:44:17                6 /home/oracle/oradata/logmnrtbs1.dbf

-- 此处查询出来fuzzy列都为NO，所以应该可以直接启动数据库，而不用执行recover操作。


SYS@TEST> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '/home/oracle/oradata/redo02.log'


SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Database altered.

SYS@TEST> alter database open resetlogs;

Database altered.

SYS@TEST> select open_mode , database_role, flashback_on from v$database;

OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
-------------------- ---------------- ------------------
READ WRITE           PRIMARY          NO

SYS@TEST> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

3.6、删除不用的日志组

col member format a100
select * from v$logfile;
select 'alter database drop logfile group '|| GROUP# ||';' from v$standby_log;
alter database drop logfile group 4;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;

执行过程：

SYS@TEST> col member format a100
SYS@TEST> select * from v$logfile;

          GROUP# STATUS  TYPE    MEMBER                                                  IS_
---------------- ------- ------- ------------------------------------------------------- ---
               3         ONLINE  /home/oracle/oradata/redo03.log                         NO
               2         ONLINE  /home/oracle/oradata/redo02.log                         NO
               1         ONLINE  /home/oracle/oradata/redo01.log                         NO
               4         STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo04.log     NO
               5         STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo05.log     NO
               6         STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo06.log     NO
               7         STANDBY /home/oracle/oradata/standby_redo07.log                 NO

7 rows selected.

SYS@TEST> select 'alter database drop logfile group '|| GROUP# ||';' from v$standby_log;

'ALTERDATABASEDROPLOGFILEGROUP'||GROUP#||';'
---------------------------------------------------------------------------
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;

SYS@TEST> alter database drop logfile group 4;

Database altered.

SYS@TEST> alter database drop logfile group 5;
alter database drop logfile group 5
*
ERROR at line 1:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SYS@TEST> alter database drop logfile group 6;

Database altered.

SYS@TEST> alter database drop logfile group 7;

Database altered.

SYS@TEST> alter database drop logfile group 5;
alter database drop logfile group 5
*
ERROR at line 1:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 5;
ALTER DATABASE CLEAR LOGFILE GROUP 5
*
ERROR at line 1:
ORA-00350: log 5 of instance TEST (thread 1) needs to be archived
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log'


SYS@TEST> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/oradgphy/standby_redo05.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1


SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 5;
ALTER DATABASE CLEAR LOGFILE GROUP 5
*
ERROR at line 1:
ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/oradgphy/standby_redo05.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1


SYS@TEST> alter database drop logfile group 5;

Database altered.


SYS@TEST> select * from v$logfile;

          GROUP# STATUS  TYPE    MEMBER                            IS_
---------------- ------- ------- --------------------------------- ---
               3         ONLINE  /home/oracle/oradata/redo03.log   NO
               2         ONLINE  /home/oracle/oradata/redo02.log   NO
               1         ONLINE  /home/oracle/oradata/redo01.log   NO

本文结束，感兴趣的朋友可以自行测试不同情况下的还原。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-09-25，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

oracle

本文分享自 DB宝微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

数据库

sql

oracle

登录后参与评论

0 条评论

热度