Procedure for migrating an Oracle Database from 12c to 19c on a new server using Data Pump:

Procedure for migrating an Oracle Database from 12c to 19c on a new server using Data Pump:

Target Server Preparation:

1. Install Oracle Database 19c software on the target server.
2. Create a new Oracle home directory for the 19c database (e.g., /u01/app/oracle/product/19.0.0/dbhome_1).
3. Set environment variables (e.g., ORACLE_HOME, ORACLE_SID, PATH).
4. Create a new database instance (e.g., SID=NEWDB).
5. Configure the listener and tnsnames.ora files.

Data Pump Migration:

1. On the source server (12c), create a directory for the Data Pump export files (e.g., /u01/export).
2. Run the Data Pump export command to export the entire database:

expdp system/password@source_db schemas=ALL directory=EXP_DIR dumpfile=full_db_exp.dmp logfile=exp_log.log

1. On the target server (19c), create a directory for the Data Pump import files (e.g., /u01/import).
2. Run the Data Pump import command to import the entire database:

impdp system/password@new_db directory=IMP_DIR dumpfile=full_db_exp.dmp logfile=imp_log.log

1. Monitor the import process and check for any errors.

Faster Approach using Data Pump:

1. Use the PARALLEL option to speed up the export and import processes.
2. Use the COMPRESSION option to reduce the size of the export files.
3. Use the DATA_OPTIONS option to enable data transformation and optimization.
4. Use the TABLE_EXISTS_ACTION option to handle existing tables in the target database.

Example:

expdp system/password@source_db schemas=ALL directory=EXP_DIR dumpfile=full_db_exp.dmp logfile=exp_log.log parallel=4 compression=all data_options=transform_optimize

impdp system/password@new_db directory=IMP_DIR dumpfile=full_db_exp.dmp logfile=imp_log.log parallel=4 table_exists_action=replace

Post-Migration Tasks:

1. Update the database statistics.
2. Rebuild indexes and recompile stored procedures.
3. Test the migrated database thoroughly.

Note: Ensure you have sufficient disk space and memory on both servers to accommodate the Data Pump export and import processes. Additionally, consider using Oracle GoldenGate or other replication tools for a zero-downtime migration.

Running DataPump Jobs Results In ORA-31634 ORA-31664

DataPump job (export or import) and this fails with the following errors:

ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

1. If you are just running a single job then there are probably old job names that are still found in the database and this is creating the conflict. You could try to remove these job using the steps in the following

Note 336014.1 - How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?

2. If you are running lots of jobs (more than 99) at a time, then the problem is that DataPump cannot build more than 99 consecutive jobnames when using the default job_name because the default job_name is system-generated in the form SYS_EXPORT_<mode>_NN, where NN expands to a 2-digit incrementing integer starting at 01, therefore the largest value for NN would be 99.

From Oracle Database Utilities guide:

JOB_NAME
Default: system-generated name of the form SYS_EXPORT_<mode>_NN

Purpose
Used to identify the export job in subsequent actions, such as when the ATTACH parameter is used to attach to a job, or to identify the job using the DBA_DATAPUMP_JOBS or USER_DATAPUMP_JOBS views.

Syntax and Description
JOB_NAME=jobname_string

The jobname_string specifies a name of up to 30 bytes for this export job. The bytes must represent printable characters and spaces. If spaces are included, then the name must be enclosed in single quotation marks (for example, 'Thursday Export'). The job name is implicitly qualified by the schema of the user performing the export operation. The job name is used as the name of the master table, which controls the export job.

The default job name is system-generated in the form SYS_EXPORT_<mode>_NN, where NN expands to a 2-digit incrementing integer starting at 01. An example of a default name is 'SYS_EXPORT_TABLESPACE_02'.

Example
The following example shows an export operation that is assigned a job name of exp_job:

#> expdp hr DIRECTORY=data_dump_dir DUMPFILE=export_job.dmp JOB_NAME=export_job NOLOGFILE=YES

To avoid this issue, please use the job_name DataPump parameter to create unique job names that will not conflict with any other job names in the database instead of using the default job_name.

Java Update in Oracle

1. Java version in Goldengate.

 

GoldenGate 19c comes with an "embedded" JDK version located at $OGG_HOME/jdk. We can upgrade to the latest JDK 1.8.x available for Oracle GoldenGate.

 

To upgrade $OGG_HOME/jdk to the latest

 

Download  Patch 36270061: Oracle SERVER JRE 8 Update 411

The download contains file ->  p36270061_180411_Linux-x86-64.zip

Which in turn contains files ->  \server-jre-8u411-linux-x64.tar.gz\server-jre-8u411-linux-x64.tar\jdk1.8.0_411\bin\

Just replace existing -> $OGG_HOME/jdk/*.* with ones from server-jre-8u411-linux-x64.tar\jdk1.8.0_411

 

 

 

Another Option :

JAVA_VERSION "1.8.0_411" is a part of latest OGG binaries, It is recommended to upgrade to latest OGG version :
Oracle GoldenGate 19.24.0.0.240716 Patch Set Availability ( Doc ID 3037155.1 )

 

 

 

Ref.

Can the Embedded Java Version Part Of OGG Home Be Upgraded ( Doc ID 2359395.1 )
How To Update JDK For Golden Gate $OGG_HOME/jdk? ( Doc ID 2803652.1 )

 

 

 

2. Java Version in OEM agent.

 

By default, EM 13.4 Agent uses java version "1.8.0_231" (On Linux/ Solaris / Windows).

Please note that before upgrading the JDK on Agents, Ensure that OMS's (OEM) JDK has been Updated already.

 

Apply RU 19 ( Patch 34692141 ) or a later version on the Agent Home before upgrading the JDK on agent.

 

 

For Linux

1. Download Patch 35009178 from JDK 8 tab of Supported Java SE Downloads on MOS (Doc ID 1439822.2)

File Name: p35009178_180371_Linux-x86-64.zip

2. Stop Agent

<Agent_Base>/agent_inst/bin/emctl stop agent

3. Go to <AGENT_ORACLE_HOME>/oracle_common/

Example: <Agent_Base>/agent_13.4.0.0.0/oracle_common/

4. Take backup of jdk directory.

$mv jdk jdk_bak

5. Copy p35009178_180371_Linux-x86-64.zip to $AGENT_ORACLE_HOME/oracle_common/

6. Unzip p35009178_180371_Linux-x86-64.zip and check the readme.txt for the instructions to install it.

$tar zxvf jdk-8u371-linux-x64.tar.gz

It will create jdk1.8.0_371 directory. Rename it to jdk.

7. Confirm the version of the JDK by checking:

<AGENT_ORACLE_HOME>/oracle_common/jdk/bin/java -version

8. Start Agent

<Agent_Base>/agent_inst/bin/emctl start agent

 

 

 

 

Ref.

EM 13.4: How to Use the Latest Certified JDK 8 Update with 13.4 Agents (Doc ID 2659208.1)

 

 

3. Java Version in RDBMS Oracle Home.

 

JDK and PERL Patches for Oracle Database Home and Grid Home (Doc ID 2584628.1)

Oracle Database Health Check

#Oracle Database Health Check 

ps -ef | grep pmon

ps -ef | grep tns

!sq

set lines 300;

SELECT DATABASE_ROLE, DB_UNIQUE_NAME, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

archive log list

select flashback_on from v$database;

df -h

cat /etc/*release*

uname -a

prtdiag -v

sar 1 5 

iostat -xnctz 5

SELECT name FROM v$controlfile;

set pagesize 40;

set lines 300;

col MEMBER format a60;

select GROUP#,STATUS,MEMBER from v$logfile;

SELECT name FROM v$controlfile;

SELECT value FROM v$parameter WHERE name='audit_file_dest';

Select owner, object_type, count(*) as Total From dba_objects Where status!= 'VALID' group by owner,object_type Order by owner, object_type;

set lines 300;

set pagesize 120;

select start_time,OUTPUT_BYTES_PER_SEC ,ELAPSED_SECONDS, INPUT_TYPE, STATUS from v$rman_backup_job_details;

set colsep |

set linesize 100 pages 100 trimspool on numwidth 14 

col name format a25

col owner format a15 

col "Used (GB)" format a15

col "Free (GB)" format a15 

col "(Used) %" format a15 

col "Size (M)" format a15 

SELECT d.status "Status", d.tablespace_name "Name", 

 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 

 TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)", 

 TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 

 TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %"

 FROM sys.dba_tablespaces d, 

 (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, 

 (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE 

 d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT 

 (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') 

UNION ALL 

SELECT d.status 

 "Status", d.tablespace_name "Name", 

 TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 

 TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)",

 TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 

 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %" 

 FROM sys.dba_tablespaces d, 

 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, 

 (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t 

 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND 

 d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'; 

 

 

 

#Standby database

ps -ef | grep pmon

ps -ef | grep tns

!sq

set lines 300;

 

SELECT DATABASE_ROLE, DB_UNIQUE_NAME, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

 

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#))

ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN

(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

select instance_name, to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') as startup_time from v$instance;

Oracle Database 19c Maintenance and Monitoring Standard Operating Procedure (SOP)

Oracle Database 19c Maintenance and Monitoring Standard Operating Procedure

Purpose:

Ensure the reliability, performance, and security of the Oracle Database 19c environment.

Scope:

This SOP applies to all Oracle Database 19c instances.

Responsibilities:

- Database Administrators (DBAs)

- System Administrators

Maintenance Tasks:

- Daily:

    - Check database alert logs for errors.

    - Verify backup completion.

    - Monitor system resources (CPU, memory, disk space).

- Weekly:

    - Run database backups (RMAN).

    - Perform database maintenance tasks (e.g., rebuild indexes, update statistics).

    - Review database performance metrics.

- Monthly:

    - Perform operating system updates.

    - Review database security settings.

Monitoring Tasks:

- Database Performance:

    - Monitor CPU utilization.

    - Monitor disk I/O.

    - Monitor memory utilization.

- Database Logs:

    - Monitor alert logs.

    - Monitor trace files.

- Backup Monitoring:

    - Verify backup completion.

    - Verify backup integrity.

Log and Trace Purging:

- ADRCI Commands:

    - adrci exec="SHOW ALERT" to view alert logs.

    - adrci exec="SHOW TRACE" to view trace files.

    - adrci exec="PURGE ALERT" to purge alert logs.

    - adrci exec="PURGE TRACE" to purge trace files.

Backup Script:

```

bash

#!/bin/bash

rman target=/ <<EOF

BACKUP DATABASE;

EOF

```

Schedule:

Daily, weekly, and monthly tasks should be scheduled using a scheduling tool (e.g., cron).

Documentation:

- Database documentation should include:

    - Database configuration.

    - Backup schedules.

    - Maintenance schedules.

    - Performance metrics.