Oracle的控制文件管理
更新時(shí)間：2023年12月20日 15:18:00 作者：multis
Oracle 的控制文件記錄了當(dāng)前數(shù)據(jù)庫(kù)的結(jié)構(gòu)信息,包含數(shù)據(jù)文件及日志文件的信息以及相關(guān)的狀態(tài)、歸檔信息等,本文主要介紹了Oracle的控制文件管理,感興趣的可以了解一下
1、控制文件概述
1.1 概念
Oracle 的控制文件記錄了當(dāng)前數(shù)據(jù)庫(kù)的結(jié)構(gòu)信息，包含數(shù)據(jù)文件及日志文件的信息以及相關(guān)的狀態(tài)、歸檔信息等?？刂莆募且粋€(gè)二進(jìn)制文件，一個(gè)控制文件只屬于一個(gè)數(shù)據(jù)庫(kù)。當(dāng)數(shù)據(jù)庫(kù)的物理結(jié)構(gòu)發(fā)生改變時(shí)，Oracle會(huì)自動(dòng)更新控制文件。當(dāng)增加、重命名、刪除一個(gè)數(shù)據(jù)文件或者一個(gè)重做日志文件時(shí)，Oracle 服務(wù)器進(jìn)程會(huì)立即更新控制文件以反映數(shù)據(jù)庫(kù)結(jié)構(gòu)的變化。用戶不能手工編輯控制文件，控制文件的修改由 Oracle 自動(dòng)完成。
數(shù)據(jù)庫(kù)的啟動(dòng)和正常運(yùn)行都離不開(kāi)控制文件（數(shù)據(jù)庫(kù)在 mount 階段讀取控制文件，open 階段一直使用），一定要備份控制文件，控制文件損壞將導(dǎo)致整個(gè)數(shù)據(jù)庫(kù)損壞，數(shù)據(jù)庫(kù)正常工作至少需要一個(gè)控制文件，生產(chǎn)庫(kù)至少需要兩個(gè)控制文件（多個(gè)控制文件之間是鏡像關(guān)系），控制文件的位置和數(shù)量由初始化參數(shù)（control_files）決定。啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，Oracle 從初始化參數(shù)文件中獲取控制文件的名字及位置，并打開(kāi)控制文件，然后從控制文件中讀取數(shù)據(jù)文件和重做日志文件的信息，最后打開(kāi)數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)運(yùn)行時(shí)，會(huì)更改控制文件。
1.2 控制文件的內(nèi)容包括
（1）數(shù)據(jù)庫(kù)的名稱(chēng)、ID、創(chuàng)建的時(shí)間戳；
（2）表空間的名稱(chēng)；
（3）聯(lián)機(jī)日志文件、數(shù)據(jù)文件的位置、名稱(chēng)；
（4）聯(lián)機(jī)日志的 Sequence 號(hào)碼；
（5）檢查點(diǎn)的信息；
（6）撤銷(xiāo)段的開(kāi)始或結(jié)束；
（7）歸檔信息；
（8）備份信息。
2、查看控制文件的個(gè)數(shù)、名稱(chēng)和位置
2.1 使用 show parameter 命令
SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /app/oracle/oradata/ORCL/control01.ctl, /app/oracle/oradata/ORCL/control02.ctl
2.2 查看 v$controlfile 視圖
SQL> desc v$controlfile
Name Type Nullable Default Comments
--------------------- ------------- -------- ------- --------
STATUS VARCHAR2(7) Y
NAME VARCHAR2(513) Y
IS_RECOVERY_DEST_FILE VARCHAR2(3) Y
BLOCK_SIZE NUMBER Y
FILE_SIZE_BLKS NUMBER Y
CON_ID NUMBER Y
SQL> select * from v$controlfile;
STATUS NAME IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS CON_ID
------- -------------------------------------------------------------------------------- --------------------- ---------- -------------- ----------
/app/oracle/oradata/ORCL/control01.ctl NO 16384 646 0
/app/oracle/oradata/ORCL/control02.ctl NO 16384 646 0 3、查看控制文件的內(nèi)容
3.1 將控制文件轉(zhuǎn)出為文本文件
SQL> alter database backup controlfile to trace as '/home/oracle/ctl001.txt'; Database altered.
3.2 查看文件的內(nèi)容
[oracle@rac1 ~]$ cat ctl001.txt
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=orcl_%d_%S_%t_%r.dbf
--
-- DB_UNIQUE_NAME="orcl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/app/archive'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER'
-- LOG_ARCHIVE_DEST_1='NOALTERNATE'
-- LOG_ARCHIVE_DEST_1='NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/app/oracle/oradata/ORCL/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/app/oracle/oradata/ORCL/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/app/oracle/oradata/ORCL/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/app/oracle/oradata/ORCL/system01.dbf',
'/app/oracle/oradata/ORCL/test01.DBF',
'/app/oracle/oradata/ORCL/sysaux01.dbf',
'/app/oracle/oradata/ORCL/undotbs01.dbf',
'/app/oracle/oradata/ORCL/wms01.DBF',
'/app/oracle/oradata/ORCL/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- 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 '/app/archive/orcl_62725153_0000000001_1_1005785759.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/app/archive/orcl_62725153_0000000001_1_1123878365.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- 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.
ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/ORCL/temp01.dbf'
SIZE 33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/app/oracle/oradata/ORCL/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/app/oracle/oradata/ORCL/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/app/oracle/oradata/ORCL/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/app/oracle/oradata/ORCL/system01.dbf',
'/app/oracle/oradata/ORCL/test01.DBF',
'/app/oracle/oradata/ORCL/sysaux01.dbf',
'/app/oracle/oradata/ORCL/undotbs01.dbf',
'/app/oracle/oradata/ORCL/wms01.DBF',
'/app/oracle/oradata/ORCL/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- 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 '/app/archive/orcl_62725153_0000000001_1_1005785759.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/app/archive/orcl_62725153_0000000001_1_1123878365.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/ORCL/temp01.dbf'
SIZE 33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--注：第一種日志可用 第二種日志不可用
3.3 使用 strings 命令直接查看控制文件
SQL> select * from v$controlfile;
STATUS NAME IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS CON_ID
------- -------------------------------------------------------------------------------- --------------------- ---------- -------------- ----------
/app/oracle/oradata/ORCL/control01.ctl NO 16384 646 0
/app/oracle/oradata/ORCL/control02.ctl NO 16384 646 0
SQL> host cp /app/oracle/oradata/ORCL/control01.ctl /home/oracle/control01.ctl.bak
SQL> host ls -l
總用量 10360
-rw-r----- 1 oracle oinstall 10600448 1月 12 13:40 control01.ctl.bak
-rw-r--r-- 1 oracle oinstall 5849 1月 11 16:09 ctl001.txt
drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 公共
drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 模板
drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 視頻
drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 圖片
drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 文檔
drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 下載
drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 音樂(lè)
drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 桌面
[oracle@node1 ~]$ strings control01.ctl.bak3.4 查看控制文件包含的記錄片段
SQL> select type,record_size,records_total,records_used from v$controlfile_record_section;
4、控制文件的多路復(fù)用
數(shù)據(jù)庫(kù)的啟動(dòng)和正常運(yùn)行都離不開(kāi)控制文件，控制文件損壞將導(dǎo)致整個(gè)數(shù)據(jù)庫(kù)損壞，數(shù)據(jù)庫(kù)正常工作至少需要一個(gè)控制文件，由于控制文件極其重要，生產(chǎn)庫(kù)最少創(chuàng)建控制文件的兩個(gè)以上副本，可以通過(guò)多路復(fù)用技術(shù)，將控制文件的副本創(chuàng)建到不同的磁盤(pán)上。這樣，如果一個(gè)控制文件損壞了，可以自動(dòng)使用另一個(gè)控制文件。
但控制文件并不是越多越好，因?yàn)楫?dāng) Oracle 更新控制文件時(shí)，會(huì)將所有的控制文件全部進(jìn)行更新，對(duì)數(shù)據(jù)庫(kù)的性能會(huì)有一定的影響，讀取時(shí)則僅讀取第一個(gè)控制文件。
控制文件的位置和數(shù)量由初始化參數(shù)（control_files）決定。啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，Oracle 從初始化參數(shù)文件中獲取控制文件的名字及位置，并打開(kāi)控制文件，然后從控制文件中讀取數(shù)據(jù)文件和重做日志文件的信息，最后打開(kāi)數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)運(yùn)行時(shí)，會(huì)更改控制文件。
增加控制文件的步驟如下：
4.1 查看當(dāng)前使用的控制文件
SQL> show parameter control_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /app/oracle/oradata/ORCL/control01.ctl, /app/oracle/oradata/ORCL/control02.ctl
4.2 修改參數(shù)文件
[oracle@node1 controlfile]$ mkdir /app/oracle/controlfile SQL> alter system set control_files = '/app/oracle/oradata/ORCL/control01.ctl','/app/oracle/oradata/ORCL/control02.ctl','/app/oracle/controlfile/contrl03.ctl' scope = spfile sid = '*'; System altered
4.3 重啟數(shù)據(jù)庫(kù)到 nomount 狀態(tài)，復(fù)制所需的控制文件
SQL> startup force nomount; ORACLE instance started. Total System Global Area 8002730448 bytes Fixed Size 8915408 bytes Variable Size 1308622848 bytes Database Buffers 6677331968 bytes Redo Buffers 7860224 bytes SQL> show parameter control_file; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /app/oracle/oradata/ORCL/control01.ctl, /app/oracle/oradata/ORCL/control02.ctl, /app/oracle/controlfile/contrl03.ctl SQL> host cp /app/oracle/oradata/ORCL/control02.ctl /app/oracle/controlfile/contrl03.ctl SQL> alter database mount; Database altered. SQL> alter database open; Database altered.
5、控制文件丟失的解決辦法
5.1 控制文件部分丟失
一個(gè)或多個(gè)控制文件丟失，至少有一個(gè)控制文件存在。比如，原來(lái)有 3 個(gè)控制文件，丟失一個(gè)導(dǎo)致數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)。解決方法如下：
（1）將已經(jīng)存在的控制文件復(fù)制到目的路徑并更改為正確的控制文件名稱(chēng)；
（2）修改 control_files 參數(shù)將丟失的控制文件去掉。
5.2 模擬控制文件丟失
5.2.1 查看控制文件信息
SQL> show parameter control_file; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /app/oracle/oradata/ORCL/control01.ctl, /app/oracle/oradata/ORCL/control02.ctl, /app/oracle/controlfile/contrl03.ctl
5.2.2 刪除控制文件
SQL> host rm -rf /app/oracle/controlfile/contrl03.ctl
5.2.3 重啟數(shù)據(jù)庫(kù)
SQL> startup force; ORACLE instance started. Total System Global Area 8002730448 bytes Fixed Size 8915408 bytes Variable Size 1308622848 bytes Database Buffers 6677331968 bytes Redo Buffers 7860224 bytes ORA-00205: error in identifying control file, check alert log for more info
5.2.4 查看 alert 文件信息
SQL> SELECT * FROM v$diag_info;
INST_ID NAME VALUE CON_ID
---------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
1 Diag Enabled TRUE 0
1 ADR Base /app/oracle 0
1 ADR Home /app/oracle/diag/rdbms/orcl/orcl 0
1 Diag Trace /app/oracle/diag/rdbms/orcl/orcl/trace 0
1 Diag Alert /app/oracle/diag/rdbms/orcl/orcl/alert 0
1 Diag Incident /app/oracle/diag/rdbms/orcl/orcl/incident 0
1 Diag Cdump /app/oracle/diag/rdbms/orcl/orcl/cdump 0
1 Health Monitor /app/oracle/diag/rdbms/orcl/orcl/hm 0
1 Default Trace File /app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2861.trc 0
1 Active Problem Count 4 0
1 Active Incident Count 75 0
1 ORACLE_HOME /app/oracle/product/19.3.0/db 0
12 rows selected
[oracle@node1 trace]$ cd /app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@node1 trace]$ tail 300 alert_orcl.log
tail: 無(wú)法打開(kāi)"300" 讀取數(shù)據(jù): 沒(méi)有那個(gè)文件或目錄
==> alert_orcl.log <==
ORA-205 signalled during: ALTER DATABASE MOUNT...
2023-01-12T14:21:46.156054+08:00
Errors in file /app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_2581.trc:
ORA-00202: ????: ''/app/oracle/controlfile/contrl03.ctl''
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 1 new persistent data failures
2023-01-12T14:21:48.271581+08:00
Using default pga_aggregate_limit of 5088 MB5.3 控制文件丟失處理
5.3.1 將已經(jīng)存在的控制文件復(fù)制到目的路徑并更改為正確的控制文件名稱(chēng)
SQL> host cp /app/oracle/oradata/ORCL/control02.ctl /app/oracle/controlfile/contrl03.ctl SQL> alter database mount; Database altered. SQL> alter database open; Database altered.
5.3.2 修改 control_files參數(shù)，將丟失的控制文件去掉
SQL> show parameter control_file; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /app/oracle/oradata/ORCL/control01.ctl, /app/oracle/oradata/ORCL/control02.ctl, /app/oracle/controlfile/contrl03.ctl SQL> alter system set control_files = '/app/oracle/oradata/ORCL/control01.ctl','/app/oracle/oradata/ORCL/control02.ctl' scope = spfile sid = '*'; System altered. SQL> startup force nomount ORACLE instance started. Total System Global Area 8002730448 bytes Fixed Size 8915408 bytes Variable Size 1308622848 bytes Database Buffers 6677331968 bytes Redo Buffers 7860224 bytes SQL> alter database mount; Database altered. SQL> alter database open; Database altered.
6、控制文件版本不一致
6.1 模擬控制文件版本不一致問(wèn)題：
6.1.1 查看數(shù)據(jù)庫(kù)控制文件信息
SQL> show parameter control_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /app/oracle/oradata/ORCL/control01.ctl, /app/oracle/oradata/ORCL/control02.ctl
6.1.2 修改參數(shù)文件，只保留第一個(gè)控制文件
SQL> alter system set control_files = '/app/oracle/oradata/ORCL/control01.ctl','/app/oracle/oradata/ORCL/control02.ctl' scope = spfile sid = '*'; System altered. SQL> alter system set control_files = '/app/oracle/oradata/ORCL/control01.ctl' scope=spfile sid = '*'; System altered. SQL> startup force ORACLE instance started. Total System Global Area 8002730448 bytes Fixed Size 8915408 bytes Variable Size 1308622848 bytes Database Buffers 6677331968 bytes Redo Buffers 7860224 bytes Database mounted. Database opened.
6.1.3 創(chuàng)建一張表
SQL> create table hr.STUDENT_INFO_01 as select * from hr.STUDENT_INFO; Table created.
6.2 修改參數(shù)文件，添加兩個(gè)控制文件
SQL> alter system set control_files = '/app/oracle/oradata/ORCL/control01.ctl','/app/oracle/oradata/ORCL/control02.ctl' scope=spfile sid = '*'; System altered.
6.3 重啟數(shù)據(jù)庫(kù)，提示版本錯(cuò)誤
SQL> startup force ORACLE instance started. Total System Global Area 8002730448 bytes Fixed Size 8915408 bytes Variable Size 1308622848 bytes Database Buffers 6677331968 bytes Redo Buffers 7860224 bytes ORA-00214: control file '/app/oracle/oradata/ORCL/control01.ctl' version 36188 inconsistent with file '/app/oracle/oradata/ORCL/control02.ctl' version 36173
6.4 解決方法：利用最新版本的控制文件替換舊的控制文件
SQL> host cp /app/oracle/oradata/ORCL/control01.ctl /app/oracle/oradata/ORCL/control02.ctl SQL> startup force ORACLE instance started. Total System Global Area 8002730448 bytes Fixed Size 8915408 bytes Variable Size 1308622848 bytes Database Buffers 6677331968 bytes Redo Buffers 7860224 bytes Database mounted. Database opened.
7、控制文件的備份
7.1 使用 alter database backup controlfile 備份
7.1.1 語(yǔ)法
alter database backup controlfile to '<dir>';
7.1.2 備份控制文件
SQL> alter database backup controlfile to '/home/oracle/controlfile.bak'; Database altered.
7.1.3 查看控制文件的備份
SQL> host ls -l /home/oracle 總用量 20712 -rw-r----- 1 oracle oinstall 10600448 1月 12 13:40 control01.ctl.bak -rw-r----- 1 oracle oinstall 10600448 1月 12 15:13 controlfile.bak -rw-r--r-- 1 oracle oinstall 5849 1月 11 16:09 ctl001.txt drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 公共 drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 模板 drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 視頻 drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 圖片 drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 文檔 drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 下載 drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 音樂(lè) drwxr-xr-x 2 oracle oinstall 6 12月 19 20:19 桌面 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
7.1.4 恢復(fù)控制文件
使用控制文件覆蓋數(shù)據(jù)庫(kù)，用redo日志去恢復(fù)
一個(gè)一個(gè)嘗試 redo01.log,redo02.log 或redo03.log.....
[oracle@node1 ~]$ rm -rf /app/oracle/oradata/ORCL/control01.ctl
[oracle@node1 ~]$ rm -rf /app/oracle/oradata/ORCL/control02.ctl
[oracle@node1 ~]$ cp /home/oracle/controlfile.bak /app/oracle/oradata/ORCL/control01.ctl
[oracle@node1 ~]$ cp /home/oracle/controlfile.bak /app/oracle/oradata/ORCL/control02.ctl
[oracle@node1 ~]$ sas
SQL> startup
ORACLE instance started.
Total System Global Area 8002730448 bytes
Fixed Size 8915408 bytes
Variable Size 1308622848 bytes
Database Buffers 6677331968 bytes
Redo Buffers 7860224 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> recover database using backup controlfile;
ORA-00279: change 7636324 generated at 01/12/2023 15:37:27 needed for thread 1
ORA-00289: suggestion : /app/archive/orcl_62725153_0000000001_1_1125934647.dbf
ORA-00280: change 7636324 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/oradata/ORCL/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.7.2 使用 rman 備份（推薦）
7.2.1 查看數(shù)據(jù)庫(kù)的歸檔狀態(tài)
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /app/archive Oldest online log sequence 49 Next log sequence to archive 51 Current log sequence 51
7.2.2 備份控制文件
[oracle@node1 ~]$ rman target / RMAN> backup current controlfile format '/app/rmanbak/control_2023_01_12.bak';
7.2.3 查看控制文件的備份信息
RMAN> list backup of controlfile;
7.2.4 使用 rman 恢復(fù)控制文件
由于誤操作、磁盤(pán)故障等導(dǎo)致控制文件全部丟失時(shí)，可以使用備份的控制文件進(jìn)行恢復(fù)操作。下面模擬控制文件全部丟失時(shí)，恢復(fù)數(shù)據(jù)庫(kù)的操作
SQL> show parameter control_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /app/oracle/oradata/ORCL/control01.ctl, /app/oracle/oradata/ORCL/control02.ctl SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. [oracle@node1 ~]$ rm -rf /app/oracle/oradata/ORCL/control01.ctl [oracle@node1 ~]$ rm -rf /app/oracle/oradata/ORCL/control02.ctl
7.2.5 啟動(dòng)數(shù)據(jù)庫(kù)，出現(xiàn)錯(cuò)誤
SQL> startup ORACLE instance started. Total System Global Area 8002730448 bytes Fixed Size 8915408 bytes Variable Size 1308622848 bytes Database Buffers 6677331968 bytes Redo Buffers 7860224 bytes ORA-00205: error in identifying control file, check alert log for more info
7.2.6 數(shù)據(jù)庫(kù)啟動(dòng)到 nomount 狀態(tài)
SQL> select status from v$instance; STATUS STARTED
7.2.7 使用 RMAN 還原控制文件
RMAN> restore controlfile from '/app/rmanbak/control_2023_01_12.bak'; Starting restore at 12-JAN-23 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1709 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/app/oracle/oradata/ORCL/control01.ctl output file name=/app/oracle/oradata/ORCL/control02.ctl Finished restore at 12-JAN-23
7.2.8 啟動(dòng)數(shù)據(jù)庫(kù)到 mount 狀態(tài)
RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed
7.2.9 恢復(fù)數(shù)據(jù)庫(kù)
RMAN> recover database; Starting recover at 12-JAN-23 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1709 device type=DISK starting media recovery archived log for thread 1 with sequence 51 is already on disk as file /app/oracle/oradata/ORCL/redo03.log archived log file name=/app/oracle/oradata/ORCL/redo03.log thread=1 sequence=51 media recovery complete, elapsed time: 00:00:01 Finished recover at 12-JAN-23
7.2.10 打開(kāi)數(shù)據(jù)庫(kù)
以 resetlogs 模式才能打開(kāi)數(shù)據(jù)庫(kù)。
RMAN> alter database open resetlogs; Statement processed
7.3 生成創(chuàng)建控制文件的腳本
7.3.1 語(yǔ)法
alter database backup controlfile to trace as '<dir>'
7.3.2 生成創(chuàng)建控制文件的腳本
SQL> alter database backup controlfile to trace as '/home/oracle/ctl002.txt' ; Database altered.
7.3.3 查看文件 /home/oracle/ctl002.txt 的內(nèi)容：
創(chuàng)建控制文件主要包含三部分內(nèi)容：
（1）設(shè)置日志文件的大小及位置；
（2）設(shè)置數(shù)據(jù)文件的位置；
（3）設(shè)置正確的字符集。
[oracle@node1 ~]$ cat ctl002.txt
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=orcl_%d_%S_%t_%r.dbf
--
-- DB_UNIQUE_NAME="orcl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/app/archive'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER'
-- LOG_ARCHIVE_DEST_1='NOALTERNATE'
-- LOG_ARCHIVE_DEST_1='NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/app/oracle/oradata/ORCL/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/app/oracle/oradata/ORCL/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/app/oracle/oradata/ORCL/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/app/oracle/oradata/ORCL/system01.dbf',
'/app/oracle/oradata/ORCL/test01.DBF',
'/app/oracle/oradata/ORCL/sysaux01.dbf',
'/app/oracle/oradata/ORCL/undotbs01.dbf',
'/app/oracle/oradata/ORCL/wms01.DBF',
'/app/oracle/oradata/ORCL/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- 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 '/app/archive/orcl_62725153_0000000001_1_1005785759.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/app/archive/orcl_62725153_0000000001_1_1123878365.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- 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.
ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/ORCL/temp01.dbf'
SIZE 33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/app/oracle/oradata/ORCL/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/app/oracle/oradata/ORCL/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/app/oracle/oradata/ORCL/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/app/oracle/oradata/ORCL/system01.dbf',
'/app/oracle/oradata/ORCL/test01.DBF',
'/app/oracle/oradata/ORCL/sysaux01.dbf',
'/app/oracle/oradata/ORCL/undotbs01.dbf',
'/app/oracle/oradata/ORCL/wms01.DBF',
'/app/oracle/oradata/ORCL/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- 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 '/app/archive/orcl_62725153_0000000001_1_1005785759.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/app/archive/orcl_62725153_0000000001_1_1123878365.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/ORCL/temp01.dbf'
SIZE 33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--注：大致意思是分為兩種創(chuàng)建控制文件方式，第一種日志可用， 第二種日志不可用的狀態(tài)
7.3.4 日志可用的情況下重建控制文件
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@node1 ~]$ rm -rf /app/oracle/oradata/ORCL/control01.ctl
[oracle@node1 ~]$ rm -rf /app/oracle/oradata/ORCL/control02.ctl
SQL> startup nomount
ORACLE instance started.
Total System Global Area 8002730448 bytes
Fixed Size 8915408 bytes
Variable Size 1308622848 bytes
Database Buffers 6677331968 bytes
Redo Buffers 7860224 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/app/oracle/oradata/ORCL/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/app/oracle/oradata/ORCL/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/app/oracle/oradata/ORCL/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/app/oracle/oradata/ORCL/system01.dbf',
'/app/oracle/oradata/ORCL/test01.DBF',
'/app/oracle/oradata/ORCL/sysaux01.dbf',
'/app/oracle/oradata/ORCL/undotbs01.dbf',
'/app/oracle/oradata/ORCL/wms01.DBF',
'/app/oracle/oradata/ORCL/users01.dbf'
CHARACTER SET ZHS16GBK
20 ;
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/ORCL/temp01.dbf'
SIZE 33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.7.3.5 日志不可用的情況下重建控制文件
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@node1 ~]$ rm -rf /app/oracle/oradata/ORCL/control01.ctl
[oracle@node1 ~]$ rm -rf /app/oracle/oradata/ORCL/control02.ctl
SQL> startup nomount
ORACLE instance started.
Total System Global Area 8002730448 bytes
Fixed Size 8915408 bytes
Variable Size 1308622848 bytes
Database Buffers 6677331968 bytes
Redo Buffers 7860224 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/app/oracle/oradata/ORCL/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/app/oracle/oradata/ORCL/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/app/oracle/oradata/ORCL/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/app/oracle/oradata/ORCL/system01.dbf',
'/app/oracle/oradata/ORCL/test01.DBF',
'/app/oracle/oradata/ORCL/sysaux01.dbf',
'/app/oracle/oradata/ORCL/undotbs01.dbf',
'/app/oracle/oradata/ORCL/wms01.DBF',
'/app/oracle/oradata/ORCL/users01.dbf'
CHARACTER SET ZHS16GBK
20 ;
Control file created.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE
ORA-00279: change 7647399 generated at 01/12/2023 19:00:06 needed for thread 1
ORA-00289: suggestion : /app/archive/orcl_62725153_0000000002_1_1125935795.dbf
ORA-00280: change 7647399 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/oradata/ORCL/redo01.log
ORA-00310: ???????????????? 1; ???????? 2 ORA-00334: ????????:
'/app/oracle/oradata/ORCL/redo01.log'
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE
ORA-00279: change 7647399 generated at 01/12/2023 19:00:06 needed for thread 1
ORA-00289: suggestion : /app/archive/orcl_62725153_0000000002_1_1125935795.dbf
ORA-00280: change 7647399 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/app/oracle/oradata/ORCL/redo02.log
Log applied.
Media recovery complete.}
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/ORCL/temp01.dbf'
SIZE 33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.到此這篇關(guān)于Oracle的控制文件管理的文章就介紹到這了,更多相關(guān)Oracle 控制文件內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
詳解PL/SQL Developer連接本地Oracle 11g 64位數(shù)據(jù)庫(kù)
本篇文章主要介紹了PL/SQL Developer連接本地Oracle 11g 64位數(shù)據(jù)庫(kù) ，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-04-04
Oracle給用戶授權(quán)truncatetable的實(shí)現(xiàn)方案
這篇文章主要介紹了Oracle給用戶授權(quán)truncatetable的實(shí)現(xiàn)方案,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下2017-05-05
Oracle如何在SQL語(yǔ)句中對(duì)時(shí)間操作、運(yùn)算
時(shí)間操作運(yùn)算對(duì)大家來(lái)說(shuō)應(yīng)該都不陌生,這篇文章主要給大家介紹了關(guān)于Oracle如何在SQL語(yǔ)句中對(duì)時(shí)間操作、運(yùn)算的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-09-09
Oracle中返回結(jié)果集的存儲(chǔ)過(guò)程分享
Oracle不像SQLServer那樣在存儲(chǔ)過(guò)程中用Select就可以返回結(jié)果集，而是通過(guò)Out型的參數(shù)進(jìn)行結(jié)果集返回的2012-07-07
Oracle中RegExp_Like正則表達(dá)式基本用法舉例
這篇文章主要給大家介紹了關(guān)于Oracle中RegExp_Like正則表達(dá)式基本用法的相關(guān)資料,REGEXP_LIKE(x,pattern)函數(shù)的功能類(lèi)似于like運(yùn)算符,用于判斷源字符串是否匹配或包含指定模式的子串,需要的朋友可以參考下2024-02-02
oracle 將以逗號(hào)隔開(kāi)的字符串'85,86,87' 轉(zhuǎn)成行2009-06-06
這篇文章主要為大家詳細(xì)介紹了Oracle 10g安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
oracle11g客戶端連接12c服務(wù)器ORA-01017錯(cuò)誤問(wèn)題解決
這篇文章主要介紹了oracle11g客戶端連接12c服務(wù)器ORA-01017錯(cuò)誤，本文給大家分享完美解決方法，對(duì)oracle 12c錯(cuò)誤ORA-01017問(wèn)題解決方法感興趣的朋友跟隨小編一起看看吧2023-06-06
oracle數(shù)據(jù)庫(kù)臨時(shí)表代碼舉例總結(jié)
臨時(shí)表是一種特殊的表,當(dāng)需要對(duì)某一(也可以是多個(gè))表中的一批數(shù)據(jù)進(jìn)行反復(fù)的操作時(shí),通過(guò)為這批數(shù)據(jù)創(chuàng)建一個(gè)臨時(shí)表,可能會(huì)簡(jiǎn)化操作并且有可能提高效率,這篇文章主要給大家介紹了關(guān)于oracle數(shù)據(jù)庫(kù)臨時(shí)表的相關(guān)資料,需要的朋友可以參考下2024-02-02
oracle drop table(表)數(shù)據(jù)恢復(fù)方法
drop table刪除表以后怎樣可以恢復(fù)，本文整理了一些操作語(yǔ)句,感興趣的朋友可以研究下，或許可以幫助到你2013-04-04