（工程部）工程日志

[实施目标]

使用rman将数据库移植到ASM存储区

[项目环境]

[前期工作进展]

1 在阵列中创建分区并绑定到主机

[root@aix221 /]# lsdev -c adapter

ent0      Available 09-08 2-Port 10/100/1000 Base-TX PCI-X Adapter (14108902)

ent1      Available 09-09 2-Port 10/100/1000 Base-TX PCI-X Adapter (14108902)

fcs0      Available 00-08 FC Adapter

[root@aix221 /]# lscfg -vpl fcs0

  fcs0             U787F.001.DPM533R-P1-C4-T1  FC Adapter

        Part Number.................80P4543

        EC Level....................A

        Serial Number...............1F4500AB06

        Manufacturer................001F

        Customer Card ID Number.....280B

        FRU Number..................     80P4544

        Device Specific.(ZM)........3

        Network Address.............10000000C942B448

划分区volslice create aix221 -z 25GB cuug

绑定到主机lun perm lun 4 rw wwn 10000000C942B448

主机搜索设备 cfgmgr -v

2创建卷组 使用smit vg

[root@aix221 /]# lspv

hdisk0          00cf823d59523518                    rootvg          active

hdisk2          0000c9a2ab92bcb4                    asmvg           active

序号要是不出来使用命令：chdev -l hdisk2 -a pv=yes

3创建逻辑分区

[root@aix221 /]# mklv -y lv_asm1 asmvg 80

[root@aix221 /]# mklv -y lv_asm2 asmvg 80

[root@aix221 /]# mklv -y lv_asm3 asmvg 80

[root@aix221 /]# mklv -y lv_asm4 asmvg 80

[root@aix221 /]# mklv -y lv_asm5 asmvg 75

[root@aix221 /]# lsvg -l asmvg

asmvg:

LV NAME             TYPE       LPs     PPs     PVs  LV STATE      MOUNT POINT

loglv02             jfslog     1       1       1    closed/syncd  N/A

lv_asm1             jfs        80      80      1    closed/syncd  N/A

lv_asm2             jfs        80      80      1    closed/syncd  N/A

lv_asm3             jfs        80      80      1    closed/syncd  N/A

lv_asm4             jfs        80      80      1    closed/syncd  N/A

lv_asm5             jfs        75      75      1    closed/syncd  N/A

4设置权限

[root@aix221 /]# chown -R oracle:dba /dev/rlv_asm*

[root@aix221 /]# ls -l /dev/rlv_asm*                                    

crw-rw----    1 oracle   dba          49,  2 Jul 24 10:05 /dev/rlv_asm1 

crw-rw----    1 oracle   dba          49,  3 Jul 24 10:05 /dev/rlv_asm2 

crw-rw----    1 oracle   dba          49,  4 Jul 24 10:05 /dev/rlv_asm3 

crw-rw----    1 oracle   dba          49,  5 Jul 24 10:05 /dev/rlv_asm4 

crw-rw----    1 oracle   dba          49,  6 Jul 24 10:05 /dev/rlv_asm5 

[root@aix221 /]# chmod -R 666 /dev/lv_asm*

[root@aix221 /]# ls -l /dev/lv_asm*

brw-rw-rw-    1 root     system       49,  2 Jul 24 10:05 /dev/lv_asm1

brw-rw-rw-    1 root     system       49,  3 Jul 24 10:05 /dev/lv_asm2

brw-rw-rw-    1 root     system       49,  4 Jul 24 10:05 /dev/lv_asm3

brw-rw-rw-    1 root     system       49,  5 Jul 24 10:05 /dev/lv_asm4

brw-rw-rw-    1 root     system       49,  6 Jul 24 10:06 /dev/lv_asm5

[实施步骤]

1创建ASM实例的pfile文件，在$ORACLE_HOME/dbs下建立

[oracle@aix221 dbs]$ cat init+ASM.ora

background_dump_dest='/u01/app/oracle/admin/+ASM/bdump'

core_dump_dest='/u01/app/oracle/admin/+ASM/cdump'

instance_type='asm'

large_pool_size=12M

remote_login_passwordfile='SHARED'

user_dump_dest='/u01/app/oracle/admin/+ASM/udump'

[oracle@aix221 dbs]$ mkdir -p /u01/app/oracle/admin/+ASM/bdump

[oracle@aix221 dbs]$ mkdir -p /u01/app/oracle/admin/+ASM/cdump

[oracle@aix221 dbs]$ mkdir -p /u01/app/oracle/admin/+ASM/udump

2使用root用户在$ORACLE_HOME/bin下执行俩个脚本

[oracle@aix221 dbs]$ cd $ORACLE_HOME/bin

[oracle@aix221 bin]$ pwd

/u01/app/oracle/product/10.2.0/db_1/bin

[root@aix221 /]# cd /u01/app/oracle/product/10.2.0/db_1/bin

[root@aix221 bin]# ./localconfig delete

/etc/oracle does not exist. Creating it now.

./localconfig[715]: /etc/init.cssd:  not found

[root@aix221 bin]# ./localconfig add  

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'system'..

Operation successful.

Configuration for local CSS has been initialized

Adding to inittab

Startup will be queued to init within 30 seconds.

Checking the status of new Oracle init process...

Expecting the CRS daemons to be up within 600 seconds.

CSS is active on these nodes.

        aix221

CSS is active on all nodes.

Oracle CSS service is installed and running under init(1M)

3使用pfile文件开启实例+ASM到nomount状态

[oracle@aix221 ~]$ export ORACLE_SID=+ASM

[oracle@aix221 ~]$ sqlplus / as sysdba

SQL> startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/init+ASM.ora';

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

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

+ASM             STARTED

4创建spfile文件设置asm_diskstring参数ASM使用的设备，nomount状态下

SQL> create spfile from pfile;

SQL> shutdown immediate;

SQL> startup nomount;

SQL> show parameter asm

NAME                                 TYPE        VALUE

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

asm_diskgroups                       string

asm_diskstring                       string

asm_power_limit                      integer     1

SQL> alter system set asm_diskstring='/dev/rlv_asm*' scope=spfile;

System altered.

SQL> shutdown immediate;

5创建ASM磁盘组

SQL> startup nomount;

SQL> create diskgroup log1 external redundancy disk '/dev/rlv_asm1';

SQL> create diskgroup log2 external redundancy disk '/dev/rlv_asm2';

SQL> create diskgroup data1 external redundancy disk '/dev/rlv_asm3';

SQL> create diskgroup data2 external redundancy disk '/dev/rlv_asm4';

SQL> create diskgroup recover external redundancy disk '/dev/rlv_asm5';

Diskgroup created.

SQL> select name,STATE from v$asm_diskgroup;

NAME                           STATE

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

LOG1                           MOUNTED

LOG2                           MOUNTED

DATA1                          MOUNTED

DATA2                          MOUNTED

RECOVER                        MOUNTED

6修改spfile初始化参数文件，把控制文件、数据文件、日志文件的路径指向到ASM磁盘组

[oracle@aix221 ~]$ export ORACLE_SID=prod

[oracle@aix221 ~]$ sqlplus / as sysdba

SQL> show parameter control

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     7

control_files                        string      /u01/app/oracle/oradata/prod/c

                                                 ontrol01.ctl, /u01/app/oracle/

                                                 oradata/prod/control02.ctl, /u

                                                 01/app/oracle/oradata/prod/con

                                                 trol03.ctl

SQL> alter system set control_files='+DATA1' scope=spfile;

SQL> show parameter create

NAME                                 TYPE        VALUE

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

create_bitmap_area_size              integer     8388608

create_stored_outlines               string

db_create_file_dest                  string

db_create_online_log_dest_1          string

db_create_online_log_dest_2          string

db_create_online_log_dest_3          string

db_create_online_log_dest_4          string

db_create_online_log_dest_5          string

SQL> alter system set db_create_file_dest='+DATA1' scope=spfile;

SQL> alter system set db_create_online_log_dest_1='+LOG1' scope=spfile;

SQL> alter system set db_create_online_log_dest_2='+LOG2' scope=spfile;

SQL> shutdown immediate;

7数据库启动到nomount状态，转储控制文件

RMAN> startup nomount;

RMAN> restore controlfile from '/u01/app/oracle/oradata/prod/control01.ctl';

Starting restore at 24-JUL-14

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

output filename=+DATA1/prod/controlfile/backup.256.853759937

Finished restore at 24-JUL-14

8数据库启动到mount状态，copy数据文件到ASM磁盘组

RMAN> mount database;

database mounted

released channel: ORA_DISK_1

RMAN> run{

2> allocate channel c1 type disk;

3> allocate channel c2 type disk;

4> backup as copy database format '+DATA1';

5> }

9利用rman的switch命令修改控制文件内的数据文件指针，使其指向新位置

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA1/prod/datafile/system.258.853760305"

datafile 2 switched to datafile copy "+DATA1/prod/datafile/undotbs1.260.853760389"

datafile 3 switched to datafile copy "+DATA1/prod/datafile/sysaux.257.853760303"

datafile 4 switched to datafile copy "+DATA1/prod/datafile/users.261.853760405"

datafile 5 switched to datafile copy "+DATA1/prod/datafile/example.259.853760389"

10恢复数据库并打开

RMAN> recover database;

Starting recover at 24-JUL-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 24-JUL-14

RMAN> open database;

database opened

11查看数据文件和控制文件

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

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

prod             OPEN

SQL> select name from v$datafile;

NAME

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

+DATA1/prod/datafile/system.258.853760305

+DATA1/prod/datafile/undotbs1.260.853760389

+DATA1/prod/datafile/sysaux.257.853760303

+DATA1/prod/datafile/users.261.853760405

+DATA1/prod/datafile/example.259.853760389

SQL> select name from v$controlfile;

NAME

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

+DATA1/prod/controlfile/backup.256.853759937

12迁移spfile、temp文件到ASM磁盘组

1）创建pfile并用pfile启动数据库

SQL> create pfile from spfile;

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/product/10.2.0

                                                 /db_1/dbs/spfileprod.ora

SQL> shutdown immediate;                  

ORACLE instance shut down.

SQL> startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initprod.ora';

2）在ASM磁盘组上创建spfile文件

SQL> create spfile='+DATA1' from pfile;

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@aix221 ~]$ export ORACLE_SID=+ASM

[oracle@aix221 ~]$ asmcmd

ASMCMD> ls

DATA1/

DATA2/

LOG1/

LOG2/

RECOVER/

ASMCMD> cd DATA1/

ASMCMD> ls

PROD/

ASMCMD> cd prod

ASMCMD> ls

CONTROLFILE/

DATAFILE/

PARAMETERFILE/

ASMCMD> cd controlfile

ASMCMD> ls

backup.256.853759937

ASMCMD> cd ..

ASMCMD> cd PARAMETERFILE

ASMCMD> ls

spfile.262.853761693

3）编辑pfile指向到asm上的spfile

[oracle@aix221 dbs]$ mv initprod.ora initprod.ora.bak

[oracle@aix221 dbs]$ vi initprod.ora   

spfile='+DATA1/PROD/PARAMETERFILE/spfile.262.853761693'

4）把原来的spfile改名使用新的参数文件

[oracle@aix221 dbs]$ mv spfileprod.ora spfileprod.ora.bak

开库查看

[oracle@aix221 dbs]$ export ORACLE_SID=prod

[oracle@aix221 dbs]$ sqlplus / as sysdba

SQL> startup

SQL> show parameter pfile;

NAME                                 TYPE        VALUE

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

spfile                               string      +DATA1/prod/parameterfile/spfi

                                                 le.262.853761693

13迁移temp表空间，在ASM磁盘上为temp表空间添加数据文件并删除原数据文件

1）添加数据文件

SQL> select name,status,enabled from v$tempfile;

NAME                                          STATUS  ENABLED

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

/u01/app/oracle/oradata/prod/temp01.dbf       ONLINE  READ WRITE

SQL> alter tablespace temp add tempfile '+DATA1';

Tablespace altered.

SQL> select name,status,enabled from v$tempfile;

NAME                                          STATUS  ENABLED

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

/u01/app/oracle/oradata/prod/temp01.dbf       ONLINE  READ WRITE

+DATA1/prod/tempfile/temp.263.853765655       ONLINE  READ WRITE

2）删除原数据文件

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/prod/temp01.dbf';

Tablespace altered.

SQL> select name,status,enabled from v$tempfile;

NAME                                          STATUS  ENABLED

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

+DATA1/prod/tempfile/temp.263.853765655       ONLINE  READ WRITE

14迁移redo logfile 到ASM磁盘组

1. 查看当前日志组

SQL>select * from v$logfile

    GROUP# STATUS  TYPE    MEMBER                                        IS_

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

         3         ONLINE  /u01/app/oracle/oradata/prod/redo03.log       NO

         2         ONLINE  /u01/app/oracle/oradata/prod/redo02.log       NO

         1         ONLINE  /u01/app/oracle/oradata/prod/redo01.log       NO

         1         ONLINE  /prod_log/prod/redo11.log                     NO

         2         ONLINE  /prod_log/prod/redo12.log                     NO

         3         ONLINE  /prod_log/prod/redo13.log                     NO

2. 给日志组添加成员

SQL> alter database add logfile member '+LOG1' to group 1;

SQL> alter database add logfile member '+LOG2' to group 1;

SQL> alter database add logfile member '+LOG1' to group 2;

SQL> alter database add logfile member '+LOG2' to group 2;

SQL> alter database add logfile member '+LOG1' to group 3;

SQL> alter database add logfile member '+LOG2' to group 3;

3. 删除原日志组

SQL> alter database drop logfile member '/u01/app/oracle/oradata/prod/redo01.log';

SQL> alter database drop logfile member '/u01/app/oracle/oradata/prod/redo02.log';

SQL> alter database drop logfile member '/prod_log/prod/redo11.log','/prod_log/prod/redo12.log';

切日志，删剩余成员

SQL> alter system switch logfile;

SQL> alter database drop logfile member '/prod_log/prod/redo13.log','/u01/app/oracle/oradata/prod/redo03.log';

4. 查看

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                        IS_

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

         1         ONLINE  +LOG1/prod/onlinelog/group_1.256.853766265    NO

         1         ONLINE  +LOG2/prod/onlinelog/group_1.256.853766313    NO

         2         ONLINE  +LOG1/prod/onlinelog/group_2.257.853766381    NO

         2         ONLINE  +LOG2/prod/onlinelog/group_2.257.853766399    NO

         3         ONLINE  +LOG1/prod/onlinelog/group_3.258.853766423    NO

         3         ONLINE  +LOG2/prod/onlinelog/group_3.258.853766439    NO

15做控制文件多元化

SQL> show parameter control

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     7

control_files                        string      +DATA1/prod/controlfile/backup

                                                 .256.853759937

1）设置参数

SQL> alter system set control_files='+DATA1','+DATA2' scope=spfile;

2）重新开库时生效

SQL> shutdown immediate;

SQL> startup nomount;

SQL> show parameter control

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     7

control_files                        string      +DATA1, +DATA2

3）使用rman转储控制文件

RMAN> restore controlfile from '+DATA1/prod/controlfile/backup.256.853759937’;

4)开库查看

SQL> alter database open；

SQL> show parameter control   

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     7

control_files                        string      +DATA1/prod/controlfile/curren

                                                 t.264.853771927, +DATA2/prod/c

                                                 ontrolfile/current.261.8537719

                                                 31

5. 做全库备份

sh /home/oracle/hrman0.sh

[当前遇到的问题]

[后续工作计划]

1用压力测试脚本测试数据库

2 数据库恢复测试