Data migration from a single instance to a RAC database using DG

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 ';

PARAMETER VALUE

--------------------------------------------------------------------------------------------------------------------------------

Managed Standby TRUE

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 setlog_archive_dest_2 = 'location =/archlog/egap ';

Alter system setlog_archive_format = 'egap _ % t _ % s _ % r. arch 'scope = spfile; -- static parameter, effective after restart

Shutdown immediate;

Startup mount;

Alter database archivelog;

Alter database open;

Archive log list;

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 setfrom 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

*. Processses = 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 setfrom 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

*. Processses = 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 = egapdb1

Sqlplus/assysdba

Create 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 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

-- Note that the preceding content already exists in the listener 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)

(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 ))

)

)

2) tns
Add the following content to the tnsnames. ora file of the Master/Slave database:

Primary =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.96.16) (PORT = 1521 ))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = egap)

)

)

Standby1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.96.1) (PORT = 1521 ))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = egapdb1)

)

)

Standby2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.96.2) (PORT = 1521 ))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = egapdb2)

)

)

-- Switch between the master and slave nodes and add instances after building the DG.

6. Test network connection
1) check whether the listening status is normal
Lsnrctl status
2) test whether the connection string can normally connect to the specified database

Tnsping egap

Tnsping egapdb1

Tnsping egapdb2

Sqlplus sys/oracle @ egap assysdba

Sqlplus sys/oracle @ egapdb1 assysdba

Sqlplus sys/oracle @ egapdb2 assysdba

7. Copy the slave Database
There are two types: rman online replication 2: rman backup recovery from different machines
Method 1: rman online replication (master database backup is not required)
This method is only applicable to ORACLE 11 GB. It can automatically back up files such as datafile and control to the standby database. The master database can still run normally during the replication process, but the replication process takes a long time, it will occupy certain network resources.
1) Start the slave database to the nomount state.
Export ORACLE_SID = egapdb1
Sqlplus/as sysdba
Startup nomount;
2) execute the following command on the slave database:

Rman target sys/oracle @ primary auxiliary sys/oracle @ standby1 nocatalog

Duplicate target database forstandby from active database nofilenamecheck;

-- If the file path of the Master/Slave database remains unchanged, add nofilenamecheck. If nocatalog is added during login, the following error will be reported:

PLS-00201: identifier 'dbms _ RCVCAT. getdbid' must be declared

1 2 3 4 Next Page