Step-by-step guide with commands for configuring Oracle 19c Data Guard Physical Standby

Step 1: Install Oracle 19c software on both primary and standby servers.

• Download the Oracle Database software for Oracle 19c from the Oracle website.
• Follow the installation instructions for your operating system to install the Oracle Database software on both the primary and standby servers.

Step 2: Create a standby database on the standby server.

• On the standby server, create an empty Oracle database using the Database Configuration Assistant (DBCA) or manually with SQL scripts.
• Example SQL script to create a standby database:

sql
CREATE DATABASE <database_name>
USER SYS IDENTIFIED BY <sys_password>
USER SYSTEM IDENTIFIED BY <system_password>
LOGFILE GROUP 1 ('<log_file_directory>/redo01.log') SIZE 100M,
GROUP 2 ('<log_file_directory>/redo02.log') SIZE 100M,
GROUP 3 ('<log_file_directory>/redo03.log') SIZE 100M;

Step 3: Enable archivelog mode on the primary database.

• Connect to the primary database with a privileged user.
• Enable archivelog mode using the following SQL command:

sql
ALTER DATABASE ARCHIVELOG;

Step 4: Configure log shipping on the primary database.

• Connect to the primary database with a privileged user.
• Configure log shipping to automatically transfer archived redo logs to the standby server using the following SQL command:

sql
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='<standby_server_directory>' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

Step 5: Create standby redo logs on the standby database.

• Connect to the standby database with a privileged user.
• Create standby redo logs using the following SQL command:

sql
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('<standby_redo_log_directory>/standby_redo01.log') SIZE 100M,
GROUP 5 ('<standby_redo_log_directory>/standby_redo02.log') SIZE 100M,
GROUP 6 ('<standby_redo_log_directory>/standby_redo03.log') SIZE 100M;

Step 6: Configure Data Guard properties on the primary database.

• Connect to the primary database with a privileged user.
• Configure Data Guard properties using the following SQL command:

sql
ALTER SYSTEM SET DB_UNIQUE_NAME='<primary_db_unique_name>' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='<standby_server_directory>' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(<primary_db_unique_name>,<standby_db_unique_name>)' SCOPE=BOTH;

Step 7: Start redo log apply on the standby database.

• Connect to the standby database with a privileged user.
• Start redo log apply using the following SQL command:

sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Step 8: Monitor Data Guard configuration.

• Monitor the Data Guard configuration using Oracle Database utilities, such as Data Guard Broker, Enterprise Manager, or SQL queries, to check the status and performance of the primary and standby databases.

Step 9: Test failover and switchover scenarios.

• Perform failover and switchover scenarios to test the Data Guard configuration and ensure its reliability, following Oracle's documentation and best practices.

That's it! You have successfully configured Oracle 19c Data Guard Physical Standby using the above commands. Remember to customize the commands according to your specific environment and requirements, and refer to Oracle's documentation for detailed guidance.

Looking forward to your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav

Steps for migrating from Oracle Database to PostgreSQL

            Step 1: Preparing for Migration

1. Install PostgreSQL on the target system.
2. Create a new PostgreSQL database for the migration.
3. Install the "ora2pg" tool, which is a popular tool used for migrating from Oracle to PostgreSQL.

            Step 2: Exporting Data from Oracle

1. Use the "exp" command in Oracle to export data from the Oracle database into a dump file. For example:

exp <username>/<password>@<oracle_db> file=<export_file>.dmp

            Step 3: Converting Oracle Dump to PostgreSQL Format

1. Use the "ora2pg" tool to convert the Oracle dump file to a PostgreSQL-compatible format. For example:

ora2pg -t SHOW -c <export_file>.dmp -o <output_file>.sql -d <pg_db> -u <pg_user> -p <pg_password>

            Step 4: Creating Tables and Importing Data in PostgreSQL

1. Execute the converted SQL file in PostgreSQL to create tables and import data. For example:

psql -d <pg_db> -U <pg_user> -p <pg_port> -h <pg_host> -f <output_file>.sql

            Step 5: Migrating Stored Procedures, Triggers, and Views

1. Manually convert stored procedures, triggers, and views from Oracle PL/SQL to PostgreSQL PL/pgSQL syntax.
2. Create the converted objects in PostgreSQL using appropriate PostgreSQL syntax.

            Step 6: Testing and Verification

1. Perform thorough testing of the migrated data and functionality to ensure data integrity and application functionality.

Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav