| Questions to Ask before Creation of Standby |
|
| Ques1- OS version
and DB version should be same |
| Ques2-Mount Points
are same or Difference |
| Ques3-Same User and
Groups should be there as Primary |
| Oues4-Permissions
should be same as Primary |
| Ques5-Physical or
Logical |
| Ques6-Purpose of
Making standby |
| Ques7-Which
Mode(maximum Performance, maximum protection, maximum availability) |
| Ques8-Standard or
Enterprise Edition(In standard archive should be send Manually) |
| Ques9-time gap
between primary and standby. |
PHYSICAL STANDBY
Primary Database Standby Database
Name-ORCL Name-ORCLDR
|
| step-I > Enable Forced
Logging |
| This option ensure that even in the event that a 'nologging'
operation is done, force logging |
| takes precedence and all
operations are logged into the redo logs. |
|
| SQL> ALTER DATABASE FORCE
LOGGING; |
|
| step-2 > Create a Password
File |
| A password file must be created on the
Primary and copied over to the Standby site. The sys password |
| must be identical on both sites. |
|
| [oracle@localhost u01]$
orapwd file=$ORACLE_HOME/dbs/orapworcl password=sys123 force=y |
|
| To check whether password
file is there or not, if not there then create it as above. |
|
| SQL> select * from
v$pwfile_users; |
|
|
| Check the size of online redo
logs |
|
| SQL> select group#,
bytes/1024/1024 mb_size from v$log; |
|
| step-3 > Configure a
Standby Redo Log |
|
| Standby Redo log is added to
enable Data Guard Maximum Availability and Maximum |
| protection modes. It is
important to configure the Standby Redo Logs (SRL) |
| with the same size as the
online redo logs. |
| (maximum number of logfiles
for each thread + 1) * maximum number of threads |
|
| ALTER DATABASE ADD STANDBY
LOGFILE group 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M; |
| ALTER DATABASE ADD STANDBY
LOGFILE group 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M; |
| ALTER DATABASE ADD STANDBY
LOGFILE group 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M; |
|
| Set Primary Database
Initialization Parameters |
|
| Data Guard must use spfile, in
order to configure it we create and configure the standby parameters |
| on a regular pfile, and once
it is ready we convert it to an spfile. |
|
| SQL> create
pfile='?/dbs/pfileorcl.ora' from spfile; |
|
| Step-4 > Edit the pfile to
add the standby parameters, here shown: |
|
| db_unique_name='orcl' |
| LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldr)' |
| LOG_ARCHIVE_DEST_2='SERVICE=orcldr
LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, |
| PRIMARY_ROLE)
DB_UNIQUE_NAME=orcldr' |
| *.fal_server=orcldr |
| *.fal_client=orcl |
| *.standby_file_management=auto |
| *.log_file_name_convert='/u01/app/oracle/oradata/orcldr/','/u01/app/oracle/oradata/orcl/' |
| *.db_file_name_convert='/u01/app/oracle/oradata/orcldr/','/u01/app/oracle/oradata/orcl/' |
|
| Once the new parameter file is
ready we create spfile from pfile |
|
| SQL> shutdown immediate; |
| SQL> startup nomount
pfile='?/dbs/pfileorcl.ora'; |
| SQL> create spfile from
pfile='/?/dbs/pfileorcl.ora'; |
| SQL> shutdown immediate; |
| SQL> startup; |
|
Send pfile to standby server
and rename it to initorcldr.ora and change following parameter
| Step-5
> Copying the pfile created in primary database and renames it to |
| initorcldr.ora, and changes
these parameters: |
|
| db_unique_name='orcldr' |
| LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcldr,orcl)' |
| LOG_ARCHIVE_DEST_2='SERVICE=orcl
LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES |
| ,PRIMARY_ROLE)
DB_UNIQUE_NAME=orcl' |
| *.fal_server=orcl |
| *.fal_client=orcldr |
| *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' |
| *.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldr/' |
| *.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldr/ |
|
| A password file must be
created on the Primary and copied over to the Standby site. The sys |
| password must be identical on
both sites. Copy orapworcl to $ORACLE_HOME/dbs and |
rename file to orapworcldr
Step-6>Configure listeners for the primary
|
| On Primary system: use Oracle
Net Manager to configure a listener for orcl database. Then |
| restart the listener. |
|
| $lsnrctl stop |
| $lsnrctl start |
|
| On Primary system: use Oracle
Net Manager to create network service names for orcl and orcldr. |
| Check tnsping to both
services: |
| $tnsping orcl |
$tnsping orcldr
Configure listeners for the standby
|
| On Standby server: use Net
Manager to configure a listener for orcldr database. Then |
| restart the listener. |
|
| $lsnrctl stop |
| $lsnrctl start |
|
| On Standby system: use Oracle
Net Manager to create network service names for orcl and orcldr. |
| Check tnsping to both
services: |
| $tnsping orcl |
$tnsping orcldr
Step-7> Backup database
| NOTE--
Here We Make a backup folder in /u01 |
|
| SQL> host rman target / |
| RMAN> configure channel
device type disk format '/u01/backup/%U'; |
| RMAN > backup full database
format '/u01/backup/ %d_%U.bckp' plus archivelog |
| format '/u01/backup/%d_%U.bckp'; |
|
| Backup controlfile and
Archive log for Standby |
| RMAN> backup current
controlfile for standby; |
| RMAN> backup archivelog
all; |
| RMAN> exit |
|
|
|
|
Send Backup to Orcldr
| [oracle@localhost backup]$
scp * oracle@192.168.116.131:/u01/backup |
scp * oracle@192.168.196.129:/u01/backup
| Step-8
> Create mount points for Oracle or Client Provide it |
|
| Mount point should be created
on standby server , this includes the location for controlfiles |
| , redo logs, datafiles,
archive log destination and alert logs |
|
| mkdir -p
/u01/app/oracle/oradata/orcldr/ |
| mkdir -p
/u01/app/oracle/flash_recovery_area/ |
| mkdir -p
/u01/app/oracle/admin/orcldr/adump |
| mkdir -p /u01/app/oracle/admin/orcldr/bdump |
| mkdir -p /u01/app/oracle/admin/orcldr/cdump |
mkdir -p /u01/app/oracle/admin/orcldr/udump
| Use RMAN
to restore backup and setup standby |
|
| Connect to RMAN and execute
the following command to create standby database, |
| this should be performed on
standby server after copying backup and setting tnsnames. |
|
| $ export ORACLE_SID=orcldr |
| [oracle@localhost oradata]$
sqlplus '/ as sysdba' |
| SQL> startup nomount
pfile='?/dbs/initorcldr.ora'; |
| SQL> create spfile from
pfile='/?/dbs/initorcldr.ora'; |
|
| SQL> host rman
target=sys/sys@orcl auxiliary / |
| Or |
$ rman target sys/sys@orcl
auxiliary /
| Step-9
> Duplicate command for creating standby |
| |
| RMAN> duplicate target
database for standby nofilenamecheck; |
|
| Step-10 > On same standby
server START THE REDO APPLY |
|
SQL> alter database recover
managed standby database disconnect from session;
Check the Sequences are archived and applied on both sides..
SQL> select sequence#,applied from v$archived_log order by sequence#;
| SQL>
select sequence#,applied from v$archived_log order by sequence#; |
|
| If you ever need to stop log
apply services |
|
| SQL> alter database recover
managed standby database cancel; |
|
| If you want the redo data to
be applied as it is received without waiting for the current standby |
| standby redo log file to be archived, enable
the real-time apply. |
|
| SQL> alter database recover
managed standby database using current logfile disconnect; |
|
|
|
|