ORACLE 新增控制檔案

邱東陽發表於2014-03-12
Oracle

本文將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  --&gt   /dev/vg_oracle/control1

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

           /dev/raw/raw2  --&gt   /dev/vg_oracle/control2

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

           /dev/raw/raw3  --&gt   /dev/vg_oracle/control3

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

           /dev/raw/raw4  --&gt   /dev/vg_oracle/example

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

           /dev/raw/raw5  --&gt   /dev/vg_oracle/passwordfile

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

           /dev/raw/raw6  --&gt   /dev/vg_oracle/redo1a

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

           /dev/raw/raw7  --&gt   /dev/vg_oracle/redo1b

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

           /dev/raw/raw8  --&gt   /dev/vg_oracle/redo2a

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

           /dev/raw/raw9  --&gt   /dev/vg_oracle/redo2b

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

           /dev/raw/raw10  --&gt   /dev/vg_oracle/redo3a

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

           /dev/raw/raw11  --&gt   /dev/vg_oracle/redo3b

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

           /dev/raw/raw12  --&gt   /dev/vg_oracle/lv_spf_spfile

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

           /dev/raw/raw13  --&gt   /dev/vg_oracle/sysaux

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

           /dev/raw/raw14  --&gt   /dev/vg_oracle/system

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

           /dev/raw/raw15  --&gt   /dev/vg_oracle/temp

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

           /dev/raw/raw16  --&gt   /dev/vg_oracle/undotbsl

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

           /dev/raw/raw17  --&gt   /dev/vg_oracle/user

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

           /dev/raw/raw18  --&gt   /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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章