Migrate a single oracle instance to RAC through dataguard

Migrate a single oracle instance to RAC through dataguard

One instance primary to RAC standbyDataGuard Configuration:

 

	Primary	Standby
Clusterware	Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit	11g R2 Grid Infrastructure (11.2.0.4)
Cluster Nodes	Cltrac1	Srvrac1, srvrac2
DB_UNIQUE_NAME	Test1	Test2
DB_NAME	TEST1	Test1
DB_instance	Test1	Test11, test22
DB_listener	Listener	Listener2
DB storage	ASM	Linux file sys
ASM diskgroup for DB files	DATA
ORACLE_HOME	/Bee/app/oracle/product/11.2.0/db_1	/Bee/app/oracle/product/11.2.0/db_1
OS	CentOS release 6.4 (Final)	CentOS release 6.4 (Final)

 

Master database preparation:

1. The master database must be in archive mode. You can change the master database to archive mode:

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL> archive log list;

2. enable the Force log function of the master database

SQL> alter database force logging;

3. When data files are added or deleted in the master database, these files are also added or deleted in the slave database. To enable this function, follow these steps:

SQL> alter system set standby_file_management = 'auto ';

4. Add standby logs to the master database:

Alterdatabase add standby logfile '/bee/app/oracle/oradata/test1/standby01.log' size50M;

Alterdatabase add standby logfile '/bee/app/oracle/oradata/test1/standby02.log' size50M;

Alter database add standby logfile '/bee/app/oracle/oradata/test1/standby03.log' size 50 M;

5. Create a password file and set the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE or SHARED. Generally, the database has a password file by default, and this parameter is EXECUSIVE by default. Check the two items first. If they are not the default items, set them as follows:

SQL> alter system set remote_login_passwordfile = exclusive scope = spfile;

OS> orapwd password = <sys User password>

Scp mvorapwSID

6. Check whether the db_unique_name parameter of the database is set. If not, use alter system for settings:

SQL> show parameter db_unique_name;

SQL> alter system set db_unique_name = some_nam.pdf = spfile;

7. Configure the archiving location:

Alter system set log_archive_dest_1 = 'location =/bee/app/oracle/archivelog valid_for = (all_logfiles, all_roles) db_unique_name = test1 ';

Alter system set log_archive_dest_2 = 'service = test21 asyncvalid_for = (online_logfile, primary_role) db_unique_name = test2 ';

 

8. SQL> alter system setfal_server = 'test2 ';

SQL> alter system set log_archive_config = 'dg _ config = (test1, test2 )';

9. Set the file conversion method. The file is stored locally.

Altersystem set DB_FILE_NAME_CONVERT = '+ DATA/test2/datafile/', '/bee/app/oracle/oradata/test1/' scope = spfile;

Alter system setLOG_FILE_NAME_CONVERT = '+ DATA/test2/onlinelog/', '/bee/app/oracle/oradata/test1/' scope = spfile;

10. createpfile = '/tmp/test2_pfile.ora' from spfile;

11.

 

 

Listener and tnsnames Configuration:

1. we need to use the RMAN duplicate from active database command to create a slave database. We need to configure the static listener and TNS name. The yellow part is the static listener I manually added, and the rest is the original dynamic listener of the database, add both nodes:

[Grid @ srvrac2admin] $ cat listener. ora

LISTENER_TT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = LISTENER_TT) # line added by Agent

LISTENER_SCAN1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = LISTENER_SCAN1) # line added by Agent

LISTENER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = LISTENER) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_TT = ON # line added by Agent

LISTENER2 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.205.0.31) (PORT = 1522 ))

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1522 ))

)

)

 

SID_LIST_LISTENER2 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = test1)

(ORACLE_HOME =/bee/app/oracle/product/11.2.0/db_1)

(SID_NAME = test22)

)

)

The listener name must be included at startup:

[Grid @ srvrac2 admin] $ lsnrctl start LISTENER2

2. Add tnsnames. ora

Slave Node 1:

TEST1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1)

)

)

TEST21 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.205.0.30) (PORT = 1522 ))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1)

)

)

Slave database Node 2:

TEST1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1)

)

)

TEST21 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.205.0.31) (PORT = 1522 ))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1)

)

)

Master database:

Test1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test1)

)

)

Test21 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.205.0.30) (PORT = 1522 ))

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.205.0.31) (PORT = 1522 ))

(LOAD_BALANCE = yes)

)

(CONNECT_DATA =

(SERVICE_NAME = test1)

(FAILOVER_MODE =

(TYPE = select)

(METHOD = basic)

(RETRIES = 200)

(DELAY = 5)

)

)

)

The red NETWORK service name corresponds to the service in log_archive_dest_2.

Test method:

Sqlplus sys/oracle @ test21 as sysdba

3.

Prepare the standby database environment

1. After the pfile of the master database is created, copy it to the same location on the slave database server and change its name using the SID of the slave database. Modify pfile as follows:

Depending on the configuration and file location of your slave database, you may need to modify the AUDIT_FILE_DEST, CONTROL_FILES, and DISPATCHERS parameters (there may be other parameters to be modified ).

Change db_unique_name in the LOG_ARCHIVE_DEST_1 parameter to the unique name of the slave database (JED2 here ).

Modify the LOG_ARCHIVE_DEST_2 parameter to the service name and unique database name (JED) corresponding to the master database ).

The FAL_SERVER parameter modifies the service Name Pointing to the master database.

Add the following parameters:

Db_unique_name = JED2

Altersystem set standby_file_management = 'auto ';

Db_file_name_convert and log_file_name_convert. If the data files and log files of the master and slave databases are in different locations, you need to set these two parameters.

Then, create the required directory structure and modify related files on the slave database server.

2. For example:

[Oracle @ srvrac1dbs] $ cat test2_pfile.ora

Test1. _ db_cache_size = 883027968

Test1. _ java_pool_size = 23554432

Test1. _ large_pool_size = 30331648

Test1. _ pga_aggregate_target = 673741824

Test1. _ sga_target = 922122547

Test1. _ shared_io_pool_size = 0

Test1. _ shared_pool_size = 203979776

Test1. _ streams_pool_size = 11777216

*. Audit_file_dest = '/bee/app/oracle/admin/test2/adump'

*. Audit_trail = 'db'

*. Compatible = '11. 2.0.4.0'

*. Control_files = '+ DATA/test2/controlfile/control01.ctl', '+ DATA/test2/controlfile/control02.ctl'

*. Db_block_size = 8192

*. Db_domain =''

*. Db_file_name_convert = '/bee/app/oracle/oradata/test1/', '+ DATA/test2/datafile /'

*. Log_file_name_convert = '/bee/app/oracle/oradata/test1/', '+ DATA/test2/datafile /'

*. Db_name = 'test1'

*. Db_unique_name = 'test2'

*. Diagnostic_dest = '/bee/app/oracle'

*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = test2XDB )'

*. Fal_server = 'test1'

*. Log_archive_config = 'dg _ config = (test1, test2 )'

*. Log_archive_dest_1 = 'location = + DATA/test2/archivelogvalid_for = (all_logfiles, all_roles) db_unique_name = test2'

*. Log_archive_dest_2 = 'service = test1async valid_for = (online_logfile, primary_role) db_unique_name = test1'

*. Log_archive_format = '% t _ % s _ % r. dbf'

*. Open_cursors = 300

*. Pga_aggregate_target = 1072693248

*. Processses = 150

*. Remote_login_passwordfile = 'clusive'

*. Sga_target = 3218079744

*. Standby_file_management = 'auto'

*. Undo_tablespace = 'undotbs1'

3. Start the slave Database export ORACLE_SID = test11

Startup nomount pfile = '/bee/app/oracle/product/11.2.0/db_1/dbs/test2_pfile.ora'

4. Create a slave database and restore the database from RMAN:

[Oracle @ srvrac1 ~] $ Export ORACLE_SID = test11

[Oracle @ srvrac1 ~] $ Rman target sys/oracle @ test1 auxiliary sys/oracle @ test21

 

Duplicate target database for standby from active databasenofilenamecheck;

5. Start the redo application:

SQL> alter database recover managed standbydatabase disconnect from session

Or synchronize alter database recover managed standby database using currentlogfile disconnect from session in real time;

6. test:

Update in the master database:

SQL> insert into test. test_table values (17, 'jhpcc ');

 

1 rowcreated.

 

SQL> commit;

 

Commitcomplete.

 

SQL> alter system archive log current;

 

System altered.

Check whether the data is synchronized in the slave database. for analysis, see alert. log on both sides.

SQL> alter database recover managed standbydatabase cancel;

SQL> alter database open read only;

SQL> SQL> select * from test. test_table;

 

ID NAME

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

 

17 jhpcc

7. The 11G version supports active standby databases. You can enable the database in read-only mode and start the log application at the same time:

Alter database recover managed standby databasedisconnect

 

8. createspfile = '+ DATA/cltdbhz1/spfilecltdbhz1.ora' from pfile = '/bee/app/oracle/product/11.2.0/db_1/dbs/cltdbhz1_pfile.ora ';

 

 

Register the second node to CRS

1. [oracle @ srvrac2 dbs] $ catinittest22.ora

2. spfile = '+ DATA/test2/spfiletest2.ora

3. From the second node of the database, $ export ORACLE_SID = test22

$ Sqlplus/as sysdba SQL> startup mount;

4. srvctl add database-d test2-ntest1-o/bee/app/oracle/product/11.2.0/db_1-p + DATA/test2/spfiletest2.ora-rphysical_standby-a DATA (executed by oracle Users)

5. srvctl add instance-d test2-itest11-n srvrac1

6. srvctl add instance-d test2-itest22-n srvrac2

7. srvctl start database-d test2

8. srvctl modify database-d test2-s mount

9. srvctl status database-d test2

 

When startup mount in step 3

Because the parameter file is generated from the master database of a single instance, several errors are reported when the second node of RAC is started:

Question 1:

ORA-00304: requested INSTANCE_NUMBER isbusy

Solution:

Alter system set instance_number = 1 scope = spfile sid = 'test11 ';

Alter system set instance_number = 2 scope = spfile sid = 'test22 ';

Then restart

 

Question 2:

ORA-01102: cannot mount database inEXCLUSIVE mode

Solution:

SQL> show parameter cluster_databas

 

NAME TYPE VALUE

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

Cluster_database boolean FALSE

Cluster_database_instances integer 1

SQL> alter system setcluster_database = true scope = spfile;

Alter system setcluster_database_instances = 2 scope = spfile;

System altered.

 

Select instance_name, status from gv $ instance;

 

Question 3:

ORA-01620: no public threads are availablefor mounting

Cause: The value of the initializationparameter THREAD is zero, its default value. There are no threads which havebeen publicly enabled, and not mounted.

 

Action: Shut down the instance, change thevalue of the initialization parameter to a thread which is privately enabledand not mounted. If the database is open in another instance, then a maythread be publicly enabled.

 

 

 

SQL> show parameter thread

 

NAME TYPE VALUE

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

Parallel_threads_per_cpu integer 2

Thread integer 0

Alter system set thread = 1 scope = spfilesid = 'test11 ';

Alter system set thread = 2 scope = spfilesid = 'test22 ';

Check status:

Select * from V $ DATAGUARD_STATUSorder by TIMESTAMP;

Select STATUS, GAP_STATUS fromV $ ARCHIVE_DEST_STATUS where DEST_ID = 2;

Select SEQUENCE #, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V $ ARCHIVED_LOG where name = 'test2' order byFIRST_TIME;

Select DEST_ID, STATUS, DESTINATION, ERROR from V $ ARCHIVE_DEST where DEST_ID <= 2;

 

Dataguard master-slave switchover

When switching between a master database and a slave database in the RAC status, only one instance of the master database is running. You need to turn off other nodes before switching, and then start other nodes after switching. Query Master/Slave database status

SQL> select database_role, switchover_status from v $ database;

Switch the master database to the slave database status

SQL> alter database commit to switchover to physical standbywithsession shutdown;

If the status of the master database is "sessions active", you must add the with sessionshutdown statement after the command. Status.

Restart the original database to the mount status.

SQL>Shutdown immediate;

SQL>Startup mount;

 

Run the switch command on the original slave database.

SQL> alter database commit to switchover to primary withsession shutdown;

SQL>Alter database open;

After the master-slave switchover, enable the log application on the new slave database:

 

SQL> alter database recover managed standby databaseusing current logfile disconnect from session;

 

[Root @ srvrac2 ~] # Srvctl start instance-d test2-I test22

PRCR-1013: cannot start resource ora. test2.db

PRCR-1064: cannot start resource ora. test2.db on node srvrac2

The CRS-5017: the resource action "ora. test2.db start" encountered The followingerror:

ORA-01618: redo thread 2 is not enabled-cannot mount

. Fordetails refer to "(: CLSN00107 :)" in "/bee/app/11.2.0/grid/log/srvrac2/agent/crsd/oraagent_oracle/oraagent_oracle.log ".

 

CRS-2674: Start of 'ora. test2.db 'on 'srvrac2' failed

 

 

 

ORA-01618: redo thread 2 is not enabled-cannot mount

Http://tiany.blog.51cto.com/513694/1415383

 

Alterdatabase add logfile thread 2 ('+ data') size 50 m;

Alterdatabase add logfile thread 2 ('+ data') size 50 m;

Alterdatabase add logfile thread 2 ('+ data') size 50 m;

Alterdatabase enable thread 2;

 

 

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-30013: undo tablespace 'undotbs1' is currently in use

SQL> alter system set db_create_file_dest = '+ data ';

SQL> create undo tablespace undotbs2;

Altersystem set undo_tablespace = 'undotbs1 'scope = spfile sid = 'test11 ';

Altersystem set undo_tablespace = 'undotbs2 'scope = spfile sid = 'test22 ';