oracle 之日志文件控制文件学习笔记

打开归档

shutdown immediate;

startup mount

alter database archivelog;

archive log list;

修改归档路径

mkdir /u01/app/oracle/arch

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch';

控制文件：

查看控制文件路径

show parameter control_files

select name from v$controlfile;

控制文件里存储：

数据文件信息

日志文件信息

备份信息

备份二进制控制文件：

alter database backup controlfile to 'XXXXXXX';

备份文本模式的控制文件:

alter database backup controlfile to trace as 'XXXXX';

分为两部分

可变部分：

不可变部分：

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

---通过查询,也可以看控制文件信息

SELECT type, record_size, records_total, records_used FROM v$controlfile_record_section;

实验1，数据库有多个控制文件的时候，丢失其中一个控制文件

方法一：

SQL> show parameter control

NAME TYPE VALUE

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

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/orcl/c

ontrol01.ctl, /u01/app/oracle/

oradata/orcl/control02.ctl

rm /u01/app/oracle/oradata/orcl/control02.ctl

startup force;

告警日志：

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control02.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE MOUNT...

Sun May 13 09:51:04 2018

Checker run found 1 new persistent data failures

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

alter open;

方法二(创建spfile修改)

SQL> show parameter control

NAME TYPE VALUE

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

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/orcl/c

ontrol01.ctl, /u01/app/oracle/

oradata/orcl/control02.ctl

rm /u01/app/oracle/oradata/orcl/control02.ctl

shutdown abort;

startup nomount;

alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl' scope=spfile;

shutdown immediate;

startup;

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

方法二(创建pfile修改)

SQL> show parameter control

NAME TYPE VALUE

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

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/orcl/c

ontrol01.ctl, /u01/app/oracle/

oradata/orcl/control02.ctl

rm /u01/app/oracle/oradata/orcl/control02.ctl

shutdown abort;

create pfile from spfile;

vi /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora

把*.control_files='
/u01/app/oracle/oradata/orcl/control01.ctl'这行编辑为这样

create spfile from pfile;

startup;

设置数据库有三个控制文件。

注意先关闭数据库

shutdown immediate;

cp的时候注意是oracle用户

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

cp /u01/app/oracle/oradata/orcl/control02.ctl /u01/app/oracle/oradata/orcl/control03.ctl

startup nomount;

alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl' scope=spfile;

shutdown immediate;

startup;

备份控制文件

alter database backup controlfile to trace as '/tmp/ctl';

除去脚本注释

cat /tmp/ctl | grep -v ^- | grep -v ^$ > /tmp/ctl.sql

查出控制文件路径

SQL> select 'rm '||name from v$controlfile;

'RM'||NAME

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

rm /u01/app/oracle/oradata/orcl/control01.ctl

rm /u01/app/oracle/oradata/orcl/control02.ctl

删除所有控制文件

rm /u01/app/oracle/oradata/orcl/control01.ctl

rm /u01/app/oracle/oradata/orcl/control02.ctl

删除了所有控制文件之后，不能正常关闭数据库，所以用了shutdown abort

shutdown abort

删除了所有控制文件后，尝试开启数据库到挂载状态（mount），发现有报错ORA-00205: error in identifying control file, check alert log for more info

startup mount

ORACLE instance started.

Total System Global Area 740724736 bytes

Fixed Size 1347512 bytes

Variable Size 436207688 bytes

Database Buffers 297795584 bytes

Redo Buffers 5373952 bytes

ORA-00205: error in identifying control file, check alert log for more info

shutdown abort

编辑/tmp/ctl.sql

vi ctl.sql (删除resetlog那一部分)

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf',

'/u01/app/oracle/oradata/orcl/sysaux01.dbf',

'/u01/app/oracle/oradata/orcl/undotbs01.dbf',

'/u01/app/oracle/oradata/orcl/users01.dbf',

'/u01/app/oracle/oradata/orcl/example01.dbf'

CHARACTER SET AL32UTF8

;

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'

SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

---（手工分界线。。注意NORESETLOGS和RESETLOGS）
-----------------------------------------------------------------------

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf',

'/u01/app/oracle/oradata/orcl/sysaux01.dbf',

'/u01/app/oracle/oradata/orcl/undotbs01.dbf',

'/u01/app/oracle/oradata/orcl/users01.dbf',

'/u01/app/oracle/oradata/orcl/example01.dbf'

CHARACTER SET AL32UTF8

;

RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'

SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

删除完后保存。

现在数据库的状态是关闭的，也就是说实例也没有起来。 Connected to an idle instance.连接了一个空实例

[oracle@ocp orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Oct 29 10:30:35 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> @/tmp/ctl.sql