Oracle数据库备份恢复
数据库基本操作:
启动数据库:
从root切换到oracle用户进入:
su - oracle
进入sqlplus环境,nolong参数表示不登录: sqlplus /nolog
以管理员模式登录: sqlplus / as sysdba
启动数据库: startup
关闭数据库: shutdown immediate
远程连接数据库:
sqlplus /nolog conn sys/sys@ip:1521/orainstance as sysdba
开启监听: lsnrctl start
查看监听: lsnrctl status
停止监听: lsnrctl stop
oracle备份恢复rman恢复到异机
注意事项:
1 此处实验环境为同平台，同字节序，同版本，源机器和目标机器相同的目录结构。
2 目标机器只需要安装oracle数据库软件即可。
3 第一次利用备份恢复测试环境，之后从源机器拷贝备份到目标机器并在控制文件中注册，再见行恢复测试。
一 拷贝参数文件备份、控制文件备份、数据文件备份、以及归档备份到新主机上
1 rman 连接到源数据库
[oracle@hear ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jun 11 01:52:52 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: HEARDB (DBID=3039837723)
RMAN>
2 分别列出参数文件备份，控制文件备份，数据文件备份，以及归档备份的名字
# 参数文件备份:
RMAN> list backup of spfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 9.36M DISK 00:00:00 10-JUN-19
BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20190610T231216
Piece Name: /home/oracle/test/db1/c-3039837723-20190610-00
SPFILE Included: Modification time: 10-JUN-19
SPFILE db_unique_name: HEARDB
# 控制文件备份如下:
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 9.33M DISK 00:00:01 10-JUN-19
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20190610T230946
Piece Name: /home/oracle/test/db1/full_0nu3pjic_HEARDB_20190610
Control File Included: Ckp SCN: 1167815 Ckp time: 10-JUN-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 9.36M DISK 00:00:00 10-JUN-19
BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20190610T231216
Piece Name: /home/oracle/test/db1/c-3039837723-20190610-00
Control File Included: Ckp SCN: 1167832 Ckp time: 10-JUN-19
# 数据文件备份如下:
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 1.09G DISK 00:02:20 10-JUN-19
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20190610T230946
Piece Name: /home/oracle/test/db1/full_0mu3pjdq_HEARDB_20190610
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1167758 10-JUN-19 /home/oracle/app/oradata/heardb/system01.dbf
2 Full 1167758 10-JUN-19 /home/oracle/app/oradata/heardb/sysaux01.dbf
3 Full 1167758 10-JUN-19 /home/oracle/app/oradata/heardb/undotbs01.dbf
4 Full 1167758 10-JUN-19 /home/oracle/app/oradata/heardb/users01.dbf
5 Full 1167758 10-JUN-19 /home/oracle/app/oradata/heardb/example01.dbf
# 数据文件备份如下:
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 1.09G DISK 00:02:20 10-JUN-19
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20190610T230946
Piece Name: /home/oracle/test/db1/full_0mu3pjdq_HEARDB_20190610
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1167758 10-JUN-19 /home/oracle/app/oradata/heardb/system01.dbf
2 Full 1167758 10-JUN-19 /home/oracle/app/oradata/heardb/sysaux01.dbf
3 Full 1167758 10-JUN-19 /home/oracle/app/oradata/heardb/undotbs01.dbf
4 Full 1167758 10-JUN-19 /home/oracle/app/oradata/heardb/users01.dbf
5 Full 1167758 10-JUN-19 /home/oracle/app/oradata/heardb/example01.dbf
# 列出归档备份如下:
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
15 48.19M DISK 00:00:11 10-JUN-19
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20190610T230930
Piece Name: /home/oracle/test/db1/arch_0lu3pjda_HEARDB_20190610
List of Archived Logs in backup set 15
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 1060303 05-JUN-19 1086202 09-JUN-19
1 4 1086202 09-JUN-19 1118538 10-JUN-19
1 5 1118538 10-JUN-19 1139060 10-JUN-19
1 6 1139060 10-JUN-19 1160512 10-JUN-19
1 7 1160512 10-JUN-19 1167743 10-JUN-19
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
18 7.50K DISK 00:00:00 10-JUN-19
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20190610T231215
Piece Name: /home/oracle/test/db1/arch_0ou3pjif_HEARDB_20190610
List of Archived Logs in backup set 18
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 8 1167743 10-JUN-19 1167821 10-JUN-19
RMAN>
3 copy 这些备份到新的主机
二 恢复参数文件及控制文件
1 配置新的主机上的ORACLE_SID
xxxxxxxxxx
export ORACLE_SID=CRM
2 在新主机上发起rman连接
[oracle@hear oradata]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jun 11 02:54:56 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
3 设置ddid 并启动实例到nomount状态
RMAN> set dbid 3039837723
executing command: SET DBID
RMAN> startup nomount
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2254952 bytes
Variable Size 1744832408 bytes
Database Buffers 385875968 bytes
Redo Buffers 4923392 bytes
4 恢复spfile 文件
restore spfile to '/oracle/app/db1/dbs/spfileCRM.ora' from '/oracle/app/db1/dbs/0dnsd96i_1_1';
restore spfile to '/home/oracle/test/db1/spfileCRM.ora' from '/home/oracle/test/db1/c-3039837723-20190610-00';
# 操作
RMAN> restore spfile to '/home/oracle/test/db1/spfileCRM.ora' from '/home/oracle/test/db1/c-3039837723-20190610-00';
Starting restore at 11-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1135 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/test/db1/c-3039837723-20190610-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 11-JUN-19
5 startup force nomount
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 2137886720 bytes
Fixed Size 2254952 bytes
Variable Size 1744832408 bytes
Database Buffers 385875968 bytes
Redo Buffers 4923392 bytes
6 恢复控制文件
restore controlfile to '/oracle/CRM2/CRM/control01.ctl' from '/oracle/app/db1/dbs/0dnsd96i_1_1';
restore controlfile to '/home/oracle/app/oradata/crm/control01.ctl' from '/home/oracle/test/db1/c-3039837723-20190610-00';
# 操作
RMAN> restore controlfile to '/home/oracle/app/oradata/crm/control01.ctl' from '/home/oracle/test/db1/c-3039837723-20190610-00';
Starting restore at 11-JUN-19
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 11-JUN-19
7 启动数据库到加载状态
alter database mount;
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
三 在新的控制文件中注册数据文件备份和归档备份
x
# 新备份文件/home/oracle/backup
catalog start with '/home/oracle/backup/';
# 备份文件 /home/oracle/test/db1
catalog start with '/home/oracle/db1/';
备份文件
xxxxxxxxxx
RMAN> catalog start with '/home/oracle/db1/';
searching for all files that match the pattern /home/oracle/db1/
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/db1/full_0mu3pjdq_HEARDB_20190610
File Name: /home/oracle/db1/arch_0lu3pjda_HEARDB_20190610
File Name: /home/oracle/db1/arch_0ou3pjif_HEARDB_20190610
File Name: /home/oracle/db1/full_0nu3pjic_HEARDB_20190610
File Name: /home/oracle/db1/c-3039837723-20190610-00
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/db1/full_0mu3pjdq_HEARDB_20190610
File Name: /home/oracle/db1/arch_0lu3pjda_HEARDB_20190610
File Name: /home/oracle/db1/arch_0ou3pjif_HEARDB_20190610
File Name: /home/oracle/db1/full_0nu3pjic_HEARDB_20190610
File Name: /home/oracle/db1/c-3039837723-20190610-00
四 恢复整个库
1 RMAN> restore database;
xxxxxxxxxx
RMAN> restore database;
Starting restore at 11-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1135 device type=DISK
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/app/oradata/heardb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oradata/heardb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oradata/heardb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oradata/heardb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oradata/heardb/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/test/db1/full_0mu3pjdq_HEARDB_20190610
channel ORA_DISK_1: piece handle=/home/oracle/test/db1/full_0mu3pjdq_HEARDB_20190610 tag=TAG20190610T230946
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 11-JUN-19
2 RMAN> recover database;
xxxxxxxxxx
RMAN> recover database;
Starting recover at 11-JUN-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:06
Finished recover at 11-JUN-19
3 alter database open resetlogs 打开数据库
xxxxxxxxxx
SQL> alter database open resetlogs；