DBA--A dream...A vision....!!

Monday, January 30, 2017

Data Pump using DB link

1)Create DB link for Data Pump on temp database to access orcl:

CREATE public DATABASE LINK pushpak
CONNECT TO scott IDENTIFIED BY tiger
USING '192.168.200.129:1521/orcl.cybage.com';

2)Expdp for exporting tables

expdp dumpfile=table.dmp logfile=table.log directory=DATA_PUMP_DIR tables=emp
scp to temp db location
impdp dumpfile=table.dmp logfile=table.log directory=DATA_PUMP_DIR tables=emp table_exists_action=replace SQLFILE=file.sql

3)To import tablespace to using db_link

[cybage@oracle dpdump]$ impdp tablespaces=NEW logfile=new.log network_link=pushpak remap_tablespace=new:users table_exists_action=replace

Import: Release 11.2.0.1.0 - Production on Thu Jan 12 23:37:03 2017

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

Username: scott
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TABLESPACE_01": scott/******** tablespaces=NEW logfile=new.log network_link=pushpak remap_tablespace=new:users table_exists_action=replace
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 27 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."EMP_NEW" 14 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLESPACE_01" successfully completed at 23:37:12

File system to asm migration

File system to asm migration
############################

Check existing details
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.4/db/orcl/system01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/sysaux01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/undotbs01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/users01.dbf
/u01/app/oracle/product/11.2.0.4/db/orcl/example01.dbf

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8

SQL> show parameter db_recov

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 3882M

SQL> select NAME from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0.4/db/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

SQL> select * from v$Log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          7   52428800        512          1 YES
INACTIVE               1011076 23-JAN-17      1011080 23-JAN-17

         2          1          8   52428800        512          1 NO
CURRENT                1011080 23-JAN-17   2.8147E+14

         3          1          6   52428800        512          1 YES
INACTIVE               1011073 23-JAN-17      1011076 23-JAN-17

[oracle@localhost ~]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N         512   4096 1048576      5740     5514                0            2757              0             N DATA/
MOUNTED EXTERN N         512   4096 1048576      1928     1874                0            1874              0             N FRA/
ASMCMD>

#######################################
Actual migration:
#######################################
1)Change the controlfile parameter
ALTER SYSTEM SET control_files='+DATA','+FRA' scope=spfile;

2)change db create parameter and create pfile
ALTER SYSTEM SET db_create_file_dest='+DATA' SCOPE=spfile;
create pfile='/tmp/pfile_orcl.ora' from spfile;

3)Connect to RMAN and fire

RMAN> backup as copy database format '+DATA';

Starting backup at 23-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/product/11.2.0.4/db/orcl/system01.dbf
output file name=+DATA/orcl/datafile/system.257.934043207 tag=TAG20170123T162643 RECID=2 STAMP=934043339
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:17
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/product/11.2.0.4/db/orcl/sysaux01.dbf
output file name=+DATA/orcl/datafile/sysaux.258.934043341 tag=TAG20170123T162643 RECID=3 STAMP=934043449
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:49
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/product/11.2.0.4/db/orcl/example01.dbf
output file name=+DATA/orcl/datafile/example.259.934043451 tag=TAG20170123T162643 RECID=4 STAMP=934043468
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/product/11.2.0.4/db/orcl/undotbs01.dbf
output file name=+DATA/orcl/datafile/undotbs1.260.934043477 tag=TAG20170123T162643 RECID=5 STAMP=934043489
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/orcl/controlfile/backup.261.934043493 tag=TAG20170123T162643 RECID=6 STAMP=934043497
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/product/11.2.0.4/db/orcl/users01.dbf
output file name=+DATA/orcl/datafile/users.262.934043501 tag=TAG20170123T162643 RECID=7 STAMP=934043501
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 23-JAN-17
channel ORA_DISK_1: finished piece 1 at 23-JAN-17
piece handle=+DATA/orcl/backupset/2017_01_23/nnsnf0_tag20170123t162643_0.263.934043501 tag=TAG20170123T162643 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JAN-17

RMAN>

4)restart db and go to no mount state
connect to RMAN

RMAN> restore controlfile from '/u01/app/oracle/product/11.2.0.4/db/orcl/control01.ctl';

Starting restore at 23-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.264.934044723
output file name=+FRA/orcl/controlfile/current.256.934044729
Finished restore at 23-JAN-17

--Controlfile restored to new location by using modified parameters

5)to change path in controlfile

RMAN> switch database to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.257.934043207"
datafile 2 switched to datafile copy "+DATA/orcl/datafile/sysaux.258.934043341"
datafile 3 switched to datafile copy "+DATA/orcl/datafile/undotbs1.260.934043477"
datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.262.934043501"
datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.259.934043451"

6)Recover database

RMAN> recover database;

Starting recover at 23-JAN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:10

Finished recover at 23-JAN-17

7)Now only redolog file migration is pending
a)change parameter
alter system set db_create_online_log_dest_1='+FRA';

b)add logfile group
alter database add logfile group 4;
alter database add logfile group 5;
alter database add logfile group 6;

c)it will be created on ASM location now drop existing one

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

group 3 will not get drop because file showing status as current
SQL> select GROUP#,THREAD#,MEMBERS,STATUS from v$log;

    GROUP#    THREAD#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         3          1          1 CURRENT
         4          1          1 UNUSED
         5          1          1 UNUSED
         6          1          1 UNUSED

For that
SQL> alter system switch logfile;

System altered.

still it will show status as active

SQL> alter system checkpoint;

System altered.

and drop the group 3 redolog
alter database drop logfile group 3;

****************************Migration Complete to verify*****************************

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.257.934043207
+DATA/orcl/datafile/sysaux.258.934043341
+DATA/orcl/datafile/undotbs1.260.934043477
+DATA/orcl/datafile/users.262.934043501
+DATA/orcl/datafile/example.259.934043451

SQL> select NAME from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/controlfile/current.264.934044723
+FRA/orcl/controlfile/current.256.934044729

SQL> select GROUP#,MEMBER from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         4 +FRA/orcl/onlinelog/group_4.257.934045745
         5 +FRA/orcl/onlinelog/group_5.258.934045773
         6 +FRA/orcl/onlinelog/group_6.259.934045781

****************************************************END*************************************************

Wednesday, January 4, 2017

Switchover standby Manually

Switchover Stimulation

On primary

1)To check error

select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

2)Current status

select message from v$dataguard_status;

3)To switch to standby

SELECT PROGRAM, TYPE FROM V$SESSION WHERE TYPE=’USER’;

alter database commit to switchover to standby with session shutdown;

shut down immediate;

alter database mount standby database;

4)To verify

alter database recover managed standby database disconnect from session;

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

ORCL MOUNTED PHYSICAL STANDBY

SQL> show parameter db_unique

NAME TYPE VALUE

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

db_unique_name string orcl

On stand by

1)to convert to primary

alter database commit to switchover to primary;

2)Bounce and open the database

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

Database mounted.

Database opened.

3)To verify

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

ORCL READ WRITE PRIMARY

SQL> show parameter db_unique

NAME TYPE VALUE

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

db_unique_name string stby

Note:

If standby in dgmgrl showing error

Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Then start the real time apply on standby.

If dbf file deleted from the primary recovery from standby backup datafile

Primary:orcl

Standby:stby

👇👇👇👇👇👇👇👇👇👇👇👇👇👇
On primary
1)Report Schema
report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               YES     /home/cybage/app/cybage/oradata/orcl/system01.dbf
2    480      SYSAUX               NO      /home/cybage/app/cybage/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             YES     /home/cybage/app/cybage/oradata/orcl/undotbs01.dbf
4    11       USERS                NO      /home/cybage/app/cybage/oradata/orcl/users01.dbf
5    100      EXAMPLE              NO      /home/cybage/app/cybage/oradata/orcl/example01.dbf
6    50       NEW                  NO      /home/cybage/app/cybage/oradata/orcl/new.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/cybage/app/cybage/oradata/orcl/temp01.dbf

RMAN> exit

2)Datafile removed from primary
rm /home/cybage/app/cybage/oradata/orcl/new.dbf

On stand by
3)connect to rman of stand by and auxiliary as pri DB
rman target / auxiliary=sys/oracle@orcl

4)restored from stand by
backup as copy datafile 6 auxiliary format '/home/cybage/app/cybage/oradata/orcl/new.dbf';

on primary
5)rman target /
connect catalog rc/rc

6)to offline the datafile
sql 'alter database datafile 6 offline';
ALTER DATABASE DATAFILE '/home/cybage/app/cybage/oradata/orcl/new.dbf' OFFLINE immediate;

7)to register in catalog
catalog datafilecopy '/home/cybage/app/cybage/oradata/orcl/new.dbf';

8)Switch to datafile so controlfile updated
run {
set newname for datafile 6 to '/home/cybage/app/cybage/oradata/orcl/new.dbf';
switch datafile 6;
}

9)recover datafile for primary
recover datafile 6;

Creration Of recovery Catalog🙋🙋

1)Create user for recovery catalog

CREATE USER rc IDENTIFIED BY rc

DEFAULT TABLESPACE users

QUOTA UNLIMITED ON users;

2)Grant permission for the user

grant RECOVERY_CATALOG_OWNER TO rc;

3)Connect to Rman and catalog

rman target / catalog rc/rc@orcl

4)To register database

5)To verify whether views created for RC user

sqlplus

UN:rc

passwd:rc

select * from tab;

😊😊😊😊😊😊😊😊😊😊

Data Guard Brocker Configuration

Pri DB:orcl
standby DB:stby
*****************************************************
1)Parameter change on Primary and standby
alter system set dg_broker_start=true scope=both;

2)edit listner.ora
Put global db name in pri and standby
GLOBAL_DBNAME = orcl_DGMGRL.db domain
GLOBAL_DBNAME = stby_DGMGRL.db domain

3)create configuration
create configuration 'dgorcl' as primary database is 'orcl' connect identifier is orcl ;

4)add database
add database 'stby' as connect identifier is 'stby' maintained as physical ;

5)DGMGRL> show configuration

Configuration - dgorcl

Protection Mode: MaxPerformance
Databases:
    orcl - Primary database
    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

6)
DGMGRL> enable configuration
Enabled.
DGMGRL>
DGMGRL>
DGMGRL> show configuration
Configuration - dgorcl

Protection Mode: MaxPerformance
Databases:
    orcl - Primary database
    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

7)more details for database
DGMGRL> show database verbose orcl

Database - orcl

Role:            PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
    orcl

Properties:
    DGConnectIdentifier             = 'orcl'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'oracle'
    SidName                         = 'orcl'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.200.129)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL.cybage.com)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

Creation of physical stand by on same host for testing purpose

#Creation of physical stand by on same host for testing purpose.
#DB version Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
#Red hat Linux 6.3 64 bit
#pri database:orcl
#standby:stby
################Pushpak Pattewar####################

-)Primary
1)To create the logs on primary forcefully
Alter database force logging;

2)Configure log archive dest 1 for pri and dest 2 for standby.
alter system set LOG_ARCHIVE_DEST_1='location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=STBY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

3)Alter pfile parameter
alter system set FAL_SERVER=STBY;
alter system set FAL_CLIENT=orcl;

alter system set DB_UNIQUE_NAME='orcl' scope=spfile;

alter system set log_archive_config='dg_config=(orcl,stby)';

4)Copy password file
cp orapworcl orapwstby

5)make copy of pfile rename it to initstby.ora and do changes
stby.__db_cache_size=306184192
stby.__java_pool_size=4194304
stby.__large_pool_size=4194304
stby.__oracle_base='/home/cybage/app/cybage'#ORACLE_BASE set from environment
stby.__pga_aggregate_target=314572800
stby.__sga_target=469762048
stby.__shared_io_pool_size=0
stby.__shared_pool_size=146800640
stby.__streams_pool_size=0
*.audit_file_dest='/home/cybage/app/cybage/admin/stby/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/cybage/app/cybage/oradata/stby/control01.ctl','/home/cybage/app/cybage/flash_recovery_area/stby/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/home/cybage/app/cybage/oradata/orcl/','/home/cybage/app/cybage/oradata/stby'
*.db_domain='cybage.com'
*.db_name='orcl'
*.db_recovery_file_dest='/home/cybage/app/cybage/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='stby'
*.diagnostic_dest='/home/cybage/app/cybage'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)'
*.fal_client='stby'
*.fal_server='orcl'
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.200.129)(PORT=1521))'
*.log_archive_config='dg_config=(orcl,stby)'
*.log_archive_dest_1='location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby'
*.log_archive_dest_2='SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/home/cybage/app/cybage/oradata/orcl/','/home/cybage/app/cybage/oradata/stby'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='auto'

6)Put pri db on archivelog mode. Go to mount state and fire.
alter database archivelog;

7)Listner configuration
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /home/cybage/app/cybage/product/11.2.0/dbhome_3)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (ORACLE_HOME = /home/cybage/app/cybage/product/11.2.0/dbhome_3)
      (SID_NAME = stby)
    )
)

LISTENER =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.129)(PORT = 1521))
)

8)Tnsnames.ora configuration

STBY =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.129)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stby)
    )
)

ORCL =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.129)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)

9)Start standby in nomount mode
rman target sys/oracle@orcl auxiliary sys/oracle@stby

10)Rman>duplicate target database for standby dorecover from active database ;

11)create stand by logs on PRIMARY

alter database add standby logfile '/home/cybage/app/cybage/oradata/orcl/stdby_redo03.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/orcl/stdby_redo02.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/orcl/stdby_redo01.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/orcl/stdby_redo04.rdo' size 50M;

lOGS ON STANDBY

alter database add standby logfile '/home/cybage/app/cybage/oradata/stby/stdby_redo01.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/stby/stdby_redo02.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/stby/stdby_redo03.log' size 50M;
alter database add standby logfile '/home/cybage/app/cybage/oradata/stby/stdby_redo04.rdo' size 50M;

12)On STANDBY – START MEDIA RECOVERY
sql > Recover managed standby database disconnect;

#########################################################################################################
On Primary
To check queries

1)select NAME,DBID,LOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE from v$database;

2)select thread#, sequence#, archived, applied from v$archived_log where dest_id=2 and sequence# in ( select max(sequence#) from v$archived_log);

3)To check the archive log gap
select
LOG_ARCHIVED_ON_PRIMARY, LOG_APPLIED_ON_STANDBY, LOG_ARCHIVED_ON_PRIMARY-LOG_APPLIED_ON_STANDBY LOG_GAP
from
(select sequence# LOG_ARCHIVED_ON_PRIMARY from v$archived_log where dest_id=1 and archived='YES' and completion_time=(select max(completion_time) from v$archived_log where dest_id=1)) primary,
(select sequence# LOG_APPLIED_ON_STANDBY from v$archived_log where dest_id=2 and applied='YES' and completion_time=(select max(completion_time) from v$archived_log where dest_id=2));

4)select sequence#,applied from v$archived_log order by sequence#;

5)to check error message
select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

Total Pageviews

Monday, January 30, 2017

Wednesday, January 4, 2017

Primary:orcl

Standby:stby