<<OCM实验选讲>> 第五课 数据备份恢复实验
来源：互联网 发布：网络ip地址怎么修改 编辑：程序博客网 时间：2024/05/17 05:09
一 模拟Control File 丢失故障处理(LEO1实例)
模拟控制文件丢失并恢复:
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /home/oracle/oracle/product/10
.2.0/db_1/oradata/LEO1/control
01.ctl, /home/oracle/oracle/pr
oduct/10.2.0/db_1/oradata/LEO1
/control02.ctl, /home/oracle/o
racle/product/10.2.0/db_1/orad
ata/LEO1/control03.ctl
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string LEO1
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
[oracle@odd ~]$ ls -l /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control*
-rw-r----- 1 oracle oinstall 7061504 Jan 18 18:18 /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Jan 18 18:18 /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Jan 18 18:18 /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control03.ctl
[oracle@odd ~]$ rm /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control01.ctl
[oracle@odd ~]$
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> exit
[oracle@odd ~]$ tail oracle/product/10.2.0/db_1/admin/LEO1/bdump/alert_LEO1.log
MMNL started with pid=12, OS id=4706
Sat Jan 18 18:19:46 2014
ALTER DATABASE MOUNT
Sat Jan 18 18:19:46 2014
ORA-00202: control file: '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Sat Jan 18 18:19:46 2014
ORA-205 signalled during: ALTER DATABASE MOUNT...
[oracle@odd ~]$
[oracle@odd ~]$ ls -l /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control*
-rw-r----- 1 oracle oinstall 7061504 Jan 18 18:18 /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Jan 18 18:18 /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control03.ctl
[oracle@odd ~]$ cp /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control02.ctl /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control01.ctl
[oracle@odd ~]$ ls -l /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control*-rw-r----- 1 oracle oinstall 7061504 Jan 18 18:21 /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Jan 18 18:18 /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Jan 18 18:18 /home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/control03.ctl
[oracle@odd ~]$
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status,instance_name from v$instance;
STATUS INSTANCE_NAME
------------ ----------------
OPEN LEO1
SQL>
--EOF--
二 创建CATALOG库保存RMAN备份信息
sqlplus sys/oracle@LEO2 as sysdba
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
/home/oracle/oracle/oradata/LEO2/system01.dbf
/home/oracle/oracle/oradata/LEO2/undotbs01.dbf
/home/oracle/oracle/oradata/LEO2/sysaux01.dbf
/home/oracle/oracle/oradata/LEO2/users01.dbf
/home/oracle/oracle/oradata/LEO2/mgmt.dbf
/home/oracle/oracle/oradata/LEO2/mgmt_ecm_depot1.dbf
6 rows selected.
SQL> create tablespace catalog_tbs datafile '/home/oracle/oracle/oradata/LEO2/catalog_tbs01.dbf' size 100m autoextend on;
Tablespace created.
SQL> create user catadmin identified by catadmin default tablespace catalog_tbs;
User created.
SQL> grant connect,resource,recovery_catalog_owner to catadmin;
Grant succeeded.
SQL> exit
[oracle@even ~]$ rman catalog catadmin/catadmin@LEO2
Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jan 18 18:30:23 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> create catalog tablespace catalog_tbs;
recovery catalog created
RMAN> exit
[oracle@even ~]$ rman target sys/oracle@LEO1 catalog catadmin/catadmin@LEO2
Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jan 18 18:31:03 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: LEO1 (DBID=1715976824)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> exit
[oracle@even ~]$ sqlplus catadmin/catadmin@LEO2
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 18 18:31:29 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> select * from rc_database;
DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 2 1715976824 LEO1 1 18-JAN-14
SQL> exit
--EOF--
三 设置RMAN环境变量
[oracle@even ~]$ rman target sys/oracle@LEO1 catalog catadmin/catadmin@LEO2
Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jan 18 18:36:10 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: LEO1 (DBID=1715976824)
connected to recovery catalog database
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/oracle/product/10.2.0/db_1/dbs/snapcf_LEO1.f'; # default
RMAN> configure channel device type disk format '/home/oracle/backup/LEO1_%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/backup/LEO1_%U';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> configure controlfile autobackup format for device type disk to '/home/oracle/backup/control/controlfile_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/control/controlfile_%F';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> configure backup optimization on;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> configure retention policy to recovery window of 30 days;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/control/controlfile_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/backup/LEO1_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/oracle/product/10.2.0/db_1/dbs/snapcf_LEO1.f'; # default
RMAN>
--EOF--
四 RMAN备份和闪回数据库
LEO1开启归档
[oracle@odd ~]$ mkdir -p /home/oracle/backup/control/
[oracle@odd ~]$ mkdir -p /home/oracle/oracle/arcdata
sqlplus / as sydba
SQL> alter system set log_archive_dest_1='location=/home/oracle/oracle/arcdata' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
LEO1 ARCHIVELOG
SQL> exit
一行RMAN命令压缩备份数据库
[oracle@odd admin]$ rman target sys/oracle@LEO1 catalog catadmin/catadmin@LEO2
Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jan 18 18:57:24 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: LEO1 (DBID=1715976824)
connected to recovery catalog database
RMAN> backup as compressed backupset full database format
'/home/oracle/backup/full_leo1_%u%p%s.rmn' include current controlfile
plus archivelog format '/home/oracle/backup/arch_leo1_%u%p%s.rmn' delete all input; 2> 3>
Starting backup at 18-JAN-14
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=141 devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=10 recid=1 stamp=837197886
channel ORA_DISK_1: starting piece 1 at 18-JAN-14
channel ORA_DISK_1: finished piece 1 at 18-JAN-14
piece handle=/home/oracle/backup/arch_leo1_01oud82011.rmn tag=TAG20140118T185808 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/home/oracle/oracle/arcdata/1_10_837172792.dbf recid=1 stamp=837197886
Finished backup at 18-JAN-14
Starting backup at 18-JAN-14
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/system01.dbf
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 18-JAN-14
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/users01.dbf
channel ORA_DISK_2: starting piece 1 at 18-JAN-14
channel ORA_DISK_1: finished piece 1 at 18-JAN-14
piece handle=/home/oracle/backup/full_leo1_02oud82212.rmn tag=TAG20140118T185810 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 18-JAN-14
channel ORA_DISK_2: finished piece 1 at 18-JAN-14
piece handle=/home/oracle/backup/full_leo1_03oud82213.rmn tag=TAG20140118T185810 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: finished piece 1 at 18-JAN-14
piece handle=/home/oracle/backup/full_leo1_04oud82h14.rmn tag=TAG20140118T185810 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-JAN-14
Starting backup at 18-JAN-14
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=11 recid=2 stamp=837197906
channel ORA_DISK_1: starting piece 1 at 18-JAN-14
channel ORA_DISK_1: finished piece 1 at 18-JAN-14
piece handle=/home/oracle/backup/arch_leo1_05oud82j15.rmn tag=TAG20140118T185827 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/home/oracle/oracle/arcdata/1_11_837172792.dbf recid=2 stamp=837197906
Finished backup at 18-JAN-14
Starting Control File and SPFILE Autobackup at 18-JAN-14
piece handle=/home/oracle/backup/control/controlfile_c-1715976824-20140118-00 comment=NONE
Finished Control File and SPFILE Autobackup at 18-JAN-14
RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
54 B A A DISK 18-JAN-14 1 1 YES TAG20140118T185808
55 B F A DISK 18-JAN-14 1 1 YES TAG20140118T185810
56 B F A DISK 18-JAN-14 1 1 YES TAG20140118T185810
57 B F A DISK 18-JAN-14 1 1 YES TAG20140118T185810
74 B A A DISK 18-JAN-14 1 1 YES TAG20140118T185827
84 B F A DISK 18-JAN-14 1 1 NO TAG20140118T185830
RMAN> exit
Recovery Manager complete.
[oracle@odd admin]$ find /home/oracle/backup
/home/oracle/backup
/home/oracle/backup/control
/home/oracle/backup/control/controlfile_c-1715976824-20140118-00
/home/oracle/backup/full_leo1_02oud82212.rmn
/home/oracle/backup/arch_leo1_01oud82011.rmn
/home/oracle/backup/full_leo1_04oud82h14.rmn
/home/oracle/backup/full_leo1_03oud82213.rmn
/home/oracle/backup/arch_leo1_05oud82j15.rmn
[oracle@odd admin]$ ls -l /home/oracle/oracle/arcdata
total 0
[oracle@odd admin]$
配置flashback数据库
[oracle@odd admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 18 19:00:18 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name,open_mode,log_mode,flashback_on from v$database;
NAME OPEN_MODE LOG_MODE FLASHBACK_ON
--------- ---------- ------------ ------------------
LEO1 READ WRITE ARCHIVELOG NO
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/oracle/arcdata
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
SQL> alter system set db_recovery_file_dest_size=2g;
System altered.
SQL> !mkdir -p /home/oracle/flash
SQL> alter system set db_recovery_file_dest='/home/oracle/flash';
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/oracle/flash
db_recovery_file_dest_size big integer 2G
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select name,open_mode,log_mode,flashback_on from v$database;
NAME OPEN_MODE LOG_MODE FLASHBACK_ON
--------- ---------- ------------ ------------------
LEO1 READ WRITE ARCHIVELOG YES
SQL> exit
--EOF--
五 配置flashback数据库
请参看第四题.
--EOF--
- <<OCM实验选讲>> 第五课 数据备份恢复实验
- OCM实验-备份恢复-控制文件
- <<OCM实验选讲>> 第二课 手工建库 实验
- <<OCM实验选讲>> 第三课 网络配置 实验
- <<OCM实验选讲>> 第六课 数据仓库实验
- <<OCM实验选讲>> 第七课 Oracle数据库管理实验
- <<OCM实验选讲>> 第八课 Oracle性能优化实验
- <<OCM实验选讲>> 第十课 DG实验
- oracle 备份恢复实验
- OCM实验目录
- OCM实验-手工建库
- oracle dataguard 实验(恢复备份至异机)
- DB2数据库备份与恢复实验全过程
- oracle实验记录 (恢复,备份-含坏块数据文件)
- oracle 热备份与恢复实验
- Oracle 备份恢复 31个实验 准备工作
- DB2 数据库备份及恢复实验脚本
- （伟大的实验 ）RESETLOGS后没有备份情况下的数据恢复
- Java中的i++和i--
- zoj1091题解
- 作业3
- Learning the parts of object by NMF
- HDU 1576 A/B
- <<OCM实验选讲>> 第五课 数据备份恢复实验
- Struts2标签
- cocos2dx场景切换小例子
- JVM调优分享-(概要+例子)
- dede用户登录时，跳转到提示页时报404错误
- opencv 关于 cvCalcHist的含义（1）
- unix高级环境编程 例子 代码实现练习 第七章：进程环境
- 序言
- 二叉树的层次遍历
