Monday, October 29, 2012

Oracle 11g new features: Creation Standby Database without creating any RMAN backup

Today we will demonstrate how creation standby database on Oracle 11g without creating any RMAN backup.

Primary Database
Standby Database
IP Address 
10.10.10.1           
10.10.10.2
Hostname 
db-server
db-server2
SID  
testdb
testdb
DB_UNIQUE_NAME
TESTDB_LIVE
TESTDB_STBY
Listener 
LISTENER_PROD
LISTENER_STBY
Listener Port  
1251
1251
TnsNames Entry  
TESTDB_LIVE
TESTDB_STBY
Oradata
../11.2.0/oradata/testdb
../11.2.0/oradata/testdb/
Archive files
../arch/
../arch/

Primary database
db-server /etc/hosts
127.0.0.1               db-server localhost.localdomain localhost
::1                           localhost6.localdomain6 localhost6
10.10.10.1              db-server
10.10.10.2              db-server2


Standby database
db-server2 /etc/hosts
127.0.0.1               db-server2 localhost.localdomain localhost
::1                           localhost6.localdomain6 localhost6
10.10.10.2              db-server2
10.10.10.1              db-server



On Primary server
[oracle@db-server admin]$ more listener.ora
SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC=
  (GLOBAL_NAME=testdb)
    (ORACLE_HOME=/oracle/product/11.2.0/db_1)
      (SID_NAME=testdb)
 )
)


LISTENER_PROD =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)))
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.1)(PORT = 1521)))
  )

ADR_BASE_LISTENER_STBY = /oracle/app/oracle

[oracle@db-server admin]$ more tnsnames.ora
TESTDB_LIVE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = testdb)
    )
  )

TESTDB_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = testdb)
    )
  )


[oracle@db-server ~]$ tnsping testdb_live

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-OCT-2012 13:37:15

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = testdb)))
OK (30 msec)

[oracle@db-server ~]$ tnsping testdb_stby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-OCT-2012 13:37:19

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = testdb)))
OK (860 msec)
[oracle@db-server ~]$    






On Standby server:

[oracle@db-server2 admin]$ more listener.ora
SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC=
  (GLOBAL_NAME=testdb)
    (ORACLE_HOME=/oracle/product/11.2.0/db_1)
      (SID_NAME=testdb)
 )
)

 LISTENER_STBY =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)))
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.2)(PORT = 1521)))
  )

 ADR_BASE_LISTENER_STBY = /oracle/app/oracle




[oracle@db-server2 admin]$ more tnsnames.ora

TESTDB_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = testdb)
    )
  )

TESTDB_LIVE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = testdb)
    )
  )



[oracle@db-server2 dbs]$ tnsping testdb_stby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-OCT-2012 13:37:39

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = testdb)))
OK (30 msec)
[oracle@db-server2 dbs]$ tnsping testdb_live

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-OCT-2012 13:37:42

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = testdb)))
OK (30 msec)
[oracle@db-server2 dbs]$



Set several parameters on primary database:


[oracle@db-server admin]$ export ORACLE_SID=testdb
[oracle@db-server admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 29 13:38:37 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  870457344 bytes
Fixed Size                  1339964 bytes
Variable Size             603983300 bytes
Database Buffers          260046848 bytes
Redo Buffers                5087232 bytes
Database mounted.
Database opened.
SQL>


P.S: My primary database already on ARCHIVEMOD and now setting :
log_archive_dest_2, db_unique_name, log_archive_config etc



SQL> alter system set db_unique_name=TESTDB_LIVE scope=spfile;

System altered.




SQL> alter system set log_archive_dest_2='SERVICE=TESTDB_STBY VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' scope=spfile;

System altered.


SQL> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                              string       TESTDB_LIVE
fal_server                             string       TESTDB_STBY
SQL>



SQL> alter system set log_archive_config='dg_config=(TESTDB_LIVE,TESTDB_STBY)';

System altered.

SQL>


Enable force logging on the Primary database
SQL> alter database force logging;
Database altered.



SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oracle/product/11.2.0/oradata/testdb/redo03.log
/oracle/product/11.2.0/oradata/testdb/redo02.log
/oracle/product/11.2.0/oradata/testdb/redo01.log


Add redo logs for stanby

SQL> alter database add standby logfile '/oracle/product/11.2.0/oradata/testdb/stby_redo04.log' size 52428800;

Database altered.

SQL> alter database add standby logfile '/oracle/product/11.2.0/oradata/testdb/stby_redo05.log' size 52428800;

Database altered.

SQL> alter database add standby logfile '/oracle/product/11.2.0/oradata/testdb/stby_redo06.log' size 52428800;

Database altered.

SQL>  



SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/oracle/product/11.2.0/oradata/testdb/redo03.log
/oracle/product/11.2.0/oradata/testdb/redo02.log
/oracle/product/11.2.0/oradata/testdb/redo01.log
/oracle/product/11.2.0/oradata/testdb/stby_redo04.log
/oracle/product/11.2.0/oradata/testdb/stby_redo05.log
/oracle/product/11.2.0/oradata/testdb/stby_redo06.log

6 rows selected.

SQL> 



On Stanby server:
SQL> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                              string       TESTDB_STBY
fal_server                             string       TESTDB_LIVE
SQL> 



copy password file to stanby server ../dbs from primary server

scp orapwtestdb oracle@10.10.10.2:/oracle/product/11.2.0/db_1/dbs


create pfile on STANDBY server with below parameter

[oracle@db-server2 dbs]$ vi init.ora
DB_NAME=testdb
DB_UNIQUE_NAME=TESTDB_STBY


Startup database on nomount mode using pfile on standby server:

SQL> startup nomount pfile=init.ora
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
SQL>   


create necessary folders on Stanby server
mkdir ...



execute from prod server:


[oracle@db-server dbs]$ export ORACLE_SID=testdb
[oracle@db-server dbs]$ rman target / auxiliary sys/oracle@testdb_stby

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Oct 29 14:16:23 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2580410479)
connected to auxiliary database: TESTDB (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK
spfile
set db_unique_name='TESTDB_STBY'
set standby_file_management='AUTO'2> 3> 4> ;

Starting Duplicate Db at 29-OCT-12
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  '/oracle/product/11.2.0/db_1/dbs/orapwtestdb' auxiliary format
 '/oracle/product/11.2.0/db_1/dbs/orapwtestdb'   targetfile
 '/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora' auxiliary format
 '/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora'   ;
   sql clone "alter system set spfile= ''/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora''";
}
executing Memory Script

Starting backup at 29-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
Finished backup at 29-OCT-12

sql statement: alter system set spfile= ''/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_unique_name =
 ''TESTDB_STBY'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_unique_name =  ''TESTDB_STBY'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     870457344 bytes

Fixed Size                     1339964 bytes
Variable Size                603983300 bytes
Database Buffers             260046848 bytes
Redo Buffers                   5087232 bytes

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/oracle/product/11.2.0/oradata/testdb/control01.ctl';
   restore clone controlfile to  '/oracle/product/11.2.0/flash_recovery_area/testdb/control02.ctl' from
 '/oracle/product/11.2.0/oradata/testdb/control01.ctl';
}
executing Memory Script

Starting backup at 29-OCT-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/product/11.2.0/db_1/dbs/snapcf_testdb.f tag=TAG20121029T141830 RECID=4 STAMP=797955511
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 29-OCT-12

Starting restore at 29-OCT-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 29-OCT-12

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
 "/oracle/product/11.2.0/oradata/testdb/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/oracle/product/11.2.0/oradata/testdb/system01.dbf";
   set newname for datafile  2 to
 "/oracle/product/11.2.0/oradata/testdb/sysaux01.dbf";
   set newname for datafile  3 to
 "/oracle/product/11.2.0/oradata/testdb/undotbs01.dbf";
   set newname for datafile  4 to
 "/oracle/product/11.2.0/oradata/testdb/users01.dbf";
   set newname for datafile  5 to
 "/oracle/product/11.2.0/oradata/testdb/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/oracle/product/11.2.0/oradata/testdb/system01.dbf"   datafile
 2 auxiliary format
 "/oracle/product/11.2.0/oradata/testdb/sysaux01.dbf"   datafile
 3 auxiliary format
 "/oracle/product/11.2.0/oradata/testdb/undotbs01.dbf"   datafile
 4 auxiliary format
 "/oracle/product/11.2.0/oradata/testdb/users01.dbf"   datafile
 5 auxiliary format
 "/oracle/product/11.2.0/oradata/testdb/example01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oracle/product/11.2.0/oradata/testdb/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 29-OCT-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle/product/11.2.0/oradata/testdb/system01.dbf
output file name=/oracle/product/11.2.0/oradata/testdb/system01.dbf tag=TAG20121029T141857
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/product/11.2.0/oradata/testdb/sysaux01.dbf
output file name=/oracle/product/11.2.0/oradata/testdb/sysaux01.dbf tag=TAG20121029T141857
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:17
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/oracle/product/11.2.0/oradata/testdb/example01.dbf
output file name=/oracle/product/11.2.0/oradata/testdb/example01.dbf tag=TAG20121029T141857
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oracle/product/11.2.0/oradata/testdb/undotbs01.dbf
output file name=/oracle/product/11.2.0/oradata/testdb/undotbs01.dbf tag=TAG20121029T141857
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oracle/product/11.2.0/oradata/testdb/users01.dbf
output file name=/oracle/product/11.2.0/oradata/testdb/users01.dbf tag=TAG20121029T141857
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 29-OCT-12

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=797956072 file name=/oracle/product/11.2.0/oradata/testdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=797956072 file name=/oracle/product/11.2.0/oradata/testdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=797956072 file name=/oracle/product/11.2.0/oradata/testdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=797956072 file name=/oracle/product/11.2.0/oradata/testdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=797956072 file name=/oracle/product/11.2.0/oradata/testdb/example01.dbf
Finished Duplicate Db at 29-OCT-12

RMAN> 


On production system switch log file


SQL> alter system switch logfile;


System altered.

SQL>  

On Standby server start managed recovery process

[oracle@db-server2 dbs]$ export ORACLE_SID=testdb
[oracle@db-server2 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 29 14:39:34 2012

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> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL>


SQL> select status from v$instance;

STATUS
------------
MOUNTED


SQL> select name, log_mode, guard_status, switchover_status from v$database;

NAME      LOG_MODE     GUARD_S SWITCHOVER_STATUS
--------- ------------ ------- --------------------
TESTDB    ARCHIVELOG   NONE    SESSIONS ACTIVE

SQL>  


Archive logs not transferred yet.

Check alert.log on both databases


Alertlog On Primary:


Mon Oct 29 14:18:30 2012

Clearing standby activation ID 2580440175 (0x99ce646f)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Mon Oct 29 14:28:34 2012


Alertlog Standby server: 


Linux Error: 2: No such file or directory

Additional information: 3
Errors in file /oracle/product/11.2.0/diag/rdbms/testdb_stby/testdb/trace/testdb_lgwr_10866.trc:
ORA-00313: open failed for members of log group 4 of thread 0
ORA-00312: online log 4 thread 0: '/oracle/product/11.2.0/oradata/testdb/stby_redo04.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3



SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/product/11.2.0/oradata/testdb/stby_redo04.log' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE '/oracle/product/11.2.0/oradata/testdb/stby_redo04.log' SIZE 52428800
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files


To alter database we need to stop recover managed process

SQL>  alter database recover managed standby database cancel ;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/product/11.2.0/oradata/testdb/stby_redo04.log' SIZE 52428800;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/product/11.2.0/oradata/testdb/stby_redo05.log' SIZE 52428800;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/oracle/product/11.2.0/oradata/testdb/stby_redo06.log' SIZE 52428800;

Database altered.



Drop old one

SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> 



Restore recover manager process 

SQL> SQL>  alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL>


Archive files still not transferred yet

restarted standby server 

SQL> startup nomount
ORACLE instance started.

Total System Global Area  870457344 bytes
Fixed Size                  1339964 bytes
Variable Size             603983300 bytes
Database Buffers          260046848 bytes
Redo Buffers                5087232 bytes
SQL> alter database mount standby database;

Database altered.


On Primary database:

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

PROCESS   STATUS
--------- ------------
ARCH      CLOSING
ARCH      CLOSING
ARCH      CONNECTED
ARCH      CLOSING



On Standby server:

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
MRP0      WAIT_FOR_LOG



Some parameter is wrong, so arch files not transferred to standby server:

select status, dest_id, error from v$archive_dest_status;

STATUS       DEST_ID
--------- ----------
ERROR
-----------------------------------------------------------------
VALID              1


BAD PARAM          2


INACTIVE           3



SQL> show parameter LOG_ARCHIVE_DEST_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=TESTDB_STBY VALID_FOR=
                                                 (ONLINE_LOGFILE,PRIMARY_ROLE)


To change parameter we need to update value of log_archive_dest_state_2 parameter

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=TESTDB_STBY VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) async db_unique_name=TESTDB_STBY';

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.


Check again


SQL> select status, dest_id, error from v$archive_dest_status where dest_id=2;

STATUS       DEST_ID     ERROR
-----------------------------------------------------------------
VALID              2


Fixed, there is no any error 


Now if you will check alertlog for standby database you will see arch files going to transfer


On Primary database:

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            43

Now check it on Standby server:


SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            43

Start real-time apply process and check status of process on V$MANAGED_STANDBY dynamic view, you will see there RFS and MRPn processes 

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

SQL> SELECT PROCESS, STATUS, sequence# FROM V$MANAGED_STANDBY;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CLOSING              43
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                 44
MRP0      APPLYING_LOG         44

9 rows selected.


Now switch log file on Primary server

SQL> alter system switch logfile;

System altered.


Let`s check on standby server: 

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CLOSING              44
ARCH      CONNECTED             0
ARCH      CLOSING              43
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                 45
MRP0      APPLYING_LOG         45

9 rows selected.

For more detail please refer to : http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/ha/dataguard/physstby/physstdby.htm



at

4 comments:

  1. Mahir M. QuluzadeOctober 29, 2012 at 7:31 AM

    FROM ACTIVE DATABASE is very useful option.It is new feature(11g) of RMAN DUPLICATE command.

    You can use LOG_FILE_NAME_CONVERT and DB_FILE_NAME_CONVERT parameter explicitly with DUPLICATE command. I think you not need add standby redo logs to Standby Database, if you added standby redo logs to Primary Database before Duplicate command.

    The FAL_CLIENT database initialization parameter is no longer required for 11gR2.

    Thanks for nice post.

    Mahir

    ReplyDelete
  2. UlfetOctober 29, 2012 at 9:27 AM

    First of all Mahir thank you for your comment!

    Yes, sure, this is new feature of Oracle 11g and it is very useful and easy we can use this feature. Also we can use several parameters within DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE .... ex:
    ...
    set control_files ...
    set log_file_name_convert ...
    set log_archive_max_process ...
    set db_file_name_convert ...
    set standby_file_management ...
    set log_archive_config ...
    etc

    I just used set_db_unique_name and set standby_file_management parameters.

    Yes you were right, not essential to create new standby redo files. I follow up alert.log :)

    About fal_client this will needed when we switch between primary standby.

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/ha/dataguard/physstby/physstdby.htm

    ReplyDelete
  3. Mahir M. QuluzadeOctober 29, 2012 at 10:26 PM

    Hi Ulfet,

    I am glad, talk with you about Data Guard .

    Ulfet you can duplicate password file with DUPLICATE command only FOR STANDBY option. In this case, RMAN copies the source database password file to the destination host and overwrites any existing password file for the auxiliary instance.

    DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
    ...
    PASSWORDFILE
    NOFILENAMECHECK;


    Setting FAL_CLIENT initialization parameter is not problem, but not required. Switchover is working without setting FAL_CLIENT parameter http://docs.oracle.com/cd/E11882_01/server.112/e25608/whatsnew.htm#sthref6


    Thanks
    Mahir

    ReplyDelete
  4. UlfetOctober 29, 2012 at 10:50 PM

    Hi Mahir,
    Thanks for your comment and link. It`s surprise for me that we can use PASSWORDFILE within DUPLICATE.

    ReplyDelete

Subscribe to: Post Comments (Atom)

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...