TZDB RAC数据库迁移至新的NEWRAC TZDB

Contents

TZDB RAC数据库迁移至新的NEWRAC TZDB
环境信息
环境准备
迁移实施

环境信息

NBU Server : nbu-wg.qhlhfund.com
源主机: 10.0.10.111-112 / TZDB RAC (RHEL6.5, Oracle database 11.2.0.4）
目标主机: 10.0.10.151-152 / NEWTZDB RAC (RHEL7.5, Oracle database 11.2.0.4)
DBID=4092109955

注：DBID提前记录并修改为对应值

环境准备

requirements

Successful Netbackup backup of set.
Same version of database.
Record Source DBID/DATAFILE/TABLESPACE.
Install database software only on Destination client.
Install Netbackup client software on Destination client.

请确保数据库主机和应用服务器的网络配置是正确的

清除现有tzdb相关数据库配置

确认新的RAC环境演练测试数据库tzdb是否还需要, 如果不需要则drop之.

SSH 使用oracle用户登录目标主机10.0.10.151

Drop tzdb数据库

1. 停止所有实例

$ srvctl stop database -d tzdb

2. 在目标数据库中关闭cluster模式.

$ export ORACLE_SID=tzdb1
$ sqlplus as sysdba 

SQL> startup nomount
SQL> alter system set CLUSTER_DATABASE=FALSE scope=spfile;
SQL> shutdown immediate

3. 启动至restrict模式.

$ sqlplus as sysdba

SQL> startup mount restrict

4. 确认状态

SQL> SELECT logins,parallel FROM v$instance;

LOGINS               PARALLEL
----------------     -------------------
RESTRICTED           NO

5. Drop the database

$ rman target /
RMAN> drop database including backups;

在crs中清除tzdb配置

$ /u01/grid/app/11.2.0/bin/srvctl remove database -d tzdb

准备pfile文件以适用单实例环境

SSH使用oracle用户登录源数据库主机10.0.10.111

$ sqlplus / as sysdba
SQL> create pfile='/tmp/tzdb.pfile' from spfile;

修改相关参数

SSH 使用oracle用户登录目标主机10.0.10.151

export ORACLE_SID=tzdb1
cat > $ORACLE_HOME/dbs/init$ORACLE_SID.ora <<"EOF"
*.__db_cache_size=30786325577
*.__java_pool_size=469762048
*.__large_pool_size=67108864
*.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment
*.__pga_aggregate_target=32963873996
*.__sga_target=76879914598
*.__shared_io_pool_size=0
*.__shared_pool_size=4764729344
*.__streams_pool_size=134217728
*.audit_file_dest='/u01/oracle/app/admin/tzdb1/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB','EXTENDED'
*.cluster_database=false
*.compatible='11.2.0.4.0'
*.control_files='+DATA/tzdb/controlfile/current1','+TZ_RECO/tzdb/controlfile/current2'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='tzdb'
*.db_unique_name='tzdb'
*.db_recovery_file_dest='+TZ_RECO'
*.db_recovery_file_dest_size=192742891520
*.db_securefile='PERMITTED'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/oracle/app'
*.dispatchers=''
*.fal_client='TZDB'
*.fal_server='TZDBSTB'
*.log_archive_config='dg_config=(tzdb, tzdbstb)'
*.log_archive_dest_2='service=tzdbstb async valid_for=(online_logfiles,primary_role) db_unique_name=tzdbstb'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=30
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=3000
*.pga_aggregate_target=32963873996
*.processes=3000
*.remote_listener='tzrac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.session_cached_cursors=500
*.sessions=3305
*.sga_max_size=76879914598
*.sga_target=76879914598
*.standby_file_management='AUTO'
*.thread=1
*.undo_retention=14400
*.undo_tablespace='UNDOTBS1'
EOF

创建相关目录

SSH 使用oracle用户登录目标主机10.0.10.151

如果权限不足, 则需要切換为root用戶執行

$ mkdir -pv /u01/oracle/app/admin/tzdb1/adump

创建orapwd文件

SSH 使用oracle用户登录目标主机10.0.10.151-152

在两个节点中执行.

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID entries=10

迁移实施

如无特别说明, 以下迁移实施的操作都是在目标主机完成的.

即: 通过SSH客户端并使用oracle用户登录目标主机10.0.10.151

列出备份集

$ /usr/openv/netbackup/bin/bplist -C TZDB_4092109955 -t 4 -R -l / | head -30

比如最新的控制文件为: /ctrl_dTZDB_uht1s91i7_s20029_p1_t1136952903

恢复 controlfile

将controlfile替换为上一步中所列出的目标控制文件.

rman target / nocatalog <<"EOF"
spool log to Restore-Controlfile.log
SET DBID=4092109955
startup nomount;
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_CLIENT=TZDB_4092109955';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
restore controlfile from '/ctrl_dTZDB_uht1s91i7_s20029_p1_t1136952903'; ## 修改为实际的控制文件
ALTER DATABASE MOUNT;
RELEASE CHANNEL ch00;
}
spool log off
EOF

查看备份的归档

# Do a SET UNTIL to prevent recovery of the online logs

RMAN> list backup of archivelog all;

获取 until sequence + 1

set until sequence 33913 thread 2;

目标主机-还原&恢复验证（仅验证，可选）

注: 真正执行恢复的时候可以不用运行该步骤, 因为在正式迁移之前已经验证过了.

本数据库测试时的restore&recover时间为2小时左右.

主要作用为验证恢复是否可用，恢复时间预估等，执行以下脚本

rman target / nocatalog log=/home/oracle/Restore_preview-validate_$ORACLE_SID_$(date +%F_%H:%M).log<<"EOF"
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_CLIENT=TZDB_4092109955';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
set until sequence 33913 thread 2;
restore database preview;
restore database validate;
RELEASE CHANNEL ch00;
}
EOF

restore database

rman target / nocatalog log=/home/oracle/Restore_$ORACLE_SID_$(date +%F_%H:%M).log<<"EOF"
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_CLIENT=TZDB_4092109955';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
set until sequence 33913 thread 2;
restore database;
RELEASE CHANNEL ch00;
}
EOF

手动copy asm磁盘中的归档日志至目标主机(可选)

如果是直接停机备份/迁移则不需要此步骤.

SSH 使用grid用户登录源主机10.0.10.111, ASM copy archivelog FROM ASM to LOCAL

1. 创建用于存放归档的目录

$ mkdir ~/$(date +%Y_%m_%d)

$ chown grid:oinstall ~/$(date +%Y_%m_%d)

2. 从ASM磁盘组中复制归档至以上目录

for i in $(asmcmd ls RECO/TZDB/ARCHIVELOG/$(date +%Y_%m_%d)); do
  asmcmd cp +TZ_RECO/TZDB/ARCHIVELOG/$(date +%Y_%m_%d)/$i ~/$(date +%Y_%m_%d)
done

3. 复制归档日志到目标主机

scp -r /dbbak_exp_run/$(date +%Y_%m_%d) oracle@10.0.10.151:/dbbak_exp_run/$(date +%Y_%m_%d)

recover database

rman target / nocatalog log=/home/oracle/Recover_$ORACLE_SID_$(date +%F_%H:%M).log<<"EOF"
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_CLIENT=TZDB_4092109955';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
recover database until sequence 33913 thread 2;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
}
EOF

恢复新的归档(可选)

如果是直接停机备份/迁移则不需要此步骤.

$ rman target /

catalog start with '/dbbak_exp_run/$(date +%Y_%m_%d)';
crosscheck backupset;
recover database;

查看并修复遇到的问题.

直到没有报错, 则执行下一步.

打开数据库

目标主机-打开数据库

SQL> alter database open resetlogs;

如无报错则关闭数据库

SQL> shutdown immediate

在pfile中增加cluster相关参数

$ cp -v $ORACLE_HOME/dbs/init$ORACLE_SID.ora{,.bak}

$ vim $ORACLE_HOME/dbs/init$ORACLE_SID.ora

编辑文件修改为如下内容:

*.__db_cache_size=30786325577
*.__java_pool_size=469762048
*.__large_pool_size=67108864
*.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment
*.__pga_aggregate_target=32963873996
*.__sga_target=76879914598
*.__shared_io_pool_size=0
*.__shared_pool_size=4764729344
*.__streams_pool_size=134217728
*.audit_file_dest='/u01/oracle/app/admin/tzdb1/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB','EXTENDED'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/tzdb/controlfile/current1','+TZ_RECO/tzdb/controlfile/current2'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='tzdb'
*.db_unique_name='tzdb'
*.db_recovery_file_dest='+TZ_RECO'
*.db_recovery_file_dest_size=192742891520
*.db_securefile='PERMITTED'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/oracle/app'
*.dispatchers=''
*.fal_client='TZDB'
*.fal_server='TZDBSTB'
*.log_archive_config='dg_config=(tzdb, tzdbstb)'
*.log_archive_dest_2='service=tzdbstb async valid_for=(online_logfiles,primary_role) db_unique_name=tzdbstb'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=30
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=3000
*.pga_aggregate_target=32963873996
*.processes=3000
*.remote_listener='tzrac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.session_cached_cursors=500
*.sessions=3305
*.sga_max_size=76879914598
*.sga_target=76879914598
*.standby_file_management='AUTO'
*.undo_retention=14400
tzdb1.thread=1
tzdb2.thread=2
tzdb1.instance_number=1
tzdb2.instance_number=2
tzdb1.undo_tablespace='UNDOTBS1'
tzdb2.undo_tablespace='UNDOTBS2'
*.cluster_database=true

创建spfile, 并使用spfile重新启动数据库

使用修改后的pfile打开数据库.

export ORACLE_SID=tzdb1

SQL> startup nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora';

SQL> create spfile='+DATA/TZDB/SPFILETZDB.ORA' from pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora';

SQL> shutdown immediate

确认asm存储上的文件

使用grid用户登录, 确认文件是否生成.

asmcmd ls +DATA/TZDB/SPFILETZDB.ORA

重新创建pfile

newrac1

$ cp -v $ORACLE_HOME/dbs/init$ORACLE_SID.ora{,.cluster_database}

修改pfile内容如下:

$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
SPFILE='+DATA/TZDB/SPFILETZDB.ORA'

newrac2

首次登录RAC节点2执行操作.

创建或修改pfile内容如下:

$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
SPFILE='+DATA/TZDB/SPFILETZDB.ORA'

手动打开两个节点的数据库

确认数据库启动正常

newrac1

$ sqlplus / as sysdba
SQL> startup 
SQL> show parameter pfile
SQL> SELECT name, open_mode FROM gv$database;

newrac2

$ sqlplus / as sysdba
SQL> startup 
SQL> show parameter pfile
SQL> SELECT name, open_mode FROM gv$database;

在cluster中增加数据库

$ /u01/grid/app/11.2.0/bin/srvctl add database -d tzdb -o /u01/oracle/app/11.2.0

$ /u01/grid/app/11.2.0/bin/srvctl config database -d tzdb -a
Database unique name: tzdb
Database name:
Oracle home: /u01/oracle/app/11.2.0
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: tzdb
Database instances:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

在tzdb数据库增加实例

$ srvctl add instance -d tzdb -i tzdb1 -n newrac1

$ srvctl add instance -d tzdb -i tzdb2 -n newrac2

$ srvctl config database -d tzdb -a
Database unique name: tzdb
Database name:
Oracle home: /u01/oracle/app/11.2.0
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: tzdb
Database instances: tzdb1,tzdb2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

上面的命令输出中Disk Groups显示为空, 这是预期的. 因为我们还没有使用srvctl集群工具启动数据库

srvctl启动数据库

当前数据库已经是处于启动状态, 之前已通过sqlplus打开

$ sqlplus / as sysdba

SQL> SELECT name,open_mode FROM gv$database;

现在需要使用srvctl注册一下

$ srvctl start database -d tzdb
$ srvctl status database -d tzdb
Instance tzdb1 is running on node newrac1
Instance tzdb2 is running on node newrac2

$ crsctl stat res -t

静态监听配置(可选)

$ cat listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = tzdb)
  (ORACLE_HOME = /u01/oracle/app/11.2.0)
  (SID_NAME = tzdb1)
 )
)

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

ADR_BASE_LISTENER = /u01/oracle/app

srvctl重新启动数据库

as grid user

$ srvctl stop database -d tzdb
$ srvctl start database -d tzdb
$ srvctl config database -d tzdb -a
$ crsctl stat res -t

as oracle user

$ sqlplus / as sysdba

SET linesize 166
COLUMN HOST_NAME FORMAT a30
SELECT name DB_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,version DB_VERSION,DATABASE_STATUS,DATABASE_ROLE,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" FROM v$database,gv$instance;

确认一切正常.

重启服务器操作系统验证(建议)

最后重启两台服务器, 确认正常.

至此, 迁移操作已完成.

下一流程是: 应用连接NEWRAC测试.

désert/work/QH-tzdb-Restore

TZDB RAC数据库迁移至新的NEWRAC TZDB

环境信息

环境准备

requirements

清除现有tzdb相关数据库配置

Drop tzdb数据库

在crs中清除tzdb配置

准备pfile文件以适用单实例环境

修改相关参数

创建相关目录

创建orapwd文件

迁移实施

列出备份集

恢复 controlfile

查看备份的归档

目标主机-还原&恢复验证（仅验证，可选）

restore database

手动copy asm磁盘中的归档日志至目标主机(可选)

recover database

恢复新的归档(可选)

打开数据库

在pfile中增加cluster相关参数

创建spfile, 并使用spfile重新启动数据库

确认asm存储上的文件

重新创建pfile

newrac1

newrac2

手动打开两个节点的数据库

newrac1

newrac2

在cluster中增加数据库

在tzdb数据库增加实例

srvctl启动数据库

静态监听配置(可选)

srvctl重新启动数据库

重启服务器操作系统验证(建议)