oracle 11g改变控制文件的位置

最新推荐文章于 2024-10-18 15:57:23 发布

原创最新推荐文章于 2024-10-18 15:57:23 发布 · 5.9k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle #oracle 11g

数据库管理专栏收录该内容

3 篇文章

订阅专栏

本文介绍了两种Oracle数据库控制文件的迁移方法。方法一通过修改参数文件并直接移动文件来实现；方法二则是创建PFILE，修改控制文件路径后，再生成SPFILE完成迁移。

控制文件是oracle数据库中一个非常小的二进制文件，它含有数据库的结构信息。无论是启动数据库还是在数据库运行的过程中，控制文件都起着非常重要的作用。下面用两种方法介绍如何移动控制文件的位置。

方法一：

***查看控制文件存放路径***
SQL> show parameter control;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/test/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 fast_recovery_area/test/contro
                                                 l02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING

***修改参数文件***
[oracle@test ~]$ export ORACLE_SID=test
[oracle@test ~]$ sqlplus / as sysdba

SQL> alter system set control_files='/oracledb/test/control01.ctl','/oracledb/test/control02.ctl' scope=spfile;

System altered.

***关闭数据库***
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

****移动控制文件，用oracle用户操作，以免oracle用户无权限，后面startup时报错***
[oracle@test ~]$ mv /u01/app/oracle/oradata/test/control01.ctl /oracledb/test/control01.ctl
[oracle@test ~]$ mv /u01/app/oracle/fast_recovery_area/test/control02.ctl /oracledb/test/control02.ctl

***启动数据库***
SQL> startup
ORACLE instance started.

Total System Global Area 1.0122E+10 bytes
Fixed Size                  2237088 bytes
Variable Size            1610616160 bytes
Database Buffers         8489271296 bytes
Redo Buffers               19468288 bytes
Database mounted.
Database opened.

***查看控制文件的位置，验证是否迁移成功***
SQL> show parameter control;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /oracledb/test/control01.ctl,
                                                 /oracledb/test/control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> select * from v$controlfile;

STATUS
-------
NAME
---------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------

/oracledb/test/control01.ctl
NO       16384            594


/oracledb/test/control02.ctl
NO       16384            594

STATUS
-------
NAME
-----------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------

方法二：

***查看控制文件的位置***
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oracledb/test/control01.ctl
/oracledb/test/control02.ctl

***查看是否以spfile启动***
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.3
                                                 /dbhome_1/dbs/spfiletest.ora

***spfile是二进制文件，不能修改，用spfile生成pfile***
SQL> create pfile from spfile;

File created.

***关闭数据库***
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

***移动控制文件到目标位置***
[oracle@test ~]$ mv /oracledb/test/control01.ctl /u01/app/oracle/oradata/test/control01.ctl
[oracle@test ~]$ mv /oracledb/test/control02.ctl /u01/app/oracle/oradata/test/control02.ctl

***修改pfile中控制文件的路径***
[oracle@test ~]$ vi $ORACLE_HOME/dbs/inittest.ora

*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/test/control01.ctl','/u01/app/oracle/oradata/test/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test'

***指定pfile启动数据库***
SQL> startup pfile=$ORACLE_HOME/dbs/inittest.ora
ORACLE instance started.

Total System Global Area 1.0122E+10 bytes
Fixed Size                  2237088 bytes
Variable Size            1610616160 bytes
Database Buffers         8489271296 bytes
Redo Buffers               19468288 bytes
Database mounted.
Database opened.

***查看控制文件的路径***
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/control01.ctl
/u01/app/oracle/oradata/test/control02.ctl

***用pfile文件生成spfile文件***
SQL> create spfile from pfile;

File created.

***重启验证（以spfile启动）***
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1.0122E+10 bytes
Fixed Size                  2237088 bytes
Variable Size            1610616160 bytes
Database Buffers         8489271296 bytes
Redo Buffers               19468288 bytes
Database mounted.
Database opened.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/control01.ctl
/u01/app/oracle/oradata/test/control02.ctl