#眉標=Oracle 10g
#副標= Oracle 10g系列專欄 - 控制檔的配置與管理
#大標=控制檔的配置與管理
#作者=文／何致億
#引言=在Oracle資料庫中，控制檔（Control file）內存放資料庫檔案的組態資訊。本期將探討如何管理與配置Oracle資料庫的控制檔。
#內文起=



-----box-----
#程式1
control_files=("d:\oradata\oradb1\control01.ctl",
               "e:\oradata\oradb1\control02.ctl", 
               "f:\oradata\oradb1\control03.ctl")
-----end-----


-----box-----
#程式2
control_files=("/u01/oradata/oradb1/control01.ctl", 
               "/u02/oradata/oradb1/control02.ctl", 
               "/u03/oradata/oradb1/control03.ctl")
-----end-----


-----box-----
SELECT name FROM v$controlfile;
-----end-----


-----box-----
#程式3
ALTER SYSTEM
SET control_files=
  '/disk2/oradata/oradb1/control01.ctl',
  '/disk2/oradata/oradb1/control02.ctl',
  '/disk2/oradata/oradb1/control03.ctl',
  '/disk3/oradata/oradb1/control04.ctl'
SCOPE = SPFILE;
-----end-----


-----box-----

 
-----end-----


-----box-----
#程式4
control_files= 
  '/disk2/oradata/oradb1/control01.ctl',
  '/disk2/oradata/oradb1/control02.ctl',
  '/disk2/oradata/oradb1/control03.ctl',
  '/disk3/oradata/oradb1/control04.ctl'
-----end-----


-----box-----
SQL> SELECT member FROM v$logfile;
-----end-----



-----box-----
#程式5
MEMBER
------------------------------------------
/disk2/oradata/oradb1/redo01.log
/disk2/oradata/oradb1/redo02.log
/disk2/oradata/oradb1/redo03.log
-----end-----

-----box-----
SQL> SELECT name FROM v$datafile;
-----end-----



-----box-----
#程式6
NAME
-------------------------------------------
/disk2/oradata/oradb1/system01.dbf
/disk2/oradata/oradb1/undotbs01.dbf
/disk2/oradata/oradb1/sysaux01.dbf
/disk2/oradata/oradb1/users01.dbf
/disk2/oradata/oradb1/example01.dbf
-----end-----




-----box-----
STARTUP NOMOUNT;
-----end-----



-----box-----
#程式7
CREATE CONTROLFILE REUSE DATABASE "oradb1" 
NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/disk2/oradata/oradb1/
                  redo01.log'  SIZE 100M,
  GROUP 2 '/disk2/oradata/oradb1/  
                  redo02.log'  SIZE 100M,
  GROUP 3 '/disk2/oradata/oradb1/ 
                  redo03.log'  SIZE 100M

DATAFILE
  '/disk2/oradata/oradb1/system01.dbf',
  '/disk2/oradata/oradb1/undotbs01.dbf',
  '/disk2/oradata/oradb1/sysaux01.dbf',
  '/disk2/oradata/oradb1/users01.dbf',
  '/disk2/oradata/oradb1/example01.dbf'
-----end-----




-----box-----
ALTER DATABASE OPEN;
-----end-----




-----box-----
ALTER DATABASE OPEN RESETLOGS;
-----end-----



-----box-----
#程式8
ALTER DATABASE 
BACKUP CONTROLFILE 
TO '/disk2/oradata/oradb1/backup/control.bkp';
-----end-----




-----box-----
#程式9
ALTER DATABASE 
BACKUP CONTROLFILE TO TRACE;
-----end-----


-----box-----
#程式10
/disk1/oracle/admin/oradb1/udump/
oradb1_ora_4258.trc
Oracle Database 10g Enterprise Edition 
Release 10.1.0.2.0 - Production With the 
Partitioning, OLAP and Data Mining options
ORACLE_HOME = /disk1/oracle/ora10g
System name:	Linux
Node name:	RHEL3ES
Release:	2.4.21-4.EL
Version:	#1 Fri Oct 3 18:13:58 EDT 2003
Machine:	i686
Instance name: oradb1
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 4258, 
image: oracle@RHEL3ES (TNS V1-V3)

 . . .
 . . .
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="oradb1"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_10
--    ='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_10  
--    ='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_10 
--    ='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_10 
--    ='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_10
--    ='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED 
--      NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_10
--    ='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
. . . .
. . . .
. . . .
--
--     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 "ORADB1" 
NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 454
LOGFILE
  GROUP 1 '/disk2/oradata/oradb1/redo01.log' 
          SIZE 10M,
  GROUP 2 '/disk2/oradata/oradb1/redo02.log' 
          SIZE 10M,
  GROUP 3 '/disk2/oradata/oradb1/redo03.log' 
          SIZE 10M
-- STANDBY LOGFILE
DATAFILE
  '/disk2/oradata/oradb1/system01.dbf',
  '/disk2/oradata/oradb1/undotbs01.dbf',
  '/disk2/oradata/oradb1/sysaux01.dbf',
  '/disk2/oradata/oradb1/users01.dbf',
  '/disk2/oradata/oradb1/example01.dbf'
CHARACTER SET ZHT16BIG5
;
-- 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 
--   '/disk1/oracle/flash_recovery_area/ORADB1/
--   archivelog/2004_07_12/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE 
--   '/disk1/oracle/flash_recovery_area/ORADB1/
--   archivelog/2004_07_12/o1_mf_1_1_%u_.arc';
-- 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.

ALTER TABLESPACE TEMP ADD TEMPFILE 
     '/disk2/oradata/oradb1/temp01.dbf'
     SIZE 20971520  REUSE 
AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORADB1" 
RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 454
LOGFILE
  GROUP 1 '/disk2/oradata/oradb1/redo01.log' 
       SIZE 10M,
  GROUP 2 '/disk2/oradata/oradb1/redo02.log' 
       SIZE 10M,
  GROUP 3 '/disk2/oradata/oradb1/redo03.log' 
       SIZE 10M
-- STANDBY LOGFILE
DATAFILE
  '/disk2/oradata/oradb1/system01.dbf',
  '/disk2/oradata/oradb1/undotbs01.dbf',
  '/disk2/oradata/oradb1/sysaux01.dbf',
  '/disk2/oradata/oradb1/users01.dbf',
  '/disk2/oradata/oradb1/example01.dbf'
CHARACTER SET ZHT16BIG5
;
-- 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 
--   '/disk1/oracle/flash_recovery_area/ORADB1/
--   archivelog/2004_07_12/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE 
--   '/disk1/oracle/flash_recovery_area/ORADB1/
--   archivelog/2004_07_12/o1_mf_1_1_%u_.arc';
-- 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 
'/disk2/oradata/oradb1/temp01.dbf'
  SIZE 20971520  REUSE A
UTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--
-----end-----




-----box-----
SQL> SHOW PARAMETER control_files
-----end-----




-----box-----
SQL> SELECT name FROM v$controlfile;
-----end-----





-----box-----
#程式11
SQL> SELECT name, value FROM v$parameter
WHERE name = 'control_files';
-----end-----