4 Oracle db 12c并使用RMAN做异机恢复(3)

日期：2020-06-02 栏目：程序人生浏览：次

RMAN恢复数据库准备备份文件

这部分内容参考"[三思笔记]RMAN管理Oracle RAC数据库的备份与恢复"

因为生产的RMAN备份在Linux服务器上，可以在Linux上mount本地windows的共享目录，然后把rman备份传输过来

# mount -t cifs -o username="administrator",password="test" //192.168.1.90/iso /db/windows # cd /db/windows # ls linuxamd64_12102_database_1of2.zip linuxamd64_12102_database_2of2.zip lis4-0-11.iso

使用find命令将指定时间范围内的备份文件拷贝到windows上，然后再用pscp上传至虚拟机

find ./ -mtime -6 -exec cp -f {} /db/windows/ \;

使用awk和sort查看备份文件是否缺失

ls -l | awk -F"_" '{print $NF}' | sort

RMAN恢复

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 11 09:19:54 2016

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u1/app/oracle/product/12.1.0.2/db_1/dbs/initTESTDB.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1073741824 bytes

Fixed Size 2932632 bytes
Variable Size 293601384 bytes
Database Buffers 771751936 bytes
Redo Buffers 5455872 bytes
```

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 11 10:47:07 2016

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create spfile from pfile='/u1/pfile.ora';

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size 2924832 bytes
Variable Size 788532960 bytes
Database Buffers 771751936 bytes
Redo Buffers 13848576 bytes
```

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 11 11:11:48 2016

connected to target database: TESTDB (not mounted)

RMAN> set dbid=1234567890

executing command: SET DBID

RMAN> restore controlfile from '/u1/rmanbak/ctl_file_919512140_1_8223';

Starting restore at 11-AUG-16
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u1/app/oracle/oradata/TESTDB/control01.ctl
output file name=/u1/app/oracle/oradata/TESTDB/control02.ctl
Finished restore at 11-AUG-16
```

将数据库启动到mount状态
```sql
RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1
```

注册rman文件备份集，使用shell脚本快速生成catalog命令
``sql for file inls -1`; do echo " catalog backuppiece '/u1/rmanbak/${file}';"; done

例子：
RMAN>
cataloged backup piece
backup piece handle=/u1/rmanbak/db_bak_lev1_919512113_1_8214 RECID=8305 STAMP=919597658
```

删除之前配置的默认通道
```sql
RMAN> configure channel 1 device type disk clear;

using target database control file instead of recovery catalog
old RMAN configuration parameters are successfully deleted

RMAN> configure channel 2 device type disk clear;

old RMAN configuration parameters are successfully deleted

RMAN> configure device type disk clear;

RMAN configuration parameters are successfully reset to default value
```

获取源数据库中数据文件的路径
```sql
SQL> select file#,name from v$datafile;

FILE# NAME
----- ------------------------------------------------------------
1 +DATADG/TESTDB/system01.dbf
2 +DATADG/TESTDB/system02.dbf
3 +DATADG/TESTDB/sysaux01.dbf
4 +DATADG/TESTDB/undotbs01.dbf
5 +DATADG/TESTDB/undotbs02.dbf
6 +DATADG/TESTDB/users01.dbf

SQL> select FILE#,NAME from v$tempfile;

FILE# NAME
----- ------------------------------------------------------------
1 +DATADG/TESTDB/temp01.dbf
2 +DATADG/TESTDB/testtemp_01.dbf

SQL> select member from v$logfile;

MEMBER

+DATADG/TESTDB/redo02.log
+DATADG/TESTDB/redo01.log
+DATADG/TESTDB/redo03.log
+DATADG/TESTDB/redo04.log
```

使用awk生成set newname命令

cat filenatest.txt | awk -F"[/+]" '{print "set newname for datafile "$1" to '\''/u1/app/oracle/oradata/TESTDB/"$NF"'\'';"}'

编写如下脚本，然后执行数据还原。

rman cmdfile=restore.rman restore.rman内容： connect target sys/oracle run{ set newname for datafile 1 to '/u1/app/oracle/oradata/TESTDB/system01.dbf'; set newname for datafile 2 to '/u1/app/oracle/oradata/TESTDB/system02.dbf'; set newname for datafile 3 to '/u1/app/oracle/oradata/TESTDB/sysaux01.dbf'; set newname for datafile 4 to '/u1/app/oracle/oradata/TESTDB/undotbs01.dbf'; set newname for datafile 5 to '/u1/app/oracle/oradata/TESTDB/undotbs02.dbf'; set newname for datafile 6 to '/u1/app/oracle/oradata/TESTDB/users01.dbf'; set newname for tempfile 1 to '/u1/app/oracle/oradata/TESTDB/temp01.dbf'; set newname for tempfile 2 to '/u1/app/oracle/oradata/TESTDB/testtemp_01.dbf'; restore database; switch datafile all; switch tempfile all; }

switch命令作用是将新的数据文件路径写入control文件，用法可以参考rman备份恢复命令之switch
命令执行过程中会有如下提示：
input datafile copy RECID=56 STAMP=919619004 file name=/u1/app/oracle/oradata/TESTDB/system01.dbf
renamed tempfile 1 to /u1/app/oracle/oradata/TESTDB/temp01.dbf in control file

然后使用recover database命令进行完全恢复

$ rman target / RMAN > recover database; --可以看到在使用lev1备份进行增量恢复 channel ORA_DISK_1: reading from backup piece /u1/rmanbak/db_bak_lev1_919512036_1_8202 channel ORA_DISK_1: piece handle=/u1/rmanbak/db_bak_lev1_919512036_1_8202 tag=DB_BAK_LEV1 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00012: /u1/app/oracle/oradata/TESTDB/testdata_l1_02.dbf destination for restore of datafile 00024: /u1/app/oracle/oradata/TESTDB/testdata_t30_01.dbf destination for restore of datafile 00036: /u1/app/oracle/oradata/TESTDB/test_idx_t10_01 destination for restore of datafile 00038: /u1/app/oracle/oradata/TESTDB/testidx_t30_01.dbf

过程中遇到错误，原因是只复制了备份机，没有应用源数据库的最新归档和Online redo文件，只能进行不完全恢复

unable to find archived log archived log thread=2 sequence=7201 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR TESTSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 08/11/2016 20:27:33 RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 7201 and starting SCN of 816203242

现在数据文件和临时文件已经通过rman switch更新了位置，还有重做日志文件需要修改controlfile修改日志文件的路径
```sql
SQL> select member from v$logfile;

MEMBER

+DATADG/TESTDB/redo02.log
+DATADG/TESTDB/redo01.log
+DATADG/TESTDB/redo03.log
+DATADG/TESTDB/redo04.log

SQL> alter database backup controlfile to trace;

Database altered.

-- 从alert_TESTDB.log可以获取trace文件的位置
Fri Aug 12 08:49:26 2016
alter database backup controlfile to trace
Fri Aug 12 08:49:26 2016
Backup controlfile written to trace file /u1/app/oracle/diag/rdbms/testdb/TESTDB/trace/TESTDB_ora_9049.trc
Completed: alter database backup controlfile to trace

-- 在新文件中修改下LOGFILE的位置，并去除RMAN的信息即可。
SQL> CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 4672
2 3 4 LOGFILE
GROUP 1 '/u1/app/oracle/oradata/TESTDB/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u1/app/oracle/oradata/TESTDB/redo02.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u1/app/oracle/oradata/TESTDB/system01.dbf',
'/u1/app/oracle/oradata/TESTDB/system02.dbf',
'/u1/app/oracle/oradata/TESTDB/sysaux01.dbf',
'/u1/app/oracle/oradata/TESTDB/undotbs01.dbf',
'/u1/app/oracle/oradata/TESTDB/undotbs02.dbf',
'/u1/app/oracle/oradata/TESTDB/users01.dbf',
CHARACTER SET AL32UTF8
;
64 65 66
Control file created.

-- 新增一组日志文件
SQL> alter database add logfile thread 2
2 group 3 '/u1/app/oracle/oradata/TESTDB/redo03.log' size 50m,
3 group 4 '/u1/app/oracle/oradata/TESTDB/redo04.log' size 50m;
```

使用resetlogs打开数据库
```sql
SQL> alter database open resetlogs;

Database altered.
```

因为RMAN不会重建临时表空间，需要手动建一下临时表空间
```sql
SQL> alter tablespace temp add tempfile '/u1/app/oracle/oradata/TESTDB/temp01.dbf' size 50m;

Tablespace altered.

SQL> alter tablespace TESTTEMP add tempfile '/u1/app/oracle/oradata/TESTDB/testtemp_01.dbf' size 50m;

Tablespace altered.
```

清除未使用的redo日志组，其实正好是上面添加的redo文件
```sql
SQL> select thread#,status,enabled from v$thread;

THREAD# STATUS ENABLED
------- ---------- ----------
1 OPEN PUBLIC
2 CLOSED PUBLIC

SQL> col group for 99
SQL> select group#,thread#,archived,status from v$log;

GROUP# THREAD# ARC STATUS 1 1 YES INACTIVE 2 1 NO CURRENT 3 2 YES INACTIVE 4 2 YES INACTIVE

SQL> alter database disable thread 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.
```

删除多余的undo文件
```sql
SQL> select name from v$tablespace where name like 'UNDO%';

NAME

UNDOTBS1
UNDOTBS2

SQL> show parameter undo_tablespace;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.
```

至此整套数据库环境搭建和数据恢复完成

转载注明出处：https://www.heiqu.com/b00a4bc7ecc84137b166ac9a3e53a750.html

4 Oracle db 12c并使用RMAN做异机恢复(3)

相关推荐