windows+Oracle单机迁移至Linux+Oracle RAC迁移方案

最新推荐文章于 2024-05-21 14:55:06 发布

挠背小能手

最新推荐文章于 2024-05-21 14:55:06 发布

阅读量1.2k

点赞数

分类专栏： Oracle日常运维文章标签： oracle linux windows

本文链接：https://blog.csdn.net/m0_37625564/article/details/121702021

版权

Oracle日常运维专栏收录该内容

129 篇文章 17 订阅

订阅专栏

数据库迁移前后架构信息对比
在这里插入图片描述
根据 ORACLE官方文档中对于WINDOWS环境到LINUX环境的数据库迁移方式，结合用户数据库系统不允许长时间停机的特点，我们决定采用ORACLE DATAGUARD方式进行数据的初始同步。
正式迁移时只需要关闭主库，最新的数据已经通过DATAGUARD同步到LINUX一体机环境的数据库，此时只需要进行数据库升级以及向RAC转换操作即可，可以最小化停机时间。同时此种情况不影响原有WINDOWS环境数据库，极端情况下可以重新启用WINDOWS环境来恢复数据库。

数据恢复

此步骤使用典型的WINDOWS环境到LINUX环境DATAGUARD搭建方式即可。
即首先在WINDOWS环境进行ORACLE数据库的RMAN备份，之后将备份数据在LINUX一体机环境进行数据恢复，此步骤在正式停机迁移前一天进行，不占用正式迁移时的停机时间。
主要脚本如下：

数据库备份及备份集传输
数据库恢复

run{
set newname for datafile          1 to '+DATA/pacs/SYSTEM01.DBF';
set newname for datafile          2 to '+DATA/pacs/SYSAUX01.DBF';
set newname for datafile          3 to '+DATA/pacs/UNDOTBS01.DBF';
set newname for datafile          4 to '+DATA/pacs/USERS01.DBF';
set newname for datafile          5 to '+DATA/pacs/PACS01.DBF';
set newname for datafile          6 to '+DATA/pacs/PACS02.DBF';
set newname for datafile          7 to '+DATA/pacs/PACSINDEX01.DBF';
set newname for datafile          8 to '+DATA/pacs/PACSINDEX02.DBF';
set newname for datafile          9 to '+DATA/pacs/PACS.DBF';
set newname for datafile         10 to '+DATA/pacs/PACSINDEX.DBF';
set newname for datafile         11 to '+DATA/pacs/SYSTEM02.DBF';
restore database;
switch datafile all;
}

数据库升级

检查数据同步情况

select process,status,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby;

set linesize 180
col DEST_NAME for a20
select dest_name,status,type,DATABASE_MODE,RECOVERY_MODE from V$ARCHIVE_DEST_STATUS where type='PHYSICAL';
select dest_name,PROTECTION_MODE,GAP_STATUS,APPLIED_THREAD#,APPLIED_SEQ# from gV$ARCHIVE_DEST_STATUS where type='PHYSICAL';
select thread#,max(sequence#) "primary_current_log_sequence" from gv$archived_log group by thread#;

激活备库

1.激活DG备机
SQL> alter database activate standby database;

2.备份控制文件
alter database backup controlfile to trace as '/hom/oracle/rmanbak/control.log';

3.RESETLOGS方式重建控制文件
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PACS" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 48
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 '+DATA/pacs/REDO01.LOG'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '+data/pacs/REDO02.LOG'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '+data/pacs/REDO03.LOG'  SIZE 200M BLOCKSIZE 512,
  GROUP 4 '+data/pacs/REDO04.LOG'  SIZE 200M BLOCKSIZE 512,
  GROUP 5 '+data/pacs/REDO05.LOG'  SIZE 200M BLOCKSIZE 512,
  GROUP 6 '+data/pacs/REDO06.LOG'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '+DATA/pacs/system01.dbf',
  '+DATA/pacs/sysaux01.dbf',
  '+DATA/pacs/undotbs01.dbf',
  '+DATA/pacs/users01.dbf',
  '+DATA/pacs/pacs01.dbf',
  '+DATA/pacs/pacs02.dbf',
  '+DATA/pacs/pacsindex01.dbf',
  '+DATA/pacs/pacsindex02.dbf',
  '+DATA/pacs/pacs.dbf',
  '+DATA/pacs/pacsindex.dbf',
  '+DATA/pacs/system02.dbf'
CHARACTER SET ZHS16GBK;

3.	以升级模式打开数据库
SQL> alter database open resetlogs upgrade;

Database altered.

添加临时表空间
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/pacs/temp01.dbf' size 100m autoextend on;

Tablespace altered.

数据库升级

运行升级脚本：--注意使用稳定的网络
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off

@?/rdbms/admin/utlrp.sql 编译失效对象
@?/rdbms/admin/catuppst.sql
前面的升级脚本是运行在upgrade模式下，该脚本主要是在open模式下做些升级动作


升级后检查组件有效性脚本：
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool           03-12-2020 15:13:53
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:03:50
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.4.0  00:01:19
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:12
OLAP Analytic Workspace
.                                         VALID      11.2.0.4.0  00:00:12
OLAP Catalog
.                                         VALID      11.2.0.4.0  00:00:13
Oracle OLAP API
.                                         VALID      11.2.0.4.0  00:00:06
Oracle Enterprise Manager
.                                         VALID      11.2.0.4.0  00:02:00
Oracle XDK
.                                         VALID      11.2.0.4.0  00:00:12
Oracle Text
.                                         VALID      11.2.0.4.0  00:00:09
Oracle XML Database
.                                         VALID      11.2.0.4.0  00:00:48
Oracle Database Java Packages
.                                         VALID      11.2.0.4.0  00:00:03
Oracle Multimedia
.                                         VALID      11.2.0.4.0  00:00:47
Spatial
.                                         VALID      11.2.0.4.0  00:01:08
Oracle Expression Filter
.                                         VALID      11.2.0.4.0  00:00:03
Oracle Rules Manager
.                                       INVALID      11.2.0.4.0  00:00:02
Oracle Application Express
.                                         VALID     3.2.1.00.12
Final Actions
.                                                                00:00:00
Total Upgrade Time: 00:11:12

SQL> col COMP_NAME for a50
SQL> select comp_name,version,status from dba_registry;

SQL> select COMP_ID,COMP_NAME from dba_registry where COMP_ID='RAC';

no rows selected

SQL> @?/rdbms/admin/catclust.sql

失效组件的处理：
对于失效组件Oracle Rules Manager INVALID，参考MOS上的修复方法如下：
1- Uninstall the EXFSYS schema :
$ sqlplus / as sysdba
SQL> spool uninstall.txt
SQL> @?/rdbms/admin/catnoexf.sql

<< Wait till the Multiple PL/SQL procedures complete and SQL Prompt returns>>

SQL> spool off

2- Re-install the EXFSYS schema:
SQL> spool reinstall.txt
SQL> @?/rdbms/admin/catexf.sql

<< Wait till the Multiple PL/SQL procedures complete and SQL Prompt returns>>

SQL> spool off

3- Install Rule Manager
SQL> spool rule-install.txt
SQL> @?/rdbms/admin/catrul.sql

<< Wait till the Multiple PL/SQL procedures complete and SQL Prompt returns>>

SQL> spool off


4- Check the registry to confirm if the Oracle Rules Manager becomes valid
SQL> SELECT comp_name, status, version, schema FROM dba_registry;

升级后检查

主要检查失效对象，以及DBLINK等信息。本次迁移后未出现失效对象，同时未使用DBLINK。

select * from dba_profiles;


SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> col VERSION for fm99999999
SQL> select * from v$timezone_file;

FILENAME               VERSION
-------------------- ---------
timezlrg_14.dat      14

1 row selected.

 
SQL> select owner,OBJECT_TYPE,count(*)  from dba_objects where   STATUS='INVALID' group by owner,object_type
  2  ;

OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
PUBLIC                         SYNONYM                      7

1 row selected.

SQL> select owner,OBJECT_TYPE,object_name from dba_objects where   STATUS='INVALID' ;

OWNER                          OBJECT_TYPE         OBJECT_NAME
------------------------------ ------------------- --------------------------------------------------------------------------------------------------------------------------------
PUBLIC                         SYNONYM             EXF$ATTRIBUTE
PUBLIC                         SYNONYM             EXF$ATTRIBUTE_LIST
PUBLIC                         SYNONYM             EXF$XPATH_TAG
PUBLIC                         SYNONYM             EXF$XPATH_TAGS
PUBLIC                         SYNONYM             EVALUATE
PUBLIC                         SYNONYM             DBMS_EXPFIL
PUBLIC                         SYNONYM             USER_EXPFIL_TEXT_INDEX_ERRORS

7 rows selected.

SQL> col username for a15
SQL> col owner for a15
SQL> select owner,db_link,username,host,to_char(created,'yyyymmdd hh24:mi:ss')  from dba_db_links;

集群相关参数配置

alter system set cluster_database=true scope=spfile sid='*';
alter system set instance_number=1 scope=spfile sid='pacs1';
alter system set instance_number=2 scope=spfile sid='pacs2';
alter system set thread=1 scope=spfile sid='pacs1';
alter system set thread=2 scope=spfile sid='pacs2';

添加节点2 REDOLOG
SQL> alter database add logfile thread 2 '+DATA/pacs/redo11.log' size 200m;

Database altered.

SQL> alter database add logfile thread 2 '+DATA/pacs/redo12.log' size 200m;

Database altered.

SQL> alter database add logfile thread 2 '+DATA/pacs/redo13.log' size 200m;

Database altered.

SQL> alter database add logfile thread 2 '+DATA/pacs/redo14.log' size 200m;

Database altered.

SQL> alter database add logfile thread 2 '+DATA/pacs/redo15.log' size 200m;

Database altered.

SQL> alter database add logfile thread 2 '+DATA/pacs/redo16.log' size 200m;

Database altered.

SQL> alter database enable thread 2;

Database altered.



添加节点2 UNDO
SQL> create undo tablespace UNDOTBS2 datafile '+DATA/pacs/undotbs2_01.dbf' size 100M autoextend on;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTBS2'  sid='pacs2' scope=spfile;

System altered.

修改参数：
SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> show parameter instance

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
active_instance_count                integer
cluster_database_instances           integer     1
instance_groups                      string
instance_name                        string      pacs2
instance_number                      integer     2
instance_type                        string      RDBMS
open_links_per_instance              integer     4
parallel_instance_group              string
parallel_server_instances            integer     1
SQL> alter system set instance_number=2 scope=spfile sid='pacs2';

System altered.

SQL> alter system set instance_number=1 scope=spfile sid='pacs1';

System altered.

SQL> alter system set thread=1 scope=spfile sid='pacs1';

System altered.

SQL> alter system set thread=2 scope=spfile sid='pacs2';

System altered.

数据库加入集群管理

srvctl add database -d pacs -o $ORACLE_HOME
srvctl add instance -d pacs -i pacs1 -n orcl1
srvctl add instance -d pacs -i pacs2 -n orcl2
srvctl start database -d pacs

GRID用户查看
Crsctl stat res -t

统计信息收集

设置自动收集任务，并手动进行收集一次
BEGIN
        DBMS_AUTO_TASK_ADMIN.ENABLE(
                       client_name => 'auto optimizer stats collection',
                       operation => NULL, window_name => NULL);
    END;
    /
select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='auto optimizer stats collection';



--dba_autotask_task中的status字段不能表示CLIENT_NAME对应的是否开启；
--查询是否开启以DBA_AUTOTASK_CLIENT中的status字段为准。
SQL> exec dbms_auto_task_admin.disable(client_name=>'sql tuning advisor',operation=>null,window_name=>null);

PL/SQL procedure successfully completed.
SQL>  exec dbms_auto_task_admin.disable(client_name=>'auto space advisor',operation=>null,window_name=>null);

PL/SQL procedure successfully completed.
set linesize 180 pagesize 1000
col client_name for a35
col TASK_NAME for a25
col OPERATION_NAME for a25
col STATUS for a20
select client_name,task_name,operation_name,status from dba_autotask_task;

CLIENT_NAME                      TASK_NAME                      OPERATION_NAME                 STATUS
-------------------------------- ------------------------------ ------------------------------ --------
sql tuning advisor               AUTO_SQL_TUNING_PROG           automatic sql tuning task      ENABLED
auto optimizer stats collection  gather_stats_prog              auto optimizer stats job       ENABLED
auto space advisor               auto_space_advisor_prog        auto space advisor job         ENABLED

挠背小能手

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
windows+Oracle单机迁移至Linux+Oracle RAC迁移方案

数据库迁移前后架构信息对比根据 ORACLE官方文档中对于WINDOWS环境到LINUX环境的数据库迁移方式，结合用户数据库系统不允许长时间停机的特点，我们决定采用ORACLE DATAGUARD方式进行数据的初始同步。正式迁移时只需要关闭主库，最新的数据已经通过DATAGUARD同步到LINUX一体机环境的数据库，此时只需要进行数据库升级以及向RAC转换操作即可，可以最小化停机时间。同时此种情况不影响原有WINDOWS环境数据库，极端情况下可以重新启用WINDOWS环境来恢复数据库。数据恢复
复制链接

扫一扫