牛肉烧板黎

好好写SQL

0%

ORACLE重建控制文件

发表于 分类于

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

1
2
3
4
5
6
7
8
9
10
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
39
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.