Oracle Technology Stack

Tuesday, September 20, 2022

Stop / Kill Datapump Job (EXPDP and IMPDP)

1. Check the datapump job status

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ---------- ---------- ---------- ---------- ---
SYS SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0 0 0

2. Attach the job which need to kill

[oracle@dporadb01 dba]$ expdp \"/ as sysdba\" attach=SYS_EXPORT_FULL_01
Export: Release 12.1.0.2.0 - Production on Wed Mar 23 10:37:01 2022

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

Job: SYS_EXPORT_FULL_01
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: DAD2C6D266D0A795E0530D19020A2BD5
Start Time: Wednesday, 23 March, 2022 10:37:02
Mode: FULL
Instance: finres
Max Parallelism: 4
Timezone: +10:00
Timezone version: 18
Endianness: LITTLE
NLS character set: US7ASCII
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND "/******** AS SYSDBA" full=y directory=DBA_DUMP_DIR d umpfile=expdp_ussprd_20220321_%U.dmp logfile=expdp_ussprd.log parallel=4
State: IDLING
Bytes Processed: 1,466,272,712
Percent Done: 3
Current Parallelism: 4
Job Error Count: 0
Dump File: /slims/exports/dba/expdp_ussprd_20220321_%u.dmp
Dump File: /slims/exports/dba/expdp_ussprd_20220321_01.dmp
bytes written: 2,988,707,840
Dump File: /slims/exports/dba/expdp_ussprd_20220321_02.dmp
bytes written: 139,264

Worker 1 Status:
Instance ID: 1
Instance name: finres
Host name: dporadb01.its.utas.edu.au
Process Name: DW00
State: UNDEFINED
Object Schema: SYS
Object Name: TSDP_POLICY$
Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Completed Objects: 1
Worker Parallelism: 1

Worker 2 Status:
Instance ID: 1
Instance name: finres
Host name: dporadb01.its.utas.edu.au
Process Name: DW01
State: UNDEFINED
Object Schema: SYS
Object Name: AUD$
Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Completed Objects: 1
Total Objects: 41
Completed Rows: 3,212,545
Completed Bytes: 2,044,985,344
Percent Done: 6
Worker Parallelism: 1

Worker 3 Status:
Instance ID: 1
Instance name: finres
Host name: dporadb01.its.utas.edu.au
Process Name: DW02
State: UNDEFINED
Object Schema: K2
Object Name: UNIT_OFFERING_ENROL_20210401
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 7
Total Objects: 3,241
Completed Rows: 2,456,997
Completed Bytes: 288,557,936
Percent Done: 85
Worker Parallelism: 1

3. Check the status

Export> status

Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: IDLING
Bytes Processed: 1,466,272,712
Percent Done: 3
Current Parallelism: 4
Job Error Count: 0
Dump File: /slims/exports/dba/expdp_ussprd_20220321_%u.dmp
Dump File: /slims/exports/dba/expdp_ussprd_20220321_01.dmp
bytes written: 2,988,707,840
Dump File: /slims/exports/dba/expdp_ussprd_20220321_02.dmp
bytes written: 139,264

4. Kill Attached job

Export> kill_JOB
Are you sure you wish to stop this job ([yes]/no): yes

5. Check the datapump job status again to verify

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ---------- ---------- ---------- ---------- ---

Wednesday, September 7, 2022

Silent Installation- Oracle 19c Database

Install Database Software only

Install preinstall package for Oracle 19c

yum install -y oracle-database-preinstall-19c

If you using RHEL8, then retreive RPM's from repository as follows

curl -o oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm

yum -y localinstall oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm

Download oracle 19c database software

https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html

Copy downloaded file to the server and unzip under oracle_home

cd /u01/app/oracle/product/19.0.0/dbhome_1
cp ~/V982063-01.zip .
unzip V982063-01.zip

Backup existing response file

cd /u01/app/oracle/product/19.0.0/db_1/install/response
cp db_install.rsp db_install.rsp_bkp

Modify Response file as below

[oracle@ip-10-82-109-39 ~]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/install/response/db_install.rsp

oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=dba
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
oracle.install.db.rootconfig.executeRootScript=false

To avoid bug '[INS-08101] Unexpected error while executing the action at state: 'supportedOSCheck'' in Oracle 19c on RHEL8

export CV_ASSUME_DISTID=OEL7.8

Install Pre-requisites to check any errors

[oracle@ip-10-82-109-14 dbhome_1]$ ./runInstaller -executePrereqs -silent -responseFile /u01/app/oracle/product/19.0.0/dbhome_1/install/response/db_install.rsp

Install DB software as silent mode

[oracle@ip-10-82-109-14 dbhome_1]$ ./runInstaller -silent -responseFile /u01/app/oracle/product/19.0.0/dbhome_1/install/response/db_install.rsp

Execute root.sh from root user

[root@ip-10-82-109-14 dbhome_1]$ /u01/app/oraInventory/orainstRoot.sh
[root@ip-10-82-109-14 dbhome_1]$ /u01/app/oracle/product/19.0.0/dbhome_1/root.sh

Verify the Installation

Create Database

Modify Response file accordingly

vi /u01/app/oracle/product/19.0.0/dbhome_1/assistants/dbca/dbca.rsp

Modify template file as per the requirement

vi /u01/app/oracle/product/19.0.0/dbhome_1/assistants/dbca/templates/New_Database.dbt

Create Database using response and template (template file use within response file)

cd /u01/app/oracle/product/19.0.0/dbhome_1/assistants/dbca

dbca -createDatabase -silent -responseFile ./dbca.rsp

Configure Listener (listener and tns entries) after the DB creation

cd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin

vi tnsnames.ora

vi listener.ora

Tuesday, August 2, 2022

IMPDP Error - ORA-04031: unable to allocate shared memory

Problem

Following Error Occurred during the datapump import

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
ORA-39097: Data Pump job encountered unexpected error -4031
ORA-39065: unexpected master process exception in MAIN
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","SQLA^b52a5f10","idndef : qcuAllocIdn")
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-04031: unable to allocate 272 bytes of shared memory ("shared pool","SELECT /*+all_rows*/ SYS_XML...","SQLA^896032bf","kccdef: qkxrMemAlloc")
ORA-06512: at "SYS.KUPW$WORKER", line 2144
ORA-06512: at line 2
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","SQLA^b52a5f10","idndef : qcuAllocIdn")

Solution

If the large pool is not large enough, then you get an error ORA-4031 in the large pool. The issue is generated because the dm00 process is running out space in the large pool

1. Increase the Large pool in parameter file

SQL> alter system set large_pool_size=64m scope=spfile;

System altered.

2. Restart database to effect the parameter changes

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 432016112 bytes
Database Buffers 369098752 bytes
Redo Buffers 34816000 bytes
Database mounted.
Database opened.

Reference

DataPump Import (IMPDP) Fails With Errors ORA-39097 ORA-39065 ORA-56937 (Doc ID 1471910.1)

Monday, August 1, 2022

Change DB name using nid utility

1. Mount the Database

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 6442448976 bytes
Fixed Size 8910928 bytes
Variable Size 2030043136 bytes
Database Buffers 4378853376 bytes
Redo Buffers 24641536 bytes
Database mounted.

2. Execute nid utility to with new DB name

[oracle@ofprim ~]$ nid target= / dbname=test

DBNEWID: Release 19.0.0.0.0 - Production on Tue Aug 2 00:17:22 2022

Connected to database PRDDB (DBID=3869830090)

Connected to server version 19.12.0

Control Files in database:
+RECO/TEST_SYD1MQ/CONTROLFILE/current.309.1095468811

Change database ID and database name PRDDB to TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3869830090 to 3502387348
Changing database name from PRDDB to TEST
Control File +RECO/TEST_SYD1MQ/CONTROLFILE/current.309.1095468811 - modified
Datafile +DATA/TEST_SYD1MQ/DATAFILE/system.586.111161509 - dbid changed, wrote new name
Datafile +DATA/TEST_SYD1MQ/DATAFILE/data1.498.111160622 - dbid changed, wrote new name
Datafile +DATA/TEST_SYD1MQ/DATAFILE/indx_large.500.111160622 - dbid changed, wrote new name
Datafile +DATA/TEST_SYD1MQ/DATAFILE/tools.351.111161899 - dbid changed, wrote new name
Datafile +DATA/TEST_SYD1MQ/DATAFILE/undotbs.458.111161936 - dbid changed, wrote new name
Datafile +DATA/TEST_SYD1MQ/DATAFILE/sysaux.448.111161920 - dbid changed, wrote new name
Datafile +DATA/TEST_SYD1MQ/TEMPFILE/temp.490.111162268 - dbid changed, wrote new name
Control File +RECO/TEST_SYD1MQ/CONTROLFILE/current.309.1095468811 - dbid changed, wrote new name
Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 3502387348.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

3. Change db_name from parameter file

[oracle@ofprim ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 2 00:17:58 2022
Version 19.12.0.0.0

Connected to an idle instance.

SQL> startup nomount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 6442448976 bytes
Fixed Size 8910928 bytes
Variable Size 2030043136 bytes
Database Buffers 4378853376 bytes
Redo Buffers 24641536 bytes
SQL> alter system set db_name=testt scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted;
ORACLE instance shut down.

4. Open DB with resetlogs option

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 6442448976 bytes
Fixed Size 8910928 bytes
Variable Size 2030043136 bytes
Database Buffers 4378853376 bytes
Redo Buffers 24641536 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

Tuesday, July 21, 2020

Standby DB Error 'ORA-00318: log 11 of thread 1, expected file size doesn't match' in Alert Log

Problem

Standby Alertlog prompt following error continously

Errors in file /u01/app/oracle/diag/rdbms/prod_stb/PROD/trace/PROD_rfs_56892.trc:
ORA-00341: log 10 of thread 1, wrong log # 11 in header
ORA-00312: online log 10 thread 1: '/u03/oradata/PROD/standby_redo11.log'
2020-07-16T22:32:55.488499+06:00
Errors in file /u01/app/oracle/diag/rdbms/prod_stb/PROD/trace/PROD_rfs_56892.trc:
ORA-00318: log 11 of thread 1, expected file size 102400 doesn't match 4194304
ORA-00312: online log 11 thread 1: '/u03/oradata/PROD/standby_redo12.log'

Solution

Size of standby redolog files should be same as primary online redolog files. Need to change Standby redolog file size same as primary redolog files

1. Check primary redolog file details.

SQL> SELECT thread#, group#, members, sequence#, bytes/1024/1024, archived ,status FROM v$log ORDER BY thread#, group#;

THREAD# GROUP# MEMBERS SEQUENCE# BYTES/1024/1024 ARC STATUS
---------- ---------- ---------- ---------- --------------- --- ----------------
1 1 2 17737 2048 YES INACTIVE
1 2 2 17738 2048 YES INACTIVE
1 3 2 17739 2048 NO CURRENT

2. Check standby redolog group details from Standby Database

SQL> SELECT thread#, group#,bytes/1024/1024,status FROM v$standby_log;

THREAD# GROUP# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
1 11 512 ACTIVE
1 12 512 UNASSIGNED
1 13 512 UNASSIGNED
1 14 512 UNASSIGNED

3. Check standby redolog file details from Standby Database

SQL> SELECT group#, type, member FROM v$logfile WHERE type = 'STANDBY' order by group#;

GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
11 STANDBY /u03/oradata/PROD/standby_redo111.log
11 STANDBY /u03/oradata/PROD/standby_redo112.log
12 STANDBY /u03/oradata/PROD/standby_redo121.log
12 STANDBY /u03/oradata/PROD/standby_redo122.log
13 STANDBY /u03/oradata/PROD/standby_redo131.log
13 STANDBY /u03/oradata/PROD/standby_redo132.log
14 STANDBY /u03/oradata/PROD/standby_redo141.log
14 STANDBY /u03/oradata/PROD/standby_redo142.log

4. Cancel standby synchronization

SQL> alter database recover managed standby database cancel;

Database altered.

5. Drop Exisiting standby redolog groups

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 11;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 12;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 13;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 14;

Database altered.

6. Delete physical files from server

[oracle@prdstb ~]$ cd /u03/oradata/PROD/
[oracle@prdstb PROD]$ ls sta*
standby_redo11.log standby_redo21.log standby_redo31.log
standby_redo12.log standby_redo22.log standby_redo32.log
[oracle@prdstb PROD]$ rm -rf standby_redo*

7. Re-Create standby redolog files with same size as primary

QL> alter database add standby logfile thread 1 group 11 ('/u03/oradata/PROD/standby_redo111.log','/u03/oradata/PROD/standby_redo112.log') size 2048M;

Database altered.

SQL> alter database add standby logfile thread 1 group 12 ('/u03/oradata/PROD/standby_redo121.log','/u03/oradata/PROD/standby_redo122.log') size 2048M;

Database altered.

SQL> alter database add standby logfile thread 1 group 13 ('/u03/oradata/PROD/standby_redo131.log','/u03/oradata/PROD/standby_redo132.log') size 2048M;

Database altered.

SQL> alter database add standby logfile thread 1 group 14 ('/u03/oradata/PROD/standby_redo141.log','/u03/oradata/PROD/standby_redo142.log') size 2048M;

Database altered.

8. Re-start standby synchronization

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

Database altered.

Tuesday, July 7, 2020

OPW-00029: Password complexity failed in oracle 19c

Problem

Error occor while creating password file in oracle 19c

[oracle@oradb dbs]$ orapwd file=orapwPROD password=Welcome123 entries=30

OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.

Solution

by default oracle use 12.2 format for password complexity since DB version is 19c. Two option for overcome this issue

provide password which meet the complexity requirement

[oracle@oradb dbs]$ orapwd file=orapwPROD password=Welcome#123 entries=30

OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.

OR

Create password file for oracle db version 12.1

Check exisitng password file format

[oracle@oradb dbs]$ orapwd describe file=orapwPROD
Password file Description : format=12.2

Delete exisiting password file and Create with 12.1 format

[oracle@oradb dbs]$ rm -rf orapwPROD

[oracle@oradb dbs]$ orapwd file=orapwPROD password=Welcome123 entries=30 format=12

Check password file format again

[oracle@oradb dbs]$ orapwd describe file=orapwPROD
Password file Description : format=12

Friday, June 26, 2020

Oracle DB Upgrade to 18c fails with Error : ORA-04063: SYS.ALERT_QUE has errors

Problem
DB Upgrade from 11.2.0.4 to 18c failed with ORA-04063: SYS.ALERT_QUE has errors

Solution
SYS.ALERT_QUE corrupted in source database. recreate SYS.ALERT_QUE will sort the problem

1. Connect as sysdba and enable restricted mode

sqlplus / as sysdba
SQL> alter system enable restricted session;

2. Drop Existing ALERT_QT tables

exec dbms_aqadm.drop_queue_table('ALERT_QT',true);

3. Re-create tables, sequence, type and queue for server alert

SQL> @$ORACLE_HOME/rdbms/admin/dbmsslrt.sql
SQL> @$ORACLE_HOME/rdbms/admin/catalrt.sql
SQL> @$ORACLE_HOME/rdbms/admin/catmwin.sql
SQL> @$ORACLE_HOME/rdbms/admin/exechae.sql

4. Compile Invalid objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

5. disable restricted mode

SQL> alter system disable restricted session;

6. Retry the DB upgrade

Reference - Upgrade to 12.2 fails with Error : ORA-04063: SYS.ALERT_QUE has errors (Doc ID 2632809.1)