“SA”
shoaibansari73@gmail.com 9971920559
ORACLE 11G R2 STANDBY CREATION WITH ACTIVE DATABASE FEATURE.
Here I have two machines with oel 5 installed on both the machine, On one machine I have the database "SADB11G" installed and it is up and running. On the other machine i have just installed the Oracle software.
[oracle@prmsrv ~]$ ps -ef | grep pmon
oracle 4588 6067 0 12:55 pts/1 00:00:00 grep pmon
oracle 26087 1 0 12:35 ? 00:00:00 ora_pmon_SADB11G
[oracle@prmsrv ~]$ export ORACLE_SID=SADB11G
[oracle@prmsrv ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 8 12:56:07 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
SADB11G READ WRITE
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> alter database force logging;
Database altered.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string SADB11G
SQL> show parameter db_unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string SADB11G
SQL> alter system set log_archive_config='DG_CONFIG=(SADB11G,SADB11G_STBY)';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=SADB11G_stby NOAFFIRM ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SADB11G_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET FAL_SERVER=SADB11G_STBY;
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SADB11G/system01.dbf
/u01/app/oracle/oradata/SADB11G/sysaux01.dbf
/u01/app/oracle/oradata/SADB11G/undotbs01.dbf
/u01/app/oracle/oradata/SADB11G/users01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SADB11G/redo03.log
/u01/app/oracle/oradata/SADB11G/redo02.log
/u01/app/oracle/oradata/SADB11G/redo01.log
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='SADB11G_STBY','SADB11G' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='SADB11G_STBY','SADB11G' SCOPE=SPFILE;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup force
ORACLE instance started.
Total System Global Area 615174144 bytes
Fixed Size 1338252 bytes
Variable Size 180356212 bytes
Database Buffers 427819008 bytes
Redo Buffers 5660672 bytes
Database mounted.
Database opened.
SQL>
[root@prmsrv ~]# su - oracle
[oracle@prmsrv ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@prmsrv admin]$ cat tnsnames.ora
SADB11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prmsrv)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SADB11G.WORLD) ---------------------------- remove world from here
)
)
SADB11G_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stnsrv)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SADB11G.WORLD) ---------------------------- remove world from here
)
)
SQL>
SQL> alter database create standby controlfile as '/tmp/SADB11G_stby.ctl';
Database altered.
SQL> create pfile='/tmp/initSADB11G_stby.ora' from spfile;
File created.
SQL>
[oracle@prmsrv admin]$
[oracle@prmsrv admin]$ cd /tmp/
[oracle@prmsrv tmp]$ ls -lrt
total 9596
-rw-r----- 1 oracle oinstall 9748480 Jan 8 13:10 SADB11G_stby.ctl
-rw-r--r-- 1 oracle oinstall 1339 Jan 8 13:11 initSADB11G_stby.ora
[oracle@prmsrv tmp]$ cat initSADB11G_stby.ora
SADB11G.__db_cache_size=427819008
SADB11G.__java_pool_size=4194304
SADB11G.__large_pool_size=4194304
SADB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
SADB11G.__pga_aggregate_target=205520896
SADB11G.__sga_target=616562688
SADB11G.__shared_io_pool_size=0
SADB11G.__shared_pool_size=171966464
SADB11G.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/SADB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/SADB11G/control01.ctl','/u01/app/oracle/flash_recovery_area/SADB11G/control02.ctl'
*.db_block_size=8192
*.db_domain=''
“SA”
shoaibansari73@gmail.com 9971920559
*.db_file_name_convert='SADB11G_STBY','SADB11G'
*.db_name='SADB11G'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SADB11GXDB)'
*.fal_server='SADB11G_STBY'
*.log_archive_config='DG_CONFIG=(SADB11G,SADB11G_STBY)'
*.log_archive_dest_2='SERVICE=SADB11G_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=SADB11G_STBY'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.log_file_name_convert='SADB11G_STBY','SADB11G'
*.open_cursors=300
*.pga_aggregate_target=205520896
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=616562688
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='SADB11G_STBY' <----------------added entries="entries" file="file" forth="forth" here="here" in="in" init.ora="init.ora" on="on" the="the">
*.fal_server='SADB11G'
*.log_archive_dest_2='SERVICE=SADB11G ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=SADB11G'
On standby server
[oracle@stnsrv ~]$
[oracle@stnsrv ~]$ mkdir -p /u01/app/oracle/oradata/SADB11G
[oracle@stnsrv ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/SADB11G
[oracle@stnsrv ~]$ mkdir -p /u01/app/oracle/admin/SADB11G/adump
[oracle@stnsrv ~]$
[oracle@stnsrv ~]$ scp oracle@prmsrv:/tmp/SADB11G_stby.ctl /u01/app/oracle/oradata/SADB11G/control01.ctl
The authenticity of host 'prmsrv (192.168.116.143)' can't be established.
RSA key fingerprint is 49:cb:87:bb:eb:d4:be:e0:e9:1b:a4:5c:7f:bb:f7:3c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'prmsrv,192.168.116.143' (RSA) to the list of known hosts.
oracle@prmsrv's password:
SADB11G_stby.ctl 100% 9520KB 9.3MB/s 00:00
[oracle@stnsrv ~]$
[oracle@stnsrv ~]$ cp /u01/app/oracle/oradata/SADB11G/control01.ctl /u01/app/oracle/oradata/SADB11G/control02.ctl
[oracle@stnsrv ~]$
[oracle@stnsrv ~]$ scp oracle@prmsrv:/tmp/initSADB11G_stby.ora /tmp/initSADB11G_stby.ora
oracle@prmsrv's password:
initSADB11G_stby.ora 100% 1496 1.5KB/s 00:00
[oracle@stnsrv dbs]$ scp oracle@prmsrv:$ORACLE_HOME/dbs/orapwSADB11G $ORACLE_HOME/dbs
oracle@prmsrv's password:
orapwSADB11G 100% 1536 1.5KB/s 00:00
[oracle@stnsrv dbs]$
[oracle@stnsrv admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SADB11G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = SADB11G)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stnsrv)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@stnsrv admin]$
on primary server
SQL> SQL>
SQL>
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/SADB11G/standby_redo02.log') SIZE 50M
SQL>
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/SADB11G/standby_redo03.log') SIZE 50M
SQL>
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/SADB11G/standby_redo04.log') SIZE 50M
SQL>
Database altered.
SQL>
oracle@stnsrv admin]$
[oracle@stnsrv admin]$ export ORACLE_SID=SADB11G
[oracle@stnsrv admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 8 13:49:51 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/initSADB11G_stby.ora';
ORACLE instance started.
Total System Global Area 615174144 bytes
Fixed Size 1338252 bytes
Variable Size 180356212 bytes
Database Buffers 427819008 bytes
Redo Buffers 5660672 bytes
SQL>
[oracle@prmsrv admin]$ cat tnsnames.ora
SADB11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prmsrv)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SADB11G)
)
)
SADB11G_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stnsrv)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SADB11G)
)
)
[oracle@prmsrv admin]$ tnsping SADB11G
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2013 14:00:15
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prmsrv)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = SADB11G)))
OK (10 msec)
[oracle@prmsrv admin]$ tnsping SADB11G_STBY
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2013 14:00:24
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stnsrv)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = SADB11G)))
OK (360 msec)
[oracle@prmsrv admin]$ rman target sys/oracle@SADB11G auxiliary sys/oracle@SADB11G_STBY
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jan 8 14:02:03 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: SADB11G (DBID=273839225)
connected to auxiliary database: SADB11G (not mounted)
RMAN> DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='SADB11G_STBY' COMMENT 'Is standby'
SET LOG_ARCHIVE_DEST_2='SERVICE=SADB11G ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=SADB11G'
SET FAL_SERVER='SADB11G' COMMENT 'Is primary'
NOFILENAMECHECK;
2> 3>
Starting Duplicate Db at 08-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSADB11G' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSADB11G' targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileSADB11G.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileSADB11G.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileSADB11G.ora''";
}
executing Memory Script
Starting backup at 08-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
Finished backup at 08-JAN-13
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileSADB11G.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''SADB11G_STBY'' comment=
''Is standby'' scope=spfile";
sql clone "alter system set LOG_ARCHIVE_DEST_2 =
''SERVICE=SADB11G ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SADB11G'' comment=
'''' scope=spfile";
sql clone "alter system set FAL_SERVER =
''SADB11G'' comment=
''Is primary'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''SADB11G_STBY'' comment= ''Is standby'' scope=spfile
sql statement: alter system set LOG_ARCHIVE_DEST_2 = ''SERVICE=SADB11G ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SADB11G'' comment= '''' scope=spfile
sql statement: alter system set FAL_SERVER = ''SADB11G'' comment= ''Is primary'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 615174144 bytes
Fixed Size 1338252 bytes
Variable Size 180356212 bytes
Database Buffers 427819008 bytes
Redo Buffers 5660672 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/SADB11G/control01.ctl';
restore clone controlfile to '/u01/app/oracle/flash_recovery_area/SADB11G/control02.ctl' from
'/u01/app/oracle/oradata/SADB11G/control01.ctl';
}
executing Memory Script
Starting backup at 08-JAN-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_SADB11G.f tag=TAG20130108T140329 RECID=2
STAMP=804175413
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
Finished backup at 08-JAN-13
Starting restore at 08-JAN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 08-JAN-13
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/SADB11G/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/SADB11G/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/SADB11G/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/SADB11G/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/SADB11G/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/SADB11G/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/SADB11G/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/SADB11G/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/SADB11G/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/SADB11G/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 08-JAN-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/SADB11G/system01.dbf
output file name=/u01/app/oracle/oradata/SADB11G/system01.dbf tag=TAG20130108T140356
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:13:33
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/SADB11G/sysaux01.dbf
output file name=/u01/app/oracle/oradata/SADB11G/sysaux01.dbf tag=TAG20130108T140356
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:27
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/SADB11G/undotbs01.dbf
output file name=/u01/app/oracle/oradata/SADB11G/undotbs01.dbf tag=TAG20130108T140356
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/SADB11G/users01.dbf
output file name=/u01/app/oracle/oradata/SADB11G/users01.dbf tag=TAG20130108T140356
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 08-JAN-13
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/app/oracle/flash_recovery_area/SADB11G/archivelog/2013_01_08/o1_mf_1_7_8gqqgh9g_.arc" auxiliary
format
"/u01/app/oracle/flash_recovery_area/SADB11G_STBY/archivelog/2013_01_08/o1_mf_1_7_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 08-JAN-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=7 RECID=6 STAMP=804176424
output file name=/u01/app/oracle/flash_recovery_area/SADB11G_STBY/archivelog/2013_01_08/o1_mf_1_7_0anutgha_.arc
RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 08-JAN-13
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/SADB11G_STBY/archivelog/2013_01_08/o1_mf_1_7_0anutgha_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/SADB11G_STBY/archivelog/2013_01_08/o1_mf_1_7_0anutgha_.arc
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=804176430 file name=/u01/app/oracle/oradata/SADB11G/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=804176430 file name=/u01/app/oracle/oradata/SADB11G/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=804176430 file name=/u01/app/oracle/oradata/SADB11G/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=804176430 file name=/u01/app/oracle/oradata/SADB11G/users01.dbf
contents of Memory Script:
{
set until scn 767743;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 08-JAN-13
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file
/u01/app/oracle/flash_recovery_area/SADB11G_STBY/archivelog/2013_01_08/o1_mf_1_7_0anutgha_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/SADB11G_STBY/archivelog/2013_01_08/o1_mf_1_7_0anutgha_.arc
thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-JAN-13
Finished Duplicate Db at 08-JAN-13
RMAN>
Then i go to standby in which the auxiliary was open and I again connect
SQL> startup nomount pfile='/tmp/initSADB11G_stby.ora';
ORACLE instance started.
Total System Global Area 615174144 bytes
Fixed Size 1338252 bytes
Variable Size 180356212 bytes
Database Buffers 427819008 bytes
Redo Buffers 5660672 bytes
SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS'
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 5677
Session ID: 1 Serial number: 3
SQL> conn / as sysdba
Connected.
SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#; 2 3
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- -------------------- -------------------- ---------
7 08-JAN-2013 13:09:53 08-JAN-2013 14:20:21 YES
7 08-JAN-2013 13:09:53 08-JAN-2013 14:20:21 YES
SQL>
on the primary server
SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
System altered.
SQL> SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence# ;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------------- --------------------
2 08-JAN-2013 12:30:48 08-JAN-2013 12:59:43
3 08-JAN-2013 12:59:43 08-JAN-2013 13:04:41
4 08-JAN-2013 13:04:41 08-JAN-2013 13:04:46
5 08-JAN-2013 13:04:46 08-JAN-2013 13:07:38
6 08-JAN-2013 13:07:38 08-JAN-2013 13:09:53
7 08-JAN-2013 13:09:53 08-JAN-2013 14:20:21
7 08-JAN-2013 13:09:53 08-JAN-2013 14:20:21
7 rows selected.
SQL> ALTER SYSTEM SWITCH LOGFILE;
Session altered.
SQL> alter system switch logfile;
System altered.
SQL> SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------------- --------------------
2 08-JAN-2013 12:30:48 08-JAN-2013 12:59:43
3 08-JAN-2013 12:59:43 08-JAN-2013 13:04:41
4 08-JAN-2013 13:04:41 08-JAN-2013 13:04:46
5 08-JAN-2013 13:04:46 08-JAN-2013 13:07:38
6 08-JAN-2013 13:07:38 08-JAN-2013 13:09:53
7 08-JAN-2013 13:09:53 08-JAN-2013 14:20:21
7 08-JAN-2013 13:09:53 08-JAN-2013 14:20:21
8 08-JAN-2013 14:20:21 08-JAN-2013 15:39:30
8 08-JAN-2013 14:20:21 08-JAN-2013 15:39:30
9 08-JAN-2013 15:39:30 08-JAN-2013 15:39:40
9 08-JAN-2013 15:39:30 08-JAN-2013 15:39:40
11 rows selected.
SQL>
and then on the standby server
SQL> SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- -------------------- -------------------- ---------
7 08-JAN-2013 13:09:53 08-JAN-2013 14:20:21 YES
7 08-JAN-2013 13:09:53 08-JAN-2013 14:20:21 YES
8 08-JAN-2013 14:20:21 08-JAN-2013 15:39:30 NO ------------------------- applied is still set to no so we will go for the
managed recovery mode
9 08-JAN-2013 15:39:30 08-JAN-2013 15:39:40 NO ------------------------- applied is still set to no
SQL>
on the standby server
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#; 2 3
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- -------------------- -------------------- ---------
7 08-JAN-2013 13:09:53 08-JAN-2013 14:20:21 YES
7 08-JAN-2013 13:09:53 08-JAN-2013 14:20:21 YES
8 08-JAN-2013 14:20:21 08-JAN-2013 15:39:30 YES
9 08-JAN-2013 15:39:30 08-JAN-2013 15:39:40 YES
SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> select name,open_mode,protection_mode,database_role from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- -------------------- -------------------- ----------------
SADB11G MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
7 YES
7 YES
8 YES
9 YES
on primary for DG broker
SQL> alter system set dg_broker_start=true scope=both;
System altered.
SQL> alter system set dg_broker_config_file1='/u01/app/oracle/admin/SADB11G/primary_dg1.conf' scope=spfile;
System altered.
SQL> alter system set dg_broker_config_file2='/u01/app/oracle/admin/SADB11G/primary_dg2.conf' scope=spfile;
System altered.
SQL>
on the standby
SQL> alter system set dg_broker_start=true scope=both;
System altered.
SQL> alter system set dg_broker_config_file1='/u01/app/oracle/admin/SADB11G/primary_dg1.conf' scope=spfile;
System altered.
SQL> alter system set dg_broker_config_file2='/u01/app/oracle/admin/SADB11G/primary_dg2.conf' scope=spfile;
System altered.
[root@prmsrv ~]# su - oracle
[oracle@prmsrv ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@SADB11G
Connected.
DGMGRL> create configuration 'SADB11G' as primary database is 'SADB11G' connect identifier is 'SADB11G';
Configuration "SADB11G" created with primary database "SADB11G"
DGMGRL> add database 'SADB11G_STBY' as connect identifier is 'SADB11G_STBY';
Database "SADB11G_STBY" added
DGMGRL>
DGMGRL> show database verbose 'SADB11G_STBY';
Database - SADB11G_STBY
Role: PHYSICAL STANDBY
Intended State: OFFLINE
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
SADB11G
Properties:
DGConnectIdentifier = 'SADB11G_STBY'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'SADB11G_STBY, SADB11G'
LogFileNameConvert = 'SADB11G_STBY, SADB11G'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'stnsrv'
SidName = 'SADB11G'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stnsrv)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=SADB11G_STBY_DGMGRL)(INSTANCE_NAME=SADB11G)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Database Status:
DISABLED
DGMGRL> show database verbose 'SADB11G';
Database - SADB11G
Role: PRIMARY
Intended State: OFFLINE
Instance(s):
SADB11G
Properties:
DGConnectIdentifier = 'SADB11G'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'SADB11G_STBY, SADB11G'
LogFileNameConvert = 'SADB11G_STBY, SADB11G'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'prmsrv'
SidName = 'SADB11G'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prmsrv)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=SADB11G_DGMGRL)(INSTANCE_NAME=SADB11G)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Database Status:
DISABLED
DGMGRL> enable configuration
Enabled.
DGMGRL>
DGMGRL>
DGMGRL> show database verbose 'SADB11G';
Database - SADB11G
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
SADB11G
Properties:
DGConnectIdentifier = 'SADB11G'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'SADB11G_STBY, SADB11G'
LogFileNameConvert = 'SADB11G_STBY, SADB11G'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'prmsrv'
SidName = 'SADB11G'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prmsrv)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=SADB11G_DGMGRL)(INSTANCE_NAME=SADB11G)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL>
No comments:
Post a Comment