oracle 11g duplicate active database 建立物理DG

发布时间：2020-07-23 23:59:25 来源：网络阅读：436 作者：明天的理想栏目：关系型数据库

环境：

primary:

OS: Red Hat Enterprise　5.8

IP: 10.131.119.118

DBVersion: 11.2.0.3.0

db_name: orcl

db_unique_name: orcl

standby:

OS: Red Hat Enterprise　5.8

IP: 10.131.119.119

DBVersion: 11.2.0.3.0

db_name: orcl

db_unique_name: std

步骤1：将主库设置为归档模式与force_logging模式

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database archivelog;

SQL>alter database open;

SQL>alter database force logging;

SQL> select DBID,NAME,LOG_MODE,FORCE_LOGGING from v$database;

DBID NAME LOG_MODE FOR

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

1414106639 ORCL ARCHIVELOG YES

步骤2：将主库的pfile文件和密码文件拷备到备库

[oracle@CS-SI-DG01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:05:21 2016

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile from spfile;

File created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@CS-SI-DG01 dbs]$ scp initorcl.ora 10.131.119.119:/u2/oracle/product/11.2.0/dbhome_1/dbs/initstd.ora

oracle@10.131.119.119's password:

stty: standard input: Invalid argument

initorcl.ora 100% 1112 1.1KB/s 00:00

[oracle@CS-SI-DG01 dbs]$ scp orapworcl 10.131.119.119:/u2/oracle/product/11.2.0/dbhome_1/dbs/orapwstd

oracle@10.131.119.119's password:

stty: standard input: Invalid argument

orapworcl 100% 1536 1.5KB/s 00:00

步骤3：设置主库的监听与TNS

监听：

[oracle@CS-SI-DG01 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u2/oracle/product/11.2.0/dbhome_1)

(SID_NAME = orcl)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

ADR_BASE_LISTENER = /u2/oracle

[oracle@CS-SI-DG01 ~]$ lsnrctl reload

TNS：

[oracle@CS-SI-DG01 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

std =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = std)

)

步骤4：修改主库的参数

[oracle@CS-SI-DG01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:19:40 2016

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set log_archive_config='dg_config=(orcl,std)';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=std ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std';

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> alter system set fal_server='std';

System altered.

SQL> alter system set fal_client='orcl';

System altered.

SQL> alter system set standby_file_management='auto';

System altered.

注：修改好参数后重新启动数据库

步骤5：修改备库参数文件

[oracle@CS-SI-DG02 dbs]$ cat initstd.ora

*._undo_autotune=TRUE

*.audit_file_dest='/u2/oracle/admin/std/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/oradata/std/control01.ctl','/oradata/std/control02.ctl'#Restore Controlfile

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='/u2/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=8589934592

*.diagnostic_dest='/u2/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdXDB)'

*.job_queue_processes=1000

*.log_archive_dest_1='location=/oradata/arch/std'

*.log_archive_dest_10=' '

*.memory_max_target=0

*.memory_target=0

*.open_cursors=300

*.pga_aggregate_target=171966464

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.session_cached_cursors=240

*.sga_target=335544320

*.undo_retention=60

*.undo_tablespace='UNDOTBS2'

*.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

*.log_archive_config='dg_config=(orcl,std)'

*.log_archive_dest_state_2=enable

*.log_archive_dest_state_1=enable

*.fal_server='orcl'

*.fal_client='std'

*.standby_file_management='auto'

*.log_file_name_convert=('/oradata/orcl','/oradata/std')

*.db_file_name_convert=('/oradata/orcl','/oradata/std')

*.db_unique_name='std'

步骤6：在备库创建目录

[oracle@CS-SI-DG02 dbs]$ mkdir -p /u2/oracle/admin/std/adump

[oracle@CS-SI-DG02 dbs]$ mkdir -p /oradata/std/

[oracle@CS-SI-DG02 dbs]$ mkdir -p /oradata/arch/std

步骤7：设置备库的监听与TNS

监听：

[oracle@CS-SI-DG02 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = std)

(ORACLE_HOME = /u2/oracle/product/11.2.0/dbhome_1)

(SID_NAME = std)

)

ADR_BASE_LISTENER = /u2/oracle

STENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

[oracle@CS-SI-DG02 admin]$ lsnrctl reload

TNS：

[oracle@CS-SI-DG02 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

std =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = std)

)

步骤8：将备库启动到nomount状态

[oracle@CS-SI-DG02 admin]$ export ORACLE_SID=std

[oracle@CS-SI-DG02 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:56:58 2016

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 509411328 bytes

Fixed Size 2229824 bytes

Variable Size 285215168 bytes

Database Buffers 213909504 bytes

Redo Buffers 8056832 bytes

步骤9：在备库恢复数据库

[oracle@CS-SI-DG02 admin]$ rman target sys/oracle@orcl auxiliary sys/oracle@std nocatalog

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 27 17:30:29 2016

connected to target database: ORCL (DBID=1414106639)

using target database control file instead of recovery catalog

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 27-JAN-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=134 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u2/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format

'/u2/oracle/product/11.2.0/dbhome_1/dbs/orapwstd' ;

}

executing Memory Script

Starting backup at 27-JAN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 device type=DISK

Finished backup at 27-JAN-16

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/oradata/std/control01.ctl';

restore clone controlfile to '/oradata/std/control02.ctl' from

'/oradata/std/control01.ctl';

}

executing Memory Script

Starting backup at 27-JAN-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u2/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20160127T173104 RECID=25 STAMP=902251864

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 27-JAN-16

Starting restore at 27-JAN-16

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 27-JAN-16

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for tempfile 1 to

"/oradata/std/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/oradata/std/system01.dbf";

set newname for datafile 2 to

"/oradata/std/sysaux01.dbf";

set newname for datafile 3 to

"/oradata/std/test01.dbf";

set newname for datafile 4 to

"/oradata/std/users01.dbf";

set newname for datafile 6 to

"/oradata/std/undotbs02.dbf";

set newname for datafile 7 to

"/oradata/std/tbspart01.dbf";

set newname for datafile 8 to

"/oradata/std/tbspart02.dbf";

set newname for datafile 9 to

"/oradata/std/tbspart03.dbf";

set newname for datafile 11 to

"/oradata/std/assm01.dbf";

set newname for datafile 12 to

"/oradata/std/tbspart04.dbf";

set newname for datafile 13 to

"/oradata/std/rman01.dbf";

set newname for datafile 14 to

"/oradata/std/test1.dbf";

set newname for datafile 15 to

"/oradata/std/test2.dbf";

set newname for datafile 16 to

"/oradata/std/linktbs01.dbf";

set newname for datafile 17 to

"/oradata/std/ogg01.dbf";

set newname for datafile 18 to

"/oradata/std/tom101.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/oradata/std/system01.dbf" datafile

2 auxiliary format

"/oradata/std/sysaux01.dbf" datafile

3 auxiliary format

"/oradata/std/test01.dbf" datafile

4 auxiliary format

"/oradata/std/users01.dbf" datafile

6 auxiliary format

"/oradata/std/undotbs02.dbf" datafile

7 auxiliary format

"/oradata/std/tbspart01.dbf" datafile

8 auxiliary format

"/oradata/std/tbspart02.dbf" datafile

9 auxiliary format

"/oradata/std/tbspart03.dbf" datafile

11 auxiliary format

"/oradata/std/assm01.dbf" datafile

12 auxiliary format

"/oradata/std/tbspart04.dbf" datafile

13 auxiliary format

"/oradata/std/rman01.dbf" datafile

14 auxiliary format

"/oradata/std/test1.dbf" datafile

15 auxiliary format

"/oradata/std/test2.dbf" datafile

16 auxiliary format

"/oradata/std/linktbs01.dbf" datafile

17 auxiliary format

"/oradata/std/ogg01.dbf" datafile

18 auxiliary format

"/oradata/std/tom101.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/std/temp01.dbf in control file

executing command: SET NEWNAME

Starting backup at 27-JAN-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/oradata/orcl/sysaux01.dbf

output file name=/oradata/std/sysaux01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/oradata/orcl/system01.dbf

output file name=/oradata/std/system01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/oradata/orcl/undotbs02.dbf

output file name=/oradata/std/undotbs02.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/oradata/orcl/test01.dbf

output file name=/oradata/std/test01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00011 name=/oradata/orcl/assm01.dbf

output file name=/oradata/std/assm01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00017 name=/oradata/orcl/ogg01.dbf

output file name=/oradata/std/ogg01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00018 name=/oradata/orcl/tom101.dbf

output file name=/oradata/std/tom101.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/oradata/orcl/users01.dbf

output file name=/oradata/std/users01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00007 name=/oradata/orcl/tbspart01.dbf

output file name=/oradata/std/tbspart01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00008 name=/oradata/orcl/tbspart02.dbf

output file name=/oradata/std/tbspart02.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00009 name=/oradata/orcl/tbspart03.dbf

output file name=/oradata/std/tbspart03.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00012 name=/oradata/orcl/tbspart04.dbf

output file name=/oradata/std/tbspart04.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00013 name=/oradata/orcl/rman01.dbf

output file name=/oradata/std/rman01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00014 name=/oradata/orcl/test1.dbf

output file name=/oradata/std/test1.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00015 name=/oradata/orcl/test2.dbf

output file name=/oradata/std/test2.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00016 name=/oradata/orcl/linktbs01.dbf

output file name=/oradata/std/linktbs01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 27-JAN-16

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=25 STAMP=902252046 file name=/oradata/std/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=26 STAMP=902252046 file name=/oradata/std/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=27 STAMP=902252046 file name=/oradata/std/test01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=28 STAMP=902252046 file name=/oradata/std/users01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=29 STAMP=902252046 file name=/oradata/std/undotbs02.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=30 STAMP=902252046 file name=/oradata/std/tbspart01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=31 STAMP=902252046 file name=/oradata/std/tbspart02.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=32 STAMP=902252046 file name=/oradata/std/tbspart03.dbf

datafile 11 switched to datafile copy

input datafile copy RECID=33 STAMP=902252046 file name=/oradata/std/assm01.dbf

datafile 12 switched to datafile copy

input datafile copy RECID=34 STAMP=902252046 file name=/oradata/std/tbspart04.dbf

datafile 13 switched to datafile copy

input datafile copy RECID=35 STAMP=902252046 file name=/oradata/std/rman01.dbf

datafile 14 switched to datafile copy

input datafile copy RECID=36 STAMP=902252046 file name=/oradata/std/test1.dbf

datafile 15 switched to datafile copy

input datafile copy RECID=37 STAMP=902252046 file name=/oradata/std/test2.dbf

datafile 16 switched to datafile copy

input datafile copy RECID=38 STAMP=902252046 file name=/oradata/std/linktbs01.dbf

datafile 17 switched to datafile copy

input datafile copy RECID=39 STAMP=902252046 file name=/oradata/std/ogg01.dbf

datafile 18 switched to datafile copy

input datafile copy RECID=40 STAMP=902252046 file name=/oradata/std/tom101.dbf

Finished Duplicate Db at 27-JAN-16

步骤10：在备库创建standby日志

[oracle@CS-SI-DG02 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 18:06:26 2016

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE /oradata/std/redo03.log NO

2 ONLINE /oradata/std/redo02.log NO

3 ONLINE /oradata/std/redo01.log NO

3 rows selected.

SQL> alter database add standby logfile group 4 '/oradata/std/stb_redo04.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 5 '/oradata/std/stb_redo5.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 6 '/oradata/std/stb_redo06.log' size 50M

2 ;

Database altered.

SQL> alter database add standby logfile group 7 '/oradata/std/stb_redo07.log' size 50M;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE /oradata/std/redo03.log NO

2 ONLINE /oradata/std/redo02.log NO

3 ONLINE /oradata/std/redo01.log NO

4 STANDBY /oradata/std/stb_redo04.log NO

5 STANDBY /oradata/std/stb_redo5.log NO

6 STANDBY /oradata/std/stb_redo06.log NO

7 STANDBY /oradata/std/stb_redo07.log NO

7 rows selected.

步骤11：启动DG

[oracle@CS-SI-DG02 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 18:19:34 2016

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

向AI问一下细节

oracle 11g duplicate active database 建立物理DG

猜你喜欢

最新资讯

相关推荐

相关标签