oracle数据库：数据备份与恢复之一

一、任务

1、备份练习

把数据库设置为归档模式，并启动控制文件的自动备份，自动备份的控制文件放在闪回恢复区内。

2、恢复练习

1）手工破坏一个控制文件，然后进行恢复

2）手工破坏所有的控制文件，然后进行恢复

3）手工破坏users表空间的数据文件，然后进行恢复

4）手工破坏system表空间的数据文件，然后进行恢复

 

二、测试

1\
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

SQL>show parameter db_recovery_file_dest;

 

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

db_recovery_file_dest                string                            /home/oracle/app/oracle/fast_r

                                                                       ecovery_area

db_recovery_file_dest_size           big integer                       4977M

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     189

Next log sequence to archive   191

Current log sequence           191
2\
1)当有两个控制文件，手工破坏一个控制文件时，数据库运行正常
2)
SQL> select * from v$controlfile;

STATUS

---------------------

NAME

--------------------------------------------------------------------------------

IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS

--------- ---------- --------------

/home/oracle/app/oracle/oradata/shop/control01.ctl

NO             16384            594

/home/oracle/app/oracle/fast_recovery_area/shop/control02.ctl

NO             16384            594

RMAN> show all;

RMAN configuration parameters for database with db_unique_name SHOP are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 3 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 MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_shop.f'; # default

 

把shop的两个控制文件都删了，数据库正常读写，只是shutdown时报错

SQL> shutdown immediate

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/home/oracle/app/oracle/oradata/shop/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL>

SQL>shutdown abort

RMAN> startup nomount;

RMAN> SET DBID=3445933012;

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

SQL> alter database open resetlogs;

3）
# rm users01.dbf

SQL> startup

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2233160 bytes

Variable Size             641731768 bytes

Database Buffers          205520896 bytes

Redo Buffers                6496256 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/shop/users01.dbf'

RMAN> run{

2> sql 'alter database datafile 4 offline';

3> restore datafile 4;

4> recover datafile 4;

5> sql 'alter database datafile 4 online';

6> }

 

RMAN> sql 'alter database open';
sql statement: alter database open

 

4）
 SQL> shutdown immediate
ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/shop/system01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3
SQL> shutdown abort;
SQL> startup mount;

RMAN> run{

2> sql 'alter database datafile 1 offline';

3> restore datafile 1;

4> recover datafile 1;

5> sql 'alter database datafile 1 online';

6> }
RMAN> sql 'alter database open';

本文来源 我爱IT技术网 http://www.52ij.com/jishu/5121.html 转载请保留链接。