参数文件----pfile 可文本编辑
spfile 二进制文件,不可文本编辑
PS : 此博文是在本人参考网上博客,结合实验实践,以及自身理解制作完成的,欢迎转载,注明出处即可,谢啦
下面介绍两种修改控制文件两种方法
################ 1 #####################
到oralce用户,进入SQL环境,打开数据库
- [root@sink ~]# su - oracle
- [oracle@sink ~]$ !sql
- sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 11:45:42 2018
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
- Connected to an idle instance.
- 11:45:42 SYS @ sink >startup
- ORACLE instance started.
- Total System Global Area 835104768 bytes
- Fixed Size 2257840 bytes
- Variable Size 553651280 bytes
- Database Buffers 276824064 bytes
- Redo Buffers 2371584 bytes
- Database mounted.
- Database opened.
- 11:46:44 SYS @ sink >
首先查看controlfiles的原本路径
- 12:07:55 SYS @ sink >show parameter control
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- control_file_record_keep_time integer 7
- control_files string /u01/app/oracle/oradata/sink/c
- ontrol01.ctl, /u01/app/oracle/
- fast_recovery_area/sink/contro
- l02.ctl
- control_management_pack_access string DIAGNOSTIC+TUNING
设置需要修改的controlfile路径
- 12:35:56 SYS @ sink >alter system set control_files='/u01/app/oracle/oradata/sink/control01.ctl','/u01/app/oracle/oradata/sink/control02.ctl' scope=spfile;
-
- System altered.
scope | 效果 | |
memory | 立即生效,直到数据库关闭,若数据库使用pfile启动,这是唯一可选值,也是通常所指的默认值 | |
spfile | 会修改spfile参数,新设置只有在重新使用spfile(重启数据库的时候生效) | |
此外,若是修改静态参数(不能直接生效的参数),必须指定scope=spfile,如果指定memory或者both会报如下错误: | ||
ORA-02095: specified initialization parameter cannot be modified | ||
因为静态参数不能直接通过修改内存而生效,只能通过修改spfile,然后重启数据库生 | ||
both | 是以上两者的结合体,both—两者的意思,表示修改发生在内存上立即生效,并且修改spfile保证数据库重启后也生效 | |
scope指定system修改的生效时间,scope=memory|spfile|both,其值取决于数据库使用pfile还是spfile启动 | ||
若数据库使用pfile启动,则scope=memory是默认值,也是唯一值 | ||
若数据库使用spfile启动, 则scope=both是默认值 | ||
静态参数,不能通过修改内存(默认scope值,scope=memory,both)来生效,要通过修改spfile重启数据库来生效 |
关闭数据库,退出SQL环境
- 2:39:00 SYS @ sink >shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- 12:40:14 SYS @ sink >exit
- Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
移动控制文件到目标目录,因为有控制一个文件的原路径和目标路径重复,故mv:提示可以忽略,不影响操作
- [oracle@sink ~]$ mv /u01/app/oracle/fast_recovery_area/sink/control02.ctl /u01/app/oracle/oradata/sink/control02.ctl
- [oracle@sink ~]$ mv /u01/app/oracle/oradata/sink/control01.ctl /u01/app/oracle/oradata/sink/control01.ctl
- mv: `/u01/app/oracle/oradata/sink/control01.ctl' and `/u01/app/oracle/oradata/sink/control01.ctl' are the same file
数据库重启了,我们这里默认使用spfile启动数据库,数据库重启spfile被重新启用,所以之前的修改应该会生效
- [oracle@sink ~]$ !sql
- sqlplus / as sysdba
-
- SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 12:42:19 2018
-
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
- Connected to an idle instance.
-
- 12:42:19 SYS @ sink >startup
- ORACLE instance started.
-
- Total System Global Area 835104768 bytes
- Fixed Size 2257840 bytes
- Variable Size 553651280 bytes
- Database Buffers 276824064 bytes
- Redo Buffers 2371584 bytes
- Database mounted.
- Database opened.
- 12:42:34 SYS @ sink >
生效了,控制文件路径发生了改动
- 12:42:34 SYS @ sink >show parameter control;
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- control_file_record_keep_time integer 7
- control_files string /u01/app/oracle/oradata/sink/c
- ontrol01.ctl, /u01/app/oracle/
- oradata/sink/control02.ctl
- control_management_pack_access string DIAGNOSTIC+TUNING
好了,看样子成功了,已无大碍
- 12:43:32 SYS @ sink >col name for a55
- 12:43:43 SYS @ sink >r
- 1* select * from v$controlfile
-
- STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
- ------- ------------------------------------------ --- ---------- ---------
- /u01/app/oracle/oradata/sink/control01.ctl NO 16384 678
- /u01/app/oracle/oradata/sink/control02.ctl NO 16384 678
-
- 2 rows selected.
-
- Elapsed: 00:00:00.00
############## 2 ###################
切到oralce用户,进入SQL环境,此时显示connected,连接到了数据库
- [root@sink ~]# su - oracle
- [oracle@sink ~]$ !sql
- sqlplus / as sysdba
-
- SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 6 12:33:55 2018
-
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
-
- 12:33:55 SYS @ sink >
查看数据库的实际状态,虽然知道了已连接了数据库,但是不知道其实际的状态,因为可能是exit退出SQL环境的
- 12:33:55 SYS @ sink >select status from v$instance;
-
- STATUS
- ------------
- OPEN
-
- 1 row selected.
-
- Elapsed: 00:00:00.01
- 12:35:51 SYS @ sink >
查看controlfile的实际位置,明确目标路径的位置
- 12:35:51 SYS @ sink >show parameter control;
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- control_file_record_keep_time integer 7
- control_files string /u01/app/oracle/oradata/sink/c
- ontrol01.ctl, /u01/app/oracle/
- fast_recovery_area/sink/contro
- l02.ctl
- control_management_pack_access string DIAGNOSTIC+TUNING
查看数据库以什么参数启动(是pfile,还是spfile)
- 12:38:30 SYS @ sink >show parameter spfile;
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- spfile string /u01/app/oracle/product/11.2.0
- /dbhome_1/dbs/spfilesink.ora
有spifle参数信息 | 表示数据库默认是以spfile参数文件启动的 |
无spfile参数信息 | 表示数据库默认是以pfile参数文件启动的 |
spifle是二进制文件不能直接文本编辑,所以以spfile创建pfile
- 12:47:51 SYS @ sink >create pfile from spfile;
-
- File created.
-
- Elapsed: 00:00:00.00
若之前存在pfile ------- 然后又create pfile from spife | 会覆盖之前的pfile文件。从pfile被修改的时间可以看出 |
若之前存在spife ------ 然后又create spfile from pfile | 会覆盖之前的spfile文件,从spifle修改的时间可以看出 |
补充说明oracle启动阶段找参数文件的 顺序 和 方式 | |
如果在startup里指定了pfile=''的话,Oracle将从你指定的文件作为启动参数文件,如果是没有指定pfile的话,Oracle会先去默认目录($ORACLE_HOME/)下找spfileSID.ora,如果没有找到,则找spfile.ora,如果还是没有,找initSID.ora,这个就是以前的静态参数文件了,如果没有则找init.ora;如果没有找到的话,这时候,启动就会有,找不到参数文件的错误,同时会动态注册实例名到监听服务里。
|
把对应的controlfile移动到目标路径下,由于control01.ctl的路径没有发生变动所以mv:提示不影响操作
- [oracle@sink dbs]$ mv /u01/app/oracle/oradata/sink/control01.ctl /u01/app/oracle/oradata/sink/control01.ctl
- mv: `/u01/app/oracle/oradata/sink/control01.ctl' and `/u01/app/oracle/oradata/sink/control01.ctl' are the same file
- [oracle@sink dbs]$ mv /u01/app/oracle/fast_recovery_area/sink/control02.ctl /u01/app/oracle/oradata/sink/control02.ctl
一定要先关闭数据库之后才移动控制文件,不然会后面会出一系列错误
- 13:35:03 SYS @ sink >shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- 13:36:03 SYS @ sink >
到$ORACLE_HOME/dbs路径下vim编辑控制文件路径 然后 :wq 保存退出(w 保存 q 退出 q! 强制退出)
- [oracle@sink dbs]$ pwd
- /u01/app/oracle/product/11.2.0/dbhome_1/dbs
- [oracle@sink dbs]$ vim initsink.ora
-
- *.audit_file_dest='/u01/app/oracle/admin/sink/adump'
- *.audit_trail='db'
- *.compatible='11.2.0.4.0'
- *.control_files='/u01/app/oracle/oradata/sink/control01.ctl','/u01/app/oracle/oradata/sink/control02.ctl'
- *.db_block_size=8192
- *.db_create_file_dest='+DATA'
刚我们修改了pfile但是spfile还没有改动,所以先指定使用pfile启动数据库
- 13:41:29 SYS @ sink >startup pfile=$ORACLE_HOME/dbs/initsink.ora
- ORACLE instance started.
-
- Total System Global Area 835104768 bytes
- Fixed Size 2257840 bytes
- Variable Size 553651280 bytes
- Database Buffers 276824064 bytes
- Redo Buffers 2371584 bytes
- Database mounted.
- Database opened.
- 13:42:13 SYS @ sink >
查看此时的controlifle的路径信息,好了,没错,那么这么pifle是成功的
- 13:42:13 SYS @ sink >col name for a55
- 13:43:50 SYS @ sink >select name from v$controlfile;
-
- NAME
- -------------------------------------------------------
- /u01/app/oracle/oradata/sink/control01.ctl
- /u01/app/oracle/oradata/sink/control02.ctl
-
- 2 rows selected.
-
- Elapsed: 00:00:00.00
- 13:44:01 SYS @ sink >
用pfile生成spifle,因为pfile成功了,但是spifle还没有被修改,还没有生效,默认以spfile启动,所以此操作合理
|
- 13:44:01 SYS @ sink >create spfile from pfile;
-
- File created.
-
- Elapsed: 00:00:00.02
- 13:46:59 SYS @ sink >
PS : 如果数据库以spfile启动,然后你在执行 create spfile from pfile 则会报错 ORA-32002: cannot create SPFILE already being used by the instance |
数据库正常关闭,然后,startup 不指定任何参数而数据库又存在spfile,所以oracle 默认 以 spfile启动
- 13:46:59 SYS @ sink >shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- 13:52:08 SYS @ sink >startup
- ORACLE instance started.
-
- Total System Global Area 835104768 bytes
- Fixed Size 2257840 bytes
- Variable Size 553651280 bytes
- Database Buffers 276824064 bytes
- Redo Buffers 2371584 bytes
- Database mounted.
- Database opened.
- 13:52:22 SYS @ sink >
启动后,查询控制文件路径,发现成功转移,故修改控制文件路径成功
- 13:52:22 SYS @ sink >select name from v$controlfile;
-
- NAME
- -------------------------------------------------------
- /u01/app/oracle/oradata/sink/control01.ctl
- /u01/app/oracle/oradata/sink/control02.ctl
-
- 2 rows selected.
-
- Elapsed: 00:00:00.00
- 13:53:51 SYS @ sink >
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31405474/viewspace-2149794/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31405474/viewspace-2149794/