Networker恢复oracle单节点

  • hellohf123

    2021-12-06 14:42:25
  • 数据安全
  • 原创

Networker备份oracle单节点

http://blog.itpub.net/70004783/viewspace-2845335/


在异机恢复数据库。

此处在机器oracle203上创建一个表空间hf，创建用户hf01，并用hf01创建表格test，插入了2条数据。

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/system01.dbf
/u01/app/oracle/oradata/PROD/sysaux01.dbf
/u01/app/oracle/oradata/PROD/undotbs01.dbf
/u01/app/oracle/oradata/PROD/pdbseed/system01.dbf
/u01/app/oracle/oradata/PROD/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/PROD/users01.dbf
/u01/app/oracle/oradata/PROD/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/PROD/pdb/system01.dbf
/u01/app/oracle/oradata/PROD/pdb/sysaux01.dbf
/u01/app/oracle/oradata/PROD/pdb/undotbs01.dbf
/u01/app/oracle/oradata/PROD/pdb/users01.dbf
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/pdb/hf01.dbf
12 rows selected.
SQL> alter session set container = pdb;
Session altered.
SQL> desc hf01.test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(10)
SQL>

现在备份一下，准备接下来的恢复实验



创建异机恢复的系统，为方便，此处我和oracle203上部署一样

但是仅仅安装数据库文件，开启监听，没有进行dbca的创建。

此处的恢复系统为oracle204，并添加客户端，添加客户端参照

http://blog.itpub.net/70004783/viewspace-2845335/


我这里添加客户端选择的是


我这里oracle没安装好，所以选择的是filesystem







oracle恢复过程

在oracle204机器上，创建最简单的c参数文件

[oracle@oracle204 dbs]$ cat initprod.ora
*.db_name='prod'
*.sga_target=1496m
[oracle@oracle204 dbs]$ pwd
/u01/app/oracle/product/19c/db_1/dbs


启动数据库到nomount状态

[oracle@oracle204 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 6 10:35:18 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> startup nomount


在Networker查询最新的备份，确定rman恢复脚本。



首先从备份集恢复参数文件


依据查询的参数对应修改这一行

send 'NSR_ENV=( NSR_CLIENT= oracle203,NSR_SERVER= backserver,NSR_DATA_VOLUME_POOL= bstindex)';


RMAN> set dbid=502085259
executing command: SET DBID
RMAN> run
{
allocate channel t1 type 'SBT_TAPE';
send 'NSR_ENV=( NSR_CLIENT=oracle203,NSR_SERVER=backserver,NSR_DATA_VOLUME_POOL=bstindex)';
restore spfile from autobackup;
release channel t1;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=178 device type=SBT_TAPE
channel t1: NMDA Oracle v19.2.1.2
sent command to channel: t1
Starting restore at 2021-12-06 10:27:05
channel t1: looking for AUTOBACKUP on day: 20211206
channel t1: AUTOBACKUP found: c-502085259-20211206-01
channel t1: restoring spfile from AUTOBACKUP c-502085259-20211206-01
channel t1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2021-12-06 10:37:10
released channel: t1
RMAN> ^C


利用恢复的参数文件重启到nomount状态，准备恢复控制文件。

[oracle@oracle204 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 6 10:48:11 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
SQL>

报错文件夹不存在，肯定是参数文件有些文件夹文件系统还没有，则创建

[oracle@oracle204 dbs]$ strings spfileprod.ora >> test.txt
[oracle@oracle204 dbs]$ cat test.txt
prod.__data_transfer_cache_size=0
prod.__db_cache_size=2902458368
prod.__inmemory_ext_roarea=0
prod.__inmemory_ext_rwarea=0
prod.__java_pool_size=33554432
prod.__large_pool_size=16777216
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=1275068416
prod.__sga_target=3825205248
prod.__shared_io_pool_size=134217728
prod.__shared_pool_size=721420288
prod.__streams_pool_size=0
prod.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle
/admin/prod/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/PROD/control01.ctl','/u01/app/oracle/oradata/PROD/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_name='prod'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_PROD'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1214m
*.processes=3
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3641m
*.undo_tablespace='UNDOTBS1'
[oracle@oracle204 dbs]$

创建参数文件有，但系统里面没有的文件夹  

[oracle@oracle204 dbs]$ mkdir -p /u01/app/oracle/admin/prod/adump
[oracle@oracle204 dbs]$ mkdir -p /u01/app/oracle/oradata/PROD/


再次用新的参数文件重启系统到nomount状态，准备恢复控制文件。

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 3825204656 bytes
Fixed Size                  9141680 bytes
Variable Size             771751936 bytes
Database Buffers         3036676096 bytes
Redo Buffers                7634944 bytes
SQL>



Networker查看spfile所在的备份集，其实和上面恢复spfile是同一个备份集

RMAN> set dbid=502085259
executing command: SET DBID
RMAN> run
{
allocate channel t1 type 'SBT_TAPE';
send 'NSR_ENV=( NSR_CLIENT=oracle203,NSR_SERVER=backserver,NSR_DATA_VOLUME_POOL=bstindex)';
restore controlfile from 'c-502085259-20211206-01';
release channel t1;
}2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=21 device type=SBT_TAPE
channel t1: NMDA Oracle v19.2.1.2
sent command to channel: t1
Starting restore at 2021-12-06 10:58:58
channel t1: restoring control file
channel t1: restore complete, elapsed time: 00:10:15
output file name=/u01/app/oracle/oradata/PROD/control01.ctl
output file name=/u01/app/oracle/oradata/PROD/control02.ctl
Finished restore at 2021-12-06 11:09:14
released channel: t1
RMAN>


启动数据库到mount状态，restore数据文件。

RMAN> alter database mount;
Statement processed
RMAN> run
{
allocate channel t1 type 'SBT_TAPE';
send 'NSR_ENV=( NSR_CLIENT=oracle203,NSR_SERVER=backserver,NSR_DATA_VOLUME_POOL=bstindex)';
restore database;
release channel t1;
}2> 3> 4> 5> 6> 7>
allocated channel: t1
channel t1: SID=258 device type=SBT_TAPE
channel t1: NMDA Oracle v19.2.1.2
sent command to channel: t1
Starting restore at 2021-12-06 11:15:53
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD/system01.dbf
channel t1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD/sysaux01.dbf
channel t1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/undotbs01.dbf
channel t1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD/users01.dbf
channel t1: reading from backup piece PROD_190g1kk1_1_1
channel t1: piece handle=PROD_190g1kk1_1_1 tag=TAG20211206T085529
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:10:25
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00009 to /u01/app/oracle/oradata/PROD/pdb/system01.dbf
channel t1: restoring datafile 00010 to /u01/app/oracle/oradata/PROD/pdb/sysaux01.dbf
channel t1: restoring datafile 00011 to /u01/app/oracle/oradata/PROD/pdb/undotbs01.dbf
channel t1: restoring datafile 00012 to /u01/app/oracle/oradata/PROD/pdb/users01.dbf
channel t1: restoring datafile 00013 to /u01/app/oracle/oradata/PROD/pdb/hf01.dbf
channel t1: reading from backup piece PROD_1a0g1l78_1_1
channel t1: piece handle=PROD_1a0g1l78_1_1 tag=TAG20211206T085529
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:10:15
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD/pdbseed/system01.dbf
channel t1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD/pdbseed/sysaux01.dbf
channel t1: restoring datafile 00008 to /u01/app/oracle/oradata/PROD/pdbseed/undotbs01.dbf
channel t1: reading from backup piece PROD_1b0g1lqf_1_1
channel t1: piece handle=PROD_1b0g1lqf_1_1 tag=TAG20211206T085529
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:10:15
Finished restore at 2021-12-06 11:46:48
released channel: t1
RMAN>


recover数据库

RMAN> run
{
allocate channel t1 type 'SBT_TAPE';
send 'NSR_ENV=( NSR_CLIENT=oracle203,NSR_SERVER=backserver,NSR_DATA_VOLUME_POOL=bstindex)';
recover database;
release channel t1;
}2> 3> 4> 5> 6> 7>
allocated channel: t1
channel t1: SID=258 device type=SBT_TAPE
channel t1: NMDA Oracle v19.2.1.2
sent command to channel: t1
Starting recover at 2021-12-06 11:52:27
starting media recovery
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=18
channel t1: reading from backup piece PROD_1d0g1n0b_1_1
channel t1: piece handle=PROD_1d0g1n0b_1_1 tag=TAG20211206T093611
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:10:15
archived log file name=/u01/app/oracle/product/19c/db_1/dbs/arch1_18_1090421175.dbf thread=1 sequence=18
unable to find archived log
archived log thread=1 sequence=19
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/06/2021 12:02:46
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 19 and starting SCN of 2648001
RMAN>


这是因为Oracle不会自动截断日志，会不断要求找下一个归档日志，因此需要在RMAN脚本中指定截断  

根据报错提示，在recover脚本中添加until sequence 19，再次使用RMAN脚本recover数据库

RMAN> run
{
allocate channel t1 type 'SBT_TAPE';
send 'NSR_ENV=( NSR_CLIENT=oracle203,NSR_SERVER=backserver,NSR_DATA_VOLUME_POOL=bstindex)';
recover database until sequence 19 ;
release channel t1;
}2> 3> 4> 5> 6> 7>
allocated channel: t1
channel t1: SID=258 device type=SBT_TAPE
channel t1: NMDA Oracle v19.2.1.2
sent command to channel: t1
Starting recover at 2021-12-06 12:05:29
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2021-12-06 12:05:30
released channel: t1
RMAN>


recover成功后，打开数据库，并resetlogs

RMAN> alter database open resetlogs;
Statement processed
RMAN>


查询备份的数据，看是否正确。


请使用浏览器的分享功能分享到微信等