Friday, April 28, 2006

StandBY Database (RAC) , feature for my database(high availability)

I have had RAC oracle database, and disk (msa1000) failed. i have built a long time
so, i think 2 points to resolve this problem

1. create new database and recovery, but i tested ,that it used a long time for maintenane (cuz my data is 200G(rman backupset),)... around 4 -5 hours

2. use standby database ...
So i started for stand by database (primary[TEST] RAC to standby[TESTDB] RAC)

- create standby database to RAC
- backup full and copy to standby database same primary path
- backup control file (standBy) and copy to standby

$ sqlplus / as sysdba
sql> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/ocfs/test-control.dbf';

$ rman target / catalog rman/password@catalog
rman> CATALOG CONTROLFILECOPY '/ocfs/test-control.dbf';


- edit tnsname.ora on primary and standby every hosts
TEST =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = test1-VIP)
(HOST = test2-VIP)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)

TESTDB =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = TESTDB1-VIP)
(HOST = TESTDB2-VIP)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

"Create the standby instance"

- create orapwd on standby every system by same with primary
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwtestdb1 password=passwd

and node 2
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwtestdb2 password=passwd

- create pfile or spfile on standby same primary
sql> create pfile='/ocfs/initTESTDB.ora' from pfile;
and edit

--------------------------------TEST (primary) ------------
*.background_dump_dest='/oracle/product/10.1.0/admin/test/bdump'
*.cluster_database_instances=2
*.cluster_database=TRUE
*.compatible='10.1.0.2.0'
*.control_files='/ocfs/test/control01.ctl','/ocfs/test/control02.ctl','/ocfs/test/control03.ctl'
*.core_dump_dest='/oracle/product/10.1.0/admin/test/cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fal_client='test'
*.fal_server='TESTDB'
test2.instance_numtestr=2
test1.instance_numtestr=1
*.java_pool_size=157286400
*.log_archive_config='dg_config=(TESTDB,TEST)'
*.log_archive_dest_1='LOCATION=/ocfs/flash_recovery_area OPTIONAL REOPEN=300'
*.log_archive_dest_2='SERVICE=TESTDB valid_for=(online_logfiles,primary_role) *.db_unique_name='TEST'
*.log_archive_format='arch_%t_%s_%r.arc'
*.log_archive_start=TRUE
*.pga_aggregate_target=438304768
*.processes=500
*.remote_listener='LISTENERS_TEST'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.sessions=550
*.sga_max_size=1572864000
*.sga_target=1572864000
*.shared_pool_size=157286400
*.standby_file_management='AUTO'
test2.thread=2
test1.thread=1
*.transactions=600
*.undo_management='AUTO'
test1.undo_retention=900
*.undo_retention=900
test2.undo_retention=900
test1.undo_tablespace='UNDOTBS1'
test2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/oracle/product/10.1.0/admin/test/udump'

--------------------------------TESTDB(standby)------------
*.background_dump_dest='/oracle/product/admin/TESTDB/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.1.0.2.0'
*.control_files='/ocfs/TESTDB/control01.ctl','/ocfs/TESTDB/control02.ctl','/ocfs/TESTDB/control03.ctl'#Restore Controlfile
*.core_dump_dest='/oracle/product/admin/TESTDB/cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='test','TESTDB'
*.db_name='TEST'
*.DB_UNIQUE_NAME='TESTDB'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDBXDB)'
*.fal_client='TESTDB'
*.fal_server='test'
TESTDB2.instance_numtestr=2
TESTDB1.instance_numtestr=1
*.job_queue_processes=2
*.log_archive_config='dg_config=(TESTDB,TEST)'
*.log_archive_dest_1='LOCATION=/ocfs/flash_recovery_area OPTIONAL REOPEN=300'
*.log_archive_format='arch_%t_%s_%r.arc'
*.log_file_name_convert='TEST','TESTDB'
*.open_cursors=300
*.pga_aggregate_target=438304768
*.processes=600
*.remote_listener='LISTENERS_TESTDB'
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.sga_max_size=2072864000
*.sga_target=2072864000
*.standby_archive_dest='LOCATION=/ocfs/arc_stb'
*.standby_file_management='AUTO'
TESTDB2.thread=2
TESTDB1.thread=1
*.undo_management='AUTO'
TESTDB1.undo_tablespace='UNDOTBS1'
TESTDB2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/oracle/product/admin/TESTDB/udump'
------------------------------------------------------


"After create pfile or spfile on standby DB complete "

- set ORACLE_SID, ORACLE_HOME,
$ export ORACLE_SID=TESTDB1
$ sqlplus / as sysdba
sql> STARTUP NOMOUNT;
...
and
$ rman target sys/password@TEST catalog rman/password@catalog auxiliary /

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY;

- connect to standby and create standy redo logs
(maximum # of logfiles +1) * maximum # of threads

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
group 7 '/ocfs/TESTDB/redo07.log' size 100M,
group 8 '/ocfs/TESTDB/redo08.log' size 100M,
group 9 '/ocfs/TESTDB/redo09.log' size 100M,
group 10 '/ocfs/TESTDB/redo10.log' size 100M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
group 11 '/ocfs/TESTDB/redo11.log' size 100M,
group 12 '/ocfs/TESTDB/redo12.log' size 100M,
group 13 '/ocfs/TESTDB/redo13.log' size 100M,
group 14 '/ocfs/TESTDB/redo14.log' size 100M;

and check

SQL> SELECT * FROM V$LOG;
SQL> SELECT * FROM V$STANDBY_LOG;

- and on on standby host (redo apply)
start managed recovery and real-time apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

- register standby DB to RAC
$ srvctl add database -d TESTDB –o /oracle/product/10.1.0/db
$ srvctl add instance -d TESTDB -i TESTDB1 -n host1
$ srvctl add instance -d TESTDB -i TESTDB2 -n host2

- create standby redo logs on primary for support standby role
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
group 7 '/ocfs/test/redo07.log' size 100M,
group 8 '/ocfs/test/redo08.log' size 100M,
group 9 '/ocfs/test/redo09.log' size 100M,
group 10 '/ocfs/test/redo10.log' size 100M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
group 11 '/ocfs/test/redo11.log' size 100M,
group 12 '/ocfs/test/redo12.log' size 100M,
group 13 '/ocfs/test/redo13.log' size 100M,
group 14 '/ocfs/test/redo14.log' size 100M;

- start standby database on other node
SQL> startup nomount;
SQL> startup mount database standby database;

- verify data guard
check sequence# on standby
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

switch log on primary
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

check sequence# again....

No comments: