Create a physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE 













guide on how to create a physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command without shutting down the primary and using primary active database files (No need to take a backup)





1. Make the necessary changes to the primary database. 

a. Enable force logging.

b. Creating the password file if one does not exist.

c. Create standby redologs.

d. Modify the parameter file suitable for Dataguard.

2. Ensure that the sql*net connectivity is working fine.



3. Create the standby database over the network using the active(primary) database files.

a. Create the password file

b. Create the initialization parameter file for the standby database (auxiliary database)

c. Create the necessary mount points or the folders for the database files

d. Run the standby creation ON STANDBY by connecting to primary as target database.






DUPLICATE TARGET DATABASE  
FOR STANDBY
FROM ACTIVE DATABASE
SPFILE
   PARAMETER_VALUE_CONVERT '', ''
   SET DB_FILE_NAME_CONVERT '', ''
   SET LOG_FILE_NAME_CONVERT '', ''
   SET SGA_MAX_SIZE 200M
   SET SGA_TARGET 125M;



4. Check the log shipping and apply







--------------------





1. Prepare the production database to be the primary database



a. Ensure that the database is in archivelog mode .


SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG



b. Enable force logging




SQL> select DATABASE_ROLE, GUARD_STATUS,FORCE_LOGGING from  v$database;

DATABASE_ROLE    GUARD_S FORCE_LOGGING
---------------- ------- ---------------------------------------
PRIMARY          NONE    NO

SQL> select FORCE_LOGGING from  v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select DATABASE_ROLE, GUARD_STATUS,FORCE_LOGGING from  v$database;

DATABASE_ROLE    GUARD_S FORCE_LOGGING
---------------- ------- ---------------------------------------
PRIMARY          NONE    YES




c. Create standby redologs




SQL>  set linesize 190
SQL> col MEMBER for a60
SQL> select GROUP#, STATUS, TYPE , MEMBER from  v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ------------------------------------------------------------
         3         ONLINE  /oracle/db/oradata/orcl/redo03.log
         2         ONLINE  /oracle/db/oradata/orcl/redo02.log
         1         ONLINE  /oracle/db/oradata/orcl/redo01.log


SQL> alter database add standby logfile '/oracle/db/oradata/orcl/redo01_stb1.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/oracle/db/oradata/orcl/redo01_stb2.log' size 50M;

Database altered.

SQL>  select GROUP# STATUS, TYPE , MEMBER from  v$logfile;

    STATUS TYPE    MEMBER
---------- ------- ------------------------------------------------------------
         3 ONLINE  /oracle/db/oradata/orcl/redo03.log
         2 ONLINE  /oracle/db/oradata/orcl/redo02.log
         1 ONLINE  /oracle/db/oradata/orcl/redo01.log
         4 STANDBY /oracle/db/oradata/orcl/redo01_stb1.log
         5 STANDBY /oracle/db/oradata/orcl/redo01_stb2.log






d. Modify the primary initialization parameter for dataguard on primary,




SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclstb)';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/databases/orcl/redo/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=orclstb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstb';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.

SQL> alter system set FAL_SERVER=orclstb;
System altered.

SQL> alter system set FAL_CLIENT=orcl;
System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/databases/orclstb/data/','/u01/app/oracle/databases/orcl/data' scope=spfile;
System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/databases/orclstb/redo/','/u01/app/oracle/databases/orcl/redo' scope=spfile;
System altered.





2. Ensure that the sql*net connectivity is working fine.




[oracle@wcp12cr2 admin]$ tnsping ORCL

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 26-OCT-2017 10:28:39

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

Used parameter files:
/oracle/db/ohome/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@wcp12cr2 admin]$ tnsping ORCLSTB

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 26-OCT-2017 10:28:43

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

Used parameter files:
/oracle/db/ohome/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclstb)))
OK (10 msec)




[oracle@wcp12cr2 admin]$ sqlplus system/oracle@ORCLSTB

SQL*Plus: Release 12.1.0.1.0 Production on Thu Oct 26 10:29:05 2017

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

Last Successful login time: Thu Oct 26 2017 10:20:18 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options



3. Create the standby database



a. Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.



The username is required to be SYS and the password needs to be the same on the Primary and Standby. 

The best practice for this is to copy the passwordfile as suggested. 

The password file name must match the instance name/SID used at the standby site, not the DB_NAME.



b. Create a initialization parameter with only one parameter DB_NAME.



DB_NAME=orcl

DB_UNIQUE_NAME=orclstb

DB_BLOCK_SIZE=



c. Create the necessary directories in the standby location to place database files and trace files ($ADR_HOME).





d. Set the environment variable ORACLE_SID to the standby service and start the standby-instance.




% export ORACLE_SID=orclstb
% sqlplus "/ as sysdba"
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initcore1.ora



e. Verify if the connection 'AS SYSDBA' is working



f. On the primary system invoke the RMAN executable and connect to the primary and the auxiliary database ( i.e., the standby)






$ rman target sys/sys@orcl auxiliary sys/sys@orclstb

connected to target database: orcl (DBID=761464750)
connected to auxiliary database: orcl (not mounted)

RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;

duplicate target database for standby from active database
spfile
  parameter_value_convert 'orcl','orclstb'
  set db_unique_name='orclstb'
  set db_file_name_convert='/orcl/','/orclstb/'
  set log_file_name_convert='/orcl/','/orclstb/'
  set control_files='/u01/app/oracle/oradata/control01.ctl'
  set log_archive_max_processes='5'
  set fal_client='orclstb'
  set fal_server='orcl'
  set standby_file_management='MANUAL'
  set log_archive_config='dg_config=(orcl,orclstb)'
  set log_archive_dest_2='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'
;
}

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=147 device type=DISK

allocated channel: prmy2
channel prmy2: SID=130 device type=DISK

allocated channel: prmy3
channel prmy3: SID=137 device type=DISK

allocated channel: prmy4
channel prmy4: SID=170 device type=DISK

allocated channel: stby
channel stby: SID=98 device type=DISK

Starting Duplicate Db at 19-MAY-08

contents of Memory Script:
{
backup as copy reuse
file '/u02/app/oracle/product/11.1.0/db_1/dbs/orapwcore' auxiliary format'/u02/app/oracle/product/11.1.0/db_1/dbs/orapwcore1' 
file'/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore.ora' auxiliary format'/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora' ;
sql clone "alter system set spfile= ''/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora''";
}
executing Memory Script

Starting backup at 19-MAY-08
Finished backup at 19-MAY-08

sql statement: alter system set spfile= ''/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora''

contents of Memory Script:
{
sql clone "alter system set audit_file_dest =''/u02/app/oracle/admin/orclstb/adump'' comment='''' scope=spfile";
sql clone "alter system set dispatchers =''(PROTOCOL=TCP) (SERVICE=core1XDB)'' comment='''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =''service=core11 arch async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orclstb'' comment='''' scope=spfile";
sql clone "alter system set db_unique_name =''orclstb'' comment='''' scope=spfile";
sql clone "alter system set db_file_name_convert =''/orcl/'', ''/orclstb/'' comment='''' scope=spfile";
sql clone "alter system set log_file_name_convert =''/orcl/'', ''/orclstb/'' comment='''' scope=spfile";
sql clone "alter system set control_files =''/u01/app/oracle/oradata/control01.ctl'' comment='''' scope=spfile";
sql clone "alter system set log_archive_max_processes =5 comment='''' scope=spfile";
sql clone "alter system set fal_client =''orclstb'' comment='''' scope=spfile";
sql clone "alter system set fal_server =''orcl'' comment='''' scope=spfile";
sql clone "alter system set standby_file_management =''MANUAL'' comment='''' scope=spfile";
sql clone "alter system set log_archive_config =''dg_config=(orcl,orclstb)'' comment='''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =''service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'' comment='''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ''/u02/app/oracle/admin/orclstb/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=core1XDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=core11 arch async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orclstb'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''orclstb'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/orcl/'', ''/orclstb/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/orcl/'', ''/orclstb/'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''orclstb'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''orcl'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(orcl,orclstb)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 845348864 bytes

Fixed Size 1303188 bytes
Variable Size 482348396 bytes
Database Buffers 356515840 bytes
Redo Buffers 5181440 bytes

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/control01.ctl';
sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 19-MAY-08
channel prmy1: starting datafile copy
copying standby control file
output file name=/u02/app/oracle/product/11.1.0/db_1/dbs/snapcf_orcl.f tag=TAG20080519T173406 RECID=2 STAMP=655148053
channel prmy1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 19-MAY-08

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to"/u02/app/oracle/oradata/orclstb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to "/u02/app/oracle/oradata/orclstb/system01.dbf";
set newname for datafile 2 to "/u02/app/oracle/oradata/orclstb/sysaux01.dbf";
set newname for datafile 3 to "/u02/app/oracle/oradata/orclstb/undotbs01.dbf";
set newname for datafile 4 to "/u02/app/oracle/oradata/orclstb/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format "/u02/app/oracle/oradata/orclstb/system01.dbf" 
datafile 2 auxiliary format "/u02/app/oracle/oradata/orclstb/sysaux01.dbf" 
datafile 3 auxiliary format "/u02/app/oracle/oradata/orclstb/undotbs01.dbf" 
datafile 4 auxiliary format "/u02/app/oracle/oradata/orclstb/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/app/oracle/oradata/orclstb/temp01.dbf in control file

executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting backup at 19-MAY-08
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u02/app/oracle/oradata/orcl/system01.dbf
channel prmy2: starting datafile copy
input datafile file number=00002 name=/u02/app/oracle/oradata/orcl/sysaux01.dbf
channel prmy3: starting datafile copy
input datafile file number=00003 name=/u02/app/oracle/oradata/orcl/undotbs01.dbf
channel prmy4: starting datafile copy
input datafile file number=00004 name=/u02/app/oracle/oradata/orcl/users01.dbf
output file name=/u02/app/oracle/oradata/orclstb/undotbs01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
channel prmy3: datafile copy complete, elapsed time: 00:00:24
output file name=/u02/app/oracle/oradata/orclstb/users01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
channel prmy4: datafile copy complete, elapsed time: 00:00:16
output file name=/u02/app/oracle/oradata/orclstb/system01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
channel prmy1: datafile copy complete, elapsed time: 00:02:32
output file name=/u02/app/oracle/oradata/orclstb/sysaux01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
channel prmy2: datafile copy complete, elapsed time: 00:02:32
Finished backup at 19-MAY-08

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=2 STAMP=655148231 file name=/u02/app/oracle/oradata/orclstb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=655148231 file name=/u02/app/oracle/oradata/orclstb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=655148231 file name=/u02/app/oracle/oradata/orclstb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=655148231 file name=/u02/app/oracle/oradata/orclstb/users01.dbf
Finished Duplicate Db at 19-MAY-08
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4







4. Start managed recovery



Connect to standby using SQL*Plus and start the MRP (Managed Recovery Process). Compare the primary last sequence and MRP (Managed Recovery Process) applying sequence.




SQL> alter database recover managed standby database disconnect from session;



5. Open standby database in Read Only (active dataguard)



If you are licensed to use Active Dataguard (ADG) than open the Standby Database in READ ONLY and start the recovery.




SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect;




6. DGMGRL,



From standby server, Connect to Standby,



DGMGRL>connect sys/@



If Standby is already in mount mode and MRP is running (NOTE : DG broker automatically start the MRP if standby DB in mount mode)




DGMGRL>edit database  set state=apply-off;

DGMGRL>shutdown

DGMGRL>edit database  set state=apply-on;

DGMGRL>startup  --->MRP will get started automatically



Please note if standby_file_management was set to manual. Ensure you set it back to Auto on the standby database once the standby creation is completed































Comments











Post a Comment



















Popular posts from this blog









Oracle Historical Session Information with ASH >10g




















Image







  In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time:   You can use this 2 views to obtain this information:   v$sysmetric_history  --> This is a sort term view, that contains data of last hour  by intervals of  minutes  dba_hist_sysmetric_summary  -->This is a long term view, that contains data of last month by intervals of 1 hour.    v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types:   set linesize 190 col  METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ;   for example, if you are looking for metrics related with connected sessions to database, you can do:   SQL> set linesize 190 col  METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history where lo








Read more










Purging  and archiving  Oracle alert.log and listener.log





















  As i said in  " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools.   1-With logrotate  Linux-command.  I think this is the best option if you have an Oracle linux because it can be setup easily...   Put this lines in a config file but modifing your alert.log and listener.log paths   # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log  { monthly rotate 13 notifempty missingok copytruncate nocreate }  # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate }  i put them into alert_logrotate.conf as you can see:  [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct  5 03:08 alert_logrotate.conf  [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files 








Read more










Check sessions and processes limits in Oracle 





















  Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit:   set linesize 190 set pages 200 select * from gv$resource_limit;      INST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE ---------- ------------------------------ ------------------- --------------- --------------- ------------          1 processes                                       44             300        300             300          1 sessions                                        52             215        512             512           1 enqueue_locks                                   30             295       6480            6480          1 enqueue_resources                               24             165       2472       UNLIMITED          1 ges_procs                                        0               0          0               0          1 ges_ress                                         0               0       








Read more