11g資料庫遷移ASM

路途中的人2012發表於2016-01-18
資料庫ASM
1.遷移引數檔案

[oracle@PROD1 ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 2 20:34:02 2016


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> show parameter pfile;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilePROD1.ora
在+DATA磁碟組中建立對應的目錄PROD1；PROD1/parameter/;
[oracle@PROD1 ~]$ su - grid
Password: 
[grid@PROD1 ~]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd DATA/
ASMCMD> ls
ASM/
DB_UNKNOWN/
PROD1/
ASMCMD> cd PROD1
ASMCMD> ls
parameter/
ASMCMD> cd parameter/  
ASMCMD> pwd
+DATA/PROD1/parameter
ASMCMD> ls

ASMCMD> cp /u01/app/oracle/product/11.2.0//dbhome_1/dbs/spfilePROD1.ora .
copying /u01/app/oracle/product/11.2.0//dbhome_1/dbs/spfilePROD1.ora -> +DATA/PROD1/parameter/spfilePROD1.ora

ASMCMD> exit
[grid@PROD1 ~]$ su - oracle
Password: 
[oracle@PROD1 ~]$ cd $ORACLE_HOME/dbs
[oracle@PROD1 dbs]$ ls
fls  hc_+ASM.dat  hc_PROD1.dat  init+ASM.ora  init.ora  lkPROD1  orapwPROD1  spfilePROD1.ora
[oracle@PROD1 dbs]$ ls
fls  hc_+ASM.dat  hc_PROD1.dat  init+ASM.ora  init.ora  lkPROD1  orapwPROD1  spfilePROD1.ora
[oracle@PROD1 dbs]$ mv spfilePROD1.ora spfilePROD1.ora.bak

[oracle@PROD1 dbs]$ vi initPROD1.ora
SPFILE='+data/prod1/parameter/spfilePROD1.ora'

[oracle@PROD1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 2 20:44:03 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount force;
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size    2257800 bytes
Variable Size  536874104 bytes
Database Buffers  285212672 bytes
Redo Buffers    6586368 bytes
SQL> show parameter pfile;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string +DATA/prod1/parameter/spfilepr
od1.ora

遷移控制檔案
SQL> show parameter control_files;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files     string /u01/app/oracle/oradata/PROD1/
control01.ctl, /u01/app/oracle
/oradata/PROD1/control02.ctl
SQL> alter system set control_files='+data' scope=spfile;
System altered.
SQL> startup mount force;
ORACLE instance started.


Total System Global Area  830930944 bytes
Fixed Size    2257800 bytes
Variable Size  536874104 bytes
Database Buffers  285212672 bytes
Redo Buffers    6586368 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> ho rman target /


Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jan 2 20:51:00 2016


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: PROD1 (not mounted)


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


Starting restore at 02-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK


channel ORA_DISK_1: copied control file copy
output file name=+DATA/prod1/controlfile/current.260.900103889
Finished restore at 02-JAN-16

RMAN> exit
Recovery Manager complete.

SQL> show parameter control_files;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files     string +DATA/prod1/controlfile/curren
t.260.900103889

遷移資料檔案
SQL> alter database mount;

Database altered.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/system01.dbf
/u01/app/oracle/oradata/PROD1/sysaux01.dbf
/u01/app/oracle/oradata/PROD1/undotbs01.dbf
/u01/app/oracle/oradata/PROD1/users01.dbf

SQL> ho rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jan 2 20:58:57 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD1 (DBID=2121445908, not open)

RMAN> backup as copy database format '+data';

Starting backup at 02-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
output file name=+DATA/prod1/datafile/system.269.900104401 tag=TAG20160102T205959 RECID=1 STAMP=900104464
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
output file name=+DATA/prod1/datafile/sysaux.257.900104465 tag=TAG20160102T205959 RECID=2 STAMP=900104509
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
output file name=+DATA/prod1/datafile/undotbs1.256.900104511 tag=TAG20160102T205959 RECID=3 STAMP=900104512
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/prod1/controlfile/backup.270.900104513 tag=TAG20160102T205959 RECID=4 STAMP=900104515
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
output file name=+DATA/prod1/datafile/users.271.900104517 tag=TAG20160102T205959 RECID=5 STAMP=900104516
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 02-JAN-16
channel ORA_DISK_1: finished piece 1 at 02-JAN-16
piece handle=+DATA/prod1/backupset/2016_01_02/nnsnf0_tag20160102t205959_0.272.900104517 tag=TAG20160102T205959 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-JAN-16

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/prod1/datafile/system.269.900104401"
datafile 2 switched to datafile copy "+DATA/prod1/datafile/sysaux.257.900104465"
datafile 3 switched to datafile copy "+DATA/prod1/datafile/undotbs1.256.900104511"
datafile 4 switched to datafile copy "+DATA/prod1/datafile/users.271.900104517"

RMAN> recover database;

Starting recover at 02-JAN-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 02-JAN-16

遷移臨時表空間
SQL> alter database open RESETLOGS;

Database altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/temp01.dbf

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

Tablespace altered.

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

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/prod1/tempfile/temp.273.900104921

遷移日誌檔案


SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/redo03.log
/u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo01.log
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_online_log_dest_1='+data';

System altered.

SQL> ho vi redo.sql
SET SERVEROUTPUT ON;


DECLARE


   CURSOR rlc IS


      SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL


      FROM   V$LOG


      UNION


      SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL


      FROM   V$STANDBY_LOG


      ORDER BY 1;


   stmt     VARCHAR2(2048);


BEGIN


   FOR rlcRec IN rlc LOOP


      IF (rlcRec.srl = 'YES') THEN


         stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||


                 rlcRec.thr || ' SIZE ' || rlcRec.bytes;


         EXECUTE IMMEDIATE stmt;


         stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;


         EXECUTE IMMEDIATE stmt;


      ELSE


         stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||


                 rlcRec.thr || ' SIZE ' ||  rlcRec.bytes;


         EXECUTE IMMEDIATE stmt;


         BEGIN


            stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;


            DBMS_OUTPUT.PUT_LINE(stmt);


            EXECUTE IMMEDIATE stmt;


         EXCEPTION


            WHEN OTHERS THEN


               EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';


               EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';


               EXECUTE IMMEDIATE stmt;


         END;


      END IF;


   END LOOP;


END;


/
SQL> @redo.sql
ALTER DATABASE DROP LOGFILE GROUP 1
ALTER DATABASE DROP LOGFILE GROUP 2
ALTER DATABASE DROP LOGFILE GROUP 3


PL/SQL procedure successfully completed.

SQL> select name from v$controlfile
  2  union all
  3  select name from v$datafile
  4  union all
  5  select member from v$logfile;


NAME
--------------------------------------------------------------------------------
+DATA/prod1/controlfile/current.260.900103889
+DATA/prod1/datafile/system.269.900104401
+DATA/prod1/datafile/sysaux.257.900104465
+DATA/prod1/datafile/undotbs1.256.900104511
+DATA/prod1/datafile/users.271.900104517
+DATA/prod1/onlinelog/group_2.276.900105383
+DATA/prod1/onlinelog/group_1.275.900105377
+DATA/prod1/onlinelog/group_4.274.900105375


8 rows selected.
遷移完成；
























,

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29067253/viewspace-1978144/，如需轉載，請註明出處，否則將追究法律責任。

相關文章