自律自由

yaogang

0%

duplicate db from active database/duplicate复制数据库

发表于 更新于 分类于

源库需要开启归档，不需要备份，不会影响源库（当然，一点点clone性能除外）

channel使用target库，可以配置 parallelism

环境信息

1
2
3
4
5
6
7
8
9
source db:

sid=SRM
db_name = SRM
db_file_path = /u01/app/oracle/oradata/SRM/
sys_pwd = oracle
oracle_version = Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
服务器资源 = 4核4G内存40G磁盘
OS = CentOS Linux release 7.6.1810 (Core)

###操作步骤
####1. 启动auxiliary instance

1）. 新建静态listener – 注意 sid 区分大小写，一定要和ORACLE_SID一样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
lsnrctl stop
vim listener.ora
---------------------------------------------------------------------------------------
LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=SRM)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=SRM))
    (SID_DESC=
      (GLOBAL_DBNAME=SRM2)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=SRM2))
  )
---------------------------------------------------------------------------------------
lsnrctl start
lsnrctl status

2）增加tns

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
---------------------------------------------------------------------------------------
SRM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SRM)
    )
  )
SRM2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SRM2)
    )
  )
---------------------------------------------------------------------------------------

3）复制密码文件，或者新建

1
[oracle@yg-test dbs]$ cp orapwSRM orapwSRM2

*4）创建pfile并修改启动nomount, 注意: 如果自己创建pfile要加上：.compatible=’11.2.0.4.0’**

1
SQL> create pfile='/home/oracle/dup_pfile.ora' from spfile;

5）修改dup_pfile.ora中的：

1
2
3
*.db_name='SRM2'
*.audit_file_dest='/u01/app/oracle/admin/SRM2/adump'
*.control_files='/u01/app/oracle/oradata/SRM2/control01.ctl','/u01/app/oracle/oradata/SRM2/control02.ctl'

6）添加参数

1
2
db_file_name_convert='/u01/app/oracle/oradata/SRM','/u01/app/oracle/oradata/SRM2'
log_file_name_convert='/u01/app/oracle/oradata/SRM','/u01/app/oracle/oradata/SRM2'

7）新建dup库的新文件夹：

1
2
mkdir -p /u01/app/oracle/admin/SRM2/adump /u01/app/oracle/oradata/SRM2

####2. 开始基于源库duplicate

1）修改环境变量ORACLE_SID: SRM –> SRM2

1
2
3
export ORACLE_SID=SRM2
[oracle@yg-test ~]$ echo $ORACLE_SID
SRM2

2）以nomount模式启动：

1
2
3
[oracle@yg-test ~]$ sqlplus / as sysdba
SQL> startup nomount pfile=/home/oracle/dup_pfile.ora;
SQL> exit

3）检查实例进程

1
2
3
[oracle@yg-test ~]$ ps -ef |grep smon
oracle   27297     1  0 01:54 ?        00:00:01 ora_smon_SRM
oracle   46994     1  0 07:21 ?        00:00:00 ora_smon_SMR2

4）检查远程sys登录 – 注意都必须是 ： EXCLUSIVE

1
2
3
4
5
6
7
8
[oracle@yg-test ~]$ sqlplus / as sysdba
SQL> set line 140
SQL> show parameter remote_login_passwordfile

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
remote_login_passwordfile            string                            EXCLUSIVE

5）测试source db 和 aux db 的sys远程登录,都要正常登录，不能为idle状态

1
2
3
sqlplus sys/oracle@srm as sysdba
sqlplus sys/oracle@srm2 as sysdba

6）rman登录source库

1
2
3
4
5
6
[oracle@yg-test ~]$ rman target /
connected to target database: SRM (DBID=2040369693)

RMAN> configure device type disk parallelism 4;
RMAN> connect auxiliary sys/oracle@srm2
connected to auxiliary database: SRM (not mounted)  -- 注意以后nomount启动，rman连上为 not mounted

7）开始复制 – 注意: 如果ctronl_file已经拷贝，由于其他原因再次拷贝，请先备份删除控制文件

1
RMAN> duplicate target database TO SRM2 from active database nofilenamecheck;

8）重建spfile

1
2
3
4
5
6
7
8
SQL> shu immediate;
SQL>  create spfile from pfile='/home/oracle/dup_pfile.ora';
SQL> startup
SQL> show parameter spfile;
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
spfile                               string                            /u01/app/oracle/product/11.2.0
                                                                       /dbhome_1/dbs/spfileSRM2.ora

9）校验 –查到之前数据，成功

1
2
3
4
5
SQL> select * from reco;
        ID
----------
         1