Home Database Mysql Tutorial 数据库迁移方案之DG方式迁移

数据库迁移方案之DG方式迁移

Jun 07, 2016 pm 05:26 PM
oracle migrate

一:环境信息1)主库(单实例)主机平台:AIX6.1数据库版本:11.2.0.3(psu5)2)备库(部署了crs)主机平台:AIX6.1数据库版本:11.2.0.3(psu5)二:方案背景由

一:环境信息

1)主库(单实例)

主机平台:AIX6.1

数据库版本:11.2.0.3(psu5)

2)备库(部署了crs)

主机平台:AIX6.1

数据库版本:11.2.0.3(psu5)

二:方案背景

  由于业务量增加,数据库需要由单实例,改成两节点rac。为了减少停机时间,采用DG方式迁移。

三迁移方案:

1.检查数据库是否支持Data Guard(只有企业版才支持DG)

SQL> select * from v$option where parameter = 'Managed Standby'; PARAMETER VALUE ---------------------------------------------------------------- ---------------------------------------------------------------- Managed Standby TRUE

2.修改主库为归档模式及force logging状态
1)

SQL> alter database force logging; Database altered.

2)
SQL> archive log list;
如果未开归档,开启归档模式

alter system set log_archive_dest_2='location=/archlog/egap'; alter system set log_archive_format='egap_%t_%s_%r.arch' scope=spfile; --静态参数,重启后生效 shutdown immediate; startup mount; alter database archivelog; alter database open; archive log list;

3.创建备库pfile文件
在主库上创建pfile,修改,并添加DG备库所有参数,然后传至备库

SQL> create pfile='/data01/pfileegap' from spfile;

1)备库需要添加的参数
DB_UNIQUE_NAME;LOG_ARCHIVE_DEST_1;FAL_SERVER;FAL_CLIENT; STANDBY_FILE_MANAGEMENT=AUTO;DB_FILE_NAME_CONVERT;LOG_FILE_NAME_CONVERT
2)根据pfile中涉及到路径需要提前在备库主机上建好(如果主备库路径不一致要修改)
如主库*.audit_file_dest='/apps/oracle/admin/egap/adump'
我们在备库需要建 mkdir -p /apps/oracle/admin/egap/adump
cd /apps/oracle/admin
chown -R oracle:oinstall egap
chmod -R 775 egap

改变前参数文件

*.__db_cache_size=27648851968 *.__java_pool_size=67108864 *.__large_pool_size=67108864 *.__oracle_base='/apps/oracle'#ORACLE_BASE set from environment *.__pga_aggregate_target=10334765056 *.__sga_target=30937186304 *.__shared_io_pool_size=0 *.__shared_pool_size=2952790016 *.__streams_pool_size=0 *.audit_file_dest='/apps/oracle/admin/egap/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/data01/egap/control01.ctl','/data01/egap/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='egap' *.diagnostic_dest='/apps/oracle' *.log_archive_dest_1='location=/archlog/egap' *.log_archive_format='egap_%t_%s_%r.arch' *.open_cursors=300 *.pga_aggregate_target=10307502080 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=30922506240 *.undo_tablespace='UNDOTBS1'

改变后参数文件

*.__db_cache_size=27648851968 *.__java_pool_size=67108864 *.__large_pool_size=67108864 *.__oracle_base='/apps/oracle'#ORACLE_BASE set from environment *.__pga_aggregate_target=10334765056 *.__sga_target=30937186304 *.__shared_io_pool_size=0 *.__shared_pool_size=2952790016 *.__streams_pool_size=0 *.audit_file_dest='/apps/oracle/admin/egapdb/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/data01/egapdb/control01.ctl','/data01/egapdb/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='egap' *.diagnostic_dest='/apps/oracle' *.log_archive_dest_1='location=/archlog/egapdb1' *.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=egap' *.log_archive_format='egapdb_%t_%s_%r.arch' *.open_cursors=300 *.pga_aggregate_target=10307502080 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=30922506240 *.undo_tablespace='UNDOTBS1' *.DB_UNIQUE_NAME=egapdb *.FAL_SERVER=primary *.FAL_CLIENT=standby1 *.STANDBY_FILE_MANAGEMENT=AUTO *.DB_FILE_NAME_CONVERT='/data01/egap','/data01/egapdb' *.LOG_FILE_NAME_CONVERT='/data01/egap','/data01/egapdb' *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(egap,egapdb)'

###注意db_file_name_convert和log_file_name_convert参数指定的路径要存在

4)根据修改后的pfile创建备库spfile

export ORACLE_SID=egapdb1 sqlplus / as sysdba create spfile from pfile;

--使用新生成的spfile检查是否能够成功启动实例

4.生成备库的密码文件
scp主库密码文件到备库,并改名
如果主库没有密码文件,需要新建

orapwd file=/oracle/app/oracle/product/v11.2.0.3/db_1/dbs/orapwegap password=oracle entries=5 ignorecase=y

--主库密码文件传到备库以后要重启备库

5.配置主备库监听及net服务
1)listener
--主库
一般建库后都会配置监听我们无需再配置
--备库(因为安装了cluster,所以用的是cluster的监听)
lsnrctl status 查看监听文件位置,并在监听文件中加入如下类容

LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent #LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent --注意集群安装完毕以后,上面部分内容在监听中已经存在 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /apps/oracle/product/11.2.0.3/db_1) (PROGRAM = extproc) ) (SID_DESC = (ORACLE_HOME = /apps/oracle/product/11.2.0.3/db_1) (SID_NAME = egapdb1) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.96.1)(PORT = 1521)(IP = FIRST)) ) )

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to encrypt oracle view How to encrypt oracle view Apr 11, 2025 pm 08:30 PM

Oracle View Encryption allows you to encrypt data in the view, thereby enhancing the security of sensitive information. The steps include: 1) creating the master encryption key (MEk); 2) creating an encrypted view, specifying the view and MEk to be encrypted; 3) authorizing users to access the encrypted view. How encrypted views work: When a user querys for an encrypted view, Oracle uses MEk to decrypt data, ensuring that only authorized users can access readable data.

How to delete all data from oracle How to delete all data from oracle Apr 11, 2025 pm 08:36 PM

Deleting all data in Oracle requires the following steps: 1. Establish a connection; 2. Disable foreign key constraints; 3. Delete table data; 4. Submit transactions; 5. Enable foreign key constraints (optional). Be sure to back up the database before execution to prevent data loss.

What to do if the oracle can't be opened What to do if the oracle can't be opened Apr 11, 2025 pm 10:06 PM

Solutions to Oracle cannot be opened include: 1. Start the database service; 2. Start the listener; 3. Check port conflicts; 4. Set environment variables correctly; 5. Make sure the firewall or antivirus software does not block the connection; 6. Check whether the server is closed; 7. Use RMAN to recover corrupt files; 8. Check whether the TNS service name is correct; 9. Check network connection; 10. Reinstall Oracle software.

How to paginate oracle database How to paginate oracle database Apr 11, 2025 pm 08:42 PM

Oracle database paging uses ROWNUM pseudo-columns or FETCH statements to implement: ROWNUM pseudo-columns are used to filter results by row numbers and are suitable for complex queries. The FETCH statement is used to get the specified number of first rows and is suitable for simple queries.

How to solve the problem of closing oracle cursor How to solve the problem of closing oracle cursor Apr 11, 2025 pm 10:18 PM

The method to solve the Oracle cursor closure problem includes: explicitly closing the cursor using the CLOSE statement. Declare the cursor in the FOR UPDATE clause so that it automatically closes after the scope is ended. Declare the cursor in the USING clause so that it automatically closes when the associated PL/SQL variable is closed. Use exception handling to ensure that the cursor is closed in any exception situation. Use the connection pool to automatically close the cursor. Disable automatic submission and delay cursor closing.

How to create cursors in oracle loop How to create cursors in oracle loop Apr 12, 2025 am 06:18 AM

In Oracle, the FOR LOOP loop can create cursors dynamically. The steps are: 1. Define the cursor type; 2. Create the loop; 3. Create the cursor dynamically; 4. Execute the cursor; 5. Close the cursor. Example: A cursor can be created cycle-by-circuit to display the names and salaries of the top 10 employees.

How to create oracle dynamic sql How to create oracle dynamic sql Apr 12, 2025 am 06:06 AM

SQL statements can be created and executed based on runtime input by using Oracle's dynamic SQL. The steps include: preparing an empty string variable to store dynamically generated SQL statements. Use the EXECUTE IMMEDIATE or PREPARE statement to compile and execute dynamic SQL statements. Use bind variable to pass user input or other dynamic values ​​to dynamic SQL. Use EXECUTE IMMEDIATE or EXECUTE to execute dynamic SQL statements.

How to stop oracle database How to stop oracle database Apr 12, 2025 am 06:12 AM

To stop an Oracle database, perform the following steps: 1. Connect to the database; 2. Shutdown immediately; 3. Shutdown abort completely.

See all articles