I Post Earlier Some Topics About Dealing with Data Guard , Today i will start Mention Steps How To Create Data Guard Standby Database Manually . Its Long East Steps .Just Follow Them :
primary Information :
Instance Name : prim
Hostname : PrimDB10g
Standby Information :
Instance Name : Stdby
Hostname : StdbyDB10g
Some Note :
1-On Standby Hostname Just install Database Software without Create Any Database.
2-Primary Database and Standby Database should be able to ping .
3-Primary and Standby Should be The Same .
Step #1 : On primary Database
-Primary Database Should be in Archive log mode.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Current log sequence 1
SQL > Shutdown immediate ;
SQL > Startup mount ;
SQL > Alter database archivelog ;
SQL > Alter database open ;
– Enable Force Logging to make sure takes precedence and all operations are logged
into the redo logs.
SQL> ALTER DATABASE FORCE LOGGING;
-Create Password Since password file should be the same on Both , you can create it on Primary Database and Copy to Standby with Change name .
[oracle@PrimDB10g]$ orapwd file=@ORACLE_HOME/db/orapwprim password=oracle force=y
Step #2 :Adding Standby Logfile .
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/u01/app/oracle/oradata/prim/stby04.log’ size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/u01/app/oracle/oradata/prim/stby04.log’ size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/u01/app/oracle/oradata/white/stby04.log’ size 50m;
Step #3:
On Primary Database Should Create Pfile , to modify it and add parameters .
SQL> CREATE PFILE FROM SPFILE;
OR
SQL> CREATE PFILE=’/initSID’ from spfile;
prim.__db_cache_size=121634816
prim.__java_pool_size=41943040
prim.__large_pool_size=4194304
prim.__shared_pool_size=109051904
prim.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/prim/adump’
*.background_dump_dest=’/u01/app/oracle/admin/prim/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u01/app/oracle/oradata/prim/control01.ctl’,’/u01/app/oracle/oradata/prim/control02.ctl’,’/u01/app/oracle/oradata/prim/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/prim/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’prim’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=283115520
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/prim/udump’
db_unique_name=’prim’
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prim,stdby)’
LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/oradata/prim/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim’
LOG_ARCHIVE_DEST_2=’SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=stdby’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=stdby
FAL_CLIENT=prim
standby_file_management=auto
db_file_name_convert=’/u01/app/oracle/oradata/stdby/’,’/u01/app/oracle/oradata/prim/’
log_file_name_convert=’/u01/app/oracle/oradata/stdby/’,’/u01/app/oracle/oradata/prim/’
Once you Create and modify pfile you should startup database with it .
SQL > Shutdown immediate
SQL > Startup nomount pfile=’/u01/initPrim.ora’;
SQL > Alter database mount ;
SQL > alter database open ;
SQL > Create Spfile from pfile = ‘/u01/initPrim.ora’;
Bounce Database again .
Step#4 : Backup Primary Database Using RMAN
RMAN> backup full database format ‘/u01/rman/%d_%U.bkp’ plus archivelog format ‘/u01/rman/%d_%U.bkp’;
Next, create a standby controlfile backup via RMAN:
RMAN> configure channel device type disk format ‘/u01/rman/%U’;
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;
Move The backupset to Standby Using SCP
scp -r oracle@Primary-Database-ip :/u01/rman oracle@standby-database-ip:/u01/
The primary and standby database location for backup folder must be same.
Steps#5 : Start Working On Standby Database .
you should Create standby folders , to make sure No error will be appear .
mkdir -p /u01/app/oracle/oradata/stdby
mkdir -p /u01/app/oracle/oradata/stdby/arch
mkdir -p /u01/app/oracle/admin/stdby
mkdir -p /u01/app/oracle/admin/stdby/adump
mkdir -p /u01/app/oracle/admin/stdby/bdump
mkdir -p /u01/app/oracle/admin/stdby/udump
mkdir -p /u01/app/oracle/flash_recovery_area/PRIM
mkdir -p /u01/app/oracle/flash_recovery_area/PRIM/onlinelog
Step#6: Parameter file for Standby
stdby.__db_cache_size=121634816
stdby.__java_pool_size=41943040
stdby.__large_pool_size=4194304
stdby.__shared_pool_size=109051904
stdby.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/stdby/adump’
*.background_dump_dest=’/u01/app/oracle/admin/stdby/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u01/app/oracle/oradata/stdby/control01.ctl’,’/u01/app/oracle/oradata/stdby/control02.ctl’,’/u01/app/oracle/oradata/stdby/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/stdby/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’prim’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdbyXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=283115520
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/stdby/udump’
db_unique_name=’stdby’
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prim,stdby)’
LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/oradata/stdby/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby’
LOG_ARCHIVE_DEST_2=’SERVICE=prim LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=prim’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=’prim’
FAL_CLIENT=’stdby’
standby_file_management=auto
db_file_name_convert=’/u01/app/oracle/oradata/prim/’,’/u01/app/oracle/oradata/stdby/’
log_file_name_convert=’/u01/app/oracle/oradata/prim/’,’/u01/app/oracle/oradata/stdby/’
Step#7:Configure Listener and tnsnames.ora On Both :
On Primary Database :
tnsnames.ora
STDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdby)
)
)
PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prim)
)
)
Same On Standby , On Standby Databsase Use netca to create listener . and make sure its up
test the connection between both nodes via tnsping m and its should be able to Connect .
Step#8:
on Standby Database
echo “stdby:/u01/app/oracle/product/10.2.0/db_1:N” >> /etc/oratab
export ORACLE_SID=stdby
sqlplus / as sysdba
SQL > startup nomount pfile=’/u01/initSTDBY.ora’;
SQL > Create Spfile from pfile = ‘/u01/initSTDBY.ora’;
Bounce Database.
SQL > Startup nomount ;
SQL > exit;
export ORACLE_SID=stdby ;
rman target=sys/oracle@prim auxiliary=/
Connection should be like the following :
connected to target database: PRIM (DBID=3603586489) –Prim
connected to auxiliary database: PRIM (not mounted) –Standby
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
Now We Done Creating Standby Database , we will Start Sync :
SQL> alter database recover managed standby database disconnect from session;
You can Check If everything is up to date with standby , use archive log list on both node to check archive log number .
Thank you
Osama Mustafa