Skip to content
March 10, 2014 / doganay

HOW TO CREATE PHYSICAL STANDBY DATABASE

1)
--primary
alter database force logging;

2)
--primary listener.ora
MYLISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <MY_PRIMARY_HOST>)(PORT = <MY_PRIMARY_PORT>))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = MYKEY))
)
)

SID_LIST_MYLISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = MYDB1)
(ORACLE_HOME = <MY_PRIMARY_HOST>)
(SID_NAME = MYDB1)
)
)

LOGGING_MYLISTENER = ON
ADMIN_RESTRICTIONS_MYLISTENER = ON
INBOUND_CONNECT_TIMEOUT_MYLISTENER=50
DYNAMIC_REGISTRATION_MYLISTENER=OFF

standby listener.ora
--------------------
MYLISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <MY_STANDBY_HOST>)(PORT = <MY_STANDBY_PORT>))
)
)

SID_LIST_MYLISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = <MYDB1>)
(ORACLE_HOME = <STANDBY_ORACLE_HOME>)
(SID_NAME = <MYDB1>)
)
)

admin_restrictions_MYLISTENER=on
logging_MYLISTENER=ON
inbound_connect_timeout_MYLISTENER=50
DYNAMIC_REGISTRATION_MYLISTENER=OFF

3)
primary tnsnames.ora
--------------------
MYDB1_PRI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <MY_PRIMARY_HOST>)(PORT = <MY_PRIMARY_PORT>))
)
(CONNECT_DATA =
(SERVICE_NAME = MYDB1)
)
)

MYDB1_STD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <MY_STANDBY_HOST>)(PORT = <MY_STANDBY_PORT>))
)
(CONNECT_DATA =
(SERVICE_NAME = MYDB1)
)
)

standby tnsnames.ora
--------------------
MYDB1_PRI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <MY_PRIMARY_HOST>)(PORT = <MY_PRIMARY_PORT>))
)
(CONNECT_DATA =
(SERVICE_NAME = MYDB1)
)
)

MYDB1_STD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <MY_STANDBY_HOST>)(PORT = <MY_STANDBY_PORT>))
)
(CONNECT_DATA =
(SERVICE_NAME = MYDB1)
)
)

MY_PRIMARY_HOST $ scp orapwMYDB1 MY_STANDBY_HOST:<STANDBY_ORACLE_HOME>/dbs

standby:
-------
--create <STANDBY_ORACLE_HOME>/dbs/initMYDB1.ora
sqlplus / as sysdba
SQL> startup nomount

4)
primary:

rman
connect target sys@MYDB1_PRI
connect auxiliary sys@MYDB1_STD

run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
allocate auxiliary channel stby3 type disk;
allocate auxiliary channel stby4 type disk;
duplicate target database for standby from active database NOFILENAMECHECK;
release channel prmy1;
release channel prmy2;
release channel prmy3;
release channel prmy4;
}

5)
standby
-------
*.db_unique_name=MYDB1
*.fal_client=MYDB1_STD
*.fal_server=MYDB1_PRI
*.standby_file_management=auto

6)
--standby
---------
shu immediate
startup mount
create spfile from pfile
shu immediate
startup mount

7)
--primary
---------
alter system set log_archive_dest_2='SERVICE=<SID>_STD ASYNC MAX_FAILURE=3 REOPEN=300';
alter system set log_archive_dest_state_2=ENABLE;
alter system set archive_lag_target=900 scope=both;
spool add_std_redo.sql
select 'alter database add standby logfile thread 1 group ' from dual
union all
select max(group#)+1||'' from v$logfile
union all
select '('''||rtrim (regexp_substr (member, '.*\/'), '/')||'/'||'std_redo01.log'') size ' from v$logfile where rownum<=1
union all
select bytes||';' from v$log where rownum<=1
union all
select 'alter database add standby logfile thread 1 group ' from dual
union all
select max(group#)+2||'' from v$logfile
union all
select '('''||rtrim (regexp_substr (member, '.*\/'), '/')||'/'||'std_redo02.log'') size ' from v$logfile where rownum<=1
union all
select bytes||';' from v$log where rownum<=1;
spool off

8)
--standby
select thread#,
group#,
sequence#,
status,
archived
from v$standby_log;

alter system set standby_file_management=manual;

@add_std_redo

alter system set standby_file_management=auto;
alter database recover managed standby database disconnect from session;

select process, status , sequence# from v$managed_standby;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: