首页
登录 | 注册

实现控制文件的多路复用

控制文件的重要性:

数据库在启动时经历三个过程:nomount-mount-open

nomount状态:根据特定的规则去指定路径下寻找参数文件,根据参数文件中配置的参数信息分配SGA,启动后台进程,打开数据库实例,注意参数文件中记录了控制文件的路径

mount状态:根据参数文件中定义的控制文件的路径找到并打开控制文件,注意控制文件中记录了数据文件以及日志文件等信息

open状态:根据控制文件中记录的数据文件和日志文件的信息,打开加载这些文件,打开数据库。

其中控制文件是一个很小的二进制文件,不能直接打开,可以转储出来查看

SQL> alter database backup controlfile to trace as '/opt/oracle/backup/control.ctl';

 

数据库已更改。

 

SQL>

 

查看控制文件内容、

-- 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=%t_%s_%r.dbf

--

-- DB_UNIQUE_NAME="test"

--

-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'

-- LOG_ARCHIVE_MAX_PROCESSES=4

-- STANDBY_FILE_MANAGEMENT=MANUAL

-- STANDBY_ARCHIVE_DEST=?/dbs/arch

-- FAL_CLIENT=''

-- FAL_SERVER=''

--

-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch'

-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE 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 "TEST" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

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

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

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

-- STANDBY LOGFILE

DATAFILE

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

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

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

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

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

CHARACTER SET AL32UTF8

;

 

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- 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 '/u01/app/oracle/arch/1_1_824297850.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch/1_1_915120894.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 '/u01/app/oracle/oradata/test/temp01.dbf'

     SIZE 30408704  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 "TEST" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

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

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

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

-- STANDBY LOGFILE

DATAFILE

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

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

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

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

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

CHARACTER SET AL32UTF8

;

 

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- 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 '/u01/app/oracle/arch/1_1_824297850.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch/1_1_915120894.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 '/u01/app/oracle/oradata/test/temp01.dbf'

     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

-- End of tempfile additions.

--

 

可以看到控制文件里面记录了数据文件,重做日志,字符集,备份文件等信息,还包括一些日志序列号,检查点信息等,在实例打开的时候会比对控制文件中记录的SCN号和数据文件头部记录的SCN号,如果不一致则会进行恢复,因此对于一个数据库环境来说,控制文件的重要性是不言而喻的。因此在生产环境中就需要考虑到控制文件的多路复用。以便更好的保护控制文件。

下面就介绍控制文件多路复用的实现方法:


备份参数文件:


SQL> create pfile='/opt/oracle/initorcl.ora' from spfile;

 

文件已创建。

看控制文件的存放路径


方法一:修改增加控制文件的存放路径(由于control_files是静态参数,修改完之后必须重启数据库生效)

相关命令为:

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

可以看到我增加了一个名为control03.ctl的控制文件,路径为

/u01/app/oracle/oradata/test/control03.ctl',因为是测试,所以不考虑不同磁盘的冗
关闭数据库:

SQL> shut immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL>


复制控制文件:

[oracle@oracle ~]$ cd /u01/app/oracle/oradata/test/

[oracle@oracle test]$ ls

control01.ctl  control02.ctl  example01.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

[oracle@oracle test]$ cp control01.ctl control03.ctl

[oracle@oracle test]$ ls control*

control01.ctl  control02.ctl  control03.ctl

启动数据库并查看控制文件的路径:



可以看到增加的control03.ctl已经记录到control_files里面了,修改已经生效。


方法二:直接修改pfile文件并关闭数据库

SQL> create pfile='/opt/oracle/initorcl.ora' from spfile;

 

文件已创建。

 

SQL> shut immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL>

修改pfile

[oracle@oracle ~]$ vi initorcl.ora

 

test.__db_cache_size=398458880

test.__java_pool_size=4194304

test.__large_pool_size=8388608

test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

test.__pga_aggregate_target=381681664

test.__sga_target=570425344

test.__shared_io_pool_size=0

test.__shared_pool_size=146800640

test.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/test/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/test/control01.ctl','/u01/app/oracle/oradata/test/control02.ctl','/u01/app/oracle/oradata/test/control03.ctl','/u01/app/oracle/oradata/test/control04.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='test'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.log_archive_dest_1='location=/u01/app/oracle/arch'

*.memory_target=951058432

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

~

~

~

~

~

~

~

~

~

~

~

~

"initorcl.ora" 28L, 1094C 已写入                                                                                                          

[oracle@oracle ~]$

复制控制文件:

[oracle@oracle test]$ cp control01.ctl control04.ctl

启动数据库:

生成pfile文件

SQL> create spfile from pfile='/opt/oracle/initorcl.ora';

 

文件已创建。

 

SQL>

重启数据库



修改已经生效。



相关文章

  • 关于页面缓存的信息,可以用 cat /proc/meminfo  看到.其中的Cached 指用于pagecache的内存大小(diskcache-SwapCache).随着写入缓存页,Dirty 的值会增加. 一旦开始把缓存页写入硬盘,W ...
  • 经常做恢复验证,每次都有些小不同,想省点事,搞个一键还原可好?适用于不同实例,源端和目标端最好版本一致,平台一致,开始吧. 步骤介绍: #适用oracle 11g单机环境 #备份在192.168.1.1的RESTORDIR目录下,采用rma ...
  • 为了云计算的安全需要自己控制加密密钥
    由于其规模经济和易用性,各组织已迅速接受云计算.外包所需的基础设施要容易得多,特别是在多租户环境和中等市场企业中,这些企业很难为自己的基础设施融资. CloudKMS(企业拥有密钥,但它们存储在云软件中):基于软件的多租户云KMS特别不适合 ...
  • 报表中如何控制附件的上传和下载权限
    目前,多用户的软件平台已经是再常见不过的了,有成千上万个用户账号的平台也很正常.为了保证每个用户准确访问各自的信息,免不了要做这样或那样的权限控制,而且这种权限控制往往是需要基于数据层面进行管理的. 对于报表平台的数据权限管理,我们可以直观 ...
  • 注意,我并不会是百分之一百正确的.例如,一个系统很可能没有/proc文件系统支持,但是大多数系统肯定有.这里我假定你是"随大流的",并有一个典型配置的系统. 什么是符号(Symbols)? 内核符号表(Kernel Sy ...
  • Qt Creator Pro文件根据不同编译器自动选择对应的第三方库
    开发环境: Ubuntu 16.04 x64 + Qt Creator 4.7.2 + Qt-4.8.7 目标板: TI am335x开发板 编译工具: arm-arago-linux-gnueabi-gcc   开发中需要链接FFmpeg ...

2020 unjeep.com webmaster#unjeep.com
12 q. 0.011 s.
京ICP备10005923号