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

Migrating from Oracle 11g to Oracle 19c involves a series of steps, including upgrading the database and performing data migration.

Migrating from Oracle 11g to Oracle 19c involves a series of steps, including upgrading the database and performing data migration. Here are the general steps involved:

Perform a full backup of your Oracle 11g database.

Install the Oracle 19c software on your server.

Create a new Oracle 19c database instance.

Upgrade the Oracle 11g database to the Oracle 19c version using the Database Upgrade Assistant (DBUA) tool. Here are the steps:

a. Launch the DBUA tool and select the option to upgrade an existing database.

b. Select the Oracle 11g database that you want to upgrade.

c. Follow the prompts and select the appropriate upgrade options, including the database version and the components to upgrade.

d. Review the upgrade summary and click "Upgrade" to start the upgrade process.

e. Monitor the progress of the upgrade and resolve any issues that may arise.

Perform post-upgrade tasks, including updating the initialization parameters, reviewing the database settings, and ensuring that the database is functioning correctly.

Migrate the data from the Oracle 11g database to the Oracle 19c database. This can be done using several methods, including Oracle Data Pump, Oracle GoldenGate, and SQL*Plus.

Test the new Oracle 19c database to ensure that it is functioning correctly and that all data has been migrated successfully.

Here are some commands you may use for data migration:

Oracle Data Pump export from Oracle 11g:

expdp username/password@source_db directory=DATA_PUMP_DIR dumpfile=export.dmp logfile=export.log full=y

Here are some commands you may use for data migration:

Oracle Data Pump export from Oracle 11g:

expdp username/password@source_db directory=DATA_PUMP_DIR dumpfile=export.dmp logfile=export.log full=y

Oracle Data Pump import to Oracle 19c:

impdp username/password@target_db directory=DATA_PUMP_DIR dumpfile=export.dmp logfile=import.log full=y

SQL*Plus export/import:

exp username/password@source_db file=export.dmp log=export.log full=y imp username/password@target_db file=export.dmp log=import.log full=y

Note that the specific steps and commands may vary depending on your environment and specific requirements. It is recommended that you consult the Oracle documentation and seek assistance from an experienced Oracle database administrator (DBA) to ensure a successful migration.

After migrating from Oracle 11g to Oracle 19c, there are some post-migration steps that you should perform to ensure that your new database is properly configured and functioning correctly. Here are some general steps and commands that you can use:

Update initialization parameters: Review the initialization parameters for the new Oracle 19c database and update them as necessary. Some common parameters to check include MEMORY_TARGET, SGA_TARGET, PGA_AGGREGATE_TARGET, LOG_ARCHIVE_DEST_n, DB_RECOVERY_FILE_DEST, and DB_CREATE_FILE_DEST.

Update optimizer statistics: Gather optimizer statistics for all database objects using the DBMS_STATS package. You can use the following command to gather statistics for all objects in the database:

EXEC DBMS_STATS.GATHER_DATABASE_STATS;

Review database security settings: Check the database security settings to ensure that they are appropriate for your environment. This includes checking user privileges, roles, and passwords, as well as enabling appropriate auditing and security policies.

Perform a full database backup: Take a full backup of the new Oracle 19c database using RMAN or another backup tool. This ensures that you have a recoverable copy of the database in case of any issues.

Perform testing: Test the new Oracle 19c database to ensure that it is functioning correctly and that all data has been migrated successfully. This includes running queries and applications to ensure that they work as expected.

Monitor database performance: Monitor the performance of the new Oracle 19c database to ensure that it is meeting your performance requirements. Use the various Oracle performance monitoring tools, such as AWR reports, to identify and address any performance issues.

Ensure high availability: Configure high availability features such as Oracle Data Guard or Oracle RAC to ensure that your database is always available in case of any hardware or software failures.

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