duplicate 数据库 from backupset [oracle 11.2.0.3 + asm] => [oracle 11.2.0.3 + fs ]详解数据库

参考 rhel64_11gr2_asm.txt 安装好另一台机器的rhel6.4,gi software,rdbms software。

vi /etc/hosts 

10.1.1.35 asmnode

10.1.1.36 asmnodedup

在asmnode 节点 查看initdbasm.ora 文件,确保target database 处于归档模式,否则duplicate 会报错 

[[email protected] dbs]$ pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

[[email protected] dbs]$ ls -l

total 28

-rw-rw—- 1 oracle asmadmin 1544 Apr 25 21:49 hc_dbasm.dat

-rw-r—– 1  oracle  oinstall            40 Apr 25 21:49 initdbasm.ora

-rw-r–r– 1  oracle  oinstall       2851 May 15  2009 init.ora

-rw-r—– 1  oracle  asmadmin     24 Apr 25 21:06  lkDBASM

-rw-r—– 1  oracle  oinstall       1536 Apr 25 21:06 orapwdbasm

[[email protected] dbs]$ cat initdbasm.ora 

SPFILE=’+DG_DATA/dbasm/spfiledbasm.ora’

在asmnode 节点 用oracle 用户创建 参数文件 abc.ora 文件

[[email protected] dbs]$ sqlplus / as sysdba;

sql> create pfile=’?/dbs/abc.ora’ from memory;

在asmnode 节点 用oracle 用户创建备份集

备份数据库及控制文件及归档文件,plus archivelog 紧接着不能是 all

run {

 allocate channel ch00 device type disk;

 sql ‘alter system checkpoint’;

 backup as compressed backupset database 

 include current controlfile format ‘/u01/backup/DB_%T_%U’ 

 
plus archivelog delete all input format ‘/u01/backup/ARCH_%T_%U’;

 release channel ch00;

}

单纯归档备份, archivelog 必须紧接着 all 

run {

 allocate channel ch00 device type disk;

 sql ‘alter system checkpoint’;

 
backup as compressed backupset archivelog all delete all input format ‘/u01/backup/arch_%T_%U’;

 release channel ch00;

}

备份过程中均有备份集的存放路径输出

查看备份集

[[email protected] backup]$ cd /u01/backup/

[[email protected] backup]$ ls -l

total 251104

-rw-r—– 1 oracle asmadmin    
42496 May 11 16:48 ARCH_20160511_24r5b7av_1_1

-rw-r—– 1 oracle asmadmin               2560 May 11 16:49 ARCH_20160511_27r5b7cq_1_1

-rw-r—– 1 oracle asmadmin   255967232 May 11 16:49 DB_20160511_25r5b7b1_1_1

-rw-r—– 1 oracle asmadmin  
   1114112 May 11 16:49 DB_20160511_26r5b7co_1_1

把备份集拷贝到asmnodedup 节点的相同路径下,如果不是相同路径下,可通过ln -s 来解决

[[email protected] backup]$ scp /u01/backup/ARCH* [email protected]:/u01/backup/

[[email protected] backup]$ scp /u01/backup/DB* [email protected]:/u01/backup/




在 asmnodedup 节点 创建pfile参数文件

export ORACLE_SID = dbasmd

[[email protected] dbs]$ vi /u01/app/grid/product/11.2.0/grid_1/dbs/initdbasmd.ora

*.audit_file_dest=’/u01/app/oracle/admin/dbasmd/adump’

*.audit_trail=’DB’

*.compatible=’11.2.0.0.0′

*.control_files=’/u01/app/oracle/oradata/dbasmd/current.260.911598793’#Set by RMAN

*.core_dump_dest=’/u01/app/oracle/diag/rdbms/dbasmd/dbasmd/cdump’

*.db_block_size=8192

*.db_domain=”

*.db_name=’DBASMD’#Reset to original value by RMAN

*.diagnostic_dest=’/u01/app/oracle’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbasmXDB)’

*.log_buffer=7020544# log buffer update

*.memory_target=1536M

*.open_cursors=300

*.optimizer_dynamic_sampling=2

*.optimizer_mode=’ALL_ROWS’

*.plsql_warnings=’DISABLE:ALL’# PL/SQL warnings at init.ora

*.processes=300

*.query_rewrite_enabled=’TRUE’

*.remote_login_passwordfile=’EXCLUSIVE’

*.resource_manager_plan=”

*.result_cache_max_size=3936K

*.sessions=472

*.skip_unusable_indexes=TRUE

*.undo_tablespace=’UNDOTBS1′

#*.db_create_file_dest=’/u01/app/oracle/oradata/dbasmd/’

*.db_file_name_convert=(‘+DG_DATA/dbasm/’,’/u01/app/oracle/oradata/dbasmd/’)
*.log_file_name_convert=(‘+DG_DATA/dbasm/’,’/u01/app/oracle/oradata/dbasmd/’)


需要修改db_file_name_convert,log_file_name_convert

或者直接设定 db_create_file_dest

在 asmnodedup 节点 创建必要的目录

mkdir -p /u01/app/oracle/admin/dbasmd/adump

mkdir -p /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace

mkdir -p /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/cdump

mkdir -p /u01/app/oracle/oradata/dbasmd/{controlfile,datafile,tempfile,onlinelog}

需要保证target和auxiliary 库的sys用户口令相同。

从asmnode 拷贝原数据库的密码文件到 asmnodedup 节点 oracle 用户 $ORACLE_HOME/dbs 目录下,然后重命名

[[email protected] dbs]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwdbasm [email protected]:/u01/app/oracle/product/11.2.0/db_1/dbs 

[[email protected] dbs]$ mv orapwdbasm orapwdbasmd

或者在 asmnodedup 使用 orapwd 创建一个和target database 的sys一样密码的密码文件。

在 asmnodedup 节点 grid 用户 用asmcd 查看一下

[[email protected] bin]$ ./asmcmd

ASMCMD> ls -l

State    Type    Rebal  Name

MOUNTED  EXTERN  N      DG_DATA/

静态监听,两个节点都要添加


asmnode 节点  grid 用户下添加
静态监听

[[email protected] admin]$ vi /u01/app/grid/product/11.2.0/grid_1/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

       (SID_DESC =

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

          (SID_NAME =dbasm)

        )

  )


asmnodedup 节点  grid 用户下添加
静态监听

[[email protected] admin]$ vi /u01/app/grid/product/11.2.0/grid_1/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

        (SID_DESC =

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

           (SID_NAME =dbasmd)

         )

  )

在 asmnode, asmnodedup 节点 重启监听,看如输出状态为
UNKNOWN 就表示静态监听已添加成功  

[[email protected] admin]$ lsnrctl stop

[[email protected] admin]$ lsnrctl start

Services Summary…

Service “dbasmd” has 1 instance(s).

  Instance “dbasmd”, status
UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

  

在 asmnode,asmnodedup 节点, oracle 用户下 添加 tnsnames.ora 文件

tns_dbasm =

  (DESCRIPTION =

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

    (CONNECT_DATA =

       (SERVER = DEDICATED)

       (SERVICE_NAME = dbasm)

    )

   )

tns_dbasmd =                 

  (DESCRIPTION =

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

    (CONNECT_DATA =

       (SERVER = DEDICATED)

       (SERVICE_NAME = dbasmd)

    )

)  

在 asmnodedup 节点 oracle 用户  

[[email protected] dbs]$
sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 6 15:52:22 2016

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

SQL> conn / as sysdba;

Connected to an idle instance.

SQL>
startup nomount pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initdbasmd.ora’;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size
   2228784 bytes

Variable Size
 956304848 bytes

Database Buffers
 637534208 bytes

Redo Buffers
   7344128 bytes

在 asmnodedup 节点 需要创建spfile,否则后面会报错 ORA-32001: write to SPFILE requested but no SPFILE is in use

SQL>
create spfile from  pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initdbasmd.ora’;

在 asmnodedup 节点 开始复制, auxiliary 需要启动到 nomount状态, 否则duplicate时就会报错

MAN-04006: error from auxiliary database: ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

[[email protected] dbs]$
rman target [email protected]_dbasm auxiliary [email protected]_dbasmd

Recovery Manager: Release 11.2.0.3.0 – Production on Fri May 6 16:12:29 2016

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

connected to target database: DBASM (DBID=2252470157)

connected to auxiliary database: DBASMD (not mounted)

RMAN>
duplicate target database to dbasmd ;

Starting Duplicate Db at 11-MAY-16

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:

{

   sql clone “create spfile from memory”;

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1603411968 bytes

Fixed Size                     2228784 bytes

Variable Size                956304848 bytes

Database Buffers             637534208 bytes

Redo Buffers                   7344128 bytes

contents of Memory Script:

{

   sql clone “alter system set  db_name = 

 ”DBASM” comment=

 ”Modified by RMAN duplicate” scope=spfile”;

   sql clone “alter system set  db_unique_name = 

 ”DBASMD” comment=

 ”Modified by RMAN duplicate” scope=spfile”;

   shutdown clone immediate;

   startup clone force nomount

   restore clone primary controlfile;

   alter clone database mount;

}

executing Memory Script

sql statement: alter system set  db_name =  ”DBASM” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set  db_unique_name =  ”DBASMD” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1603411968 bytes

Fixed Size                     2228784 bytes

Variable Size                956304848 bytes

Database Buffers             637534208 bytes

Redo Buffers                   7344128 bytes

Starting restore at 11-MAY-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=245 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/DB_20160511_26r5b7co_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/backup/DB_20160511_26r5b7co_1_1 tag=TAG20160511T164832

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/dbasmd/controlfile/current.260.911598793

Finished restore at 11-MAY-16

database mounted

contents of Memory Script:

{

   set until scn  1586002;

   set newname for datafile  1 to 

 “/u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185”;

   set newname for datafile  2 to 

 “/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189”;

   set newname for datafile  3 to 

 “/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191”;

   set newname for datafile  4 to 

 “/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201”;

   restore

   clone database

   ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 11-MAY-16

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201

channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/DB_20160511_25r5b7b1_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/backup/DB_20160511_25r5b7b1_1_1 tag=TAG20160511T164832

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05

Finished restore at 11-MAY-16

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=11 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185

datafile 2 switched to datafile copy

input datafile copy RECID=12 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189

datafile 3 switched to datafile copy

input datafile copy RECID=13 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191

datafile 4 switched to datafile copy

input datafile copy RECID=14 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201

contents of Memory Script:

{

   set until scn  1586002;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 11-MAY-16

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=113

channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/ARCH_20160511_27r5b7cq_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/backup/ARCH_20160511_27r5b7cq_1_1 tag=TAG20160511T164930

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_113_910127181.dbf thread=1 sequence=113

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_113_910127181.dbf RECID=32 STAMP=911600707

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

Finished recover at 11-MAY-16

Oracle instance started

Total System Global Area    1603411968 bytes

Fixed Size                     2228784 bytes

Variable Size                956304848 bytes

Database Buffers             637534208 bytes

Redo Buffers                   7344128 bytes

contents of Memory Script:

{

   sql clone “alter system set  db_name = 

 ”DBASMD” comment=

 ”Reset to original value by RMAN” scope=spfile”;

   sql clone “alter system reset  db_unique_name scope=spfile”;

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

sql statement: alter system set  db_name =  ”DBASMD” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1603411968 bytes

Fixed Size                     2228784 bytes

Variable Size                956304848 bytes

Database Buffers             637534208 bytes

Redo Buffers                   7344128 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE “DBASMD” RESETLOGS ARCHIVELOG 

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP   1 ( ‘/u01/app/oracle/oradata/dbasmd/onlinelog/group_1.257.910127185’ ) SIZE 50 M  REUSE,

  GROUP   2 ( ‘/u01/app/oracle/oradata/dbasmd/onlinelog/group_2.258.910127185’ ) SIZE 50 M  REUSE,

  GROUP   3 ( ‘/u01/app/oracle/oradata/dbasmd/onlinelog/group_3.259.910127185’ ) SIZE 50 M  REUSE

 DATAFILE

  ‘/u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185’

 CHARACTER SET ZHS16GBK

contents of Memory Script:

{

   set newname for tempfile  1 to 

 “/u01/app/oracle/oradata/dbasmd/tempfile/temp.263.910127193”;

   switch clone tempfile all;

   catalog clone datafilecopy  “/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189”, 

 “/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191”, 

 “/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201”;

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/dbasmd/tempfile/temp.263.910127193 in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189 RECID=1 STAMP=911600724

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191 RECID=2 STAMP=911600724

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201 RECID=3 STAMP=911600724

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=911600724 file name=/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=911600724 file name=/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=911600724 file name=/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201

contents of Memory Script:

{

   
Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 11-MAY-16

至此,复制完成,用sqlplus 登陆 

[[email protected] bin]$
sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 9 16:55:37 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL>
select sysdate from dual;

SYSDATE

————

11-MAY-16

至此 duplicate 数据库完成

/*********************************************************************************/

原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/3907.html

(0)
上一篇 2021年7月16日
下一篇 2021年7月16日

相关推荐

发表回复

登录后才能评论