Anuj Singh Oracle DBA: Rman Standby on same host from backup ...

Friday, 1 December 2017

Rman Standby on same host from backup ...

Standby on same host from backup

for sql prompt

SET TERMOUT OFF 
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
SELECT '[-'||sys_context('USERENV', 'DATABASE_ROLE')||'-]'||LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') as sqlprompt FROM DUAL;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON

copy password file

/u01/app/oracle/product/12.1.0/db_2/dbs

 cp orapwvihcdb1 orapwvihcdb2

 cat tnsnames.ora

vihcdb1 =(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=000.001.000.200)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SID=vihcdb1)))
vihcdb2 =(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=000.001.000.200)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SID=vihcdb2)))

vihcdb2tdy=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=000.001.000.200)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SID=vihcdb2)))
vihcdb1prim=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=000.001.000.200)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SID=vihcdb1)))


cat listener.ora

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
      (SID_NAME = plsextproc)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_2)
      (PROGRAM = extproc)
    )
  (SID_DESC =
      (GLOBAL_DBNAME = vihcdb1 )
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_2)
      (SID_NAME = vihcdb1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = vihcdb2)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_2)
      (SID_NAME = vihcdb2 )
    )

    (SID_DESC =
      (GLOBAL_DBNAME = vihcdb1_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_2)
      (SID_NAME = vihcdb1 )
    )
    (SID_DESC =
      (GLOBAL_DBNAME = vihcdb2_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_2)
      (SID_NAME = vihcdb2 )
    )
 )
LISTENER =
 (DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 000.001.000.200)(PORT = 1521))
  )
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
 )

lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2017 07:11:21

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=209.18.114.253)(PORT=1521)))

Services Summary...

Service "59ebeca81c41621ee053fd7212d1f229" has 1 instance(s).

Instance "vihcdb1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "VIHCDB2_DGB" has 1 instance(s).

Instance "vihcdb2", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "VIHCDBXDB" has 1 instance(s).

Instance "vihcdb1", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: cloud-ora, pid: 715>

(ADDRESS=(PROTOCOL=tcp)(HOST=cloud-ora)(PORT=59277))

Service "anuj" has 1 instance(s).

Instance "vihcdb1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "plsextproc" has 1 instance(s).

Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Service "vihcdb1" has 2 instance(s).

Instance "vihcdb1", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Instance "vihcdb1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "vihcdb1_CFG" has 2 instance(s).

Instance "vihcdb1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Instance "vihcdb2", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "vihcdb1_DGB" has 1 instance(s).

Instance "vihcdb1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "vihcdb1_DGMGRL" has 1 instance(s).

Instance "vihcdb1", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Service "vihcdb2" has 2 instance(s).

Instance "vihcdb2", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Instance "vihcdb2", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "vihcdb2_DGMGRL" has 1 instance(s).

Instance "vihcdb2", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

The command completed successfully

Init file from primary                                                                                                                                                                                                                                                          init file Standby 
 strings spfilevihcdb1.ora                                                                                                            strings spfilevihcdb2.ora
vihcdb1.__data_transfer_cache_size=0                                                                                                  vihcdb2.__data_transfer_cache_size=0
vihcdb1.__db_cache_size=176160768                                                                                                     vihcdb2.__db_cache_size=306184192
vihcdb1.__inmemory_ext_roarea=0                                                                                                       vihcdb2.__inmemory_ext_roarea=0
vihcdb1.__inmemory_ext_rwarea=0                                                                                                       vihcdb2.__inmemory_ext_rwarea=0
vihcdb1.__java_pool_size=4194304                                                                                                      vihcdb2.__java_pool_size=4194304
vihcdb1.__large_pool_size=8388608                                                                                                     vihcdb2.__large_pool_size=8388608
vihcdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment                                                              vihcdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
vihcdb1.__pga_aggregate_target=109051904                                                                                              vihcdb2.__pga_aggregate_target=109051904
vihcdb1.__sga_target=536870912                                                                                                        vihcdb2.__sga_target=536870912
vihcdb1.__shared_io_pool_size=20971520                                                                                                vihcdb2.__shared_io_pool_size=0
vihcdb1.__shared_pool_size=310378496                                                                                                  vihcdb2.__shared_pool_size=201326592
vihcdb1.__streams_pool_size=0                                                                                                         vihcdb2.__streams_pool_size=0
*.archive_lag_target=0                                                                                                                *.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/vihcdb1/adump'                                                                               *.audit_file_dest='/u01/app/oracle/admin/vihcdb1/adump'
*.audit_trail='DB'                                                                                                                    *.audit_trail='DB'
*.compatible='12.0.0'                                                                                                                 *.compatible='12.0.0'
*.control_files='/u01/app/oracle/oradata/vihcdb1/ora_control1.ctl','/u01/app/oracle/oradata/vihcdb1/ora_control2.ctl'                 *.control_files='/u01/app/oracle/oradata/vihcdb2/ora_control1.ctl','/u01/app/oracle/oradata/vihcdb2/ora_control2.ctl'#Restore Controlfile
*.data_guard_sync_latency=0                                                                                                           *.data_guard_sync_latency=0
*.db_block_size=8192                                                                                                                  *.db_block_size=8192
*.db_domain=''                                                                                                                        *.db_create_file_dest='/u01/app/oracle/oradata/vihcdb2'
*.db_file_name_convert='/u01/app/oracle/oradata/vihcdb1','/u01/app/oracle/oradata/vihcdb2'                                            *.db_create_online_log_dest_1='/u01/app/oracle/oradata/vihcdb2'
*.db_name='vihcdb1'                                                                                                                   *.db_domain=''
*.db_recovery_file_dest_size=4294967296                                                                                               *.db_file_name_convert='/u01/app/oracle/oradata/vihcdb1','/u01/app/oracle/oradata/vihcdb
*.db_recovery_file_dest='/u01/app/oracle/vihcdb1'                                                                                     *.db_name='VIHCDB1'
*.db_securefile='PREFERRED'                                                                                                           *.db_recovery_file_dest_size=4294967296
*.db_unique_name='vihcdb1'                                                                                                            *.db_recovery_file_dest='/u01/app/oracle/vihcdb1'
*.dg_broker_config_file1='/u01/app/DG/prim_broker_DG2.dat'                                                                            *.db_securefile='PREFERRED'
*.dg_broker_start=TRUE                                                                                                                *.db_unique_name='VIHCDB2'
*.diagnostic_dest='/u01/app/oracle'                                                                                                   *.dg_broker_config_file1='/u01/app/DG/stdy_broker_DG2.dat'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=VIHCDBXDB)'                                                                                    *.dg_broker_start=TRUE
*.enable_pluggable_database=TRUE                                                                                                      *.diagnostic_dest='/u01/app/oracle'
*.fal_server=''                                                                                                                       *.dispatchers='(PROTOCOL=TCP) (SERVICE=VIHCDBXDB)'
vihcdb1.local_listener='(address=(protocol=tcp)(host=209.18.114.253)(port=1521))'                                                     *.enable_pluggable_database=TRUE
*.log_archive_config='dg_config=(vihcdb1,VIHCDB2)'                                                                                    *.fal_server='vihcdb1'
*.log_archive_dest_1='location=/u01/app/Archive_Vihcdb1'                                                                              *.log_archive_config='dg_config=(vihcdb1,VIHCDB2)'
*.log_archive_dest_state_2='ENABLE'                                                                                                   *.log_archive_dest_1='location=/u01/app/Archive_Vihcdb2'
vihcdb1.log_archive_format='%t_%s_%r.dbf'                                                                                             *.log_archive_dest_2=''
*.log_archive_max_processes=4                                                                                                         *.log_archive_dest_state_2='DEFER'
*.log_archive_min_succeed_dest=1                                                                                                      vihcdb2.log_archive_format='%t_%s_%r.dbf'
vihcdb1.log_archive_trace=0                                                                                                           *.log_archive_max_processes=4
*.log_file_name_convert='/u01/app/oracle/oradata/vihcdb1','/u01/app/oracle/oradata/vihcdb2'                                           *.log_archive_min_succeed_dest=1
*.open_cursors=1000                                                                                                                   vihcdb2.log_archive_trace=0
*.processes=300                                                                                                                       *.log_file_name_convert='/u01/app/oracle/oradata/vihcdb1','/u01/app/oracle/oradata/vihcdb2'
*.remote_login_p                                                                                                                      *.open_cursors=1000
asswordfile='EXCLUSIVE'                                                                                                               *.processes=300
*.sga_target=536870912                                                                                                                *.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'                                                                                                      *.sga_target=536870912
*.undo_tablespace='UNDOTBS1'                                                                                                          *.standby_file_management='AUTO'


on primary 
*.log_archive_dest_2='service="vihcdb2tdy"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="vihcdb2" net_timeout=30','valid_for=(online_logfile,all_roles)'




for convert parameter ..  select  distinct substr(name, 1, instr(name, '/',-1)) PATH 
                       from  (
                       select name  from v$datafile
                       union all
                       select NAME  from v$controlfile
                       union all
                       select MEMBER  name from v$logfile
                       union all
                       select name from v$tempfile
) order by 1;                   



for windows

select distinct substr(name, 1, instr(name, '\',-1)) PATH
 from (
select name from v$datafile
 union all
 select NAME from v$controlfile
 union all
 select MEMBER name from v$logfile
 union all
 select name from v$tempfile
 ) order by 1;

*************************************************************************************

with REGEXP_INSTR for windows and unix 




 set linesize 70 pagesize 300
 col PATH for a70
 select distinct substr(name, 1, REGEXP_INSTR(name, '[^/|\]*$')-1 )PATH
from (
select name from v$datafile
union all
select NAME from v$controlfile
union all
select MEMBER name from v$logfile
union all
select name from v$tempfile
union all 
 select FILENAME from V$BLOCK_CHANGE_TRACKING
union all
Select NAME from v$flashback_database_logfile
union all 
SELECT name FROM v$archived_log
 ) 
order by 1;
 
 
 *************************************************************************************
 




alter system set db_file_name_convert='/u01/app/oracle/oradata/vihcdb1','/u01/app/oracle/oradata/vihcdb2' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/vihcdb1','/u01/app/oracle/oradata/vihcdb2' scope=spfile;


SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = defer scope=both;
System altered.





set pause on pagesize 200 linesize 300
col name    for a30
col value   for a70
col ses_mod for a10
col sys_mod for a10
col ins_mod for a10
SELECT p.name,
       p.type,
       p.value,
       p.isses_modifiable      as SES_MOD,
       p.issys_modifiable      as SYS_MOD,
 p.isinstance_modifiable as INS_MOD
       FROM    v$parameter   p
WHERE 1=1  
and lower(name) IN   ('enabled_PDBs_on_standby','remote_login_passwordfile' ,'standby_file_management','log_archive_dest_1'
,'log_archive_dest_state_2','log_archive_dest_2','log_archive_config','db_file_name_convert','log_file_name_convert'
, 'db_name', 'db_unique_name', 'log_archive_format',  'remote_login_passwordfile','fal_server','fal_client','log_archive_config','db_recovery_file_dest_size','db_recovery_file_dest' )
ORDER By VALUE  NULLS LAST;
/







Prompt Oracle 12c
set pause on pagesize 200 linesize 300
col name    for a30
col value   for a70
col ses_mod for a10
col sys_mod for a10
col ins_mod for a10
SELECT p.name,
       p.type,
       p.value,
       p.isses_modifiable      as SES_MOD,
       p.issys_modifiable      as SYS_MOD,
 p.isinstance_modifiable as INS_MOD
       FROM    v$parameter   p
WHERE 1=1  
and name IN   ('enabled_PDBs_on_standby','remote_login_passwordfile' ,'standby_file_management','log_archive_dest_1','log_archive_dest_state_2','log_archive_dest_2','log_archive_config','db_file_name_convert','log_file_name_convert', 'db_name', 'db_unique_name', 'log_archive_format',  'remote_login_passwordfile','fal_server','fal_client','log_archive_config' )
ORDER By VALUE  NULLS LAST;
/


set pause on pagesize 200 linesize 300
col name    for a30
col value   for a70
col ses_mod for a10
col sys_mod for a10
col ins_mod for a10
SELECT p.name,
       p.type,
       p.value,
       p.isses_modifiable      as SES_MOD,
       p.issys_modifiable      as SYS_MOD,
 p.isinstance_modifiable as INS_MOD
       FROM    v$parameter   p
WHERE 1=1  
and name IN   ('remote_login_passwordfile' ,'standby_file_management','log_archive_dest_1','log_archive_dest_state_2','log_archive_dest_2','log_archive_config','db_file_name_convert','log_file_name_convert', 'db_name', 'db_unique_name', 'log_archive_format',  'remote_login_passwordfile','fal_server','fal_client','log_archive_config' )
ORDER By VALUE  NULLS LAST;
/

set pause on pagesize 200 linesize 300
col name    for a30
col value   for a70
col ses_mod for a10
col sys_mod for a10
col ins_mod for a10
col family  for a20
SELECT p.name,
       p.INST_ID as INS,
       p.FAMILY,
       p.type,
       p.value,
       -- p.isses_modifiable      as SES_MOD,
       p.ISSPECIFIED      as SYS_MOD
       FROM    gv$spparameter p
WHERE 1=1  
and name IN   ('remote_login_passwordfile' ,'standby_file_management','log_archive_dest_1','log_archive_dest_state_2','log_archive_dest_2','log_archive_config','db_file_name_convert','log_file_name_convert', 'db_name', 'db_unique_name', 'log_archive_format',  'remote_login_passwordfile','fal_server','fal_client','log_archive_config' )
ORDER By VALUE  NULLS LAST;
/

Rman Backp on primary 
rman target / nocatalog
run
{   sql "alter system switch logfile";
     allocate channel ch1 type disk format '/u01/app/RmanBackup/VIHCDB1/stby_cfile.%U';
     backup database;
     backup current controlfile for standby;
     sql "alter system archive log current";
}

=====================================================
start standby nomount with init file !!!!!!

[oracle@cloud-ora dbs]$ rman |tee /tmp/dup1.txt
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Nov 24 16:55:34 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect auxiliary /
connected to auxiliary database: VIHCDB1 (not mounted)
RMAN> duplicate target database for standby backup location '/u01/app/RmanBackup/VIHCDB1' nofilenamecheck;
Starting Duplicate Db at 24-NOV-17

contents of Memory Script:
{
   restore clone standby controlfile from  '/u01/app/RmanBackup/VIHCDB1/stby_cfile.8gskblnl_1_1';
}
executing Memory Script

Starting restore at 24-NOV-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=253 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/vihcdb2/ora_control1.ctl
output file name=/u01/app/oracle/oradata/vihcdb2/ora_control2.ctl
Finished restore at 24-NOV-17

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=253 device type=DISK

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/vihcdb2/temp01.dbf";
   set newname for tempfile  2 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/temp01.dbf";
   set newname for tempfile  3 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/vihcdb2/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/system01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/vihcdb2/users01.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf";
   set newname for datafile  8 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf";
   set newname for datafile  9 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/system01.dbf";
   set newname for datafile  10 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf";
   set newname for datafile  11 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf";
   set newname for datafile  12 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf";
   set newname for datafile  14 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf";
   set newname for datafile  15 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/vihcdb2/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/vihcdb2/pdbseed/temp01.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/vihcdb2/ANUJ/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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 24-NOV-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/vihcdb2/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/vihcdb2/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/vihcdb2/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/vihcdb2/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/RmanBackup/VIHCDB1/20171124_vihcdb1_264_1_960876431
channel ORA_AUX_DISK_1: piece handle=/u01/app/RmanBackup/VIHCDB1/20171124_vihcdb1_264_1_960876431 tag=TAG20171124T060635
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/vihcdb2/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/RmanBackup/VIHCDB1/20171124_vihcdb1_266_1_960876496
channel ORA_AUX_DISK_1: piece handle=/u01/app/RmanBackup/VIHCDB1/20171124_vihcdb1_266_1_960876496 tag=TAG20171124T060635
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/RmanBackup/VIHCDB1/20171124_vihcdb1_259_1_960876392
channel ORA_AUX_DISK_1: piece handle=/u01/app/RmanBackup/VIHCDB1/20171124_vihcdb1_259_1_960876392 tag=TAG20171124T060452
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:37
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/vihcdb2/ANUJ/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/RmanBackup/VIHCDB1/20171124_vihcdb1_257_1_960876292
channel ORA_AUX_DISK_1: piece handle=/u01/app/RmanBackup/VIHCDB1/20171124_vihcdb1_257_1_960876292 tag=TAG20171124T060452
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:37
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/RmanBackup/VIHCDB1/20171124_vihcdb1_250_1_960872172
channel ORA_AUX_DISK_1: piece handle=/u01/app/RmanBackup/VIHCDB1/20171124_vihcdb1_250_1_960872172 tag=TAG20171124T045611
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 24-NOV-17

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

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=9 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=10 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=11 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=12 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=13 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=14 STAMP=960915581 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf
Finished Duplicate Db at 24-NOV-17
===========================

set linesize 200 pagesize 200
column member            format a50
column first_change#   format 99999999999999999999
column next_change#   format 99999999999999999999
select l.thread#,
       lf.group#,
       lf.member,
       trunc(l.bytes/1024/1024) as size_mb,
       l.status,
       l.archived,
       lf.type,
       lf.is_recovery_dest_file as rdf,
       l.sequence#,
       l.first_change#,
       l.next_change#   
from   v$logfile lf , v$log l
where  l.group# = lf.group#
union 
select l.thread#,
       lf.group#,
       lf.member,
       trunc(l.bytes/1024/1024) as size_mb,
       l.status,
       l.archived,
       lf.type,
       lf.is_recovery_dest_file as rdf,
       l.sequence#,
       l.first_change#,
       l.next_change#   
from   v$logfile lf , v$standby_log l
where  l.group# = lf.group#
order by 7;


prompt Standby info !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
COLUMN column1  FORMAT a70
COLUMN column2  FORMAT a60
set pages 0 linesize 200
SELECT /* First Column */
      '[DB Info]' || chr(10)
    || TRIM(SUBSTR('DB ID                       : ' || d.dbid                                              ,1,50)) || chr(10)
    || TRIM(SUBSTR('DB Name                     : ' || d.name                                              ,1,50)) || chr(10)
    || TRIM(SUBSTR('CDB                         : ' || d.CDB                                               ,1,50)) || chr(10)
    || TRIM(SUBSTR('DB Unique Name              : ' || d.db_unique_name                                    ,1,50)) || chr(10)
    || TRIM(SUBSTR('Platform                    : ' || d.platform_name                                     ,1,50)) || chr(10)
    || TRIM(SUBSTR('DB Created                  : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS')         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Open Mode                   : ' || d.open_mode                                         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Open Resetlogs              : ' || d.open_resetlogs                                    ,1,50)) || chr(10)
    || TRIM(SUBSTR('Flashback ON                : ' || d.flashback_on                                      ,1,50)) || chr(10)
    || TRIM(SUBSTR('ArchiveLog Mode             : ' || d.log_mode                                          ,1,50)) || chr(10)
    || TRIM(SUBSTR('ArchiveLog Compression      : ' || d.archivelog_compression                            ,1,50)) || chr(10)
    || TRIM(SUBSTR('Force Logging               : ' || d.force_logging                                     ,1,50)) || chr(10)
    || TRIM(SUBSTR('Remote Archive              : ' || d.remote_archive                                    ,1,50)) || chr(10)
    || TRIM(SUBSTR('Last Open Incarnation#      : ' || d.last_open_incarnation#                            ,1,50)) || chr(10)
    || TRIM(SUBSTR('Recovery Target Inc#        : ' || d.recovery_target_incarnation#                      ,1,50)) || chr(10)
    || chr(10)   
    || '[Controlfile Info]' || chr(10)
    || TRIM(SUBSTR('Controlfile Type!!!!        : ' || d.controlfile_type                   ,1,50)) || chr(10)
       column1
 ,    
                          '[DataGuard Information]' || chr(10)
    || TRIM(SUBSTR('Primary DB Unique Name      : ' || d.primary_db_unique_name             ,1,50)) || chr(10)
    || TRIM(SUBSTR('DataGuard Role              : ' || d.database_role                      ,1,50)) || chr(10)
    || TRIM(SUBSTR('Protection Mode             : ' || d.protection_mode                    ,1,50)) || chr(10)
    || TRIM(SUBSTR('Protection Level            : ' || d.protection_level                   ,1,50)) || chr(10)
    || TRIM(SUBSTR('DataGuard Broker            : ' || d.dataguard_broker                   ,1,50)) || chr(10)
    || TRIM(SUBSTR('DataGuard Status            : ' || d.guard_status                       ,1,50)) || chr(10)
    || TRIM(SUBSTR('SwitchOver Status           : ' || d.switchover_status                  ,1,50)) || chr(10)
    || TRIM(SUBSTR('Activation SCN              : ' || d.activation#                        ,1,50)) || chr(10)
    || TRIM(SUBSTR('SwitchOver SCN              : ' || d.switchover#                        ,1,50)) || chr(10)
    || TRIM(SUBSTR('Standby Became Primary SCN  : ' || d.standby_became_primary_scn         ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data MIN   : ' || d.supplemental_log_data_min          ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data PK    : ' || d.supplemental_log_data_pk           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data UI    : ' || d.supplemental_log_data_ui           ,1,50)) || chr(10)
    || TRIM(SUBSTR('Supplemental Log Data PL    : ' || d.supplemental_log_data_pl           ,1,50)) || chr(10)
    || chr(10)   
    || '[Fast Start Failover Info]' || chr(10)
    || TRIM(SUBSTR('FS Failover Status          : ' || d.fs_failover_status               ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Current Target  : ' || d.fs_failover_current_target         ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Threshold       : ' || d.fs_failover_threshold              ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Observer Present: ' || d.fs_failover_observer_present        ,1,50)) || chr(10)
    || TRIM(SUBSTR('FS Failover Observer Host   : ' || d.fs_failover_observer_host           ,1,50)) || chr(10)
       column2
FROM v$database d ;





[DB Info]                                                              [DataGuard Information]
DB ID                       : 2963737589                               Primary DB Unique Name      :
DB Name                     : VIHCDB1                                  DataGuard Role              : PRIMARY
CDB                         : YES                                      Protection Mode             : MAXIMUM PERFORMANCE
DB Unique Name              : vihcdb1                                  Protection Level            : MAXIMUM PERFORMANCE
Platform                    : Linux x86 64-bit                         DataGuard Broker            : ENABLED
DB Created                  : 23-SEP-2017 13:52:53                     DataGuard Status            : NONE
Open Mode                   : READ WRITE                               SwitchOver Status           : TO STANDBY
Open Resetlogs              : NOT ALLOWED                              Activation SCN              : 2963791093
Flashback ON                : YES                                      SwitchOver SCN              : 2963791093
ArchiveLog Mode             : ARCHIVELOG                               Standby Became Primary SCN  : 0
ArchiveLog Compression      : DISABLED                                 Supplemental Log Data MIN   : NO
Force Logging               : YES                                      Supplemental Log Data PK    : NO
Remote Archive              : ENABLED                                  Supplemental Log Data UI    : NO
Last Open Incarnation#      : 1                                        Supplemental Log Data PL    : NO
Recovery Target Inc#        : 1
                                                                       [Fast Start Failover Info]
[Controlfile Info]                                                     FS Failover Status          : DISABLED
Controlfile Type         : CURRENT                                     FS Failover Current Target  :
                                                                       FS Failover Threshold       : 0
                                                                       FS Failover Observer Present:
                                                                       FS Failover Observer Host   :
or

set head off verify off echo off pages 1500 linesize 120 feedback off
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select
 'NAME.............................................................: '||     NAME              ,
 'DB UNIQUE NAME...................................................: '||     DB_UNIQUE_NAME    ,
 'CONTROLFILE TYPE.................................................: '||     CONTROLFILE_TYPE  ,
 'Open mode  ......................................................: '||     open_mode         ,   
 'CDB..............................................................: '||     CDB               ,
 'PROTECTION MODE..................................................: '||     PROTECTION_MODE   ,
 'PROTECTION LEVEL.................................................: '||     PROTECTION_LEVEL  ,
 'DATABASE ROLE....................................................: '||     DATABASE_ROLE     ,
 'FLASHBACK ON.....................................................: '||     FLASHBACK_ON      ,
 'SWITCHOVER STATUS................................................: '||     SWITCHOVER_STATUS ,
 'FS_FAILOVER_STATUS...............................................: '||     FS_FAILOVER_STATUS,
 'DATAGUARD BROKER.................................................: '||     DATAGUARD_BROKER  ,
 'LOG MODE.........................................................: '||     LOG_MODE          ,
 'FORCE LOGGING....................................................: '||     FORCE_LOGGING     ,
'CURRENT SCN.......................................................: '||     CURRENT_SCN       , 
'STANDBY BECAME PRIMARY SCN........................................: '||     STANDBY_BECAME_PRIMARY_SCN  ,
'RESETLOGS TIME....................................................: '||     RESETLOGS_TIME  
from v$database;
set head on verify on echo on feedback on   




NAME.............................................................: VIHCDB1
DB UNIQUE NAME...................................................: vihcdb1
CONTROLFILE TYPE.................................................: CURRENT
Open mode  ......................................................: READ WRITE
CDB..............................................................: YES
PROTECTION MODE..................................................: MAXIMUM PERFORMANCE
PROTECTION LEVEL.................................................: MAXIMUM PERFORMANCE
DATABASE ROLE....................................................: PRIMARY
FLASHBACK ON.....................................................: YES
SWITCHOVER STATUS................................................: TO STANDBY
FS_FAILOVER_STATUS...............................................: DISABLED
DATAGUARD BROKER.................................................: ENABLED
LOG MODE.........................................................: ARCHIVELOG
FORCE LOGGING....................................................: YES
CURRENT SCN.......................................................: 4112418
STANDBY BECAME PRIMARY SCN........................................: 0
RESETLOGS TIME....................................................: 23-09-2017 13:52:53


alter system set standby_file_management=manual scope=both sid='*';
 
 
select distinct bytes from v$log;
BYTES
----------
52428800


set linesize 150
select 'alter database add standby logfile '''||regexp_substr(MEMBER,'/.+/')||'stdby_'||regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||''' size '||bytes||';' "Create Standby redo" from v$logfile lf , v$log l
where l.group# = lf.group#
union all
select 'alter database add standby logfile '''||regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'||''' size '||bytes||';' "Create Standby redo" from v$logfile lf , v$log l
where l.group# = lf.group#
and rownum <=1
 /



SQL> alter database add standby logfile '/u01/app/oracle/oradata/vihcdb2/stdby_redo01a.log' size 52428800;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/vihcdb2/stdby_redo02a.log' size 52428800;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/vihcdb2/stdby_redo03a.log' size 52428800;

Database altered.

alter database add standby logfile '/u01/app/oracle/oradata/vihcdb2/stdby_redo04a.log' size 52428800;


some sql for 

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(***,****)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=***' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=**** ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=****' scope=both sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_max_processes=10 scope=both sid='*';
alter system set fal_server=**** scope=both sid='*';
alter system set db_file_name_convert='*****','*****1' scope=spfile sid='*';
alter system set log_file_name_convert='*****','*****1' scope=spfile sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';

*.audit_file_dest='/ora01/app/oracle/admin/ORA11G/adump'
*.control_files=  '/ora01/app/oracle/oradata/ORA11G/control01.ctl',  '/ora01/app/oracle/recovery_area/SORA11G/control02.ctl'
*.db_file_name_convert='/ORA11G/','/SORA11G/'
*.db_name='ORA11G'
*.db_unique_name='SORA11G'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11GXDB)'
*.fal_server='ORA11G'
*.log_archive_config='DG_CONFIG=(ORA11G,nORA11G)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=SORA11G'
*.log_archive_dest_2='SERVICE=ORA11G ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=ORA11G'
*.log_file_name_convert='/ORA11G/','/nORA11G/'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'

FAL_SERVER – specify server name where to request missing archived logs
DB_FILE_NAME_CONVERT – specify how to convert datafile names from primary database
LOG_FILE_NAME_CONVERT – specify how to convert logfile names from primary database
STANDBY_FILE_MANAGEMENT – if set to AUTO files added or dropped on primary database are automatically added or dropped on standby


SQL> ALTER SYSTEM SET log_archive_dest_2 ='service=vihcdbstdy2 async valid_for=(online_logfile,primary_role) db_unique_name=VIHCDB2' ;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2 ='service=vihcdbstdy2 async valid_for=(online_logfile,primary_role) db_unique_name=VIHCDB2' scope=both;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2 ='service=vihcdb2tdy async valid_for=(online_logfile,primary_role) db_unique_name=VIHCDB2' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE scope=both;

System altered.



SQL> ALTER SYSTEM SET log_archive_dest_2 ='service=vihcdb2tdy async valid_for=(online_logfile,primary_role) db_unique_name=VIHCDB2' scope=both;

System altered.

ALTER SYSTEM SET log_archive_dest_2 ='service=vihcdb1prim async valid_for=(online_logfile,primary_role) db_unique_name=VIHCDB1' scope=both;



SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;



set linesize 200 pagesize 200
alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
col STANDBY_DEST for a20
select min(sequence#) min_sequence,min(first_time) min_first_time,max(sequence#) max_sequence, max(first_time) Max_first_time , applied,STANDBY_DEST from v$archived_log
where STANDBY_DEST='YES'
group by applied,STANDBY_DEST;

SQL> alter database recover managed standby database cancel ;

Database altered.


SQL> alter database recover managed standby database disconnect;

Database altered.


 dgmgrl /
- prim & standby
DGMGRL> show database <db_un> statusreport
 - stanby
DGMGRL> validate database <db_un> verbose

show database verbose vihcdb1 
show database verbose vihcdb2
show database vihcdb1 statusreport;
show database vihcdb2 statusreport;
 show parameter dg_broker_config_file
[oracle@cloud-ora DG]$ pwd
/u01/app/DG
SQL> alter system set DG_BROKER_CONFIG_FILE1='/u01/app/DG/prim_broker_DG1.dat' scope=both;
System altered.
SQL> alter system set DG_BROKER_CONFIG_FILE1='/u01/app/DG/prim_broker_DG2.dat' scope=both;
System altered.
 alter system set dg_broker_start=false scope=both;

[oracle@cloud-ora DG]$ dgmgrl /
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Nov 28 08:46:33 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "vihcdb1"
Connected as SYSDG.
DGMGRL> connect sys/vihaan*23
Connected to "vihcdb1"
Connected as SYSDG.


create configuration vihcdb1 as  primary database is vihcdb1  connect identifier is vihcdb1;

DGMGRL> create configuration vihcdb1 as  primary database is vihcdb1  connect identifier is vihcdb1;
Configuration "vihcdb1" created with primary database "vihcdb1"



DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL>  show configuration;
Configuration - vihcdb1
  Protection Mode: MaxPerformance
  Members:
  vihcdb1 - Primary database
    Warning: ORA-16789: standby redo logs configured incorrectly
    vihcdb2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING   (status updated 19 seconds ago)
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL>  show configuration;
Configuration - vihcdb1
  Protection Mode: MaxPerformance
  Members:
  vihcdb1 - Primary database
    vihcdb2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 5 seconds ago)


DGMGRL>  show configuration;
Configuration - vihcdb1
  Protection Mode: MaxPerformance
  Members:
  vihcdb1 - Primary database
    vihcdb2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 5 seconds ago)
DGMGRL> show database verbose vihcdb2
Database - vihcdb2
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 1.00 KByte/s
  Active Apply Rate:  233.00 KByte/s
  Maximum Apply Rate: 233.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    vihcdb2
  Properties:
    DGConnectIdentifier             = 'vihcdb2tdy'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DataGuardSyncLatency            = '0'
    DbFileNameConvert               = '/u01/app/oracle/oradata/vihcdb1, /u01/app/oracle/oradata/vihcdb2'
    LogFileNameConvert              = '/u01/app/oracle/oradata/vihcdb1, /u01/app/oracle/oradata/vihcdb2'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    PreferredObserverHosts          = ''
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cloud-ora)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=VIHCDB2_DGMGRL)(INSTANCE_NAME=vihcdb2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/Archive_Vihcdb2'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'
  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/vihcdb2/vihcdb2/trace/alert_vihcdb2.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/vihcdb2/vihcdb2/trace/drcvihcdb2.log
Database Status:
SUCCESS

SELECT client_process, process, thread#, sequence#, status   FROM v$managed_standby 
WHERE client_process='LGWR' or process='MRP0'
ORDER BY PROCESS;


Error              : ORA-16047: DGID mismatch between destination setting and
show parameter log_archive_config
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(VIHCDB1,VIHCDB2)



SQL>  show parameter db_unique
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      vihcdb1

alter system set log_archive_config='dg_config=(vihcdb1,VIHCDB2)' scope=both;



http://anuj-singh.blogspot.co.uk/2013/06/archive-log-status-and-error.html

set serveroutput on
DECLARE
CURSOR c is
select s.db_unique_name,
s.database_mode,
s.dest_id id, 
s.status stats,
s.recovery_mode,
s.protection_mode, 
s.standby_logfile_count,
s.standby_logfile_active,
s.archived_thread#, 
s.archived_seq#,
s.applied_thread#,
s.applied_seq#,
d.status, 
d.destination, 
d.archiver,
d.transmit_mode, 
d.affirm, 
d.async_blocks,
d.net_timeout, 
d.delay_mins, 
d.reopen_secs,
d.register,
d.binding, 
d.compression, 
d.error, 
to_char(d.FAIL_DATE,'dd-mm-yyyy hh24:mi:ss') FAIL_DATE
from v$archive_dest_status s, v$archive_dest d
where d.dest_id=s.dest_id
and s.db_unique_name != 'NONE'
and d.destination is not null;
BEGIN
dbms_output.put_line('-----------------------------------------------------');
FOR r IN c LOOP
DBMS_OUTPUT.PUT_LINE('Host Name          : ' || UTL_INADDR.GET_HOST_NAME);  
DBMS_OUTPUT.PUT_LINE('Ip Address         : ' || UTL_INADDR.GET_HOST_ADDRESS);  
dbms_output.put_line('Error date         : ' ||r.FAIL_DATE );
dbms_output.put_line('Dest ID            : ' ||r.id );
dbms_output.put_line('Status             : ' ||r.stats);
dbms_output.put_line('DB Name            : ' ||r.db_unique_name );
dbms_output.put_line('DB Mode            : ' ||r.database_mode);
dbms_output.put_line('Recovery Mode      : ' ||r.recovery_mode);
dbms_output.put_line('Protection Mode    : ' ||r.protection_mode);
dbms_output.put_line('SRL Count          : ' ||r.standby_logfile_count );
dbms_output.put_line('SRLActive          : ' ||r.standby_logfile_active);
dbms_output.put_line('Archived Thread#   : ' ||r.archived_thread# );
dbms_output.put_line('ArchivedSeq#       : ' ||r.archived_seq#);
dbms_output.put_line('Applied Thread#    : ' ||r.applied_thread# );
dbms_output.put_line('Destination        : ' ||r.destination);
dbms_output.put_line('Archiver           : ' ||r.archiver);
dbms_output.put_line('Transmit Mode      : ' ||r.transmit_mode);
dbms_output.put_line('Affirm             : ' ||r.affirm);
dbms_output.put_line('Asynchronous Blocks: ' ||r.async_blocks);
dbms_output.put_line('Net Timeout        : ' ||r.net_timeout);
dbms_output.put_line('Delay (Mins)       : ' ||r.delay_mins);
dbms_output.put_line('Reopen (Secs)      : ' ||r.reopen_secs);
dbms_output.put_line('Register           : ' ||r.register);
dbms_output.put_line('Binding            : ' ||r.binding);
dbms_output.put_line('Compression        : ' ||r.compression);
dbms_output.put_line('Error              : ' ||r.error);
dbms_output.put_line('----------------------------------------------------');
END LOOP;
END;
/


-----------------------------------------------------
Host Name          : cloud-ora
Ip Address         : 209.18.114.253
Error date         : 27-11-2017 04:40:57
Dest ID            : 2
Status             : ERROR
DB Name            : VIHCDB2
DB Mode            : UNKNOWN
Recovery Mode      : IDLE
Protection Mode    : MAXIMUM PERFORMANCE
SRL Count          : 0
SRLActive          : 0
Archived Thread#   : 0
ArchivedSeq#       : 0
Applied Thread#    : 0
Destination        : vihcdbstdy2
Archiver           : LGWR
Transmit Mode      : ASYNCHRONOUS
Affirm             : NO
Asynchronous Blocks: 61440
Net Timeout        : 30
Delay (Mins)       : 0
Reopen (Secs)      : 300
Register           : YES
Binding            : OPTIONAL
Compression        : DISABLE
Error              : ORA-12154: TNS:could not resolve the connect identifier specified
col name for a50
select distinct  tf.name,STATUS from v$tablespace ts,v$tempfile tf where ts.TS#=tf.TS#

set linesize 200 pagesize 200
alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
col STANDBY_DEST for a20
select min(sequence#) min_sequence,min(first_time) min_first_time,max(sequence#) max_sequence, max(first_time) Max_first_time , applied,STANDBY_DEST from v$archived_log
where STANDBY_DEST='YES'
group by applied,STANDBY_DEST;

=======================


How To Open Physical Standby For Read Write Testing and Flashback (Doc ID 805438.1)

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)


How to Configure Static Listener Registration for Standalone,RAC and Data Guard setup (Doc ID 2312510.1)

4) Static Listener Registration Syntax on a Primary , Standby Database for shipping logs :

Primary :

Unique name is decided updated in pfile :
db_unique_name=orcl_p

Update Listener with static entry Listener.ora file :

SID_LIST_listener_name_here =
    (SID_LIST =
       (SID_DESC =
          (GLOBAL_DBNAME = orcl_p) ===============> db_unique_name on Primary db
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
          (SID_NAME = orcl) ===============> Instance_name in Primary db
        )
      )

Standby:

Unique name is decided updated in pfile :
db_unique_name=orcl_s

Update Listener with static entry :

SID_LIST_listener_name_here =
      (SID_LIST =
         (SID_DESC =
            (GLOBAL_DBNAME = orcl_s) ===============> db_unique_name on Standby db
            (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
            (SID_NAME = orcls) ===============> Instance_name in Standby db
          )
       )

========================================================
 
Example 

cat listener.ora
# listener.ora Network Configuration File: /oracle/db/ohome/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
 (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /oracle/db/ohome)
      (SID_NAME = orcl)
    )
 )

 
 
 [oracle@wcp12cr2 admin]$ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 12 00:11:08 2024

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

Connected to an idle instance.

SQL>

Resolving ORA-00752 or ORA-600 [3020] During Standby Recovery (Doc ID 1265884.1)

Restore Datafile from Primary Database Service
This example uses 2 nodes to copy the file which doubles the potential bandwidth. More instances can be added (where applicable) and/or the number of channels changed for each instance as desired.
$ rman target sys/<password> <- It is necessary to connect with the password
RMAN > run {
allocate channel c1 type disk connect '/@<standby instance 1 tnsnames.ora descriptor>';
allocate channel c2 type disk connect '/@<standby instance 1 tnsnames.ora descriptor>';
allocate channel c3 type disk connect '/@<standby instance 1 tnsnames.ora descriptor>';
allocate channel c4 type disk connect '/@<standby instance 1 tnsnames.ora descriptor>';
allocate channel c5 type disk connect '/@<standby instance 2 tnsnames.ora descriptor>';
allocate channel c6 type disk connect '/@<standby instance 2 tnsnames.ora descriptor>';
allocate channel c7 type disk connect '/@<standby instance 2 tnsnames.ora descriptor>';
allocate channel c8 type disk connect '/@<standby instance 2 tnsnames.ora descriptor>';
restore datafile <comma separated list of data files> from service '<primary database tnsnames.ora descriptor>' section size 64G;
}


CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT='sys/password@node1:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT='sys/password@node1:1521/clusterservice' FORMAT='/backup/FULL/%d_%U'

7 comments:

Anuj Singh said...: SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/vihcdbd/','/u01/app/oracle/oradata/vihcdbd8/',
2 '/u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/','/u01/app/oracle/oradata/vihcdbd8/ANUJ/datafile/',
3 '/u01/app/oracle/oradata/vihcdbd/VIHCDBD/datafile/','/u01/app/oracle/oradata/vihcdbd8/VIHCDBD/datafile/',
4 '/u01/app/oracle/oradata/vihcdbd/VIHCDBD1/datafile/','/u01/app/oracle/oradata/vihcdbd8/VIHCDBD1/datafile/' scope=spfile;

System altered.

SQL> SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/vihcdbd/VIHCDBD/onlinelog/','/u01/app/oracle/oradata/vihcdbd8/VIHCDBD/onlinelog/' scope=spfile;

System altered.; 28 April 2018 at 13:46
Anuj Singh said...: for windows

SQL>
SQL>
SQL> select distinct substr(name, 1, instr(name, '\',-1)) PATH
2 from (
3 select name from v$datafile
4 union all
5 select NAME from v$controlfile
6 union all
7 select MEMBER name from v$logfile
8 union all
9 select name from v$tempfile
10 ) order by 1;

PATH
--------------------------------------------------------------------------------

C:\APP\ORACLE\ORADATA\ORCL\

SQL>; 12 August 2018 at 20:12
nhuthuy said...: Thanks for sharing, nice post! Post really provice useful information!

An Thái Sơn với website anthaison.vn chuyên sản phẩm máy đưa võng hay máy đưa võng tự động tốt cho bé là địa chỉ bán máy đưa võng giá rẻ tại TP.HCM và giúp bạn tìm máy đưa võng loại nào tốt hiện nay.; 18 July 2019 at 04:38
Anuj Singh said...: Primary: SQL>

alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
set linesize 300
col host for a30
select select 'on_Primary' On_Primary, sysdate, SYS_CONTEXT('USERENV','HOST') HOST,thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:SQL>

alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
set linesize 300
col host for a30
select 'on_standby' On_standby, sysdate, SYS_CONTEXT('USERENV','HOST') HOST, thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:SQL>

alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
set linesize 300
col host for a30
select 'on_standby' On_standby, sysdate, SYS_CONTEXT('USERENV','HOST') HOST, thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;; 4 April 2020 at 14:19
Pratik said...: # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

MONU =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mrac10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = monupri)
)
)

[oracle@mrac10 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = monupri )
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = monu)
)
(SID_DESC =
(GLOBAL_DBNAME = monustby )
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = monus)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mrac10)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
[oracle@mrac10 admin]$; 10 May 2020 at 17:30
Anuj Singh said...: STEP BY STEP RMAN DUPLICATE Database From RAC ASM To RAC ASM (Doc ID 1913937.1); 19 September 2021 at 13:50
Anuj Singh said...: Creating a Physical Standby Database (Doc ID 1475344.1); 3 January 2023 at 21:30

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Friday, 1 December 2017

Rman Standby on same host from backup ...

Rman Standby on same host from backup ...

Restore Datafile from Primary Database Service

7 comments:

Oracle DBA

anuj blog Archive