Database migration scheme based on DG migration

I. Environment Information 1) master database (Single Instance) host platform: AIX6.1 database version: 11.2.0.3 (psu5) 2) backup database (crs deployed) host platform: AIX6.1 database version: 11.2.0.3 (psu5) 2: Background

I. Environment Information 1) master database (Single Instance) host platform: AIX6.1 database version: 11.2.0.3 (psu5) 2) backup database (crs deployed) host platform: AIX6.1 database version: 11.2.0.3 (psu5) 2: Background

I. Environment Information

1) master database (Single Instance)

Host platform: AIX6.1

Database Version: 11.2.0.3 (psu5)

2) slave database (crs deployed)

Host platform: AIX6.1

Database Version: 11.2.0.3 (psu5)

Ii. Background

As the business volume increases, the database needs to be changed from a single instance to two node rac. To reduce the downtime, use the DG migration method.

Iii. Migration solution:

1. Check whether the database supports Data Guard (only the Enterprise Edition supports DG)

SQL> select * from v $ option where parameter = 'managed standby'; PARAMETERVALUE ------------------------------------------------------------ manage Managed StandbyTRUE

2. Modify the master database to archive mode and force logging status
1)

SQL> alter database force logging; Database altered.

2)
SQL> archive log list;
If archive is not enabled, enable the archive mode.

Alter system set log_archive_dest_2 = 'location =/archlog/egap '; alter system set log_archive_format = 'egap _ % t _ % s _ % r. arch 'scope = spfile; -- static parameter. shutdown immediate; startup mount; alter database archivelog; alter database open; archive log list; takes effect after restart;

3. Create a pfile file for the standby Database
Create a pfile on the master database, modify it, add all the parameters of the DG slave database, and then upload them to the slave database.

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

1) parameters to be added to the standby Database
DB_UNIQUE_NAME; LOG_ARCHIVE_DEST_1; FAL_SERVER; FAL_CLIENT; STANDBY_FILE_MANAGEMENT = AUTO; DB_FILE_NAME_CONVERT; LOG_FILE_NAME_CONVERT
2) The paths involved in pfile must be created on the standby Database Host in advance (if the paths of the active and standby databases are inconsistent, you must modify them)
For example, the master database *. audit_file_dest = '/apps/oracle/admin/egap/adump'
We need to create mkdir-p/apps/oracle/admin/egap/adump in the slave database.
Cd/apps/oracle/admin
Chown-R oracle: oinstall egap
Chmod-R 775 egap

Parameter file before modification

*. _ 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 = 'clusive '*. sga_target = 30922506240 *. undo_tablespace = 'undotbs1'

Parameter file after modification

*. _ 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 ASYNCVALID_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 = 'clusive '*. 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 )'

### Note that the path specified by the db_file_name_convert and log_file_name_convert parameters must exist

4) create a slave database spfile based on the modified pfile.

Export ORACLE_SID = egapdb1sqlplus/as sysdbacreate spfile from pfile;

-- Use the newly generated spfile to check whether the instance can be started successfully

4. Generate the password file of the slave Database
Scp master database password file to slave database, and renamed
If the master database does not have a password file, you need to create a new one.

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

-- After the master database password file is uploaded to the slave database, restart the slave database.

5. Configure Master/Slave database listening and net Services
1) listener
-- Master database
Generally, listeners are configured after the database is created. We do not need to configure the listeners.
-- Slave database (cluster listening is used because cluster is installed)
Lsnrctl status to view the location of the listening file and add the following class content to the listening File

LISTENER_SCAN3 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = LISTENER_SCAN3 )))) # line added by AgentLISTENER_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 AgentLISTENER_SCAN1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = LISTENER_SCAN1 )))) # line added by region = ON # line added by AgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON # line added by region = ON # line added by Agent -- note that after the cluster is installed, SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME =/apps/oracle/product/11.2.0.3/db_1) already exists in the listener) (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 ))))