ITPub博客

首页 > 数据库 > Oracle > ORACLE 添加控制文件

ORACLE 添加控制文件

原创 Oracle 作者:邱东阳 时间:2014-03-12 10:54:40 0 删除 编辑

本文将ASM、文件系统、裸设备添加文件系统的方法都做了示例。

ASM

1、查看当前控制文件信息

 

SQL> select name from v$controlfile;

 

NAME

--------------------------------------------------------------------------------

+DATA1/dbca/controlfile/current.261.837597295

+DATA1/dbca/controlfile/current.260.837597303

 

SQL>

SQL> show parameter control_files;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      +DATA1/dbca/controlfile/curren

                                                 t.261.837597295, +DATA1/dbca/c

                                                 ontrolfile/current.260.8375973

                                                 03

SQL>

 

 

2、关闭数据库

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

 

3、使用RMAN复制控制文件

 

[oracle@dbca ~]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Mar 10 14:28:08 2014

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

connected to target database (not started)

 

RMAN> startup nomount

 

Oracle instance started

 

Total System Global Area     583008256 bytes

 

Fixed Size                     2022504 bytes

Variable Size                176161688 bytes

Database Buffers             402653184 bytes

Redo Buffers                   2170880 bytes

RMAN> restore controlfile to '+DATA1' from '+DATA1/dbca/controlfile/current.261.837597295';

 

Starting restore at 2014-03-10 14:30:38

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

 

channel ORA_DISK_1: copied control file copy

Finished restore at 2014-03-10 14:30:51

 

RMAN>

 

4、使用asmcmd查看复制的控制文件名

 

[oracle@dbca ~]$ echo $ORACLE_SID

+ASM

[oracle@dbca ~]$ asmcmd

ASMCMD> cd /data1/dbca/controlfile

ASMCMD> ls

Current.260.837597303

Current.261.837597295

backup. 379.841849487

ASMCMD> mkalias backup.379.841849487 current.ctl (创建文件别名)

ASMCMD> mkalias Current.260.837597303 current1.ctl

ASMCMD> ls

Current.260.837597303

Current.261.837597295

backup. 379.841849487

current1.ctl

current.ctl

 

5、修改spfile中的control_files参数

 

[oracle@dbca u01]$ echo $ORACLE_SID

dbca

[oracle@dbca u01]$ sqlplus /nolog

 

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 10 14:36:00 2014

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

SQL> conn / as sysdba

SQL> alter system set control_files='+DATA1/dbca/controlfile/current.261.837597295',

  2  '+DATA1/dbca/controlfile/current.ctl',

  3  '+DATA1/dbca/controlfile/current1.ctl' scope=spfile;

 

System altered.

 

SQL> shutdown immediate;

ORA-01507: database not mounted

 

 

ORACLE instance shut down.

SQL>

 

6、启动数据库查看修改后的参数

 

SQL> startup;

ORACLE instance started.

 

Total System Global Area  583008256 bytes

Fixed Size                  2022504 bytes

Variable Size             159384472 bytes

Database Buffers          419430400 bytes

Redo Buffers                2170880 bytes

Database mounted.

Database opened.

SQL> show parameters control_files;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      +DATA1/dbca/controlfile/curren

                                                 t.261.837597295, +DATA1/dbca/c

                                                 ontrolfile/current.ctl, +DATA1

                                                 /dbca/controlfile/current1.ctl

SQL> select name from v$controlfile;

 

NAME

--------------------------------------------------------------------------------

+DATA1/dbca/controlfile/current.261.837597295

+DATA1/dbca/controlfile/current.ctl

+DATA1/dbca/controlfile/current1.ctl

 

SQL>

 

文件系统

1、关闭数据库

 

SQL> shutdown immediate;

 

2、复制控制文件

 

SQL>host copy /u01/app/oracle/oradata/dbca/control01.ctl  /uo1/app/oracle/oradata/dbca/control02.ctl

 

3、多个 实例下需要配置listener.ora (否则找不到TNS

 

[oracle@dbca admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

LISTENER2 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.187)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = dbca)

      (GLOBAL_DBNAME = dbca)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

    )

  )

SID_DESC =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = dbca2)

      (GLOBAL_DBNAME = dbca2)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

    )

  )

 

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.188)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

 

LISTENER4 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.187)(PORT = 1521))

    )

  )

 

4、重启监听启动数据库到nomount

 

[oracle@dbca admin]$ lsnrctl stop

[oracle@dbca admin]$ lsnrctl start

 

[oracle@dbca admin]$ sqlplus / as sysdba

SQL> startup nomount

5、添加控制文件

 

SQL> alter system set control_files='/u01/app/oracle/oradata/dbca/control01.ctl',

  2  '/u01/app/oracle/oradata/dbca/control02.ctl' scope=spfile;

 

6、强迫打开数据库查看控制文件

 

SQL> startup force

SQL> show parameter control_files;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                string        /u01/app/oracle/oradata/dbca/control01.ctl/u01/app/oracle/oradata/dbca/control02.ctl

裸设备

1、查看VG剩余空间

 

vgdisplay v|more

 

 

 

2、创建lv

 

[root@yangzai /]# lvcreate -n control4 -L 200M vg_oracle

  Logical volume "control4" created

 

 

3、配置lv与裸设备的关联

 

[root@yangzai /]# vi /etc/sysconfig/rawdevices

 

# This file and interface are deprecated.

# Applications needing raw device access should open regular

# block devices with O_DIRECT.

# raw device bindings

# format: 

#         

# example: /dev/raw/raw1 /dev/sda1

#          /dev/raw/raw2 8 5

 

 

/dev/raw/raw1 /dev/vg_oracle/control1

/dev/raw/raw2 /dev/vg_oracle/control2

/dev/raw/raw3 /dev/vg_oracle/control3

/dev/raw/raw4 /dev/vg_oracle/example

/dev/raw/raw5 /dev/vg_oracle/passwordfile

/dev/raw/raw6 /dev/vg_oracle/redo1_1

/dev/raw/raw7 /dev/vg_oracle/redo1_2

/dev/raw/raw8 /dev/vg_oracle/redo2a

/dev/raw/raw9 /dev/vg_oracle/redo2b

/dev/raw/raw10 /dev/vg_oracle/redo3a

/dev/raw/raw11 /dev/vg_oracle/redo3b

/dev/raw/raw12 /dev/vg_oracle/lv_spf_spfile

/dev/raw/raw13 /dev/vg_oracle/sysaux

/dev/raw/raw14 /dev/vg_oracle/system

/dev/raw/raw15 /dev/vg_oracle/temp

/dev/raw/raw16 /dev/vg_oracle/undotbs1

/dev/raw/raw17 /dev/vg_oracle/users

 

/dev/raw/raw18 /dev/vg_oracle/control4

 

 

4、重启裸设备服务

 

[root@yangzai vg_oracle]# /sbin/service rawdevices restart

Assigning devices:

           /dev/raw/raw1  -->   /dev/vg_oracle/control1

/dev/raw/raw1:  bound to major 253, minor 12

           /dev/raw/raw2  -->   /dev/vg_oracle/control2

/dev/raw/raw2:  bound to major 253, minor 13

           /dev/raw/raw3  -->   /dev/vg_oracle/control3

/dev/raw/raw3:  bound to major 253, minor 14

           /dev/raw/raw4  -->   /dev/vg_oracle/example

/dev/raw/raw4:  bound to major 253, minor 4

           /dev/raw/raw5  -->   /dev/vg_oracle/passwordfile

/dev/raw/raw5:  bound to major 253, minor 16

           /dev/raw/raw6  -->   /dev/vg_oracle/redo1a

/dev/raw/raw6:  bound to major 253, minor 6

           /dev/raw/raw7  -->   /dev/vg_oracle/redo1b

/dev/raw/raw7:  bound to major 253, minor 7

           /dev/raw/raw8  -->   /dev/vg_oracle/redo2a

/dev/raw/raw8:  bound to major 253, minor 8

           /dev/raw/raw9  -->   /dev/vg_oracle/redo2b

/dev/raw/raw9:  bound to major 253, minor 9

           /dev/raw/raw10  -->   /dev/vg_oracle/redo3a

/dev/raw/raw10: bound to major 253, minor 10

           /dev/raw/raw11  -->   /dev/vg_oracle/redo3b

/dev/raw/raw11: bound to major 253, minor 11

           /dev/raw/raw12  -->   /dev/vg_oracle/lv_spf_spfile

/dev/raw/raw12: bound to major 253, minor 15

           /dev/raw/raw13  -->   /dev/vg_oracle/sysaux

/dev/raw/raw13: bound to major 253, minor 1

           /dev/raw/raw14  -->   /dev/vg_oracle/system

/dev/raw/raw14: bound to major 253, minor 0

           /dev/raw/raw15  -->   /dev/vg_oracle/temp

/dev/raw/raw15: bound to major 253, minor 3

           /dev/raw/raw16  -->   /dev/vg_oracle/undotbsl

/dev/raw/raw16: bound to major 253, minor 2

           /dev/raw/raw17  -->   /dev/vg_oracle/user

/dev/raw/raw17: bound to major 253, minor 5

           /dev/raw/raw18  -->   /dev/vg_oracle/control4

/dev/raw/raw18: bound to major 253,minor 6

done

[root@yangzai vg_oracle]#

 

 

5、修改裸设备的权限及所有者

 

[root@yangzai vg_oracle]# chown oracle:dba /dev/raw/raw*

[root@yangzai vg_oracle]# chmod 660 /dev/raw/raw*

[root@yangzai vg_oracle]#

[root@yangzai etc]# vi /etc/rc.local

touch /var/lock/subsys/local

#!/bin/sh

#

# This script will be executed *after* all the other init scripts.

# You can put your own initialization stuff in here if you don't

# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

chown oracle:dba /dev/raw/raw*

chmod 660 /dev/raw/raw*

 

6、编辑映射裸设备映射文件

 

[root@yangzai orcl]# vi orcl_raw.conf  

 

 

control1=/dev/raw/raw1

 

control2=/dev/raw/raw2

 

control3=/dev/raw/raw3

 

example=/dev/raw/raw4

 

passwordfile=/dev/raw/raw5

 

redo1_1=/dev/raw/raw6

 

redo1_2=/dev/raw/raw7

 

redo2a=/dev/raw/raw8

 

redo2b=/dev/raw/raw9

 

redo3a=/dev/raw/raw10

 

redo3b=/dev/raw/raw11

 

lv_spf_spfile=/dev/raw/raw12

 

sysaux=/dev/raw/raw13

 

system=/dev/raw/raw14

 

temp=/dev/raw/raw15

 

undotbs1=/dev/raw/raw16

 

users=/dev/raw/raw17

 

control4=/dev/raw/raw18

 

"orcl_raw.conf" 47L, 432C written                                                          

[root@yangzai orcl]#

 

 

 

7、复制控制文件

 

使用DD

SQL> shutdown immediate;

[root@yangzai orcl]#dd if=/dev/raw/raw1 of=/dev/raw/raw18 bs=8K

 

使用sql

SQL> startup mount

SQL>alter database backup controlfile to ‘/dev/raw/raw18’;

8、添加控制文件

 

SQL>startup nomount

SQL> alter system set control_files='/dev/raw/raw1', '/dev/raw/raw2', '/dev/raw/raw3', '/dev/raw/raw18' scope=spfile;

 

 

9、打开数据库查看控制文件

 

SQL>alter database open

SQL> show parameter control_files;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                string        /dev/raw/raw1, /dev/raw/raw2, /dev/raw/raw3, /dev/raw/raw18

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29532781/viewspace-1108114/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2014-03-11

  • 博文量
    38
  • 访问量
    173606