单实例数据库迁移到rac环境(三)

[oracle@rac1 ~]$ srvctl stop database -d racdb -o immediate
[oracle@rac1 ~]$ srvctl remove database -d racdb 
Remove the database racdb? (y/[n]) y

[oracle@rac1 ~]$ crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host        
----------------------------------------------------------------------
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    0/5    0/0    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    0/5    0/0    ONLINE    ONLINE    rac1        
ora.rac1.ons   application    0/3    0/0    ONLINE    ONLINE    rac1        
ora.rac1.vip   application    0/0    0/0    ONLINE    ONLINE    rac1        
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    0/5    0/0    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    0/5    0/0    ONLINE    ONLINE    rac2        
ora.rac2.ons   application    0/3    0/0    ONLINE    ONLINE    rac2        
ora.rac2.vip   application    0/0    0/0    ONLINE    ONLINE    rac2

节点2上同样需要修改/etc/oratab文件
[root@rac1 ~]# tail -n 1 /etc/oratab 
+ASM1:/u01/app/oracle/product/10.2.0/db_1:N  

[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[oracle@rac1 ~]$ asmcmd
ASMCMD> rm -rf +DATA/RACDB
ASMCMD> rm -rf +FRA/RACDB

[oracle@rac1 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jan 3 12:43:13 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn /as sysdba
Connected.

SQL> col state format a10
SQL> col name format a15
SQL> col failgroup format a20  
SQL> set line 200
SQL> select  state,redundancy,total_mb,free_mb,name,failgroup from v$asm_disk;

STATE      REDUNDANCY              TOTAL_MB    FREE_MB NAME            FAILGROUP
---------- --------------------- ---------- ---------- --------------- --------------------
NORMAL     UNKNOWN                      286          0
NORMAL     UNKNOWN                      286          0
NORMAL     UNKNOWN                      286          0
NORMAL     UNKNOWN                      286          0
NORMAL     UNKNOWN                      286          0
NORMAL     UNKNOWN                    10208      10114 FRA_0000        FRA_0000
NORMAL     UNKNOWN                     9537       9443 DATA_0001       DATA_0001
NORMAL     UNKNOWN                     9537       9443 DATA_0000       DATA_0000
8 rows selected.

备注：前5个磁盘分别为表决盘和ocr，data磁盘组做了normal冗余

SQL> select  group_number,name,state,type,total_mb,free_mb,unbalanced  from v$asm_diskgroup;

GROUP_NUMBER NAME            STATE      TYPE                 TOTAL_MB    FREE_MB UNB
------------ --------------- ---------- ------------------ ---------- ---------- ---
           1 DATA            MOUNTED    NORMAL                  19074      18886 N
           2 FRA             MOUNTED    EXTERN                  10208      10114 N

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      orcl
db_unique_name                       string      orcl
global_names                         boolean     FALSE
instance_name                        string      orcl
lock_name_space                      string
log_file_name_convert                string
service_names                        string      orcl.yang.com

SQL> create table test1.migrate(a varchar2(20));
Table created.

SQL> insert into test1.migrate a values ('successful');
1 row created.

SQL> commit;
Commit complete.

SQL> create pfile='/home/oracle/rman_bak/initorcl.ora' from spfile;
File created.

SQL> select file_id,file_name,tablespace_name from dba_data_files order by 1;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- ---------------
         1 /u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM
         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1
         3 /u01/app/oracle/oradata/orcl/sysaux01.dbf          SYSAUX
         4 /u01/app/oracle/oradata/orcl/users01.dbf           USERS
         5 /u01/app/oracle/oradata/orcl/example01.dbf         EXAMPLE
         6 /u01/app/oracle/oradata/orcl/exp_rac01.dbf         EXP_RAC
         7 /u01/app/oracle/oradata/orcl/exp_rac_index01.dbf   EXP_RAC_INDEX
         8 /u01/app/oracle/oradata/orcl/exp_rac1_01.dbf       EXP_RAC1
         9 /u01/app/oracle/oradata/orcl/exp_rac1_index_01.dbf EXP_RAC1_INDEX
9 rows selected.

SQL> select file_id,file_name,tablespace_name from dba_temp_files order by 1;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- ---------------
         1 /u01/app/oracle/oradata/orcl/temp01.dbf            TEMP

SQL> select file_name,tablespace_name from dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- --------------------
/u01/app/oracle/oradata/orcl/temp01.dbf            TEMP

[oracle@server49 ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Jan 3 12:10:06 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1299224612)

RMAN> backup incremental level 0 
2> format '/home/oracle/rman_bak/orcl_%U'
3> database plus archivelog
4> delete all input;

RMAN> backup current controlfile format '/home/oracle/rman_bak/migrate.ctl';

RMAN> list backup of database summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
2       B  0  A DISK        03-JAN-12       1       1       NO         TAG20120103T121503

[oracle@server49 ~]$ scp -rp /home/oracle/rman_bak/ rac1:/home/oracle/

[oracle@rac1 ~]$ cat /home/oracle/rman_bak/initorcl.ora 
*.__db_cache_size=79691776
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__shared_pool_size=109051904
*.__streams_pool_size=8388608
orcl1.instance_name='orcl1'
orcl2.instance_name='orcl2'
orcl1.instance_number=1
orcl2.instance_number=2
*.cluster_database=true
*.cluster_database_instances=2
orcl1.thread=1
*.compatible='10.2.0.5.0'
*.control_files='+DATA/orcl/controlfile/control01.ctl','+FRA/orcl/controlfile/control02.ctl'
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
*.db_block_size=8192
*.db_domain='yang.com'
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=21474836480
*.log_archive_dest_1='LOCATION=+FRA/orcl/archivelog'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=213909504
*.undo_management='AUTO'
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'

备注：需要格外注意的是，这个时候不能加orcl2.thread=2参数，否则后面无法将数据库启动到mount状态

[oracle@rac1 ~]$ export ORACLE_SID=orcl1
[oracle@rac1 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jan 3 13:00:10 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn /as sysdba
Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/rman_bak/initorcl.ora';
ORACLE instance started.

Total System Global Area  213909504 bytes
Fixed Size                  2095152 bytes
Variable Size             125831120 bytes
Database Buffers           79691776 bytes
Redo Buffers                6291456 bytes

SQL> create spfile='+DATA/ORCL/PARAMETERFILE/spfileorcl.ora' from pfile='/home/oracle/rman_bak/initorcl.ora';
File created.

ASMCMD> pwd
+DATA/ORCL/PARAMETERFILE
ASMCMD> ls
spfileorcl.ora

SQL> shutdown abort;
ORACLE instance shut down.

[oracle@rac1 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jan 3 13:09:48 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  213909504 bytes
Fixed Size                  2095152 bytes
Variable Size             125831120 bytes
Database Buffers           79691776 bytes
Redo Buffers                6291456 bytes

[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Jan 3 13:15:05 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: orcl (not mounted)
RMAN> restore controlfile from '/home/oracle/rman_bak/migrate.ctl';

Starting restore at 2012-01-03 13:15:11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 instance=orcl1 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output filename=+DATA/orcl/controlfile/control01.ctl
output filename=+FRA/orcl/controlfile/control02.ctl
Finished restore at 2012-01-03 13:15:19

[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[oracle@rac1 ~]$ asmcmd
ASMCMD> pwd
+data/orcl/controlfile
ASMCMD> ls
control01.ctl
current.263.771599715
ASMCMD> cd +fra/orcl/controlfile
ASMCMD> ls
control02.ctl
current.260.771599717

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  213909504 bytes
Fixed Size                  2095152 bytes
Variable Size             125831120 bytes
Database Buffers           79691776 bytes
Redo Buffers                6291456 bytes
Database mounted.

SQL> select host_name,status,thread# from gv$instance;

HOST_NAME            STATUS                  THREAD#
-------------------- -------------------- ----------
rac1.yang.com        MOUNTED                       1

[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Jan 3 17:29:22 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1299224612, not open)

RMAN> run {
2> set newname for datafile 1 to '+DATA/orcl/datafile/system01.dbf';
3> set newname for datafile 2 to '+DATA/orcl/datafile/undotbs01.dbf';
4> set newname for datafile 3 to '+DATA/orcl/datafile/sysaux01.dbf';
5> set newname for datafile 4 to '+DATA/orcl/datafile/users01.dbf';
6> set newname for datafile 5 to '+DATA/orcl/datafile/example01.dbf';
7> set newname for datafile 6 to '+DATA/orcl/datafile/exp_rac01.dbf';
8> set newname for datafile 7 to '+DATA/orcl/datafile/exp_rac_index01.dbf';
9> set newname for datafile 8 to '+DATA/orcl/datafile/exp_rac1_01.dbf';
10> set newname for datafile 9 to '+DATA/orcl/datafile/exp_rac1_index_01.dbf';
11> set newname for tempfile 1 to '+DATA/orcl/tempfile/temp01.dbf';
12> restore database;
13> switch datafile all;
14> switch tempfile all;
15> }

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    440      SYSTEM               ***     +DATA/orcl/datafile/system01.dbf
2    30       UNDOTBS1             ***     +DATA/orcl/datafile/undotbs01.dbf
3    250      SYSAUX               ***     +DATA/orcl/datafile/sysaux01.dbf
4    5        USERS                ***     +DATA/orcl/datafile/users01.dbf
5    100      EXAMPLE              ***     +DATA/orcl/datafile/example01.dbf
6    410      EXP_RAC              ***     +DATA/orcl/datafile/exp_rac01.dbf
7    300      EXP_RAC_INDEX        ***     +DATA/orcl/datafile/exp_rac_index01.dbf
8    300      EXP_RAC1             ***     +DATA/orcl/datafile/exp_rac1_01.dbf
9    300      EXP_RAC1_INDEX       ***     +DATA/orcl/datafile/exp_rac1_index_01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    0        TEMP                 32767       +DATA/orcl/tempfile/temp01.dbf

ASMCMD> pwd
+data/orcl/datafile
ASMCMD> ls
EXAMPLE.268.771620011
EXP_RAC.260.771620003
EXP_RAC1.262.771620007
EXP_RAC1_INDEX.266.771620007
EXP_RAC_INDEX.261.771620003
SYSAUX.267.771620009
SYSTEM.297.771619999
UNDOTBS1.305.771620015
USERS.309.771620065
example01.dbf
exp_rac01.dbf
exp_rac1_01.dbf
exp_rac1_index_01.dbf
exp_rac_index01.dbf
sysaux01.dbf
system01.dbf
undotbs01.dbf
users01.dbf

RMAN> recover database;
Starting recover at 2012-01-03 18:59:41
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=20
channel ORA_DISK_1: reading from backup piece /home/oracle/rman_bak/orcl_09mvru2e_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rman_bak/orcl_09mvru2e_1_1 tag=TAG20120103T183453
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=+FRA/orcl/archivelog/1_20_771443882.arc thread=1 sequence=20
unable to find archive log
archive log thread=1 sequence=21
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/03/2012 18:59:47
RMAN-06054: media recovery requesting unknown log: thread 1 seq 21 lowscn 479325

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ----------------------------------------
         3 /u01/app/oracle/oradata/orcl/redo03.log
         2 /u01/app/oracle/oradata/orcl/redo02.log
         1 /u01/app/oracle/oradata/orcl/redo01.log

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '+DATA/orcl/onlinelog/redo01.dbf';
Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '+DATA/orcl/onlinelog/redo02.dbf';
Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '+DATA/orcl/onlinelog/redo03.dbf';
Database altered.

SQL> alter database add logfile thread 2 group 4 '+DATA' size 50M;
Database altered.

SQL> alter database add logfile thread 2 group 5 '+DATA' size 50M;
Database altered.

SQL> alter database add logfile thread 2 group 6 '+DATA' size 50M;
Database altered.

SQL> select thread#,bytes/(1024*1024),status from v$log;

   THREAD# BYTES/(1024*1024) STATUS
---------- ----------------- ------------------------------------------------
         1                50 INACTIVE
         1                50 ACTIVE
         1                50 CURRENT
         2                50 UNUSED
         2                50 UNUSED
         2                50 UNUSED

6 rows selected.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 479325 generated at 01/03/2012 18:34:52 needed for thread 1
ORA-00289: suggestion : +FRA/orcl/archivelog/1_21_771443882.arc
ORA-00280: change 479325 for thread 1 is in sequence #21

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.

SQL> select open_mode,name from gv$database;

OPEN_MODE                      NAME
------------------------------ ---------------------------
READ WRITE                     ORCL

SQL> select comp_name,version,status from dba_registry;

COMP_NAME                      VERSION              STATUS
------------------------------ -------------------- --------------------
Oracle Database Catalog Views  10.2.0.5.0           VALID
Oracle Database Packages and T 10.2.0.5.0           VALID
ypes

Oracle Workspace Manager       10.2.0.5.0           VALID
JServer JAVA Virtual Machine   10.2.0.5.0           VALID
Oracle XDK                     10.2.0.5.0           VALID
Oracle Database Java Packages  10.2.0.5.0           VALID
Oracle Expression Filter       10.2.0.5.0           VALID
Oracle Data Mining             10.2.0.5.0           VALID
Oracle Text                    10.2.0.5.0           VALID

COMP_NAME                      VERSION              STATUS
------------------------------ -------------------- --------------------
Oracle XML Database            10.2.0.5.0           VALID
Oracle Rules Manager           10.2.0.5.0           VALID
Oracle interMedia              10.2.0.5.0           VALID
OLAP Analytic Workspace        10.2.0.5.0           VALID
Oracle OLAP API                10.2.0.5.0           VALID
OLAP Catalog                   10.2.0.5.0           VALID
Spatial                        10.2.0.5.0           VALID
Oracle Enterprise Manager      10.2.0.5.0           VALID

SQL> alter system set thread=1 scope=spfile sid='orcl1';
System altered.

SQL> alter system set thread=2 scope=spfile sid='orcl2';
System altered.

SQL> select bytes/(1024*1024) MB from dba_data_files where tablespace_name='UNDOTBS1';

        MB
----------
        30

SQL> create undo tablespace undotbs2 datafile '+DATA/ORCL/DATAFILE/undotbs02.dbf' size 30M;
Tablespace created.

SQL> alter system set undo_tablespace=undotbs2 sid='orcl2';
System altered.

SQL> alter database enable thread 2;
Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  213909504 bytes
Fixed Size                  2095152 bytes
Variable Size             125831120 bytes
Database Buffers           79691776 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.

SQL> select instance_number,instance_name,host_name ,status from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME        HOST_NAME            STATUS
--------------- -------------------- -------------------- --------------------
              1 orcl1                rac1.yang.com        OPEN
              2 orcl2                rac2.yang.com        OPEN

SQL> select * from test1.migrate;

A
------------------------------------------------------------
successful

SQL> col file_name format a40
SQL> col tablespace_name format a20
SQL> select file_id,file_name,tablespace_name from dba_data_files order by 1;

   FILE_ID FILE_NAME                                TABLESPACE_NAME
---------- ---------------------------------------- --------------------
         1 +DATA/orcl/datafile/system01.dbf         SYSTEM
         2 +DATA/orcl/datafile/undotbs01.dbf        UNDOTBS1
         3 +DATA/orcl/datafile/sysaux01.dbf         SYSAUX
         4 +DATA/orcl/datafile/users01.dbf          USERS
         5 +DATA/orcl/datafile/example01.dbf        EXAMPLE
         6 +DATA/orcl/datafile/exp_rac01.dbf        EXP_RAC
         7 +DATA/orcl/datafile/exp_rac_index01.dbf  EXP_RAC_INDEX
         8 +DATA/orcl/datafile/exp_rac1_01.dbf      EXP_RAC1
         9 +DATA/orcl/datafile/exp_rac1_index_01.db EXP_RAC1_INDEX
           f

   FILE_ID FILE_NAME                                TABLESPACE_NAME
---------- ---------------------------------------- --------------------
        10 +DATA/orcl/datafile/undotbs02.dbf        UNDOTBS2

SQL> @$ORACLE_HOME/rdbms/admin/catclust.sql;
[oracle@rac1 ~]$ crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host        
----------------------------------------------------------------------
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    0/5    0/0    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    0/5    0/0    ONLINE    ONLINE    rac1        
ora.rac1.ons   application    0/3    0/0    ONLINE    ONLINE    rac1        
ora.rac1.vip   application    0/0    0/0    ONLINE    ONLINE    rac1        
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    0/5    0/0    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    0/5    0/0    ONLINE    ONLINE    rac2        
ora.rac2.ons   application    0/3    0/0    ONLINE    ONLINE    rac2        
ora.rac2.vip   application    0/0    0/0    ONLINE    ONLINE    rac2      

[oracle@rac1 ~]$ srvctl add database -d orcl -o $ORACLE_HOME 
[oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl1 -n rac1
[oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl2 -n rac2

[oracle@rac1 ~]$ srvctl stop database -d orcl -o immediate
[oracle@rac1 ~]$ srvctl start database -d orcl
[oracle@rac1 ~]$ crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host        
----------------------------------------------------------------------
ora.orcl.db    application    0/0    0/1    ONLINE    ONLINE    rac1        
ora....l1.inst application    0/5    0/0    ONLINE    ONLINE    rac1        
ora....l2.inst application    0/5    0/0    ONLINE    ONLINE    rac2        
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    0/5    0/0    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    0/5    0/0    ONLINE    ONLINE    rac1        
ora.rac1.ons   application    0/3    0/0    ONLINE    ONLINE    rac1        
ora.rac1.vip   application    0/0    0/0    ONLINE    ONLINE    rac1        
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    0/5    0/0    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    0/5    0/0    ONLINE    ONLINE    rac2        
ora.rac2.ons   application    0/3    0/0    ONLINE    ONLINE    rac2        
ora.rac2.vip   application    0/0    0/0    ONLINE    ONLINE    rac2