oracle课堂笔记---第二十六天

控制文件

丢失部分控制文件：

show parameter  control_files;

SQL> select * from v$controlfile;

$ >/u01/app/oracle/oradata/orcl/control01.ctl

SQL> select * from v$tablespace; 报错

SQL> alter system checkpoint; 报错

$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

SQL> shutdown abort

SQL> startup nomount

SQL> show parameter control_files

$ cp /u01/app/oracle/fast_recovery_area/orcl/control02.ctl /u01/app/oracle/oradata/orcl/control01.ctl

SQL> alter database mount;

SQL> alter database open;

丢失全部控制文件（有自动备份）：

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> backup datafile 4;

$ >/u01/app/oracle/oradata/orcl/control01.ctl

$ >/u01/app/oracle/fast_recovery_area/orcl/control02.ctl

SQL> select * from v$tablespace; 报错

SQL> alter system checkpoint; 报错

SQL> shutdown abort

SQL> startup nomount

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

RMAN> recover database;

RMAN> alter database open resetlogs;

丢失全部控制文件（没有自动备份）：

SQL> alter database backup controlfile to '/home/oracle/control.bak';

SQL> alter database backup controlfile to trace;

SQL> select * from v$diag_info;

spfile

有自动备份：

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> backup datafile 4;

联机恢复：

$ mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak

SQL> alter system set resource_limit=true; 报错

SQL> create spfile='/home/oracle/spfile.bak' from memory;

$ mv /home/oracle/spfile.bak $ORACLE_HOME/dbs/spfileorcl.ora

脱机恢复：

SQL> shutdown immediate

$ mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak

RMAN> startup

RMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_26/o1_mf_s_894118741_c2vkgo8x_.bkp';

RMAN> startup force

没有备份：

利用alert_orcl.log中的参数值，构造initorcl.ora

SQL> create spfile='/home/oracle/spfile.bak' from pfile;

利用备份init.ora：

$ vi /u01/app/oracle/product/11.2.0/db_1/dbs/init.ora 完善参数

SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/init.ora'

SQL> create spfile from pfile;

redo log

丢失一个成员：

SQL> select GROUP#, MEMBERS from v$log;

SQL> select GROUP#, MEMBER from v$logfile;

SQL> alter database add logfile member '/home/oracle/redo01b.log' to group 1;

SQL> alter database add logfile member '/home/oracle/redo02b.log' to group 2;

SQL> alter database add logfile member '/home/oracle/redo03b.log' to group 3;

SQL> alter system switch logfile;

SQL> alter system switch logfile;

SQL> alter system switch logfile;

故障：

SQL> select group#, status from v$log; 确认current组

$ rm -f /home/oracle/redo02b.log 删除current组成员

SQL> alter system switch logfile;

$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

恢复：

SQL> alter database drop logfile member '/home/oracle/redo02b.log';

SQL> alter database add logfile member '/home/oracle/redo02b.log' reuse to group 2; 重复用

如果是当前日志组，不能删除成员，只能先切换再修改

丢失inactive日志组：

故障：

SQL> alter system checkpoint;

SQL> select group#, status from v$log; 确认inactive组

SQL> shutdown abort

$ rm -f /home/oracle/redo03b.log /u01/app/oracle/oradata/orcl/redo03.log

$ startup 报错

恢复：

SQL> startup mount

SQL> select group#, status, archived from v$log;

SQL> alter database clear logfile group 3;

SQL> alter database open;

如果日志未归档：

SQL> alter database clear unarchived logfile group 3;

做数据库的全备份

丢失current日志组（正常关闭数据库）：

故障：

SQL> select group#, status from v$log; 确认current组

SQL> shutdown immediate

$ rm -f /home/oracle/redo02b.log /u01/app/oracle/oradata/orcl/redo02.log

SQL> startup 报错

恢复：断开重连

SQL> startup mount

SQL> select group#, status , archived from v$log;

SQL> alter database clear unarchived logfile group 2; 可以确认是否正常关闭数据库

SQL> alter database open;

做数据库的全备份（必须做全备份）Backup database