ORACLE重建控制文件

        因为一次断电导致控制文件丢失,启动数据库报ORA-00205错误:

1
2
3
4
5
6
7
8
9
SQL> startup
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2925120 bytes
Variable Size 285216192 bytes
Database Buffers 125829120 bytes
Redo Buffers 5459968 bytes
ORA-00205: error in identifying control file, check alert log for more info

        通过v$instance查看数据库status是started但是无法对数据库进行操作,show pdbs命令不显示任何信息。由于两份控制文件均丢失,无奈只能手动重建控制文件。

获取参数文件信息

        通过spfile创建pfile查看初始化参数中控制文件位置以及数据库名。

1
2
3
SQL> create pfile from spfile;
File created.

        Linux环境生成的pfile文件在$ORACLE_HOME/dbs目录下,查看初始化参数信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[oracle@localhost dbs]$ cat initorcl.ora
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=125829120
orcl.__java_pool_size=4194304
orcl.__large_pool_size=8388608
orcl.__oracle_base='/u01/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=41943040
orcl.__sga_target=377487360
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=134217728
orcl.__streams_pool_size=0
*._catalog_foreign_restore=FALSE
*.aq_tm_processes=0
*.audit_file_dest='/u01/app/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oradata/orcl/control01.ctl','/u01/app/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.job_queue_processes=0
*.memory_target=397m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

获取数据库字符集

        数据库只需启动到nomount状态即可执行以下SQL获取字符集。

1
2
3
4
5
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.US7ASCII

获取数据文件

        所有的数据文件都在/u01/app/oradata/目录下,使用find $PWD | xargs ls -ld可以很方便的获取数据文件的全路径名。重建控制文件我们只需要redo日志文件和数据文件即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[oracle@localhost orcl]$ find $PWD | xargs ls -ld
drwxr-x---. 4 oracle oinstall 232 9月 8 16:06 /u01/app/oradata/orcl
-rw-r-----. 1 oracle oinstall 104865792 9月 8 16:01 /u01/app/oradata/orcl/interface_1.dbf
-rw-r-----. 1 oracle oinstall 104865792 9月 8 16:00 /u01/app/oradata/orcl/interface.dbf
drwxr-x---. 2 oracle oinstall 148 4月 18 17:49 /u01/app/oradata/orcl/pdborcl
-rw-r-----. 1 oracle oinstall 1304174592 9月 8 16:01 /u01/app/oradata/orcl/pdborcl/example01.dbf
-rw-r-----. 1 oracle oinstall 20979712 4月 26 13:00 /u01/app/oradata/orcl/pdborcl/pdborcl_temp012017-04-18_05-49-05-PM.dbf
-rw-r-----. 1 oracle oinstall 5251072 9月 8 16:01 /u01/app/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf
-rw-r-----. 1 oracle oinstall 555753472 9月 8 16:01 /u01/app/oradata/orcl/pdborcl/sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 9月 8 16:01 /u01/app/oradata/orcl/pdborcl/system01.dbf
drwxr-x---. 2 oracle oinstall 94 4月 18 17:18 /u01/app/oradata/orcl/pdbseed
-rw-r-----. 1 oracle oinstall 20979712 4月 26 16:57 /u01/app/oradata/orcl/pdbseed/pdbseed_temp012017-04-18_05-18-30-PM.dbf
-rw-r-----. 1 oracle oinstall 534781952 4月 26 17:24 /u01/app/oradata/orcl/pdbseed/sysaux01.dbf
-rw-r-----. 1 oracle oinstall 262152192 4月 26 17:24 /u01/app/oradata/orcl/pdbseed/system01.dbf
-rw-r-----. 1 oracle oinstall 52429312 9月 8 16:00 /u01/app/oradata/orcl/redo01.log
-rw-r-----. 1 oracle oinstall 52429312 9月 8 16:11 /u01/app/oradata/orcl/redo02.log
-rw-r-----. 1 oracle oinstall 52429312 9月 8 16:00 /u01/app/oradata/orcl/redo03.log
-rw-r-----. 1 oracle oinstall 765468672 9月 8 16:07 /u01/app/oradata/orcl/sysaux01.dbf
-rw-r-----. 1 oracle oinstall 838868992 9月 8 16:08 /u01/app/oradata/orcl/system01.dbf
-rw-r-----. 1 oracle oinstall 62922752 9月 8 16:03 /u01/app/oradata/orcl/temp01.dbf
-rw-r-----. 1 oracle oinstall 162537472 9月 8 16:08 /u01/app/oradata/orcl/undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 9月 8 16:00 /u01/app/oradata/orcl/users01.dbf

        重建控制文件的必要信息都已获取,下面开始执行命令重建控制文件

执行CREATE CONTROLFILE重建控制文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE CONTROLFILE REUSE DATABASE 'ORCL' NORESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/u01/app/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oradata/orcl/redo03.log' SIZE 50M
DATAFILE
'/u01/app/oradata/orcl/interface_1.dbf',
'/u01/app/oradata/orcl/interface.dbf',
'/u01/app/oradata/orcl/pdborcl/example01.dbf',
'/u01/app/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf',
'/u01/app/oradata/orcl/pdborcl/sysaux01.dbf',
'/u01/app/oradata/orcl/pdborcl/system01.dbf',
'/u01/app/oradata/orcl/pdbseed/sysaux01.dbf',
'/u01/app/oradata/orcl/pdbseed/system01.dbf',
'/u01/app/oradata/orcl/sysaux01.dbf',
'/u01/app/oradata/orcl/system01.dbf',
'/u01/app/oradata/orcl/undotbs01.dbf',
'/u01/app/oradata/orcl/users01.dbf'
CHARACTER SET US7ASCII;

        重建控制文件后并不能直接打开数据库,需要对system01.dbf这个数据文件进行介质恢复,直接执行recover database即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
SQL> alter pluggable database pdborcl open;
Pluggable database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/orcl/system01.dbf
/u01/app/oradata/orcl/sysaux01.dbf
/u01/app/oradata/orcl/undotbs01.dbf
/u01/app/oradata/orcl/pdbseed/system01.dbf
/u01/app/oradata/orcl/users01.dbf
/u01/app/oradata/orcl/pdbseed/sysaux01.dbf
/u01/app/oradata/orcl/pdborcl/system01.dbf
/u01/app/oradata/orcl/pdborcl/sysaux01.dbf
/u01/app/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf
/u01/app/oradata/orcl/pdborcl/example01.dbf
/u01/app/oradata/orcl/interface.dbf
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/orcl/interface_1.dbf
12 rows selected.