控制文件及归档日志管理

1.控制文件的管理

本文主要讲多元控制文件,控制文件的恢复和查看控制文件信息
控制文件作用:记录数据文件位置和大小,记录重做文件的位置和大小,记录rman(recovery manage)备份记录等信息。

1.创建多元控文件,避免单点故障

1.使用spfile文件
SQL> show parameter spfile;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/u01/app/oracle/product
                                                 /10.2.0/db2/dbs/spfiledb2.ora
2.显示当前控制文件
SQL> select name from v$controlfile;
 
NAME
--------------------------------------------------------------------------------
/oracle/u01/app/oracle/product/10.2.0/db2/dbs/cntrldb2.dbf
3.修改参数文件并关闭oracle数据库
SQL> alter system set control_files='$ORACLE_HOME/dbs/control01.ctl','$ORACLE_HOME/dbs/control02.ctl' scope=spfile;
 
System altered.
 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
4.复制控制文件
SQL>! cp '$ORACLE_HOME/dbs/cntrldb2.dbf  '$ORACLE_HOME/dbs/control01.ctl'
SQL>! cp '$ORACLE_HOME/dbs/cntrldb2.dbf  '$ORACLE_HOME/dbs/control02.ctl'
注:一般应复制到别的位置
5.一步步的启动oracle
SQL> startup nomount;
ORACLE instance started.
 
Total System Global Area  192937984 bytes
Fixed Size                  1266536 bytes
Variable Size             134220952 bytes
Database Buffers           54525952 bytes
Redo Buffers                2924544 bytes
SQL> alter database mount;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
可见oracle启动成功
6.查看当前使用的控制文件
SQL> show parameter control_files;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oracle/u01/app/oracle/product
                                                 /10.2.0/db2/dbs/control01.ctl,
                                                  /oracle/u01/app/oracle/produc
                                                 t/10.2.0/db2/dbs/control02.ctl
这样,在oracle上就有了多个控制文件,防此单点故障
7.关于pfile
如果使用pfile文件启动oracle,要做多元控制文件,与spfile文件不同的只有第3步,因为spfile是二进制文件,它是用命令在线修改,而pfile是文本文件,要手动编写控制文件的位置。
vi $ORACLE_HOME/dbs/initdb2.ora
db_name=db2
undo_tablespace=undotbs
undo_management=AUTO
shared_pool_size=120M
*.sga_target=189715200
control_files='/oracle/u01/app/oracle/product/10.2.0/db2/dbs/control01.ctl','/oracle/u01/app/oracle
/product/10.2.0/db2/dbs/control02.ctl'
8,简单排错
在做多元控制文件时,在修改完参数文件后一定要先关oracle再复制文件,否定可能会出现如下错误。
SQL> startup
ORACLE instance started.
 
Total System Global Area  192937984 bytes
Fixed Size                  1266536 bytes
Variable Size             134220952 bytes
Database Buffers           54525952 bytes
Redo Buffers                2924544 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/home/oracle/oracle/oradata/db2/system01.dbf'
ORA-01207: file is more recent than control file - old control file

2.控制文件恢复

上面我们已经复制了多个控制文件,如果单个控制文件损坏,可以使用其它控制文件(只要在参数文件指定就可以了),但是有可以所有的参数文件都损坏,这样就没办法了。
为了出现这种情况,在oracle能正常运行时生成一个建控制文件的SQL脚本,这样当所以控制文件都损坏也能恢复了
建脚本步骤及模拟所有控制损坏后自动生产一个新的控制文件
1.建SQL脚本
SQL> alter database backup controlfile to trace;
 
Database altered.
2.提取需要的脚本
 
[oracle@oracle ~]$ cd $ORACLE_BASE/admin/db2/bdump
[oracle@oracle bdump]$ ls -t
#加参数-t 以时间为排序,把刚才生成的.trc排到第一个
db2_lgwr_31649.trc  db2_lgwr_30069.trc  db2_lgwr_28135.trc  db2_lgwr_27693.trc
alert_db2.log       db2_lgwr_28526.trc  db2_lgwr_28059.trc  db2_mmon_27484.trc
db2_lgwr_31540.trc  db2_lgwr_28473.trc  db2_lgwr_27869.trc  db2_lgwr_27475.trc
db2_lgwr_31457.trc  db2_lgwr_28267.trc  db2_lgwr_27807.trc  db2_lgwr_25892.trc
db2_lgwr_31271.trc  db2_dbw0_28228.trc  db2_lgwr_27765.trc  db2_lgwr_25642.trc
db2_lgwr_31095.trc  db2_lgwr_28176.trc  db2_mmon_27701.trc  db2_lgwr_8049.trc
[oracle@oracle bdump]$ cp db2_lgwr_31649.trc /$ORACLE_BASE/crtcntrol.sql
[oracle@oracle bdump]$ cd $ORACLE_BASE
[oracle@oracle oracle]$ ls
admin  crtcntrol.sql  flash_recovery_area  oradata  oraInventory  product
 
[oracle@oracle oracle]$ vi crtcntrol.sql
#打开后发现有两个方案,一般用第一个,把第二个删除,最后结果为
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DB2" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/oracle/oradata/db2/redo01.log'  SIZE 100M,
  GROUP 2 '/home/oracle/oracle/oradata/db2/redo02.log'  SIZE 100M,
  GROUP 3 '/home/oracle/oracle/oradata/db2/redo03.log'  SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  '/home/oracle/oracle/oradata/db2/system01.dbf',
  '/home/oracle/oracle/oradata/db2/undotbs01.dbf',
  '/home/oracle/oracle/oradata/db2/sysaux01.dbf',
  '/home/oracle/oracle/oradata/db2/usertbs.dbf'
CHARACTER SET AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/oracle/u01/app/oracle/product/10.2.0/db2/dbs/arch1_1_689217827.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
3.关闭oracle
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
4.删除本机的控制文件(或重命名,使程序找不到)
mv control01.ctl control01.ctl.bak
mv control02.ctl control02.ctl.bak
5.运行脚本
SQL> @$ORACLE_HOME/dbs/control.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
 
Control file created.
 
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
Database altered.
 
Tablespace altered.
6.验证
SQL> select status from V$instance;
 
STATUS
------------
OPEN
 
SQL> show parameter control_file;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /oracle/u01/app/oracle/product
                                                 /10.2.0/db2/dbs/cntrldb2.dbf
可见oracle已启动,并用了新的控制文件。

2.归档日志管理

归当日志:Archivedlogredologcopy
日志的操作模式有archivelognoarchivelog

1.查看归档模式

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle/u01/app/oracle/product/10.2.0/db2/dbs/arch
Oldest online log sequence     24
Current log sequence           26
#或以下命今
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
 
可见当前为非归档模式
2.更改归档模式
SQL> shutdown immediate;
 
SQL> startup mount;
ORACLE instance started.
Database mounted.
 
SQL> alter database archivelog;
Database altered.
查看现在的模式(已变为归档模式)
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
#或如下
SQL> alter database open;
Database altered.
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
 
3.查看及配置归档位置
查看当前log_archive_dest_1
SQL> show parameter log_archive_dest_1
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string
log_archive_dest_10                  string
 
更改log_archive_dest_1位置
SQL> alter system set log_archive_dest_1='location=/home/oracle/oracle/oradata/db2/arch';
 
System altered.
 
查看
SQL> show parameter log_archive_dest_1
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/home/oracle/oracle/o
                                                 radata/db2/arch
log_archive_dest_10                  string
可见VALUE值已变了。
4.手工归档
SQL> alter system archive log current;
 
System altered.
#
SQL> alter system switch logfile;
 
System altered.
 
(未完待续)