What is the difference between restore point & guaranteed restore point?

Sometimes it is more efficient to roll-back changes in a database rather than do a point-in-time recovery. Flashback Database can rewind the entire database and changes that occurred within a given time window. The effects are similar to database point-in-time recovery.

Normal Restore Points

You can create restore points to enable you to Flashback the database to a particular point in time or SCN. You can think of it as a bookmark or alias that can be used with commands that recognize a RESTORE POINT clause as shorthand for specifying an SCN. In essence, before you perform any operations that you may have to reverse you can create a normal restore point. The name of the restore point and SCN are then recorded within the control file. So basically creating a restore point eliminates the need to determine the current SCN before performing operations or finding the proper one after the fact. A label for an SCN or time. For commands that support an SCN or time, you can often specify a restore point. Normal restore points exist in the circular list and can be overwritten in the control file. However, if the restore point pertains to an archival backup, then it will be preserved in the recovery catalog. You can use RESTORE POINTS to specify the target SCN in the following contexts:  

• RECOVER DATABASE and FLASHBACK DATABASE commands within RMAN 
• FLASHBACK TABLE in SQL*Plus
• The Fast Recovery Area must be configured (db_recovery_file_dest +db_recovery_file_dest_size).
• FLASHBACK_DATABASE must be enabled.
• Archivelog mode must be active.
• It is aligned with the parameter: db_flashback_retention_target.
• This means that logs older than the specified parameter will be deleted/overwritten, which can result in insufficient information to perform a flashback.
• Without a previous Flashback_Database operation, the Restore Point is useless (warning).
• It causes more I/O because different block versions are retained at different points in time.
• It can generate a larger number of Flashback logs.

Creating Normal Restore Points

Execute a statement such as the following, which creates a restore point named Restore_Point1:

    SQL> create restore point Restore_Point1;

              Restore point created.

 

How It Works

Restore points are named positions in time. While flashing a database back, you can specify a restore point as a destination instead of specifying an SCN or timestamp. However, flashing back to a restore point is possible only if the flashback logs are available for the time associated with the restore point. Because the restore points created by following this recipe are not guaranteed, they are known as unguaranteed or normal restore points. Normal restore points are the default type.

Guaranteed Restore Points (GRP) 

A Guaranteed Restore Point can be used to perform a Flashback Database operation even if flashback logging is not enabled for your database. It can be used to revert a whole database to a known good state days or weeks ago, as long as there is enough disk space in the flash recovery area to store the needed logs. Even effects of NOLOGGING operations like direct load inserts can be reversed using guaranteed restore points. Limits to both types of Restore Points include shrinking a datafile or dropping a tablespace can prevent flashing back the affected datafiles to the restore point. A restore point for which the database is guaranteed to retain the flashback logs for an Oracle Flashback Database

operation. Unlike a normal restore point, a guaranteed restore point does not age out of the control file and must be explicitly dropped. Guaranteed restore points utilize space in the flash recovery area, which must be defined. 

• The Fast Recovery Area must be configured (db_recovery_file_dest + db_recovery_file_dest_size).
• Archivelog mode must be active.
• It is a fixed, never-expiring pointer in the database.
• It must be manually deleted, or else the Fast Recovery Area may become full.
• FLASHBACK_DATABASE does not need to be enabled.
• It is independent of the Retention_target parameter.
• This functionality is also used by the Autoupgrade utility.
• Only Flashback Database operations use Flashback logs.
• It generates fewer Flashback logs.
• It causes less I/O because only one copy of the block image before the change is stored in the Flashback log.
• The user must have the SYSDBA system privileges.

Creating Guaranteed Restore Points

Add the guarantee keyword to your create restore point command. For example:

    SQL> create restore point Restore_Point2 guarantee flashback database;

              Restore point created.

How It Works

When a guaranteed restore point is defined, the associated flashback logs are never deleted unless the

restore point is dropped. This will reduce the available space in the fast recovery area. A filled-up fast recovery area will cause the database instance to abort, with the failure in the recovery writer (RVWR) process. So, create guaranteed restore points only when you need to go back to them after some preestablished event to be completed in the near future, such as doing a test run of the application and then reverting to the starting data sets. After the test is completed, drop the guaranteed restore points.

Flashing Back to a Specific SCN

Flash the database back to your desired SCN. For instance, to flash back to SCN 1050951 ,

issue the following RMAN command:

RMAN> flashback database to scn 1050951;

Starting flashback at 03-APR-24

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=16 device type=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished flashback at 03-APR-24

This command flashed the database back

Flashing Back to a Specific Time

Use the following command to flash back to a time just two minutes ago. Since a day has

24 hours, with 60 minutes each, 2 minutes happen to be 2/60/24 of a day:

RMAN> flashback database to time ’sysdate-2/60/24’;

Starting flashback at 13-APR-24

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 70 is already on disk as

file +FRA/cdb1/archivelog/2024_04_13/thread_1_seq_70.279.789267613

archived log for thread 1 with sequence 71 is already on disk as

file +FRA/cdb1/archivelog/2024_04_13/thread_1_seq_71.280.789285641

media recovery complete, elapsed time: 00:00:40

Finished flashback at 13-APR-24

If you want to flashback to a specific time, not in reference to a time such as sysdate,

you can use the timestamp instead of a formula:

RMAN> flashback database to time "to_date(’08/03/2012 22:00:00’,’mm/dd/yyyyhh24:mi:ss’)";

This flashes the database back to that specific timestamp.

Please feel free to ask. thank you 🙂

Toufique Khan 

CONTINUE_LOAD_ON_FORMAT_ERROR option in Oracle Data Pump

Oracle Data Pump is a utility that performs database export (expdp) and import (impdp) operations. This utility is faster and more flexible than previous versions of the exp and imp utilities.

Directs Oracle Data Pump to skip forward to the start of the next granule when a stream format error is encountered while loading table data. Stream format errors typically are the result of corrupt dump files. If Oracle Data Pump encounters a stream format error, and the original export database is not available to export the table data again, then you can use CONTINUE_LOAD_ON_FORMAT_ERROR. If Oracle Data Pump skips over data, then not all data from the source database is imported, which potentially skips

hundreds or thousands of rows.

Role of the CONTINUE_LOAD_ON_FORMAT_ERROR Option

If a data format error occurs during an Import Data Pump (impdp) operation, instead of rolling back the entire process, you can skip only the portion of data that encountered the error and continue loading from the next portion of data. This option allows the entire process to continue without fail.

When you use the `CONTINUE_LOAD_ON_FORMAT_ERROR` parameter with Oracle Data Pump, it instructs the data pump to skip forward to the start of the next granule when a stream format error is encountered while loading table data. A granule is a unit of work within Oracle Data Pump, and skipping to the next granule allows the data pump to continue processing the remaining data.

Understanding Stream Format Errors

Stream format errors in Oracle Data Pump usually indicate issues with the dump files. These errors can occur due to a variety of reasons, such as:

• Corruption during the export process
• Issues during the transfer of dump files
• Hardware or storage problems
• Software bugs or issues

How to use the CONTINUE_LOAD_ON_FORMAT_ERROR Option

DATA_OPTIONS=CONTINUE_LOAD_ON_FORMAT_ERROR Add options when running the Import Data Pump (impdp) command.

example:impdp system/orcl DIRECTORY=data_pump_dir DUMPFILE=export_1.dmp DATA_OPTIONS=CONTINUE_LOAD_ON_FORMAT_ERROR

Pros and Cons of CONTINUE_LOAD_ON_FORMAT_ERROR Option

Advantages:

This will prevent the entire process from failing and allow you to move on.

You can skip only the portion of data in error and import the remaining data successfully.

Disadvantage:

Data integrity may be compromised because the portion of data in error is not retrieved.

You may need to determine the cause of the error and resolve it.

Additional Information 

Oracle Data Pump is used for a variety of purposes, including database migration, backup, and restore. For more information about the data pump utility.

Implications of Using CONTINUE_LOAD_ON_FORMAT_ERROR

While CONTINUE_LOAD_ON_FORMAT_ERROR allows the data pump to continue processing even after encountering format errors, it comes with some significant implications:

• Incomplete Data Import: Since the data pump skips over the data that caused the format error, you risk importing incomplete data into the target database. This can result in missing rows or incomplete data sets, which can be problematic for data integrity and consistency.
• Data Loss: Depending on the extent of the format errors and the number of granules skipped, you might end up losing a significant amount of data from the source database during the import process.
• Data Integrity Concerns: Skipped data can lead to data integrity issues in the target database. Inconsistent or incomplete data can affect the reliability and accuracy of the database, leading to potential problems in data analysis, reporting, and application functionality.

Best Practices and Recommendations

To minimize the risks associated with using CONTINUE_LOAD_ON_FORMAT_ERROR, consider the following best practices:

• Validate Dump Files: Before starting the import process, validate the integrity of the dump files using Oracle Data Pump's CHECKSUM parameter. This can help identify any corrupt or incomplete dump files before they cause format errors during the import.
• Re-export Data: If possible, re-export the table data from the source database to generate new dump files without any format errors. This is the safest and most reliable way to ensure a complete and accurate import of data into the target database.
• Monitor Import Process: Monitor the import process closely and review any errors or warnings generated by Oracle Data Pump. This can help you identify and address any issues promptly, minimizing the impact on data integrity and completeness.
• Backup: Always maintain regular backups of your databases, including both source and target databases. In case of any issues or data loss during the import process, you can restore the databases from the backups to recover the lost or corrupted data.

Conclusion

While CONTINUE_LOAD_ON_FORMAT_ERROR provides a workaround to continue the data import process despite encountering format errors, it should be used cautiously and as a last resort. It's crucial to validate dump files, monitor the import process, and maintain backups to ensure data integrity, completeness, and reliability when using Oracle Data Pump for data import operations.

Please feel free to ask. thank you 🙂

Toufique Khan 

Oracle Autonomous Health Framework

Oracle Autonomous Health Framework is a collection of components that analyzes the diagnostic data collected and proactively identifies issues before they affect the health of your clusters or your Oracle Real Application Clusters (Oracle RAC) databases.

Oracle Autonomous Health Framework (AHF) maximizes availability and performance by enforcing best practices, capturing data at first failure, monitoring the whole system (server, database, I/O, and network) to proactively discover issues and notify the user and provide timely bug resolution by suggesting fixes automatically after failure.

• System administrators can use most of the components in Oracle Autonomous Health Framework interactively during installation, patching, and upgrading. 
• Database administrators can use Oracle Autonomous Health Framework to diagnose operational runtime issues and mitigate the impact of these issues.

Autonomous Health Framework (AHF) provides several diagnostic tools in a single bundle, making it easy to gather diagnostic information about the Oracle database and Clusterware, which in turn helps with problem resolution when dealing with Oracle Support.

What is Autonomous Health Framework (AHF)???

Autonomous Health Framework (AHF) combines the existing Trace File Analyzer (TFA) and ORAchk/EXAchk, along with a bunch of other stuff, into a single download and setup. 

if you install AHF using the root user and start everything as daemon processes, it will give you proactive health monitoring, which could help you fix issues before they become a problems.

Download Autonomous Health Framework (AHF)

Use the mentioned Docs to download the AHF tool.

Autonomous Health Framework (AHF) - Including TFA and ORAchk/EXAchk (Doc ID 2550798.1)

Install Oracle Autonomous Health Framework (AHF) - Using root user

Autonomous Health Framework (AHF) can be installed as the "root" user on the server, which provides the most functionality and allows it to run in a proactive manner as a daemon. In this example, we will perform an installation as the root user.

Unzip the software and run the ahf_setup command.

root@srv1/oswatcher/oracle_temp#ls -trl

total 2966656

-r-xr-xr-x    1 oracle   oinstall  766958181 Oct 29 22:21 ahf_setup

-rw-r--r--    1 oracle   oinstall        384 Oct 29 22:22 ahf_setup.dat

-rw-r--r--    1 oracle   oinstall        625 Nov 01 22:20 oracle-tfa.pub

-rw-r--r--    1 oracle   oinstall       1525 Nov 01 22:20 README.txt

-rw-r--r--    1 oracle   oinstall  751955111 Dec 03 15:07 AHF-AIX-PPC64_v21.3.0.zip

root@srv1/oswatcher/oracle_temp#

root@srv1/oswatcher/oracle_temp#./ahf_setup

AHF Installer for Platform Linux Architecture x86_64

AHF Installation Log : /tmp/ahf_install_202000_7305_2021_01_29-23_52_54.log

Starting Autonomous Health Framework (AHF) Installation

AHF Version: 20.2.0 Build Date: 202011222149

Default AHF Location : /opt/oracle.ahf

Do you want to install AHF at [/opt/oracle.ahf] ? [Y]|N : Y

AHF Location : /opt/oracle.ahf

AHF Data Directory stores diagnostic collections and metadata.

AHF Data Directory requires at least 5GB (Recommended 10GB) of free space.

Please Enter AHF Data Directory : /root

AHF Data Directory : /root/oracle.ahf/data

Do you want to add AHF Notification Email IDs ? [Y]|N : N

Login using root is disabled in sshd config. Installing AHF only on Local Node

Extracting AHF to /opt/oracle.ahf

Configuring TFA Services

Discovering Nodes and Oracle Resources

Successfully generated certificates.

Starting TFA Services

Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.

Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.

.----------------------------------------------------------------------------------------.

| Host            | Status of TFA | PID      | Port  | Version    | Build ID             |

+-----------------+---------------+----------+-------+------------+----------------------+

| srv1            | RUNNING       | 198263   | 26877 | 20.2.1.0.0 | 20200020201122214905 |

'-----------------+---------------+----------+-------+------------+----------------------'

Running TFA Inventory…

Please add world execute permissions for all directories above /root to enable TFA Non-Root Access

Adding default users to TFA Access list…

.-----------------------------------------------------------------------------.

|                         Summary of AHF Configuration                        |

+-----------------+-----------------------------------------------------------+

| Parameter       | Value                                                     |

+-----------------+-----------------------------------------------------------+

| AHF Location    | /opt/oracle.ahf                                           |

| TFA Location    | /opt/oracle.ahf/tfa                                       |

| Orachk Location | /opt/oracle.ahf/orachk                                    |

| Data Directory  | /opt/oracle.ahf.data/oracle.ahf/data                      |

| Repository      | /opt/oracle.ahf.data/oracle.ahf/data/repository           |

| Diag Directory  | /opt/oracle.ahf.data/oracle.ahf/data/srv1/diag            |

'-----------------+-----------------------------------------------------------'

Starting orachk scheduler from AHF …

AHF binaries are available in /opt/oracle.ahf/bin

AHF is successfully installed

Moving /tmp/ahf_install_202000_7305_2021_01_29-23_52_54.log to /root/oracle.ahf/data/srv1/diag/ahf/

root@srv1/oswatcher/oracle_temp#

Most Useful Things Before Installation of AHF.

Oracle Autonomous Health Framework (AHF) - with ORAchk, EXAchk & Trace File Analyzer

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

Installation/Upgrade Instructions

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

This must be completed for each node of a cluster.

1) Copy the zip file to a target machine and unzip

2) Verify the installer file integrity. ( See Below )

3) Run the command: ahf_setup

For advanced installation/upgrade options see the User Guide

Installer file Verification

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

The ahf_setup file has been signed to ensure it has not been modified from the originally created file.

To Verify the file you will need to have installed on your system 'openssl' or have a separate system that has openssl to verify the installer.

In the directory that the zip file was extracted to run :-

openssl dgst -sha256 -verify ./oracle-tfa.pub -signature ./ahf_setup.dat ./ahf_setup

Only if the command returns 'Verified OK' should the installer be used.

If the command does not return 'Verified OK' please contact Oracle Support and report this issue.

User Guide:

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

The AHF User Guide can now be found online at https://docs.oracle.com/en/engineered-systems/health-diagnostics/autonomous-health-framework/ahfug/index.html

Enhancements and Bug Fixes

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

A full list of enhancements and bugs fixed in each release is available at:

https://support.oracle.com/epmos/main/downloadattachmentprocessor?attachid=2550798.1:AHF_VERSION_HISTORY

Useful Command's

Check AHF Details

cd /opt/oracle.ahf/bin   <--- AHF Location

./ahfctl version

./ahfctl version -a

./ahfctl statusahf

./tfactl toolstatus

Start

./ahfctl startahf

Stop

./ahfctl stopahf

Diagnostic Collection

tfactl diagcollect -from "Oct/01/2022 20:00:00" -to "Oct/03/2022 07:00:00"

JServer JAVA Virtual Machine Component is in the LOADING state

After upgrading the database from Oracle 12c to 19c, Java packages show the status "LOADING".JServer JAVA Virtual Machine Component is in the LOADING state.

Symptoms:

The JAVAVM Component is in the LOADING state in the database.

Check the status of all the components in the database.

SQL> set pages 1000;

col comp_id for a12;

col comp_name for a35;

col version for a12;

col status for a12;

select comp_id,comp_name,version,status from dba_registry;

COMP_ID      COMP_NAME                                    VERSION     STATUS

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

CATALOG    Oracle Database Catalog Views           19.0.0.0.0       VALID

CATPROC    Oracle Database Packages and Types   19.0.0.0.0       VALID

RAC              Oracle Real Application Clusters         19.0.0.0.0       OPTION OFF

JAVAVM      JServer JAVA Virtual Machine         19.0.0.0.0        LOADING

XML             Oracle XDK                                         19.0.0.0.0        VALID

CATJAVA     Oracle Database Java Packages           19.0.0.0.0        VALID

XDB             Oracle XML Database                         19.0.0.0.0        VALID

OWM           Oracle Workspace Manager                 19.0.0.0.0        VALID

8 rows selected.

Solution:

Need to recompile the above packages as shown in the fix. Change the Registry entry for JAVAVM.

Follow The bellow steps to make it fix-

SQL> select comp_name,version,status from dba_registry where comp_name like '%JAVA%';

COMP_NAME                                    VERSION    STATUS

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

JServer JAVA Virtual Machine        19.0.0.0.0     LOADING

SQL> execute sys.dbms_registry.loaded('JAVAVM')

PL/SQL procedure successfully completed.

SQL> execute sys.dbms_registry.valid('JAVAVM');

PL/SQL procedure successfully completed.

Check the status of the component.

SQL> set pages 1000;

col comp_id for a12;

col comp_name for a35;

col version for a12;

col status for a12;

select comp_id,comp_name,version,status from dba_registry;

COMP_ID      COMP_NAME                                    VERSION     STATUS

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

CATALOG    Oracle Database Catalog Views           19.0.0.0.0       VALID

CATPROC    Oracle Database Packages and Types   19.0.0.0.0       VALID

RAC              Oracle Real Application Clusters         19.0.0.0.0       OPTION OFF

JAVAVM      JServer JAVA Virtual Machine         19.0.0.0.0        VALID

XML             Oracle XDK                                         19.0.0.0.0        VALID

CATJAVA     Oracle Database Java Packages           19.0.0.0.0        VALID

XDB             Oracle XML Database                         19.0.0.0.0        VALID

OWM           Oracle Workspace Manager                 19.0.0.0.0        VALID

8 rows selected.

Now The JAVAVM component is Valid.

Reference Doc Id: After Database Upgrade, Java Packages shows status "LOADING" (Doc ID 256910.1)

Dataguard Set up in oracle 12c RAC (RAC to RAC)

 

Above are the environment details for two Node RAC Dataguard setups.

Prerequisite: 

PRIMARY DB :(Node1,Node3)

Archivelog mode

check if the db is in archive log is enabled 

SQL> archive log list ;

Database log mode             Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 214

Next log sequence to archive 215

Current log sequence 215

SQL>  show parameter DB_RECOVERY_FILE_DEST

NAME                                     TYPE         VALUE

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

db_recovery_file_dest              string          +FRA

db_recovery_file_dest_size      big integer  100G

If the archive log is not enabled. Follow the below steps to change the archive log mode .

[oracle@Node1 ~]srvctl status database -d ITMS

[oracle@Node1 ~]srvctl stop database -d ITMS

[oracle@Node1 ~]srvctl start database -d ITMS -o mount 

[oracle@Node1 ~]$ . oraenv

ORACLE_SID = [ITMS1] ?

[oracle@Node1 ~]sqlplus / as sysdba    

SQL> alter database archivelog;

[oracle@Node1 ~]srvctl stop database -d ITMS 

[oracle@Node1 ~]srvctl start database -d ITMS  

Force Logging mode

SQL>  select force_logging from v$database;

FORCE_LOGGING

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

NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING

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

YES

Configure Standby Redo Log (SRL) files for both the Instance 

Min No of SRL= no of threads *(Group in threads +1)

Use the command to check the Number of Redo Groups & threads on the Primary Database.

SQL> set lines 200 pages 200

SQL> col MEMBER for a55 

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

In our environment, we have 4 groups with 2 threads. So here Number of SRLs will be 

                          SRL= 2*(4+1)=10

Note:  1-We must consider having a single member in each group so to avoid waits with a commit for each transaction in each member.

2-No need to create standby redo log files on standby and Oracle take care of it during RMAN duplicate.

Add standby redo log file :

alter database add standby logfile thread 1 

group 9 ('+REDOLOG','+REDOLOG02')  size 2048M, 

group 10 ('+REDOLOG','+REDOLOG02')  size 2048M, 

group 11 ('+REDOLOG','+REDOLOG02')  size 2048M, 

group 12 ('+REDOLOG','+REDOLOG02')  size 2048M,

group 13 ('+REDOLOG','+REDOLOG02')  size 2048M;

alter database add standby logfile thread 2 

group 14 ('+REDOLOG','+REDOLOG02')  size 2048M,

group 15 ('+REDOLOG','+REDOLOG02')  size 2048M, 

group 16 ('+REDOLOG','+REDOLOG02')  size 2048M, 

group 17 ('+REDOLOG','+REDOLOG02')  size 2048M, 

group 18 ('+REDOLOG','+REDOLOG02')  size 2048M ;

Use the command to check the Number of Standby Redo Groups & threads that are added to the Primary Database.

SQL> select group#,thread#,bytes from v$standby_log;

STANDBY_FILE_MANAGEMENT Parameter

SQL> show parameter standby_file_management

NAME                                     TYPE       VALUE

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

standby_file_management       string       MANUAL

SQL> alter system set standby_file_management = 'AUTO' scope=spfile sid=’*’;

System altered.

SQL> show parameter standby_file_management

NAME                                      TYPE     VALUE

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

standby_file_management        string      AUTO

REMOTE_LOGIN_PASSWORDFILE Parameter 

SQL> show parameter remote_login_passwordfile;

NAME                                     TYPE       VALUE

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

remote_login_passwordfile     string        EXCLUSIVE

If not set to exclusive :

SQL> alter system set remote_login_passwordfile = 'EXCLUSIVE' scope = spfile sid=’*’;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ITMS,ITMSDR)' sid='*';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ITMS' scope= both sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ITMSDR async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ITMSDR' scope=both sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='ITMS_%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER='ITMSDR' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT='ITMS' SCOPE=BOTH sid='*';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=10 sid='*';

System altered.

Note: Set the FAL_SERVER parameter with the "NET ALIAS NAME" of the standby database. This parameter will be used only when a switchover occurs and the primary starts behaving in the standby role.

Restart the database to make the Changes Effective

[oracle@Node1 ~]$ srvctl stop database -d  ITMS

[oracle@Node1 ~]$ srvctl start database -d ITMS

Now, verify all the required values have the appropriate values.

set lines 999 pages 999

col value for a110

col name for a50

select name, value

from v$parameter

where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',

'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',

'log_archive_format','log_archive_max_processes','fal_server','fal_client','standby_file_management');

NAME                                          VALUE

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

log_archive_dest_1                      LOCATION=+FRA

log_archive_dest_2                      SERVICE=ITMSDR NOAFFIRM ASYNC VALID_FOR=                                                                       (ONLINE_LOGFILES,PRIMARY_ROLE)                                                                                                 DB_UNIQUE_NAME=ITMSDR   

log_archive_dest_state_1            enable

log_archive_dest_state_2            ENABLE

fal_client                                      ITMS

fal_server                                     ITMSDR

log_archive_config                      DG_CONFIG=(ITMS,ITMSDR)

log_archive_format                     ITMS_%t_%s_%r.arc

log_archive_max_processes       10

standby_file_management           AUTO

remote_login_passwordfile         EXCLUSIVE

db_name                                      ITMS

db_unique_name                         ITMS

Create an Initialization Parameter file for the Standby database

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

Copy the Password File from Primary to Standby

 To get the details of the Password file we will check the database configuration.

[oracle@Node1 ~]$ srvctl config database -d ITMS

Database unique name: ITMS

Database name: ITMS

Oracle home: /u01/app/oracle/dbhome

Oracle user: oracle

Spfile: +DATA/ITMS/PARAMETERFILE/spfile.283.1013156439

Password file: +DATA/ITMS/PASSWORD/pwditms.270.1013155873

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,REDOLOG,REDOLOG02,FRA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: oinstall

Database instances: ITMS1,ITMS2

Configured nodes: Node1,Node3

Database is administrator managed

get the password file location and copy it to the target 

[oracle@Node1 ~] su - grid 

[grid@Node1 ~]$ . oraenv

ORACLE_SID = [+ASM1] ?

[grid@Node1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwget  --dbuniquename ITMS

+DATA/ITMS/PASSWORD/pwditms.270.1013155873

ASMCMD>  pwcopy +DATA/ITMS/PASSWORD/pwditms.270.1013155873  /home/grid/orapwditmsdr

copying +DATA/ITMS/PASSWORD/pwditms.270.1013155873 ->  /home/grid/orapwditmsdr

Copy the Parameter File & Password File to Standby Node2

[oracle@Node1 ~]$ scp  /tmp/inititmsdr.ora  root@Node2:/home/oracle/backup

root@Node2's password:

inititmsdr.ora 100% 2277 772.5KB/s 00:00

[oracle@Node1~]$ scp /home/grid/orapwditmsdr  root@Node2:/home/oracle/backup

root@Node2's password:

orapwditmsdr   100% 7680     3.0MB/s   00:00

Add TNS entries for Standby Database in Primary & Vice Verse 

Add the tns entries of both the Primary and Standby Database in the tnsnames.ora file of both the Primary and Standby Database.

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

Primary DB TNS :

==========

ITMS =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.174)(PORT = 1524))

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.175)(PORT = 1524))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ITMS)

    )

  )

ITMS1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.174)(PORT = 1524))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ITMS)

      (INSTANCE_NAME= ITMS1)

    )

  )

ITMS2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.175)(PORT = 1524))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ITMS)

      (INSTANCE_NAME= ITMS2)

    )

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

Standby DB TNS :

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

ITMSDR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.80.17)(PORT = 1524))

    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.80.18)(PORT = 1524))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ITMSDR)

    )

  )

ITMSDR1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.17)(PORT = 1524))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ITMSDR)(UR=A)

      (INSTANCE_NAME=ITMSDR1)

    )

  )

ITMSDR2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.18)(PORT = 1524))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ITMSDR)(UR=A)

      (INSTANCE_NAME=ITMSDR2)

    )

  )

We have done the Modification of Parameters in the Primary Database.

Now the Changes will be done in the Standby Database.

STANDBY DB :(Node2,Node4)

Static Entry of Listener

Add Static Entry of Listener at both nodes in Standby Database (Node2,Node4)

LISTENER_Node2:

SID_LIST_LISTENER =

(SID_LIST =

   (SID_DESC =

       (GLOBAL_DBNAME = ITMSDR)

       (ORACLE_HOME = /u01/app/oracle/dbhome)

       (SID_NAME = ITMSDR1)

   )

 )

LISTENER_Node4:

SID_LIST_LISTENER =

(SID_LIST =

   (SID_DESC =

     (GLOBAL_DBNAME = ITMSDR)

     (ORACLE_HOME = /u01/app/oracle/dbhome)

     (SID_NAME = ITMSDR2)

 )

)

Create Required Directories(PATH) for the Standby Instances

Nodes2

[oracle@Node2 ]$ cd $ORACLE_BASE/diag/rdbms/

[oracle@Node2 rdbms]$ mkdir -p primdr/ITMSDR1/trace

[oracle@Node2 rdbms]$ mkdir -p primdr/ITMSDR1/cdump

[oracle@Node2 diag]$ cd ../admin/

[oracle@Node2 admin]$ mkdir -p ITMSDR/adump

Node4

[oracle@Node4~]$ cd $ORACLE_BASE/diag/rdbms/

[oracle@Node4 rdbms]$ mkdir -p primdr/ITMSDR2/trace

[oracle@Node4 rdbms]$ mkdir -p primdr/ITMSDR2/cdump

[oracle@Node2 rdbms]$ cd ../../admin/

[oracle@Node2 admin]$ mkdir -p ITMSDR/adump

Create the ASM Directories

ASMCMD> mkdir data/itmsdr

ASMCMD> cd data/itmsdr

ASMCMD> mkdir PARAMETERFILE DATAFILE CONTROLFILE TEMPFILE ONLINELOG

Modify the Pfile that we had copied from Primary Database

vi  /home/oracle/inititmsdr.ora

*.audit_file_dest='/u01/app/oracle/admin/ITMSDR/adump'

*.audit_trail='NONE'

*.cluster_database=true

*.compatible='12.1.0.2.0'

*.control_files='+DATA/ITMSDR/control01.ctl','+DATA/ITMSDR/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ITMS'

*.db_recovery_file_dest='+FRA'

*.db_recovery_file_dest_size=161061273600

*.db_unique_name='ITMSDR'

*.diagnostic_dest='/apps/oracle/121'

*.fal_client='ITMSDR'

*.fal_server='ITMS'

ITMSDR1.instance_number=1

ITMSDR2.instance_number=2

*.log_archive_config='DG_CONFIG=(ITMSDR,ITMS)'

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ITMSDR'

*.log_archive_dest_2='SERVICE=ITMS VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ITMS'

*.log_archive_format='ITMS_%t_%s_%r.arc'

*.memory_target=0

*.open_cursors=1000

*.pga_aggregate_target=64424509440

*.processes=512

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=256

*.sga_max_size=161061273600

*.sga_target=161061273600

*.shared_servers=0

*.standby_file_management='AUTO'

ITMSDR2.thread=2

ITMSDR1.thread=1

ITMSDR1.undo_tablespace='UNDOTBS1'

ITMSDR2.undo_tablespace='UNDOTBS2'

Passwordfile 

Copy the Passwordfile that we had transferred from the Primary Database to the $ORACLE_HOME/dbs of both the Standby Nodes and Rename it on Both Nodes.

[oracle@Node2 ~]$ cp orapwdITMS /u01/app/oracle/dbhome/dbs/orapwITMSDR1

[oracle@Node2 ~]$ scp orapwdITMS  oracle@Node4:/u01/app/oracle/dbhome/dbs/orapwITMSDR2

Restore the Database Backup on Standby Node

export ORACLE_SID=ITMSDR1

export ORACLE_HOME=/u01/app/oracle/dbhome

export ORACLE_BASE=/u01/app/oracle

 Now we will create the physical standby database from the primary database using RMAN DUPLICATE Database :

[oracle@Node2 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 15 03:21:33 2020

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

Connected to an idle instance.

SQL> startup nomount pfile=' /home/oracle/inititmsdr.ora' ;

ORACLE instance started.

Total System Global Area 1.0737E+11 bytes

Fixed Size                  5298856 bytes

Variable Size            3.2481E+10 bytes

Database Buffers         7.4625E+10 bytes

Redo Buffers              263135232 bytes

[oracle@Node2 ~]$ rman target sys/passwd@ITMS auxiliary sys/passwd@ITMSDR1

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jun 15 03:32:22 2020

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

connected to target database: ITMS (DBID=xxxxxxxxxx)

connected to auxiliary database: ITMS (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

..........................................................................

Finished Duplicate Db at 15-JUN-20

RMAN> alter system register;

Statement processed

RMAN> exit

Recovery Manager complete.

[oracle@Node2 ~]$ sqlplus "/as sysdba"

SQL> select open_mode, database_role from v$database;

OPEN_MODE    DATABASE_ROLE

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

MOUNTED        PHYSICAL STANDBY

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

NAME  OPEN_MODE   DATABASE_ROLE

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

ITMS    MOUNTED       PHYSICAL STANDBY

SQL> select group#,thread#,bytes from v$standby_log;

GROUP#  THREAD#   BYTES

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

         9           1              2147483648

        10          1              2147483648

        11          1              2147483648

        12          1              2147483648

        13          1              2147483648

        14          2              2147483648

        15          2              2147483648

        16          2              2147483648

        17          2              2147483648

        18          2              2147483648

10 rows selected.

SQL> select THREAD#, max(SEQUENCE#) from v$log_history group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1            304

         2            215

This completes the copying of data from the primary database to the standby database. However, since we know that the standby database is created from a single instance, the settings file and the password file are stored in the local file system. So in the next step, we will copy the settings file and the password file to the ASM shared file system. And then will bring both the instances of standby database up and running.

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

Parameter file copy

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

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

Database altered.

SQL> select THREAD#, max(SEQUENCE#) from v$log_history group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1            304

         2            215

 

SQL> show parameter pfile

NAME                                     TYPE        VALUE

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

spfile                                         string

SQL> create spfile='+DATA' from pfile='/home/oracle/inititmsdr.ora';

File created.

ASMCMD> cd ITMSDR/

ASMCMD> ls

CONTROLFILE/

DATAFILE/

PARAMETERFILE/

ASMCMD> cd PARAMETERFILE/

ASMCMD> ls

spfile.277.123456789

ASMCMD> pwd

+DATA/ITMSDR/PARAMETERFILE

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shu abort ;

ORACLE instance shut down.

Now go to the DBS location of both the nodes and add the spfile location :

[oracle@Node2 ~]$ vi /u01/app/oracle/dbhome/dbs/initITMSDR1.ora

spfile='+DATA/ITMSDR/PARAMETERFILE/spfile.277.1230456789

[oracle@Node4~]$ vi /u01/app/oracle/dbhome/dbs/initITMSDR2.ora

spfile='+DATA/ITMSDR/PARAMETERFILE/spfile.277.1230456789

ASMCMD> pwcopy /u01/app/oracle/dbhome/dbs/orapwITMSDR1

+DATA/ITMSDR/orapwitmsdr

copying /u01/app/oracle/dbhome/dbs/orapwITMSDR1 -> +DATA/ITMSDR/orapwitmsdr

Add the Standby DB Instance to Grid Service 

 As the Standby Database is in RAC ,we will register/add the database and instance services to the Cluster .

[oracle@Node2 ~]$ srvctl add database -d ITMSDR -n ITMS -o /u01/app/oracle/dbhome -r PHYSICAL_STANDBY -startoption MOUNT -diskgroup DATA,FRA -pwfile +DATA/ITMSDR/orapwitmsdr

[oracle@Node2 ~]$ srvctl add instance -d ITMSDR -i ITMSDR1 -n Node2

[oracle@Node2  ~]$ srvctl add instance -d ITMSDR -i ITMSDR2 -n Node4

[oracle@Node2 dbs]$ sqlplus "/as sysdba"

Connected to:

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

With the Partitioning, Real Application Clusters and Automatic Storage Management options

SQL> shu abort ;

ORACLE instance shut down.

[oracle@Node2 ~]$ srvctl start database -d  ITMSDR

[oracle@Node2 ~]$ srvctl status database -d  ITMSDR

Instance ITMSDR1 is running on node Node2

Instance ITMSDR2 is running on node Node4

[oracle@Node2 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on 

Connected to:

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

With the Partitioning, Real Application Clusters and Automatic Storage Management options

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> select name,open_mode,database_role ,instance_name,status from gv$database,gv$instance ;

NAME    OPEN_MODE          DATABASE_ROLE        INSTANCE_NAME    STATUS

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

ITMS      MOUNTED              PHYSICAL STANDBY  ITMSDR1                    MOUNTED

ITMS      MOUNTED              PHYSICAL STANDBY  ITMSDR2                    MOUNTED

Verify if Log Switch is Working or Not

Primary:

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select THREAD#, max(SEQUENCE#) from v$log_history group by thread#;

THREAD#   MAX(SEQUENCE#)

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

         1            346

         2            245

At Standby :

SQL> select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;

THREAD#    MAX(SEQUENCE#)

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

         1            346

         2            245

So we have successfully configured the Dataguard for our ORACLE RAC 12c.

Reference Doc Id: MAA - 10g Creating a RAC Physical Standby for a RAC Primary (Doc ID 380449.1)

Creating a 10gr2 Data Guard Physical Standby database with Real-Time apply (Doc ID 343424.1)