Oracle 11g Data Guard duplicate from active database

Oracle 11g Data Guard duplicate from active database

1.环境

oracle:11.2.0.3.0

Primary

ip:192.168.222.110

DB_UNIQUE_NAME=lucyne_pd

standby

ip:192.168.222.111

DB_UNIQUE_NAME=lucyne_st

2.主库操作

2.1 启动FORCE LOGGING

SQL> ALTER DATABASE FORCE LOGGING;
Database altered.

2.2 设置归档

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 364081152 bytes
Fixed Size 1344988 bytes
Variable Size 234883620 bytes
Database Buffers 121634816 bytes
Redo Buffers 6217728 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

2.3设置pfile参数

DB_UNIQUE_NAME=lucyne_pd
LOG_ARCHIVE_CONFIG='DG_CONFIG=(lucyne_pd,lucyne_st)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/admin/lucyne/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lucyne_pd'
LOG_ARCHIVE_DEST_2= 'SERVICE=lucyne_st VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lucyne_st'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=lucyne_st
FAL_CLIENT='lucyne_pd'
LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/lucyne/','/u01/app/oracle/oradata/lucyne/'
STANDBY_FILE_MANAGEMENT=AUTO

2.4使用新pfile

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initlucyne.ora';
ORACLE instance started.

Total System Global Area 364081152 bytes
Fixed Size 1344988 bytes
Variable Size 234883620 bytes
Database Buffers 121634816 bytes
Redo Buffers 6217728 bytes
Database mounted.
Database opened.

2.5设置oracle net

[oracle@db11g admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db11g)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = lucyne)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = lucyne)
    )
  )
[oracle@db11g admin]$ cat tnsnames.ora
lucyne_pd =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.222.110)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = lucyne)
    )
  )

lucyne_st =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.222.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = lucyne)
    )
  )

[oracle@db11g admin]$ scp listener.ora tnsnames.ora 192.168.222.111:/u01/app/oracle/product/11.2.0/db_1/network/admin
oracle@192.168.222.111's password:
listener.ora 100% 540 0.5KB/s 00:00
tnsnames.ora 100% 377 0.4KB/s 00:00

3 备库设置
3.1建立目录

[oracle@db11g oracle]$ mkdir fast_recovery_area
[oracle@db11g oracle]$ mkdir cfgtoollogs
[oracle@db11g oracle]$ mkdir -p oradata/lucyne
[oracle@db11g oracle]$ mkdir -p admin/lucyne/{a,dp}dump
[oracle@db11g oracle]$ mkdir -p admin/lucyne/pfile
[oracle@db11g oracle]$ mkdir -p admin/lucyne/arch
[oracle@db11g oracle]$ tree -d admin/
admin/
`-- lucyne
    |-- adump
    |-- arch
    |-- dpdump
    `-- pfile

3.2copy pfile orapwd

[oracle@db11g dbs]$ scp initlucyne.ora orapwlucyne 192.168.222.111:/u01/app/oracle/product/11.2.0/db_1/dbs
The authenticity of host '192.168.222.111 (192.168.222.111)' can't be established.
RSA key fingerprint is 86:77:1d:82:13:9b:1d:2a:ed:f8:1e:c4:33:2b:3a:a4.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.222.111' (RSA) to the list of known hosts.
oracle@192.168.222.111's password:
initlucyne.ora 100% 1502 1.5KB/s 00:00
orapwlucyne 100% 1536 1.5KB/s 00:00

3.3修改pfile

DB_UNIQUE_NAME=lucyne_st
LOG_ARCHIVE_CONFIG='DG_CONFIG=(lucyne_pd,lucyne_st)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/admin/lucyne/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lucyne_st'
LOG_ARCHIVE_DEST_2= 'SERVICE=lucyne_pd VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lucyne_pd'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=lucyne_pd
FAL_CLIENT='lucyne_st'
LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/lucyne/','/u01/app/oracle/oradata/lucyne/'
STANDBY_FILE_MANAGEMENT=AUTO

3.5创建spfile并启动

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

File created.

SQL> startup nomount ;
ORACLE instance started.

Total System Global Area 364081152 bytes
Fixed Size 1344988 bytes
Variable Size 234883620 bytes
Database Buffers 121634816 bytes
Redo Buffers 6217728 bytes

3.6使用rman dupilicate

[oracle@db11g ~]$ rman target sys@lucyne_pd auxiliary sys@lucyne_st nocatalog 

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jan 7 14:37:53 2012

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

target database Password:
connected to target database: LUCYNE (DBID=3496816922)
using target database control file instead of recovery catalog
auxiliary database Password:
connected to auxiliary database: LUCYNE (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 07-JAN-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwlucyne' auxiliary format
 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwlucyne' ;
}
executing Memory Script

Starting backup at 07-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
Finished backup at 07-JAN-12

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/lucyne/control01.ctl';
   restore clone controlfile to '/u01/app/oracle/fast_recovery_area/lucyne/control02.ctl' from
 '/u01/app/oracle/oradata/lucyne/control01.ctl';
}
executing Memory Script

Starting backup at 07-JAN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_lucyne.f tag=TAG20120107T143807 RECID=10 STAMP=771950288
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 07-JAN-12

Starting restore at 07-JAN-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 07-JAN-12

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
 "/u01/app/oracle/oradata/lucyne/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile 1 to
 "/u01/app/oracle/oradata/lucyne/system01.dbf";
   set newname for datafile 2 to
 "/u01/app/oracle/oradata/lucyne/sysaux01.dbf";
   set newname for datafile 3 to
 "/u01/app/oracle/oradata/lucyne/undotbs01.dbf";
   set newname for datafile 4 to
 "/u01/app/oracle/oradata/lucyne/users01.dbf";
   set newname for datafile 5 to
 "/u01/app/oracle/oradata/lucyne/example01.dbf";
   backup as copy reuse
   datafile 1 auxiliary format
 "/u01/app/oracle/oradata/lucyne/system01.dbf" datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/lucyne/sysaux01.dbf" datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/lucyne/undotbs01.dbf" datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/lucyne/users01.dbf" datafile
 5 auxiliary format
 "/u01/app/oracle/oradata/lucyne/example01.dbf" ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/lucyne/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 07-JAN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/lucyne/system01.dbf
output file name=/u01/app/oracle/oradata/lucyne/system01.dbf tag=TAG20120107T143822
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:33
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/lucyne/sysaux01.dbf
output file name=/u01/app/oracle/oradata/lucyne/sysaux01.dbf tag=TAG20120107T143822
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/lucyne/example01.dbf
output file name=/u01/app/oracle/oradata/lucyne/example01.dbf tag=TAG20120107T143822
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/lucyne/undotbs01.dbf
output file name=/u01/app/oracle/oradata/lucyne/undotbs01.dbf tag=TAG20120107T143822
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/lucyne/users01.dbf
output file name=/u01/app/oracle/oradata/lucyne/users01.dbf tag=TAG20120107T143822
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 07-JAN-12

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=10 STAMP=771951051 file name=/u01/app/oracle/oradata/lucyne/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=771951051 file name=/u01/app/oracle/oradata/lucyne/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=771951051 file name=/u01/app/oracle/oradata/lucyne/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=771951051 file name=/u01/app/oracle/oradata/lucyne/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=771951051 file name=/u01/app/oracle/oradata/lucyne/example01.dbf
Finished Duplicate Db at 07-JAN-12

4.验证备库

SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

4.1在主库建立表

SQL> create table t as select * from emp;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> delete from t where t.empno=7934;
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.

4.2 在备库查询

SQL> select count(1) from t;
  COUNT(1)
----------
        13

至此oracle 11g datagurad 搭建完成。

http://www.isdba.net/2012/03/21/oracle-11g-data-guard-duplicate-from-active-database.html