Month: June 2021

CREATE AND CONFIGURE PHYSICAL STANDBY DATAGUARD 12C ORACLE

Enable force logging on your database

SQL> select force_logging from v$database;

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

If previous output is YES, then ignore this step

SQL> alter database force logging;

Database altered.

Check the archive log status on database

SQL> select log_mode from v$database;

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

If previous step output is NOARCHIVELOG mode, then follow below steps

SHU IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER SYSTEM SWITCH LOGFILE;
SELECT NAME FROM V$ARCHIVED_LOG;

Check the number of redolog group with their size

SQL> select group#,bytes/1024/1024 from v$log;

    GROUP# BYTES/1024/1024
---------- ---------------
         1              50
         2              50
         3              50

Check the number of threads used by the database instance

SQL> select thread#,instance from v$thread;

   THREAD# INSTANCE
---------- --------------------------------------------------------------------------------
         1 orcldgp

Formula to get the number of standby redologs on standby database.

( number of redolog group * number of threads in primary ) +1

Check the number of member in primary database

SQL> select count(member) from v$logfile;

COUNT(MEMBER)
-------------
            3

There is only one thread as this is not RAC setup(Primary)

SQL> select count(thread#) from v$thread;

COUNT(THREAD#)
--------------
             1

Apply the formula for calculating standby redolog count

SQL> select (3*1)+1 as standbylog_count from dual;

STANDBYLOG_COUNT
----------------
               4

Add standby redologs in standby database

SQL> alter database add standby logfile thread 1 '/oracle/base/oradata/orcldgp/stdbylog1.log' size 50m;

Database altered.


SQL> alter database add standby logfile thread 1 '/oracle/base/oradata/orcldgp/stdbylog2.log' size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 '/oracle/base/oradata/orcldgp/stdbylog3.log' size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 '/oracle/base/oradata/orcldgp/stdbylog4.log' size 50m;

Database altered.

Check db_unique_name parameter in standby. This name should be the original name of standby database

SQL> show parameter db_unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      orcldgp

Set log_archive_config parameter in primary

SQL> alter system set log_archive_config='DG_CONFIG=(ORCL,ORCLDGP)';

System altered.

Set log_archive_dest_2 parameter on primary

SQL> alter system set log_archive_dest_2='service=orcldgp async valid_for=(online_logfiles,primary_role) db_unique_name=orcldgp';

System altered.

Ensure that all log archive dest parameter are deferred before initial configuration

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system set log_archive_dest_state_3=defer;

System altered.

set fal server parameter on primary. As here we have two standby databases , i have set two service names. If you have only one standby ,then you can specify only one

SQL> alter system set fal_server=ORCLDG,ORCLDGP;

System altered.

Set remote login password file to exclusive(Primary)

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

Create pfile from current spfile in primary

SQL> create pfile='/tmp/initorcldgp.ora' from spfile;

File created.

Set service_names parameter with all the services used in primary database

SQL> alter system set service_names='orcl','orcldg','orcldgp';

System altered.

Set tnsnames.ora file with the tns entry of primary and standby databases. Note that standby database has an additional entry (UR=A) for auxiliary connection

[grid@orcl ~]$ vi /grid/base/product/12.1.0/grid/network/admin/tnsnames.ora


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.212)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)(UR = A)
    )
  )

ORCLDGP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldgp)(UR = A)
    )
  )

Create a password file on standby database or copy the password file from primary to standby and modify the file name with standby SID (orapw +standby SID)

[oracle@orcldg ~]$ orapwd file='$ORACLE_HOME/dbs/orapwORCLDGP' password=oracle entries=10 force=y

Set tns entries in standby tnsnames.ora file

[grid@orcldg ~]$ vi /grid/base/product/12.1.0/grid/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.202)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcldg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)(UR = A)
    )
  )


ORCLDGP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldgp)(UR = A)
    )
  )

We are not going to use RMAN duplication method to construct standby database. Rather we use conventional method. Take current controlfile backup from primary database

[oracle@orcl /]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Jun 5 17:55:09 2021

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

connected to target database: ORCL (DBID=1559282684)

RMAN> run{
2> sql 'alter system switch logfile';
3> backup current controlfile for standby format '/u01/stdctl.%T';
4> }

using target database control file instead of recovery catalog
sql statement: alter system switch logfile

Starting backup at 05-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 05-JUN-21
channel ORA_DISK_1: finished piece 1 at 05-JUN-21
piece handle=/u01/stdctl.20210605 tag=TAG20210605T232534 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-JUN-21

Starting Control File and SPFILE Autobackup at 05-JUN-21
piece handle=+FRA/ORCL/AUTOBACKUP/2021_06_05/s_1074468340.614.1074468343 comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-21

As the backup file reside in ASM, i copy the controlfile backup piece to filesystem

ASMCMD> cp +FRA/ORCL/AUTOBACKUP/2021_06_05/s_1074468340.614.1074468343 /home/grid
copying +FRA/ORCL/AUTOBACKUP/2021_06_05/s_1074468340.614.1074468343 -> /home/grid/s_1074468340.614.1074468343

Copy the controlfile backup to standby database

[grid@orcl ~]$ scp s_1074468340.614.1074468343 oracle@192.168.56.212:/home/oracle/
The authenticity of host '192.168.56.212 (192.168.56.212)' can't be established.
RSA key fingerprint is 6b:1a:39:5a:bc:c2:55:27:cd:7b:e6:26:ba:ef:a3:c9.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.212' (RSA) to the list of known hosts.
oracle@192.168.56.212's password:
s_1074468340.614.1074468343                   100%   17MB  16.5MB/s   00:00

Copy the pfile from primary to standby database home

--on primary 
[oracle@orcl ~]$ scp /oracle/base/product/12.1.0/dbhome_1/dbs/initorcl.ora oracle@192.168.56.123:/oracle/base/product/12.1.0/dbhome_1/dbs


--on standby 
[oracle@orcldg dbs]$ mv /oracle/base/product/12.1.0/dbhome_1/dbs/initorcl.ora /oracle/base/product/12.1.0/dbhome_1/dbs/initorcldgp.ora

Modify the below parameters in standby pfile. If you have different directory structures , then you need to specify db_file_name_convert and log_file_name_convert .Set db file name convert and log file name convert parameters to handle different directory structures in standby database

*.db_name='ORCL'
*.db_unique_name='ORCLDGP'
*.db_file_name_convert='+DATA','/oracle/base/oradata/orcldgp/'
*.log_file_name_convert='+DATA','/oracle/base/oradata/orcldgp/'

Startup the database in nomount mode using pfile

SQL> startup nomount pfile='/oracle/base/product/12.1.0/dbhome_1/dbs/initorcldgp.ora';
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             956305312 bytes
Database Buffers          536870912 bytes
Redo Buffers               13848576 bytes

In standby, restore the standby controlfile backup which was taken from primary

[oracle@orcldg ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jun 6 00:04:33 2021

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

connected to target database: ORCLDGP (not mounted)

RMAN> restore standby controlfile from '/home/oracle/s_1074468340.614.1074468343';

Starting restore at 06-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oracle/base/oradata/orcldgp/control01.ctl
output file name=/u01/orcldgp/control02.ctl
Finished restore at 06-JUN-21

Generate a script to display all commands for restore of datafile to different directory

SQL> select 'set newname for datafile '||file_id||' to '||file_name||' ;'from dba_data_files
  2  /

'SETNEWNAMEFORDATAFILE'||FILE_ID||'TO'||FILE_NAME||';'
--------------------------------------------------------------------------------
set newname for datafile 2 to +DATA/ORCL/DATAFILE/system.265 ;
set newname for datafile 1 to +DATA/ORCL/DATAFILE/system.257.1031501823 ;
set newname for datafile 3 to +DATA/ORCL/DATAFILE/sysaux.256.1031501729 ;
set newname for datafile 4 to +DATA/ORCL/DATAFILE/undotbs1.259.1031501899 ;
set newname for datafile 8 to +DATA/ORCL/DATAFILE/users.274.1040915137 ;
set newname for datafile 7 to +DATA/ORCL/DATAFILE/users.272.1040745563 ;
set newname for datafile 6 to +DATA/ORCL/users.new ;
set newname for datafile 5 to +DATA/ORCL/DATAFILE/example.265.1031501993 ;
set newname for datafile 9 to /home/oracle/rcat.tbs ;

9 rows selected.

SQL> select 'set newname for tempfile '||file_id||' to '||file_name||' ;'from dba_temp_files
  2  /

'SETNEWNAMEFORTEMPFILE'||FILE_ID||'TO'||FILE_NAME||';'
--------------------------------------------------------------------------------
set newname for tempfile 1 to +DATA/ORCL/TEMPFILE/temp.264.1031501981 ;

Take full backup of database either incremental level 0 or full consistent backup from your primary database

rman> backup current controlfile;
rman> backup as compressed backupset database;

Copy backup pieces to standby

[oracle@orcl ~]$ scp /tmp/s_1075683731.549.1075683733 oracle@192.168.56.212:/tmp

oracle@192.168.56.212's password:
s_1075683731.549.1075683733                   100%   17MB  16.5MB/s   00:01

Check db name and db unique name once if they are set properly. If you find any mistakes correct them. Note that ‘db name‘ should be primary db and ‘db unique name‘ should be standby db

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      ORCL


SQL> alter system set db_name=orcl scope=spfile;

System altered.

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ORCL

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

System altered.

Mount the database in standby mode on your standby database

RMAN> alter database mount standby database;

Statement processed
released channel: ORA_DISK_1

Catalog the backuppieces to make RMAN recognize the tags on disks

RMAN> catalog start with '/u01/backuppiece';

searching for all files that match the pattern /u01/backuppiece

List of Files Unknown to the Database
=====================================
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.461.1075683233
File Name: /u01/backuppiece/ncnnf0_TAG20210620T005345_0.533.1075683229
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.330.1075683605
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.530.1075683509
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.287.1075683685
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.540.1075683661

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.461.1075683233
File Name: /u01/backuppiece/ncnnf0_TAG20210620T005345_0.533.1075683229
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.330.1075683605
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.530.1075683509
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.287.1075683685
File Name: /u01/backuppiece/nnndf0_TAG20210620T005353_0.540.1075683661

Validate the size of all datafiles on primary for capacity planning on standby database

SQL> select sum(bytes/1073741824) as SIZEGB from v$datafile;

              SIZEGB
---------------------
            11.126709

Verify if sufficient space in standby database exist to accomodate the database files

[oracle@orcldg ~]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdd1        20G   11G  7.6G  59% /
tmpfs           1.9G  985M  865M  54% /dev/shm
/dev/sdd5        15G  9.3G  4.5G  68% /grid
/dev/sdd2        15G  9.2G  4.6G  67% /oracle
/dev/sde         12G  1.9G  9.2G  18% /u01

Create shell script to execute RMAN restore. Note that if the standby directory structure is different from primary ,then ‘set newname’ clause should be used or else the lines with “set newname” can be removed.

[oracle@orcldg ~]$ cat rman_restore.sh
rman target sys/password debug trace=/tmp/rmanlog << EOF > /home/oracle/rman.log

run
{
set newname for datafile 1 to '/u01/ORCLDGP/datafile/system.257.1031501823';
set newname for datafile 2 to '/u01/ORCLDGP/datafile/system.265';
set newname for datafile 3 to '/u01/ORCLDGP/datafile/sysaux.256.1031501729';
set newname for datafile 4 to '/u01/ORCLDGP/datafile/undotbs1.259.1031501899';
set newname for datafile 5 to '/oracle/example.265.1031501993';
set newname for datafile 6 to '/oracle/users.new';
set newname for datafile 7 to '/home/oracle/users.272.1040745563';
set newname for datafile 8 to '/home/oracle/users.274.1040915137';
set newname for datafile 9 to '/home/oracle/rcat.tbs';
restore database;
switch datafile all;
}
EOF

Run the script in nohup mode

[oracle@orcldg ~]$ nohup sh rman_restore.sh &
[1] 11240
[oracle@orcldg ~]$ nohup: ignoring input and appending output to `nohup.out'

Monitor the restore

[oracle@orcldg ~]$ tail -f rman.log

RMAN-03090: Starting restore at 20-JUN-21
RMAN-06009: using target database control file instead of recovery catalog
RMAN-08030: allocated channel: ORA_DISK_1
RMAN-08500: channel ORA_DISK_1: SID=21 device type=DISK

RMAN-08016: channel ORA_DISK_1: starting datafile backup set restore
RMAN-08089: channel ORA_DISK_1: specifying datafile(s) to restore from backup set
RMAN-08610: channel ORA_DISK_1: restoring datafile 00004 to /u01/ORCLDGP/datafile/undotbs1.259.1031501899
RMAN-08003: channel ORA_DISK_1: reading from backup piece /u01/nnndf0_TAG20210620T005353_0.461.1075683233

Wait until completion of restore

RMAN-08610: channel ORA_DISK_1: restoring datafile 00003 to oracle/sysaux.256.10                                      31501729
RMAN-08023: channel ORA_DISK_1: restored backup piece 1
RMAN-08180: channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
RMAN-03091: Finished restore at 21-JUN-21

RMAN>

Take archivelog backup from primary as we have inconsistent backup.Copy the archivelog backuppiece to standby

RMAN> backup archivelog all;

Catalog the backup of archivelog in standby database

RMAN> catalog start with '/tmp';

Apply the archives recover the database till the last sequence

RMAN> recover database;

Starting recover at 21-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

starting media recovery

archived log for thread 1 with sequence 345 is already on disk as file /tmp/thread_1_seq_345.561.1075831911
archived log for thread 1 with sequence 353 is already on disk as file /tmp/thread_1_seq_353.454.1075767321
archived log for thread 1 with sequence 354 is already on disk as file /tmp/thread_1_seq_354.462.1075817169
archived log for thread 1 with sequence 355 is already on disk as file /tmp/thread_1_seq_355.484.1075818959
archived log for thread 1 with sequence 356 is already on disk as file /tmp/thread_1_seq_356.510.1075820759
archived log for thread 1 with sequence 357 is already on disk as file /tmp/thread_1_seq_357.573.1075822559
archived log for thread 1 with sequence 358 is already on disk as file /tmp/thread_1_seq_358.418.1075824359
archived log for thread 1 with sequence 359 is already on disk as file /tmp/thread_1_seq_359.538.1075826159
archived log for thread 1 with sequence 360 is already on disk as file /tmp/thread_1_seq_360.539.1075827959
archived log for thread 1 with sequence 361 is already on disk as file /tmp/thread_1_seq_361.563.1075829639
archived log for thread 1 with sequence 362 is already on disk as file /tmp/thread_1_seq_362.578.1075829849
archived log for thread 1 with sequence 363 is already on disk as file /tmp/thread_1_seq_363.359.1075831647
archived log for thread 1 with sequence 364 is already on disk as file /tmp/thread_1_seq_364.464.1075832691
archived log for thread 1 with sequence 365 is already on disk as file /tmp/thread_1_seq_365.377.1075832711
archived log for thread 1 with sequence 366 is already on disk as file /tmp/thread_1_seq_366.566.1075832727
archived log file name=/tmp/thread_1_seq_345.561.1075831911 thread=1 sequence=345
unable to find archived log
archived log thread=1 sequence=346
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/21/2021 18:58:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 346 and starting SCN of 14768906

RMAN> exit

Recover the database until cancel using backup controlfile

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 14768906 generated at 06/20/2021 01:05:53 needed for thread 1
ORA-00289: suggestion : /u01/ORCLDGP/archivelog/2021_06_21/o1_mf_1_346_%u_.arc
ORA-00280: change 14768906 for thread 1 is in sequence #346


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

Enable log archive dest parameter on primary

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

Enable log_archive_config parameter

SQL> alter system set log_archive_config='DG_CONFIG=(ORCL,ORCLDG,ORCLDGP)';

System altered.

Set standby file management to auto on standby

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Check the standby logfiles on standby. You need standby redologs for switchover purpose

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
        10          1          0 YES UNASSIGNED
        11          1          0 YES UNASSIGNED
        12          1          0 YES UNASSIGNED
        13          1          0 YES UNASSIGNED

Check the count of standby redologs on standby

SQL> SELECT TYPE, count(*) FROM V$LOGFILE GROUP BY TYPE;

TYPE      COUNT(*)
------- ----------
ONLINE           3
STANDBY          8

Verify the duplicate standby logs which were created during restore

SQL> select GROUP#, MEMBER from V$LOGFILE WHERE TYPE='STANDBY' ORDER BY 1,2;

 GROUP# MEMBER
------- --------------------
     10 +DATA/ORCL/ONLINELOG
        /group_10.267.103150
        4655

     10 +FRA/ORCL/ONLINELOG/
        group_10.260.1031504
        657

     11 +DATA/ORCL/ONLINELOG
        /group_11.268.103150
        4661

     11 +FRA/ORCL/ONLINELOG/
        group_11.261.1031504
        665

     12 +DATA/ORCL/ONLINELOG
        /group_12.269.103150
        4669

     12 +FRA/ORCL/ONLINELOG/
        group_12.262.1031504
        671

     13 +DATA/ORCL/ONLINELOG
        /group_13.270.103150
        4675

     13 +FRA/ORCL/ONLINELOG/
        group_13.263.1031504
        679


8 rows selected.

Drop the duplicate redologs as they are no longer required

SQL> alter database drop logfile member '+FRA/ORCL/ONLINELOG/group_10.260.1031504657';

Database altered.

SQL> alter database drop logfile member '+FRA/ORCL/ONLINELOG/group_11.261.1031504665';

Database altered.

SQL> alter database drop logfile member '+FRA/ORCL/ONLINELOG/group_12.262.1031504671';

Database altered.

SQL> alter database drop logfile member '+FRA/ORCL/ONLINELOG/group_13.263.1031504679';

Database altered.

Set fal client and fal server parameter on primary

SQL> alter system set fal_client=orcl;

System altered.

SQL> alter system set fal_server=orcldgp;

System altered.

Enable MRP on standby



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

Database altered.

Check the message of replication status from primary

SQL> select message from v$dataguard_status;

MESSAGE
--------------------------------------------------------------------------------
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC2: Becoming the active heartbeat ARCH
ARC3: Archival started
Managed Standby Recovery not using Real Time Apply
Managed Standby Recovery not using Real Time Apply
Attempt to start background Managed Standby Recovery process

MESSAGE
--------------------------------------------------------------------------------
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery starting Real Time Apply
Clearing online redo logfile 1 +DATA/ORCL/ONLINELOG/group_1.261.1031501951
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/ORCL/ONLINELOG/group_2.262.1031501957
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/ORCL/ONLINELOG/group_3.263.1031501965
Clearing online redo logfile 3 complete
Media Recovery Waiting for thread 1 sequence 345 branch(resetlogs_id) 1054563556
FAL[client]: Error fetching gap sequence, no FAL server specified
Primary database is in MAXIMUM PERFORMANCE mode

MESSAGE
--------------------------------------------------------------------------------
RFS[1]: Assigned to RFS process (PID:18054)
RFS[1]: No standby redo logfiles available for T-1
RFS[1]: No standby redo logfiles available for T-1
RFS[2]: Assigned to RFS process (PID:18062)
Media Recovery Log /u01/ORCLDGP/archivelog/2021_06_21/o1_mf_1_345_jf1kw6fq_.arc
Media Recovery Waiting for thread 1 sequence 1
RFS[3]: Assigned to RFS process (PID:18064)
RFS[4]: Assigned to RFS process (PID:18067)
Error 12154 received logging on to the standby
FAL[client, MRP0]: Error 12154 connecting to ORCL for fetching gap sequence
Fetching gap sequence in thread 1, gap sequence 1-1

MESSAGE
--------------------------------------------------------------------------------
RFS[1]: No standby redo logfiles available for T-1

34 rows selected.

Do some manual log switches from primary

SQL> alter system switch logfile;

System altered.

Verify the sync of log sequence from standby

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      WAIT_FOR_GAP          1          1          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1        379          7          1
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0

9 rows selected.

Try to connect from primary with service name which should not throw tns error

[oracle@orcl ~]$ sqlplus test/password@orcldgp as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 21 17:30:53 2021

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


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

SQL>

Configure archivelog deletion policy in standby database to periodically delete applied archives from standby to release FRA space

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored

prctl: oracle: cannot find the project for given id or name – SOLARIS

Cause:

When trying to modify the kernel memory parameter, face below error

root@orsol:~# prctl -n project.max-shm-memory -i project oracle
prctl: oracle: cannot find the project for given id or name

Workaround:

Login to session with the user for which you try to modify memory

root@orsol:~# su - oracle
Oracle Corporation      SunOS 5.11      11.4    Aug 2018

Try it then

root@orsol:~# prctl -n project.max-shm-memory -i project oracle
project: 100: oracle
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        usage               0B
        privileged       987MB      -   deny                                 -
        system          16.0EB    max   deny                                 -

ORA-01156: recovery or flashback in progress may need access to files

Cause:

I tried to drop a standy redolog member and got error due to active recovery in standby database

SQL> alter database drop logfile member '+FRA/ORCL/ONLINELOG/group_10.260.1031504657';
alter database drop logfile member '+FRA/ORCL/ONLINELOG/group_10.260.1031504657'
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

Workaround:

Cancel the MRP

SQL> alter database recover managed standby database cancel;

Database altered.

Drop the logfile

SQL> alter database drop logfile member '+FRA/ORCL/ONLINELOG/group_10.260.1031504657';

Database altered.



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

Database altered.

FAL[client]: Error fetching gap sequence, no FAL server specified

In a physical dataguard standby database, we can see messages like fetching message error with gap sequence. These errors can sometimes be resolved automatically by fal_server parameter which will resync after a temporary network flap

Media Recovery Waiting for thread 1 sequence 345 branch(resetlogs_id) 1054563556
FAL[client]: Error fetching gap sequence, no FAL server specified

If the gap are not resolved automatically, then DBA intervention is required

  1. Check the fal parameters on both primary and standby databases
SQL> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string
fal_server                           string


SQL> alter system set fal_client=orcl;

System altered.

SQL> alter system set fal_server=orcldgp;

System altered.

2) Automatic gap resolution can be detected sometimes in v$dataguard_status view

SQL> select message from v$dataguard_status;

Error 12154 received logging on to the standby
FAL[client, MRP0]: Error 12154 connecting to ORCL for fetching gap sequence
Fetching gap sequence in thread 1, gap sequence 1-1

3) Check the connectivity from primary database to standby with service name connection string using sysdba

[oracle@orcl ~]$ sqlplus test/password@orcldgp as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 21 17:30:53 2021

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


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

SQL>

4) Check for all log archive destinations are enabled

SQL> select ''||name||' - '||VALUE||'' from v$parameter where name like '%log_archive_dest_state%' order by regexp_substr(name,'[[:digit::]]') asc fetch next 3 rows only;

''||NAME||'-'||VALUE||''
--------------------------------------------------------------------------------
log_archive_dest_state_1 - enable
log_archive_dest_state_3 - enable
log_archive_dest_state_2 - enable

5) Validate tns connectivity from primary

[oracle@orcl ~]$ tnsping orcldgp

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 21-JUN-2021 17:58:39

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

Used parameter files:
/oracle/base/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1526)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldgp)(UR = A)))
OK (10 msec)

Check for log_archive_config parameter set on both primary and standby

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(ORCL,ORCLDG,ORCLDGP
                                                 )

Check for all log_archive_dest_n parameters and validate the sub parameters inside it

SQL> select ''||name||' - '||VALUE||'' from v$parameter where name like '%log_archive_dest_%' order by regexp_substr(name,'[[:digit::]]') asc fetch next 3 rows only;

''||NAME||'-'||VALUE||''
--------------------------------------------------------------------------------
log_archive_dest_1 - location=USE_DB_RECOVERY_FILE_DEST
log_archive_dest_3 - service=orcldgp async valid_for=(online_logfiles,primary_ro
le) db_unique_name=orcldgp

log_archive_dest_2 - service="orcldg", LGWR ASYNC delay=0 net_timeout=30 db_uniq
ue_name=orcldg valid_for=(online_logfile,primary_role)

Bounce MRP

SQL> alter database recover managed standby database cancel;

Database altered.

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

Database altered.

ORA-07217: sltln: environment variable cannot be evaluated.

Cause:

Due to lack of permission on home directory , rman cannot read the data of backuppiece

RMAN> catalog start with '/home/oracle';

using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 06/21/2021 21:09:46
ORA-07217: sltln: environment variable cannot be evaluated.

Environment variables are properly set but still error arise due to directory permission miss

[oracle@orcldg u01]$ oerr ora 07217
07217, 00000, "sltln: environment variable cannot be evaluated."
// *Cause:  getenv call returned a null pointer.
// *Action: Set the environment variable and try again.

Owner of home directory is ‘root’

[oracle@orcldg /]$ ls -lrt |grep home
drwxr-xr-x.   4 root   root      4096 Feb  4  2020 home

Workaround:

Either move the backup pieces to new directory or mount point with oracle permissions

[oracle@orcldg oracle]$ mv n* databkup/

RMAN can read the directory with oracle permissions


[oracle@orcldg oracle]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jun 21 21:32:28 2021

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

connected to target database: ORCL (DBID=1559282684, not open)

RMAN> catalog start with '/oracle/databkup';

using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/databkup

List of Files Unknown to the Database
=====================================
File Name: /oracle/databkup/nnndf0_TAG20210620T005353_0.330.1075683605
File Name: /oracle/databkup/nnndf0_TAG20210620T005353_0.287.1075683685
File Name: /oracle/databkup/nnndf0_TAG20210620T005353_0.530.1075683509
File Name: /oracle/databkup/nnndf0_TAG20210620T005353_0.461.1075683233
File Name: /oracle/databkup/nnndf0_TAG20210620T005353_0.540.1075683661

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oracle/databkup/nnndf0_TAG20210620T005353_0.330.1075683605
File Name: /oracle/databkup/nnndf0_TAG20210620T005353_0.287.1075683685
File Name: /oracle/databkup/nnndf0_TAG20210620T005353_0.530.1075683509
File Name: /oracle/databkup/nnndf0_TAG20210620T005353_0.461.1075683233
File Name: /oracle/databkup/nnndf0_TAG20210620T005353_0.540.1075683661

If security or permissions are not concern, then grant ownership to oracle user to /home directory

[oracle@orcldg /]$ chown -R oracle:dba /home/

ORA-00392: log 1 of thread 1 is being cleared, operation not allowedORA-00312: online log 1 thread 1: ‘/u01/ORCLDGP/onlinelog/o1_mf_1_%u_.log’

Cause:

After cloning a database from source to destination, i was issuing resetlogs for an incomplete recovery of new database.But ended up with ora-00392 error.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/ORCLDGP/onlinelog/o1_mf_1_%u_.log'

When i check status of online logs , they were in clear state

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1          0   52428800        512          1 NO
CLEARING_CURRENT      14764677 20-JUN-21   2.8147E+14                    0

         3          1          0   52428800        512          1 YES
CLEARING              14760171 20-JUN-21     14764677 20-JUN-21          0

         2          1          0   52428800        512          1 YES
CLEARING              14755753 19-JUN-21     14760171 20-JUN-21          0

Workaround:

Manually clear the logfiles to proceed with resetlog

SQL> alter database clear logfile group 1;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1          0   52428800        512          1 NO
CURRENT               14764677 20-JUN-21   2.8147E+14                    0

         3          1          0   52428800        512          1 YES
CLEARING              14760171 20-JUN-21     14764677 20-JUN-21          0

         2          1          0   52428800        512          1 YES
CLEARING              14755753 19-JUN-21     14760171 20-JUN-21          0


SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL>

Check the fresh log files

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1          1   52428800        512          1 NO
CURRENT               14768907 21-JUN-21   2.8147E+14                    0

         2          1          0   52428800        512          1 YES
UNUSED                       0                      0                    0

         3          1          0   52428800        512          1 YES
UNUSED                       0                      0                    0

Post the logfile header clear, resetlog worked

SQL>    alter database open resetlogs;

Database altered.

ORA-65101: container database set up incorrectly

Cause:

If the database is considered as CDB and controlfile from non CDB database is restored in CDB database,then we get ora-65101

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             956305312 bytes
Database Buffers          536870912 bytes
Redo Buffers               13848576 bytes
ORA-65101: container database set up incorrectly

Workaround:

Check the parameter enable_pluggable_database

SQL> show parameter pluggable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_pluggable_database            boolean     TRUE


SQL> alter system set enable_pluggable_database=false scope=spfile;

System altered.

Set the parameter to false

SQL> show parameter pluggable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_pluggable_database            boolean     FALSE

Bounce the database

SQL> shu immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

Now you should be able to startup successfully

SQL> startup;

ORA-38760: This database instance failed to turn on flashback database

Cause:

When i start the database and try to open the database after restore of controlfile from different database, i get flashback data generation failure error

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             956305312 bytes
Database Buffers          536870912 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

Error details

SQL> !oerr ora 38760
38760, 00000, "This database instance failed to turn on flashback database"
// *Cause: Database flashback is on but this instance failed to
//         start generating flashback data. Look in alert log for more
//         specific errors.
// *Action: Correct the error or turn off database flashback.

Workaround:

Shutdown the instance

SQL> shu immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

Start the database in mount mode

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             956305312 bytes
Database Buffers          536870912 bytes
Redo Buffers               13848576 bytes
Database mounted.

Check the flashback

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES


Switch off flashback

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

Now open the database

SQL> alter database open resetlogs;

ORA-01103: database name ‘ORCL’ in control file is not ‘ORCLDGP’

Cause:

While you restore a database from source database to new database , the controlfile backup should be taken from source database and restored in new database for reconstructing the whole database with consistent scn. If the database name in destination is different,then we need to change the database name in controlfile

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             956305312 bytes
Database Buffers          536870912 bytes
Redo Buffers               13848576 bytes
ORA-01103: database name 'ORCL' in control file is not 'ORCLDGP'

Workaround:

Change the database name and db unique name in controlfile using spfile


SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      ORCLDGP

SQL> alter system set db_name=orcl scope=spfile;

System altered.

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ORCL

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

System altered.

Bounce the database

SQL> shu immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             956305312 bytes
Database Buffers          536870912 bytes
Redo Buffers               13848576 bytes

PRCR-1079 : Failed to start resource ora.LISTENER.lsnr-CRS-2501: Resource ‘ora.LISTENER.lsnr’ is disabled

Error: Listener failed to start because it is disable

[grid@orcldg ~]$ srvctl start listener
PRCR-1079 : Failed to start resource ora.LISTENER.lsnr
CRS-2501: Resource 'ora.LISTENER.lsnr' is disabled

Enable listener

[grid@orcldg ~]$ srvctl enable listener

Start listener

[grid@orcldg ~]$ srvctl start listener

Check status of listener

[grid@orcldg ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): orcldg