Recent Updates

airydbablog 5:22 pm on December 8, 2021  

ORACLE 19c ( Long Term Release ) AN INTRODUCTION:

ORACLE 19c ( Long Term Release ) AN INTRODUCTION:

Oracle Database Version 19c (12.2.0.3) released on date 13-Feb-2019, initially it was available for Oracle Exadata only but on date 25-Apr-2019 it has also made available for Linux/Windows servers. Oracle 19c includes several new features and enhancements, as well as a long-term support commitment from Oracle, that make it an attractive upgrade target for existing Oracle databases. For product longevity and patching, Oracle strongly recommends upgrading to 19c which is the Long Term Release with a support end date of April 30, 2027 (or April 30, 2024 if you choose not to pay Extended Support fees or purchase a ULA).

If you are currently running 11.2.x/12.1.x, then you will need to upgrade to the terminal release (i.e. 11.2.0.4/12.1.0.2) for the DB Release you are running and then continue the upgrade process by upgrading to the 19c. If you are currently running 12.2.0.1 or 18c, you should upgrade to 19c without delay.  18c is in Sustaining Support.

Oracle 19c Support Timelines:

ReleasePatching End DateNotes and Exceptions
Oracle 19c Long Term ReleaseApril 30, 2024 with no ES/ULA

April 30, 2027 with ES/ULA		Premier Support (PS) ends April 30, 2024, Extended Support (ES) fees will be required beginning May 01, 2024 through April 30, 2027.

Error Correction / Patching is available through April 30, 2027 with paid ES. Without paid ES, patching is only available until April 30, 2024
Ref: Oracle Documentation
Ref: Oracle Documentation

Oracle Database 19c Supported Operating Systems:

The following Operating System are supported:

  • Oracle Linux 8.1 with the Unbreakable Enterprise Kernel 6: 5.4.17-2011.0.7.el8uek.x86_64 or later
  • Oracle Linux 8 with the Red Hat Compatible kernel: 4.18.0-80.el8.x86_64 or later
  • Oracle Linux 7.4 with the Unbreakable Enterprise Kernel 4: 4.1.12-124.19.2.el7uek.x86_64 or later
  • Oracle Linux 7.4 with the Unbreakable Enterprise Kernel 5: 4.14.35-1818.1.6.el7uek.x86_64 or later
  • Oracle Linux 7.7 with the Unbreakable Enterprise Kernel 6: 5.4.17-2011.4.4.el7uek.x86_64 or later
  • Oracle Linux 7.5 with the Red Hat Compatible Kernel: 3.10.0-862.11.6.el7.x86_64 or later
  • Red Hat Enterprise Linux 8: 4.18.0-80.el8.x86_64 or later
  • Red Hat Enterprise Linux 7.5: 3.10.0-862.11.6.el7.x86_64 or later
  • SUSE Linux Enterprise Server 15: 4.12.14-23-default or later
  • SUSE Linux Enterprise Server 12 SP3: 4.4.162-94.72-default or later
  • Oracle Solaris 11.4 or later
  • AIX 7.1 TL5 SP1 or later, AIX 7.2 TL2 SP1 or later
  • Windows Server 2019, Windows Server 2016, Windows Server 2012 R2, Windows 10 64-bit (Oracle RAC is not Supported), Windows 8.1 64-bit (Oracle RAC is not Supported)

Upgrade Paths for Oracle Database 19c:

Current ReleaseUpgrade Options
18 (all releases), 12.2.0.1, 12.1.0.2, 11.2.0.4Direct upgrade is supported. Perform the upgrade using the current Oracle Database Upgrade Guide, which is this guide.
12.1.0.1 11.2.0.1, 11.2.0.2, 11.2.0.3 11.1.0.6, 11.1.0.7 10.2 or earlier releasesDirect upgrade to Oracle Database 19c is not supported.
Solution: Upgrade to an intermediate Oracle Database release that can be directly upgraded to the current release. Upgrade Oracle Database releases that are not supported for direct upgrade in this release to an intermediate Oracle Database release that is supported for direct upgrade. You can upgrade the intermediate release database to the current Oracle Database release. This restriction does not apply if you use Oracle Data Pump export/import to migrate data to the new release.
For example: If you are upgrading from release 11.2.0.2 or 11.1.0.7, then you must first upgrade to the terminal patch set release for Oracle Database 11g release 2 (11.2.0.4), which is supported for direct upgrade to Oracle Database 19c. If you are upgrading from release 10.2.0.2, 10.2.0.3, 10.2.0.4,10.2.0.5 or 10.1.0.5, then you must first upgrade to release 11.2. or 12.1 If you are upgrading from release 9.2.0.8, then you must first upgrade to a sequence of intermediate Oracle Database releases: Upgrade from release 9.2.0.8 to release 11.2.0.4. Then upgrade from release 11.2 to 19c
Note: Always update to the most recent intermediate release to which you can upgrade directly. Your case can be different from that of the examples provided here.
Ref: Oracle Documentation

Thank you for Reading…This is AIRY…Enjoy your learning.

airydbablog 1:24 pm on January 29, 2020  

Oracle SQL Training Complete Video Series :

  1. Oracle® Database SQL Fundamentals introduction – Session 1

  2. Oracle® Database SQL Fundamentals – Session 2

 

Thank you for watching.… This is Airy…Enjoy Learning:)

airydbablog 11:44 am on January 24, 2020  

Oracle 12c DBA Training Complete Videos :

  1. Oracle 12.2 DBA Classes 1 – Basic Architecture 1 of 4

  2. Oracle 12.2 DBA Classes 2 – Basic Architecture 2 of 4 Listener tnsnames

  3. Oracle 12.2 DBA Classes 3 – Basic Architecture 3 of 4

  4. Oracle 12.2 DBA Classes 4 – Basic Architecture 4 of 4

  5. Oracle 12.2 DBA Classes 5 – Multitenant Details 1 of 2

  6. Oracle 12.2 DBA Classes 6 – Multitenant Details 2 of 2

  7. Oracle 12.2 DBA Classes 7 – Oracle DB Start Stop Sequence 1 of 2

  8. Oracle 12.2 DBA Classes 8 – Oracle DB Start Stop Sequence 2 of 2 and Spfile, Pfile

  9. Oracle 12.2 DBA Classes 9 – Installation 1 of 4 ( Oracle Linux 6.5 Installation )

  10. Oracle 12.2 DBA Classes 10 – Installation 2 of 4 ( Oracle installation Prerequisites)

  11. Oracle 12.2 DBA Classes 11 – Installation 3 of 4 (Oracle Software Installation)

  12. Oracle 12.2 DBA Classes 12 – Installation 4 of 4 ( Database Creation Using dbca – CDB+PDB)

  13. Oracle 12.2 DBA Classes 13 – Oracle Environment ,Alert Log, Listener Log With Practical

  14. Oracle 12.2 DBA Classes 14 – Listener, tnsnames, netca, netmgr, spfile With Practical

  15. Oracle 12.2 DBA Classes 15 – Tablespace With Practical

  16. Oracle 12.2 DBA Classes 16 – Undo Redo with Practical

  17. Oracle 12.2 DBA Classes 17 – User Management 1 of 2 with Practical

  18. Oracle 12.2 DBA Classes 18- User Management 2 of 2 ( User privileges and roles ) with Practical

  19. Oracle 12.2 DBA Classes 19 – Backup Recovery Recovery From Offline (Cold) Backup, Renaming the DB explained with Practical

  20. Oracle 12.2 DBA Classes 20 – Oracle Datapump (expdp AND impdp) explained with Practical

  21. Oracle 12.2 DBA Classes 21 – Oracle sqlloader and oracleloader explained with Practical

  22. Oracle 12.2 DBA Classes 22 – RMAN Introduction 1 of 2 explained with Practical

  23. Oracle 12.2 DBA Classes 23 – RMAN Introduction 2 of 2 explained with Practical

  24. Oracle 12.2 DBA Classes 24 – RMAN Catalog Database explained with practical

  25. Oracle 12.2 DBA Classes 25 – RMAN Commands explained with practical

  26. Oracle 12.2 DBA Classes 26 – RMAN Incremental backup explained with practical

  27. Oracle 12.2 DBA Classes 27 – RMAN Recovery explained with example

  28. Oracle 12.2 DBA Classes 28 – RMAN Tuning and Automatic Diagnostic Repository(ADR) explained with practical

  29. Oracle 12.2 DBA Classes 29 – Flashback Technology explained with practical

  30. Oracle 12.2 DBA Classes 30 – Oracle Performance Tuning, AWR, ASH, ADDM

  31. Oracle 12.2 DBA Classes 31 – Oracle Resource Management , Non Standard Block Cache(NSBC) with practical

airydbablog 12:44 pm on April 18, 2019
Tags: Oracle Release Number, Oracle Software Version Number, Oracle Version Number   

Different ways to get Oracle Software Release Number/Version Number:

Different ways to get Oracle Software Release Number/Version Number:

Oracle_version

SQL> SELECT version FROM V$INSTANCE;

VERSION
-----------------
12.2.0.1.0

SQL> select BANNER from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

SQL> set line 200

col version for a15

col PRODUCT for a50

col STATUS for a20

select * from PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION         STATUS
--------------------------------------- -------------- ----------------------
NLSRTL                                   12.2.0.1.0      Production
Oracle Database 12c Enterprise Edition   12.2.0.1.0      64bit Production
PL/SQL                                   12.2.0.1.0      Production
TNS for Linux:                           12.2.0.1.0      Production

SQL> SET SERVEROUTPUT ON

          BEGIN

          DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || ‘.’ || DBMS_DB_VERSION.RELEASE);

          END;

           /

    12.2

PL/SQL procedure successfully completed.

Thank you for Reading…This is AIRY…Enjoy:)

Also Read Related Links:

  1. Oracle 11g New Features

  2. Oracle 11g R2 – RAC – Index

#oracle-release-number, #oracle-software-version-number, #oracle-version-number

airydbablog 7:41 pm on April 15, 2019
Tags: Archive log gap, Dataguard,   

Resolving Missing Archive log Gap in Oracle 12.2.0.1 Dataguard Using RMAN

Resolving Missing Archive log Gap Standby Side :-

 Problem : Archive AIRYARCH1_337_984864305.dbf is missing from both the side Primary and standby database side. 
           Reason can be any thing either primary recovered from redo log loss or archive logs manually deleted. 
           Standby database is waiting for AIRYARCH1_337_984864305.dbf to receive/apply and stuck on that point.

 On Primary Side :--

[oraairy@airydba AIRYPRD_Archive]$ ls -ltr

-rw-r----- 1 oraairy oinstall    722432 Mar 20 11:28 AIRYARCH1_330_984864305.dbf

-rw-r----- 1 oraairy oinstall   2508288 Mar 20 11:33 AIRYARCH1_331_984864305.dbf

-rw-r----- 1 oraairy oinstall      2048 Mar 20 11:33 AIRYARCH1_332_984864305.dbf

-rw-r----- 1 oraairy oinstall      2048 Mar 20 11:33 AIRYARCH1_333_984864305.dbf

-rw-r----- 1 oraairy oinstall   1273344 Mar 20 12:00 AIRYARCH1_334_984864305.dbf

-rw-r----- 1 oraairy oinstall      2048 Mar 20 12:00 AIRYARCH1_335_984864305.dbf

-rw-r----- 1 oraairy oinstall      1536 Mar 20 12:00 AIRYARCH1_336_984864305.dbf

-rw-r----- 1 oraairy oinstall      1024 Mar 20 12:28 AIRYARCH1_338_984864305.dbf

-rw-r----- 1 oraairy oinstall   1542656 Mar 20 12:30 AIRYARCH1_339_984864305.dbf

On Standby Side :--

[oraairy@airydbasty AIRYPRD_Archive]$ ls -ltr

-rw-r----- 1 oraairy oinstall   2508288 Mar 20 11:33 AIRYARCH1_331_984864305.dbf

-rw-r----- 1 oraairy oinstall      2048 Mar 20 11:33 AIRYARCH1_332_984864305.dbf

-rw-r----- 1 oraairy oinstall      2048 Mar 20 11:33 AIRYARCH1_333_984864305.dbf

-rw-r----- 1 oraairy oinstall   1273344 Mar 20 12:00 AIRYARCH1_334_984864305.dbf

-rw-r----- 1 oraairy oinstall      2048 Mar 20 12:00 AIRYARCH1_335_984864305.dbf

-rw-r----- 1 oraairy oinstall      1536 Mar 20 12:00 AIRYARCH1_336_984864305.dbf

-rw-r----- 1 oraairy oinstall      1024 Mar 20 12:30 AIRYARCH1_338_984864305.dbf

-rw-r----- 1 oraairy oinstall   1542656 Mar 20 12:30 AIRYARCH1_339_984864305.dbf

[oraairy@airydbasty AIRYPRD_Archive]$ sqlp

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 12:55:14 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NAME    DB_UNIQUE_NAME  DATABASE_ROLE    OPEN_MODE  LOG_MODE         PROTECTION_MODE            CURRENT_SCN

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

AIRYPRD  AIRYSTY       PHYSICAL STANDBY  MOUNTED    ARCHIVELOG    MAXIMUM AVAILABILITY  2827626

THREAD# Last_applied_Log

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

1         336

AIRYPRD@AIRYPRM> show parameter log_archive_dest_state_2;

NAME                            TYPE     VALUE

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

log_archive_dest_state_2        string     ENABLE

[oraairy@airydbasty AIRYPRD_Archive]$ rfs

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 12:57:31 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NAME    DB_UNIQUE_NAME  DATABASE_ROLE     OPEN_MODE  LOG_MODE        PROTECTION_MODE            CURRENT_SCN

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

AIRYPRD       AIRYSTY   PHYSICAL STANDBY  MOUNTED    ARCHIVELOG   MAXIMUM AVAILABILITY  2827626

PROCESS   STATUS       THREAD#  SEQUENCE# BLOCK#

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

ARCH   CONNECTED          0       0       0

DGRD   ALLOCATED          0       0       0

DGRD   ALLOCATED          0       0       0

ARCH   CLOSING            1    367    8192

ARCH   CLOSING            1    368       1

ARCH   CLOSING            1    369       1

ARCH   CLOSING            1    370       1

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

MRP0   APPLYING_LOG       1    337       1

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               1    371     658

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

44 rows selected.

THREAD# Last_applied_Log

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

1         336

AIRYPRD@AIRYSTY> alter database register logfile '/AIRYPRD_Archive/AIRYARCH1_338_984864305.dbf';

alter database register logfile '/AIRYPRD_Archive/AIRYARCH1_338_984864305.dbf'

*

ERROR at line 1:

ORA-16089: archive log has already been registered

Note: Here MRP process status is showing that it is applying log AIRYARCH1_337_984864305.dbf but actually, 
      AIRYARCH1_337_984864305.dbf does not exist physically in both the side.I have also tried some foolish, 
      as you are seeing above , to register next archive log AIRYARCH1_338_984864305.dbf but not succeeded.

Solution: 

- Need to recover standby database without archive logs,using RMAN incremental backup of primary database.  
- In my this test environment,File system architecture is same on both primary and standby side.

Steps are as under:

- Find CURRENT_SCN on primary and standby DB.
- Cancel media recovery process on standby database.
- Shutdown standby database.
- Take incremental backup of primary database using "from SCN <Standby CURRENT_SCN>".
- Create a new standby control file on primary database.
- SCP this RMAN incremental backup and newly created standby control file from primary to standby side.
- Startup standby database in nomount mode.
- Replace existing standby control file with newly created standby control file from primary DB.
- Put standby database in mount mode.
- Catalog RMAN incremental backup on standby database.
- Recover standby database.
- Clear all standby logfiles on standby database.
- Start the Media recovery process on standby database.
- Checking: Switch log on primary database , it should be apply on standby database.
- Also check alert logs of primary database and standby database for any error.
Practical Approach :

1: Find CURRENT_SCN on primary and standby DB :

On Primary Side :--

[oraairy@airydba AIRYPRD_Archive]$ sqlp

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 13:14:22 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NAME    DB_UNIQUE_NAME  DATABASE_ROLE  OPEN_MODE  LOG_MODE  PROTECTION_MODE      CURRENT_SCN

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

AIRYPRD  AIRYPRM        PRIMARY     READ WRITE ARCHIVELOG   MAXIMUM AVAILABILITY  3001970

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           /AIRYPRD_Archive/AIRYARCH

Oldest online log sequence     368

Next log sequence to archive   371

Current log sequence          371

AIRYPRD@AIRYPRM>select current_scn from gv$database;

CURRENT_SCN

-----------

3001985

On Standby Side :--

[oraairy@airydbasty AIRYPRD_Archive]$ sqlp

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 13:13:44 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NAME    DB_UNIQUE_NAME  DATABASE_ROLE    OPEN_MODE  LOG_MODE      PROTECTION_MODE     CURRENT_SCN

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

AIRYPRD  AIRYSTY       PHYSICAL STANDBY  MOUNTED   ARCHIVELOG MAXIMUM AVAILABILITY  2827626

THREAD# Last_applied_Log

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

1         336

AIRYPRD@AIRYSTY>select current_scn from gv$database;

CURRENT_SCN

-----------

2827626

AIRYPRD@AIRYSTY>select min (checkpoint_change#) from v$datafile_header;

MIN(CHECKPOINT_CHANGE#)

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

2827627

2: Cancel media recovery process on standby database :

On Standby Side :--

AIRYPRD@AIRYSTY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

3: Shutdown standby database :

On Standby Side :--

AIRYPRD@AIRYSTY> shut immediate;

ORA-01109: database not open

Database dismounted.

4: Take RMAN incremental backup of primary database using "from SCN" :

On Primary Side :--

[oraairy@airydba backup]$ pwd

/oracle/AIRYPRD/backup

[oraairy@airydba backup]$ ll

total 0

[oraairy@airydba backup]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Mar 26 13:22:44 2019

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

connected to target database: AIRYPRD (DBID=1510430767)

RMAN> run {

allocate channel CH1 device type disk;

BACKUP INCREMENTAL FROM SCN 2827626 DATABASE FORMAT '/oracle/AIRYPRD/backup/standby_%d_%t_%c_%p.rmb' tag 'FORSTANDBYGAP';

release channel CH1;

}

allocated channel: CH1

channel CH1: SID=115 device type=DISK

Starting backup at 26-MAR-19

channel CH1: starting full datafile backup set

channel CH1: specifying datafile(s) in backup set

input datafile file number=00001 name=/oracle/AIRYPRD/data/system01.dbf

input datafile file number=00003 name=/oracle/AIRYPRD/data/sysaux01.dbf

input datafile file number=00004 name=/oracle/AIRYPRD/data/undotbs01.dbf

input datafile file number=00007 name=/oracle/AIRYPRD/data/users01.dbf

channel CH1: starting piece 1 at 26-MAR-19

channel CH1: finished piece 1 at 26-MAR-19

piece handle=/oracle/AIRYPRD/backup/standby_AIRYPRD_1003930184_1_1.rmb tag=FORSTANDBYGAP comment=NONE

channel CH1: backup set complete, elapsed time: 00:00:35

channel CH1: starting full datafile backup set

channel CH1: specifying datafile(s) in backup set

including current control file in backup set

channel CH1: starting piece 1 at 26-MAR-19

channel CH1: finished piece 1 at 26-MAR-19

piece handle=/oracle/AIRYPRD/backup/standby_AIRYPRD_1003930219_1_1.rmb tag=FORSTANDBYGAP comment=NONE

channel CH1: backup set complete, elapsed time: 00:00:01

Finished backup at 26-MAR-19

released channel: CH1

RMAN> exit

Recovery Manager complete.

[oraairy@airydba backup]$ ll

total 44152

-rw-r----- 1 oraairy oinstall 33955840 Mar 26 13:30 standby_AIRYPRD_1003930184_1_1.rmb

-rw-r----- 1 oraairy oinstall 11255808 Mar 26 13:30 standby_AIRYPRD_1003930219_1_1.rmb

5: Create a new standby control file on primary database :

On Primary Side :--

[oraairy@airydba backup]$ sqlp

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 13:30:37 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NAME    DB_UNIQUE_NAME  DATABASE_ROLE  OPEN_MODE   LOG_MODE PROTECTION_MODE     CURRENT_SCN

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

AIRYPRD  AIRYPRM        PRIMARY       READ WRITE  ARCHIVELOG  MAXIMUM AVAILABILITY  3004210

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           /AIRYPRD_Archive/AIRYARCH

Oldest online log sequence     369

Next log sequence to archive   372

Current log sequence          372

AIRYPRD@AIRYPRM> alter database create standby controlfile as '/oracle/AIRYPRD/backup/standby.ctl';

Database altered.

AIRYPRD@AIRYPRM>exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

6: SCP this RMAN incremental backup and newly created standby control file from primary to standby side :

On Primary Side :--

[oraairy@airydba backup]$ ll

total 55080

-rw-r----- 1 oraairy oinstall 11190272 Mar 26 13:31 standby.ctl

-rw-r----- 1 oraairy oinstall 33955840 Mar 26 13:30 standby_AIRYPRD_1003930184_1_1.rmb

-rw-r----- 1 oraairy oinstall 11255808 Mar 26 13:30 standby_AIRYPRD_1003930219_1_1.rmb

[oraairy@airydba backup]$ scp *.* oraairy@192.168.75.110:/oracle/AIRYPRD/backup

oraairy@192.168.75.110's password:

standby.ctl                               100%   11MB  10.7MB/s   00:01

standby_AIRYPRD_1003930184_1_1.rmb   100%   32MB  32.4MB/s   00:00

standby_AIRYPRD_1003930219_1_1.rmb   100%   11MB  10.7MB/s   00:01

7: Startup standby database in nomount mode :

On Standby Side :--

[oraairy@airydbasty backup]$ pwd

/oracle/AIRYPRD/backup

[oraairy@airydbasty backup]$ ll

total 55080
-rw-r----- 1 oraairy oinstall 11190272 Mar 26 13:34 standby.ctl

-rw-r----- 1 oraairy oinstall 33955840 Mar 26 13:34 standby_AIRYPRD_1003930184_1_1.rmb

-rw-r----- 1 oraairy oinstall 11255808 Mar 26 13:34 standby_AIRYPRD_1003930219_1_1.rmb

[oraairy@airydbasty backup]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 13:35:32 2019

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

Connected to an idle instance.

AIRYPRD@AIRYSTY> startup nomount;

ORACLE instance started.

Total System Global Area  847249408 bytes

Fixed Size                 8626336 bytes

Variable Size             327159648 bytes

Database Buffers                507510784 bytes

Redo Buffers             3952640 bytes

8: Replace existing standby control file with newly created standby control file from primary DB :

On Standby Side :--

Identify the location of control file in standby database.

AIRYPRD@AIRYSTY> show parameter control_files;

NAME                            TYPE     VALUE
------------------------------- ---------  ------------------------------

control_files                   string     /oracle/AIRYPRD/data/control01.ctl,                                                        /oracle/AIRYPRD/data/control02.ctl

AIRYPRD@AIRYSTY>exit

[oraairy@airydbasty backup]$ ll

total 55080

-rw-r----- 1 oraairy oinstall 11190272 Mar 26 13:34 standby.ctl

-rw-r----- 1 oraairy oinstall 33955840 Mar 26 13:34 standby_AIRYPRD_1003930184_1_1.rmb

-rw-r----- 1 oraairy oinstall 11255808 Mar 26 13:34 standby_AIRYPRD_1003930219_1_1.rmb

[oraairy@airydbasty backup]$ cp standby.ctl /oracle/AIRYPRD/data/

[oraairy@airydbasty backup]$ cd /oracle/AIRYPRD/data

[oraairy@airydbasty data]$ ll

total 1610800

-rw-r----- 1 oraairy oinstall  11190272 Mar 26 13:15 control01.ctl

-rw-r----- 1 oraairy oinstall  11190272 Mar 26 13:15 control02.ctl

-rw-r----- 1 oraairy oinstall  11190272 Mar 26 13:38 standby.ctl

-rw-r----- 1 oraairy oinstall 692068352 Mar 26 12:56 sysaux01.dbf

-rw-r----- 1 oraairy oinstall 859840512 Mar 26 12:56 system01.dbf

-rw-r----- 1 oraairy oinstall  20979712 Nov 25 11:49 temp01.dbf

-rw-r----- 1 oraairy oinstall  57679872 Mar 26 12:56 undotbs01.dbf

-rw-r----- 1 oraairy oinstall   5251072 Mar 26 12:56 users01.dbf

Rename the existing control files of standby database.

[oraairy@airydbasty data]$ mv control01.ctl control01_old.ctl

[oraairy@airydbasty data]$ mv control02.ctl control02_old.ctl

Replace existing control files with newly created standby.ctl

[oraairy@airydbasty data]$ cp standby.ctl control01.ctl

[oraairy@airydbasty data]$ cp standby.ctl control02.ctl

[oraairy@airydbasty data]$ ll

total 1632656

-rw-r----- 1 oraairy oinstall  11190272 Mar 26 13:39 control01.ctl

-rw-r----- 1 oraairy oinstall  11190272 Mar 26 13:15 control01_old.ctl

-rw-r----- 1 oraairy oinstall  11190272 Mar 26 13:40 control02.ctl

-rw-r----- 1 oraairy oinstall  11190272 Mar 26 13:15 control02_old.ctl

-rw-r----- 1 oraairy oinstall  11190272 Mar 26 13:38 standby.ctl

-rw-r----- 1 oraairy oinstall 692068352 Mar 26 12:56 sysaux01.dbf

-rw-r----- 1 oraairy oinstall 859840512 Mar 26 12:56 system01.dbf

-rw-r----- 1 oraairy oinstall  20979712 Nov 25 11:49 temp01.dbf

-rw-r----- 1 oraairy oinstall  57679872 Mar 26 12:56 undotbs01.dbf

-rw-r----- 1 oraairy oinstall   5251072 Mar 26 12:56 users01.dbf

[oraairy@airydbasty data]$ cd /oracle/AIRYPRD/backup

[oraairy@airydbasty backup]$ ll

total 55080

-rw-r----- 1 oraairy oinstall 11190272 Mar 26 13:34 standby.ctl

-rw-r----- 1 oraairy oinstall 33955840 Mar 26 13:34 standby_AIRYPRD_1003930184_1_1.rmb

-rw-r----- 1 oraairy oinstall 11255808 Mar 26 13:34 standby_AIRYPRD_1003930219_1_1.rmb

[oraairy@airydbasty backup]$ rm standby.ctl

[oraairy@airydbasty backup]$ ll

total 44152

-rw-r----- 1 oraairy oinstall 33955840 Mar 26 13:34 standby_AIRYPRD_1003930184_1_1.rmb

-rw-r----- 1 oraairy oinstall 11255808 Mar 26 13:34 standby_AIRYPRD_1003930219_1_1.rmb

9: Put standby database in mount mode :

On Standby Side :--

[oraairy@airydbasty backup]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 13:41:32 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter database mount;

Database altered.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

10 : Catalog RMAN incremental backup on standby database :

On Standby Side :--

[oraairy@airydbasty backup]$ pwd

/oracle/AIRYPRD/backup

[oraairy@airydbasty backup]$ ll

total 44152

-rw-r----- 1 oraairy oinstall 33955840 Mar 26 13:34 standby_AIRYPRD_1003930184_1_1.rmb

-rw-r----- 1 oraairy oinstall 11255808 Mar 26 13:34 standby_AIRYPRD_1003930219_1_1.rmb

[oraairy@airydbasty backup]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Mar 26 13:41:52 2019

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

connected to target database: AIRYPRD (DBID=1510430767, not open)

RMAN> catalog start with '/oracle/AIRYPRD/backup';

 11 : Recover standby database :

On Standby Side :--

RMAN> recover database noredo;

Starting recover at 26-MAR-19

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=72 device type=DISK

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /oracle/AIRYPRD/data/system01.dbf

destination for restore of datafile 00003: /oracle/AIRYPRD/data/sysaux01.dbf

destination for restore of datafile 00004: /oracle/AIRYPRD/data/undotbs01.dbf

destination for restore of datafile 00007: /oracle/AIRYPRD/data/users01.dbf

channel ORA_DISK_1: reading from backup piece /oracle/AIRYPRD/backup/standby_AIRYPRD_1003930184_1_1.rmb

channel ORA_DISK_1: piece handle=/oracle/AIRYPRD/backup/standby_AIRYPRD_1003930184_1_1.rmb tag=FORSTANDBYGAP

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:22

Finished recover at 26-MAR-19

RMAN> exit

12: Clear all standby logfiles on standby database :

On Standby Side :--

[oraairy@airydbasty backup]$ sqlp

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 13:45:46 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NAME    DB_UNIQUE_NAME  DATABASE_ROLE    OPEN_MODE  LOG_MODE      PROTECTION_MODE      CURRENT_SCN

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

AIRYPRD  AIRYSTY      PHYSICAL STANDBY  MOUNTED   ARCHIVELOG  MAXIMUM AVAILABILITY  3004504

THREAD# Last_applied_Log

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

1         371

AIRYPRD@AIRYSTY> set pagesize 100

col MEMBER for a50

select Group#,MEMBER,TYPE,status from v$logfile;

GROUP# MEMBER                                TYPE    STATUS

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

4 /oracle/AIRYPRD/logs/origlog/redosty04.log         ONLINE

4 /oracle/AIRYPRD/logs/mirrlog/redosty04.log         ONLINE

3 /oracle/AIRYPRD/logs/origlog/redosty03.log         ONLINE

3 /oracle/AIRYPRD/logs/mirrlog/redosty03.log         ONLINE

2 /oracle/AIRYPRD/logs/origlog/redosty02.log         ONLINE

2 /oracle/AIRYPRD/logs/mirrlog/redosty02.log         ONLINE

1 /oracle/AIRYPRD/logs/origlog/redosty01.log         ONLINE

1 /oracle/AIRYPRD/logs/mirrlog/redosty01.log         ONLINE

7 /oracle/AIRYPRD/logs/origlog/redostysty01.log       STANDBY

7 /oracle/AIRYPRD/logs/mirrlog/redostysty01.log       STANDBY

8 /oracle/AIRYPRD/logs/origlog/redostysty02.log       STANDBY

8 /oracle/AIRYPRD/logs/mirrlog/redostysty02.log       STANDBY

9 /oracle/AIRYPRD/logs/origlog/redostysty03.log       STANDBY

9 /oracle/AIRYPRD/logs/mirrlog/redostysty03.log       STANDBY

10 /oracle/AIRYPRD/logs/origlog/redostysty04.log       STANDBY

10 /oracle/AIRYPRD/logs/mirrlog/redostysty04.log       STANDBY

11 /oracle/AIRYPRD/logs/origlog/redostysty05.log       STANDBY

11 /oracle/AIRYPRD/logs/mirrlog/redostysty05.log       STANDBY

12 /oracle/AIRYPRD/logs/origlog/redostysty06.log       STANDBY

12 /oracle/AIRYPRD/logs/mirrlog/redostysty06.log       STANDBY

20 rows selected.

AIRYPRD@AIRYSTY>select GROUP#,MEMBERS,BYTES,STATUS from v$log;

GROUP#    MEMBERS    BYTES STATUS

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

1       2  524288000 INACTIVE

4       2  524288000 INACTIVE

3       2  524288000 CURRENT

2       2  524288000 INACTIVE

AIRYPRD@AIRYSTY>select GROUP#,BYTES,status from v$standby_log;

GROUP# BYTES STATUS

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

7  524288000 ACTIVE

8  524288000 UNASSIGNED

9  524288000 UNASSIGNED

10  524288000 UNASSIGNED

11  524288000 UNASSIGNED

12  524288000 UNASSIGNED

6 rows selected.

AIRYPRD@AIRYSTY>ALTER DATABASE CLEAR LOGFILE GROUP 7;

Database altered.

AIRYPRD@AIRYSTY>ALTER DATABASE CLEAR LOGFILE GROUP 8;

Database altered.

AIRYPRD@AIRYSTY>ALTER DATABASE CLEAR LOGFILE GROUP 9;

Database altered.

AIRYPRD@AIRYSTY>ALTER DATABASE CLEAR LOGFILE GROUP 10;

Database altered.

AIRYPRD@AIRYSTY>ALTER DATABASE CLEAR LOGFILE GROUP 11;

Database altered.

AIRYPRD@AIRYSTY>ALTER DATABASE CLEAR LOGFILE GROUP 12;

Database altered.

AIRYPRD@AIRYSTY>exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oraairy@airydbasty backup]$ cd /AIRYPRD_Archive/

[oraairy@airydbasty AIRYPRD_Archive]$ ls -ltr

total 2038288

-rw-r----- 1 oraairy oinstall    292864 Mar 26 12:03 AIRYARCH1_366_984864305.dbf

-rw-r----- 1 oraairy oinstall   4692480 Mar 26 12:46 AIRYARCH1_367_984864305.dbf

-rw-r----- 1 oraairy oinstall     22528 Mar 26 12:47 AIRYARCH1_368_984864305.dbf

-rw-r----- 1 oraairy oinstall      2048 Mar 26 12:47 AIRYARCH1_369_984864305.dbf

-rw-r----- 1 oraairy oinstall     11264 Mar 26 12:47 AIRYARCH1_370_984864305.dbf

-rw-r----- 1 oraairy oinstall      8192 Mar 26 13:43 AIRYARCH1_373_984864305.dbf

-rw-r----- 1 oraairy oinstall   4058624 Mar 26 13:43 AIRYARCH1_372_984864305.dbf

-rw-r----- 1 oraairy oinstall      5120 Mar 26 13:44 AIRYARCH1_374_984864305.dbf

-rw-r----- 1 oraairy oinstall    528896 Mar 26 13:49 AIRYARCH1_375_984864305.dbf

On Primary Side :--

[oraairy@airydba backup]$ cd /AIRYPRD_Archive/

[oraairy@airydba AIRYPRD_Archive]$ ls -ltr

total 2039844

-rw-r----- 1 oraairy oinstall    507904 Mar 26 11:46 AIRYARCH1_360_984864305.dbf

-rw-r----- 1 oraairy oinstall      3072 Mar 26 11:46 AIRYARCH1_361_984864305.dbf

-rw-r----- 1 oraairy oinstall    180224 Mar 26 11:52 AIRYARCH1_362_984864305.dbf

-rw-r----- 1 oraairy oinstall     11264 Mar 26 11:52 AIRYARCH1_363_984864305.dbf

-rw-r----- 1 oraairy oinstall    167424 Mar 26 11:58 AIRYARCH1_364_984864305.dbf

-rw-r----- 1 oraairy oinstall      7168 Mar 26 11:58 AIRYARCH1_365_984864305.dbf

-rw-r----- 1 oraairy oinstall    292864 Mar 26 12:03 AIRYARCH1_366_984864305.dbf

-rw-r----- 1 oraairy oinstall   4692480 Mar 26 12:46 AIRYARCH1_367_984864305.dbf

-rw-r----- 1 oraairy oinstall     22528 Mar 26 12:47 AIRYARCH1_368_984864305.dbf

-rw-r----- 1 oraairy oinstall      2048 Mar 26 12:47 AIRYARCH1_369_984864305.dbf

-rw-r----- 1 oraairy oinstall     11264 Mar 26 12:47 AIRYARCH1_370_984864305.dbf

-rw-r----- 1 oraairy oinstall   1057792 Mar 26 13:15 AIRYARCH1_371_984864305.dbf

-rw-r----- 1 oraairy oinstall   4058624 Mar 26 13:43 AIRYARCH1_372_984864305.dbf

-rw-r----- 1 oraairy oinstall      8192 Mar 26 13:43 AIRYARCH1_373_984864305.dbf

-rw-r----- 1 oraairy oinstall      5120 Mar 26 13:44 AIRYARCH1_374_984864305.dbf

-rw-r----- 1 oraairy oinstall    528896 Mar 26 13:49 AIRYARCH1_375_984864305.dbf

[oraairy@airydba AIRYPRD_Archive]$ sqlp

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 13:51:30 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NAME    DB_UNIQUE_NAME  DATABASE_ROLE  OPEN_MODE  LOG_MODE  PROTECTION_MODE      CURRENT_SCN

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

AIRYPRD  AIRYPRM        PRIMARY     READ WRITE  ARCHIVELOG   MAXIMUM AVAILABILITY  3007543

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           /AIRYPRD_Archive/AIRYARCH

Oldest online log sequence     373

Next log sequence to archive   376

Current log sequence          376

13: Start the Media recovery process on standby database :

On Standby Side :--

AIRYPRD@AIRYSTY>alter database recover managed standby database disconnect;

Database altered.

AIRYPRD@AIRYSTY>exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oraairy@airydbasty AIRYPRD_Archive]$ rfs

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 13:53:24 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NAME    DB_UNIQUE_NAME  DATABASE_ROLE    OPEN_MODE  LOG_MODE      PROTECTION_MODE      CURRENT_SCN

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

AIRYPRD  AIRYSTY       PHYSICAL STANDBY  MOUNTED   ARCHIVELOG   MAXIMUM AVAILABILITY  3005956

PROCESS   STATUS            THREAD#  SEQUENCE# BLOCK#

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

ARCH   CONNECTED          0       0       0

DGRD   ALLOCATED          0       0       0

DGRD   ALLOCATED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CLOSING            1    374       1

ARCH   CLOSING            1    375       1

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

MRP0   WAIT_FOR_LOG       1    376       0

DGRD   ALLOCATED          0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

37 rows selected.

THREAD# Last_applied_Log

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

1         375

14 : Checking: Switch log on primary database , it should be apply on standby database :

On Primary Side :--

[oraairy@airydba AIRYPRD_Archive]$ sqlp

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 14:26:05 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NAME    DB_UNIQUE_NAME  DATABASE_ROLE  OPEN_MODE   LOG_MODE PROTECTION_MODE  CURRENT_SCN

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

AIRYPRD  AIRYPRM        PRIMARY      READ WRITE  ARCHIVELOG   MAXIMUM AVAILABILITY    3012029

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           /AIRYPRD_Archive/AIRYARCH

Oldest online log sequence     375

Next log sequence to archive   378

Current log sequence          378

AIRYPRD@AIRYPRM> alter system switch logfile;

System altered.

AIRYPRD@AIRYPRM>/

System altered.

AIRYPRD@AIRYPRM>/

System altered.

AIRYPRD@AIRYPRM>archive log list;

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           /AIRYPRD_Archive/AIRYARCH

Oldest online log sequence     378

Next log sequence to archive   381

Current log sequence          381

On Standby Side :--

[oraairy@airydbasty AIRYPRD_Archive]$ sqlp

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 14:26:45 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NAME    DB_UNIQUE_NAME  DATABASE_ROLE     OPEN_MODE   LOG_MODE       PROTECTION_MODE            CURRENT_SCN

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

AIRYPRD  AIRYSTY        PHYSICAL STANDBY  MOUNTED     ARCHIVELOG  MAXIMUM AVAILABILITY  3012062

THREAD# Last_applied_Log

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

1         380

AIRYPRD@AIRYSTY>exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oraairy@airydbasty AIRYPRD_Archive]$ rfs

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 26 14:27:04 2019

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NAME    DB_UNIQUE_NAME  DATABASE_ROLE    OPEN_MODE  LOG_MODE        PROTECTION_MODE      CURRENT_SCN

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

AIRYPRD  AIRYSTY       PHYSICAL STANDBY  MOUNTED    ARCHIVELOG   MAXIMUM AVAILABILITY  3012062

PROCESS   STATUS       THREAD#  SEQUENCE# BLOCK#

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

ARCH   CONNECTED          0       0       0

DGRD   ALLOCATED          0       0       0

DGRD   ALLOCATED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CLOSING            1    373       1

ARCH   CLOSING            1    374       1

ARCH   CLOSING            1    377       1

ARCH   CONNECTED          0       0       0

ARCH   CLOSING            1    378    2048

ARCH   CONNECTED          0       0       0

ARCH   CLOSING            1    379       1

ARCH   CONNECTED          0       0       0

ARCH   CLOSING            1    380       1

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

MRP0   APPLYING_LOG       1    381      45

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               1    381      45

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

44 rows selected.

THREAD# Last_applied_Log

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

1         380

15: Check alert logs of primary database and standby database for any error : No Error found

On Primary side:

2019-03-26T15:58:51.198885+05:30

LGWR: Standby redo logfile selected to archive thread 1 sequence 382

LGWR: Standby redo logfile selected for thread 1 sequence 382 for destination LOG_ARCHIVE_DEST_2

2019-03-26T15:58:51.249664+05:30

Thread 1 advanced to log sequence 382 (LGWR switch)

Current log# 1 seq# 382 mem# 0: /oracle/GPCPRD/logs/origlog/redo01.log

Current log# 1 seq# 382 mem# 1: /oracle/GPCPRD/logs/mirrlog/redo01.log

2019-03-26T15:58:52.203495+05:30

Archived Log entry 612 added for T-1.S-381 ID 0x5a06932f LAD:1

2019-03-26T15:58:52.672486+05:30

LGWR: Standby redo logfile selected to archive thread 1 sequence 383

LGWR: Standby redo logfile selected for thread 1 sequence 383 for destination LOG_ARCHIVE_DEST_2

2019-03-26T15:58:52.704818+05:30

Thread 1 advanced to log sequence 383 (LGWR switch)

Current log# 2 seq# 383 mem# 0: /oracle/GPCPRD/logs/origlog/redo02.log

Current log# 2 seq# 383 mem# 1: /oracle/GPCPRD/logs/mirrlog/redo02.log

2019-03-26T15:58:52.773352+05:30

Archived Log entry 614 added for T-1.S-382 ID 0x5a06932f LAD:1

2019-03-26T15:58:53.784963+05:30

LGWR: Standby redo logfile selected to archive thread 1 sequence 384

LGWR: Standby redo logfile selected for thread 1 sequence 384 for destination LOG_ARCHIVE_DEST_2

2019-03-26T15:58:53.863908+05:30

Thread 1 advanced to log sequence 384 (LGWR switch)

Current log# 3 seq# 384 mem# 0: /oracle/GPCPRD/logs/origlog/redo03.log

Current log# 3 seq# 384 mem# 1: /oracle/GPCPRD/logs/mirrlog/redo03.log

2019-03-26T15:58:53.960753+05:30

Archived Log entry 616 added for T-1.S-383 ID 0x5a06932f LAD:1

2019-03-26T15:58:54.705229+05:30

Thread 1 cannot allocate new log, sequence 385

Checkpoint not complete

Current log# 3 seq# 384 mem# 0: /oracle/GPCPRD/logs/origlog/redo03.log

Current log# 3 seq# 384 mem# 1: /oracle/GPCPRD/logs/mirrlog/redo03.log

2019-03-26T15:58:57.889128+05:30

LGWR: Standby redo logfile selected to archive thread 1 sequence 385

LGWR: Standby redo logfile selected for thread 1 sequence 385 for destination LOG_ARCHIVE_DEST_2

2019-03-26T15:58:57.944677+05:30

Thread 1 advanced to log sequence 385 (LGWR switch)

Current log# 4 seq# 385 mem# 0: /oracle/GPCPRD/logs/origlog/redo04.log

Current log# 4 seq# 385 mem# 1: /oracle/GPCPRD/logs/mirrlog/redo04.log

2019-03-26T15:58:57.970867+05:30

Archived Log entry 618 added for T-1.S-384 ID 0x5a06932f LAD:1

2019-03-26T15:58:59.013892+05:30

LGWR: Standby redo logfile selected to archive thread 1 sequence 386

LGWR: Standby redo logfile selected for thread 1 sequence 386 for destination LOG_ARCHIVE_DEST_2

2019-03-26T15:58:59.058135+05:30

Thread 1 advanced to log sequence 386 (LGWR switch)

Current log# 1 seq# 386 mem# 0: /oracle/GPCPRD/logs/origlog/redo01.log

Current log# 1 seq# 386 mem# 1: /oracle/GPCPRD/logs/mirrlog/redo01.log

2019-03-26T15:58:59.166737+05:30

Archived Log entry 620 added for T-1.S-385 ID 0x5a06932f LAD:1

2019-03-26T15:59:00.363840+05:30

LGWR: Standby redo logfile selected to archive thread 1 sequence 387

LGWR: Standby redo logfile selected for thread 1 sequence 387 for destination LOG_ARCHIVE_DEST_2

2019-03-26T15:59:00.413355+05:30

Thread 1 advanced to log sequence 387 (LGWR switch)

Current log# 2 seq# 387 mem# 0: /oracle/GPCPRD/logs/origlog/redo02.log

Current log# 2 seq# 387 mem# 1: /oracle/GPCPRD/logs/mirrlog/redo02.log

2019-03-26T15:59:00.484169+05:30

Archived Log entry 622 added for T-1.S-386 ID 0x5a06932f LAD:1

2019-03-26T15:59:01.358430+05:30

LGWR: Standby redo logfile selected to archive thread 1 sequence 388

LGWR: Standby redo logfile selected for thread 1 sequence 388 for destination LOG_ARCHIVE_DEST_2

2019-03-26T15:59:01.389078+05:30

Thread 1 advanced to log sequence 388 (LGWR switch)

Current log# 3 seq# 388 mem# 0: /oracle/GPCPRD/logs/origlog/redo03.log

Current log# 3 seq# 388 mem# 1: /oracle/GPCPRD/logs/mirrlog/redo03.log

2019-03-26T15:59:01.815645+05:30

Archived Log entry 624 added for T-1.S-387 ID 0x5a06932f LAD:1

2019-03-26T15:59:02.598905+05:30

LGWR: Standby redo logfile selected to archive thread 1 sequence 389

LGWR: Standby redo logfile selected for thread 1 sequence 389 for destination LOG_ARCHIVE_DEST_2

2019-03-26T15:59:02.636019+05:30

Thread 1 advanced to log sequence 389 (LGWR switch)

Current log# 4 seq# 389 mem# 0: /oracle/GPCPRD/logs/origlog/redo04.log

Current log# 4 seq# 389 mem# 1: /oracle/GPCPRD/logs/mirrlog/redo04.log

2019-03-26T15:59:02.657736+05:30

Archived Log entry 626 added for T-1.S-388 ID 0x5a06932f LAD:1

On Standby Side :

Media Recovery Waiting for thread 1 sequence 380 (in transit)

2019-03-26T14:26:20.640974+05:30

Recovery of Online Redo Log: Thread 1 Group 8 Seq 380 Reading mem 0

Mem# 0: /oracle/GPCPRD/logs/origlog/redostysty02.log

Mem# 1: /oracle/GPCPRD/logs/mirrlog/redostysty02.log

2019-03-26T14:26:21.582931+05:30

Standby controlfile consistent with primary

RFS[3]: Selected log 7 for T-1.S-381 dbid 1510430767 branch 984864305

2019-03-26T14:26:21.636157+05:30

Archived Log entry 9 added for T-1.S-380 ID 0x5a06932f LAD:1

2019-03-26T14:26:21.699217+05:30

Media Recovery Waiting for thread 1 sequence 381 (in transit)

2019-03-26T14:26:21.701530+05:30

Recovery of Online Redo Log: Thread 1 Group 7 Seq 381 Reading mem 0

Mem# 0: /oracle/GPCPRD/logs/origlog/redostysty01.log

Mem# 1: /oracle/GPCPRD/logs/mirrlog/redostysty01.log

2019-03-26T15:58:51.072186+05:30

Standby controlfile consistent with primary

RFS[3]: Selected log 8 for T-1.S-382 dbid 1510430767 branch 984864305

2019-03-26T15:58:51.199225+05:30

Media Recovery Waiting for thread 1 sequence 382 (in transit)

2019-03-26T15:58:51.587011+05:30

Archived Log entry 10 added for T-1.S-381 ID 0x5a06932f LAD:1

2019-03-26T15:58:52.523131+05:30

Recovery of Online Redo Log: Thread 1 Group 8 Seq 382 Reading mem 0

Mem# 0: /oracle/GPCPRD/logs/origlog/redostysty02.log

Mem# 1: /oracle/GPCPRD/logs/mirrlog/redostysty02.log

2019-03-26T15:58:52.657289+05:30

Standby controlfile consistent with primary

RFS[3]: Selected log 7 for T-1.S-383 dbid 1510430767 branch 984864305

2019-03-26T15:58:52.676146+05:30

Archived Log entry 11 added for T-1.S-382 ID 0x5a06932f LAD:1

2019-03-26T15:58:52.698459+05:30

Media Recovery Waiting for thread 1 sequence 383 (in transit)

2019-03-26T15:58:52.698730+05:30

Recovery of Online Redo Log: Thread 1 Group 7 Seq 383 Reading mem 0

Mem# 0: /oracle/GPCPRD/logs/origlog/redostysty01.log

Mem# 1: /oracle/GPCPRD/logs/mirrlog/redostysty01.log

2019-03-26T15:58:53.753132+05:30

Standby controlfile consistent with primary

RFS[3]: Selected log 8 for T-1.S-384 dbid 1510430767 branch 984864305

2019-03-26T15:58:53.827779+05:30

Archived Log entry 12 added for T-1.S-383 ID 0x5a06932f LAD:1

2019-03-26T15:58:53.853874+05:30

Media Recovery Waiting for thread 1 sequence 384 (in transit)

2019-03-26T15:58:53.854476+05:30

Recovery of Online Redo Log: Thread 1 Group 8 Seq 384 Reading mem 0

Mem# 0: /oracle/GPCPRD/logs/origlog/redostysty02.log

Mem# 1: /oracle/GPCPRD/logs/mirrlog/redostysty02.log

2019-03-26T15:58:57.860916+05:30

Standby controlfile consistent with primary

RFS[3]: Selected log 7 for T-1.S-385 dbid 1510430767 branch 984864305

2019-03-26T15:58:57.906465+05:30

Archived Log entry 13 added for T-1.S-384 ID 0x5a06932f LAD:1

2019-03-26T15:58:57.937176+05:30

Media Recovery Waiting for thread 1 sequence 385 (in transit)

2019-03-26T15:58:57.937566+05:30

Recovery of Online Redo Log: Thread 1 Group 7 Seq 385 Reading mem 0

Mem# 0: /oracle/GPCPRD/logs/origlog/redostysty01.log

Mem# 1: /oracle/GPCPRD/logs/mirrlog/redostysty01.log

2019-03-26T15:58:59.000858+05:30

Standby controlfile consistent with primary

RFS[3]: Selected log 8 for T-1.S-386 dbid 1510430767 branch 984864305

2019-03-26T15:58:59.037911+05:30

Archived Log entry 14 added for T-1.S-385 ID 0x5a06932f LAD:1

2019-03-26T15:58:59.050885+05:30

Media Recovery Waiting for thread 1 sequence 386 (in transit)

2019-03-26T15:58:59.051181+05:30

Recovery of Online Redo Log: Thread 1 Group 8 Seq 386 Reading mem 0

Mem# 0: /oracle/GPCPRD/logs/origlog/redostysty02.log

Mem# 1: /oracle/GPCPRD/logs/mirrlog/redostysty02.log

2019-03-26T15:59:00.351987+05:30

Standby controlfile consistent with primary

RFS[3]: Selected log 7 for T-1.S-387 dbid 1510430767 branch 984864305

2019-03-26T15:59:00.393364+05:30

Archived Log entry 15 added for T-1.S-386 ID 0x5a06932f LAD:1

2019-03-26T15:59:00.407205+05:30

Media Recovery Waiting for thread 1 sequence 387 (in transit)

2019-03-26T15:59:00.407626+05:30

Recovery of Online Redo Log: Thread 1 Group 7 Seq 387 Reading mem 0

Mem# 0: /oracle/GPCPRD/logs/origlog/redostysty01.log

Mem# 1: /oracle/GPCPRD/logs/mirrlog/redostysty01.log

2019-03-26T15:59:01.346401+05:30

Standby controlfile consistent with primary

RFS[3]: Selected log 8 for T-1.S-388 dbid 1510430767 branch 984864305

2019-03-26T15:59:01.359001+05:30

Archived Log entry 16 added for T-1.S-387 ID 0x5a06932f LAD:1

2019-03-26T15:59:01.381953+05:30

Media Recovery Waiting for thread 1 sequence 388 (in transit)

2019-03-26T15:59:01.382377+05:30

Recovery of Online Redo Log: Thread 1 Group 8 Seq 388 Reading mem 0

Mem# 0: /oracle/GPCPRD/logs/origlog/redostysty02.log

Mem# 1: /oracle/GPCPRD/logs/mirrlog/redostysty02.log

2019-03-26T15:59:02.571260+05:30

Standby controlfile consistent with primary

RFS[3]: Selected log 7 for T-1.S-389 dbid 1510430767 branch 984864305

2019-03-26T15:59:02.613542+05:30

Archived Log entry 17 added for T-1.S-388 ID 0x5a06932f LAD:1

2019-03-26T15:59:02.627310+05:30

Media Recovery Waiting for thread 1 sequence 389 (in transit)

2019-03-26T15:59:02.627713+05:30

Recovery of Online Redo Log: Thread 1 Group 7 Seq 389 Reading mem 0

Mem# 0: /oracle/GPCPRD/logs/origlog/redostysty01.log

Mem# 1: /oracle/GPCPRD/logs/mirrlog/redostysty01.log

[oragpc@airydba ~]$ cat rfs 

set line 100
col DB_UNIQUE_NAME for a15
col OPEN_MODE for a10
select name,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,LOG_MODE,PROTECTION_MODE,CURRENT_SCN from v$database;
select process,status,thread#,sequence#,block# from v$managed_standby;
select thread#,max(sequence#) as "Last_applied_Log" from v$log_history group by thread#;

[oragpc@airydba ~]$ cat sqlp 

set line 100
col DB_UNIQUE_NAME for a15
col OPEN_MODE for a10
select name,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,LOG_MODE,PROTECTION_MODE,CURRENT_SCN from v$database;
archive log list;

Thank you for reading… This is Airy…Enjoy Learning:)

 

#archive-log-gap, #dataguard, #oracle-12c

airydbablog 1:23 pm on June 9, 2017
Tags: Oracle12cR1   

Oracle 12c R1 New Features For DBAs

Oracle 12c R1 New Features For DBAs :

1: Enterprise Manager and Other Tools

Enterprise Manager (EM) Cloud Control home page

Enterprise Manager Express home page versus Enterprise Manager Database Control

SQL Developer with new functionalities

OUI, DBCA

2: Basics of Multitenant Container Database (CDB)

Benefits of the multitenant architecture

Differences between the root container and pluggable database containers

Structure of the root

Structure of Pluggable Database (PDB)

CDB_xxx and DBA_xxx views

Impacts in Security, Data Guard, Oracle GoldenGate, Oracle Streams, XStream, and Scheduler

3: Configuring and Creating CDB and PDBs

Tools: DBCA and SQL*Plus

Configuration and creation of CDB

Exploring the Structure (CDB_xxx views and EM)

Tools used: SQL Developer, DBCA and SQL*Plus and EM Database Express

Create a new PDB from PDB$SEED

Create a PDB from a non-CDB

Clone a PDB into the same CDB or into another CDB using DB links

Plug in an unplugged PDB

4: Managing CDB and PDBs

Connect to CDB as administrator

Connect to a PDB using a service name

Start up a CDB

Open / Close a PDB (SQL*Plus, srvctl, EM)

Open / Close all PDBs

Shut down a CDB

Preserve or discard the PDBs open mode when the CDB restarts

Change PDB state

5: Managing Tablespaces, Common and Local Users, Privileges and Roles

Create permanent tablespaces in the root and PDBs

Manage common and local schemas and users in the root and PDBs

Manage system and object privileges to common and local grantees granted “commonly” or “locally”

Manage common and local roles granted “commonly” or “locally”

6: Managing Backup / Recovery / Flashback

Backup a whole CDB

Backup a PDB

Recover CDB from redo log files, control files, undo datafiles loss

Recover PDB from PDB datafiles loss

Flashback at CDB level

Explain the two methods of migration

7: Online Datafile Move and Automatic Data Optimization

Data classification in 12c : tablespace, group, object, row levels

Configure heat map

Automatic movement and compression

Compression levels and types

Policy declaration: simple declarative SQL extension

Customized automated action execution with user-defined function

Execution in scheduled maintenance windows and by MMON

Customized schedule with DBMS_ILM package

8: In-Database Archiving

Challenges of old data in tables and 11g solutions

In-database archiving new solutions

Use ROW ARCHIVAL clause to enable row lifecycle state for applications

Set ROW ARCHIVAL VISIBILITY for session level visibility control

Use predicate on ORA_ARCHIVE_STATE column

Temporal Validity versus Temporal History (Transaction Time of FDA)

New clause of CREATE / ALTER TABLE to set a Temporal Validity: PERIOD FOR

New SQL temporal data type

9: Auditing Enhancements

Review of 11g R2 audit trail implementation

Overview of the Unified Audit Trail

Enabling the Unified Audit Trail

Creating a separate tablespace for the unified audit trail

Granting the AUDIT_ADMIN role to a user for audit trail configuration and management

Configuring the Unified Audit Trail to set a tolerance level for loss of audit records

Creating audit policies

10: Privileges Enhancements

Implementing Separation of Duty for Database Administration Tasks

Using Database Privilege Analysis

Overview of Database Privilege Analysis

Granting the CAPTURE_ADMIN role to enable management of privilege captures

Creating and starting/stopping privilege captures

Viewing privilege profile data

Querying DBA_PRIV_CAPTURES

Explaining the purpose of the default ORA$DEPENDENCY profile

11: Oracle Data Redaction

Overview of Oracle Data Redaction

Types of Masking Policies

Administering the EXEMPT REDACTION POLICY system privilege to enable a user to view unmasked values

Managing Masking Policies

Best practices for defining policy expressions

Understanding Masking of Statements Containing Subqueries

Viewing Information About Masking Policies by Querying REDACTION_POLICIES and REDACTION_COLUMNS

12: General RMAN New Features and FDA Enhancements

Making Database Connections With RMAN

Using the SYSBACKUP Privilege

Using SQL, DESCRIBE Command, Duplication Operation with the NOOPEN option

Backing up and Restoring Very Large Files

Creating Multisection Backups

Transporting Data Across Platforms

Prerequisites and Limitations

Transporting Data: Processing steps

13: Monitoring DB Operations

Overview

Use cases

Current Tools

Define a DB operation

Monitoring: Bracketing an Operation

Monitoring the Progress of Operations

DB Operation Tuning

DB Operation Active Report

14: Real-Time ADDM and Compare Period Advisor

Emergency Monitoring

Real time ADDM

Use cases

Goals

Define Workload dimensions

Reported items

Root Causes

Requirements for a report

15: ADR and Network Enhancements

ADR file types

New File types

New File locations

New command for ADRCI

Improve performance by Compression

Setup Compression

16: In-Memory Column Store

In-Memory Database option goals and benefits

Row format and columnar format

New SGA component:

IMCU synchronization

Deployment and behaviors

Compression ratio

New dictionary tables and added columns

New statistics: IM %

17: In-Memory Caching

Setting up Full Database In-Memory Caching

Using Full Database In-Memory Caching

Explaining the two buffer replacement algorithms of Automatic Big Table Caching

Configuring Automatic Big Table Caching with PARALLEL_DEGREE_POLICY and DB_BIG_TABLE_CACHE_PERCENT_TARGET Using Automatic Big Table Caching

18: SQL Tuning

Adaptive Execution Plans

SQL Plan Directives

Statistics Gathering Performance Improvements

Histogram Enhancements

Enhancements to Extended Statistics

Adaptive SQL Plan Management

19: Resource Manager and Other Performance Enhancements

Manage resources between PDBs

Manage resources within a PDB

Manage resources with CDB and PDBs plans

Manage runaway queries

Control the in-memory database repopulation resource consumption

Automated maintenance tasks

Current Architecture: Unix multiprocess / one thread, NT/Windows one process / multithread

New Architecture: Multiprocess / MultiThread

20: Index and Table Enhancements

Multiple indexes on the same set of columns as long as some characteristic is different

Create table with INVISIBLE columns

Support for invisible columns

Describe online redefinition supports

Explain LOCK timeout during FINISH_REDEF_TABLE

Describe the Advanced Row Compression

Using the following DDL statements in an online manner

21: Oracle Database Cloud Service: Overview

Database as a Service Architecture & Features and Tooling

Software Editions: Included Database Options and Management Packs

Automated Database Provisioning

Managing the Compute Node Associated With a Database Deployment

Backing Up and Recovering Databases on Database as a Service

Performing Recovery by Using the Service Console

Patching Database as a Service

Using the Oracle Database Cloud Service Console to Manage Patches

 

Thank you for reading… This is Airy…Enjoy Learning:)

#oracle12cr1

airydbablog 7:53 pm on May 19, 2017  

Deleting a Node from RAC

Click on Below given link to get the step by step process to Delete a Node from RAC.

Deleting-A-Node-from-cluster-By-AIRYDBA

Password for opening the document is : Airydba@159

 

Thank you for reading… This is Airy…Enjoy Learning:)

airydbablog 7:33 pm on May 19, 2017  

Adding 3rd Node in RAC using addnode.sh

Click on Below given link to get the step by step Adding 3rd Node in RAC using addnode.sh:

AddNode-airydba

Password for opening the document is : Airydba@159

Thank you for reading… This is Airy…Enjoy Learning:)

airydbablog 6:19 pm on March 31, 2017  

Important_Frequently_Asked_PL/SQL_Questions_With_Answers-Set-3

Click on below given link to download the document:

1: Important_Frequently_Asked_PL/SQL_Questions_With_Answers-Set-3

Password For Opening Document : Airydba@159

Thank you for Reading…This is AIRY…Enjoy Learning:)

airydbablog 6:13 pm on March 31, 2017  

Important_Frequently_Asked_PL/SQL_Questions_With_Answers-Set-2(Part-B)

Click on below given link to download the document:

1: Important_Frequently_Asked_PL/SQL_Questions_With_Answers-Set-2(Part-B)

Password For Opening Document : Airydba@159

Thank you for Reading…This is AIRY…Enjoy Learning:)