Oracle Technology Stack: 2022

Tuesday, September 20, 2022

Apex Loading Failed - 503 Service Unavailable or 404 Page not Found

Problem

When try to access Apex via URL 'https:/localhost/ords/pdb1' , following error occurred.

404 Not found error with detail - The request could not be mapped to any database

The following errors may be seen in the web server related logs

ORA-01017: invalid username/password; logon denied (Associate with the HTTP-403 error.)

Cause

Either one of DB user APEX_PUBLIC_USER, APEX_LISTENER, APEX_REST_PUBLIC_USER, ORDS_PUBLIC_USER expired due to No non-expiry profile exist for the db user accounts

Cause

1. Connect to the container database follow the steps

SQL> ALTER SESSION SET container = CDB$ROOT;
SQL> select account_status from dba_users where username='ORDS_PUBLIC_USER';
SQL> Alter user ords_public_user identified by 'xxxx';
SQL> Alter user ords_public_user account unlock;

2. Connect to pluggable database and perform following steps

SQL> ALTER SESSION SET container = PDB1;
SQL> Alter user APEX_PUBLIC_USER identified by xxxx;
SQL> Alter user APEX_REST_PUBLIC_USER identified by xxxx;
SQL> Alter user APEX_LISTENER identified by xxxx;
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
SQL> ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK;
SQL> ALTER USER APEX_LISTENER ACCOUNT UNLOCK;

3. Create profile and assign to users from Pluggable database

SQL> ALTER SESSION SET container = PDB1;
SQL> Create profile apex_public_profile limit password_life_time unlimited;
SQL> alter user apex_public_user profile apex_public_profile;
SQL> alter user apex_rest_public_user profile apex_public_profile;
SQL> alter user apex_listener profile apex_public_profile;
SQL> alter user ords_public_user profile apex_public_profile;

4. Restart ORDS

[root@wcsprd ~]# /etc/init.d/ords restart

[root@wcsprd ~]# /u01/app/oracle/product/ords/ords stop
[root@wcsprd ~]# /u01/app/oracle/product/ords/ords start

Reference

APEX Unavailable After the Expiration of the APEX_PUBLIC_USER Password - 503 Service Unavailable or 404 Page not Found (Doc ID 2058706.1)

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.