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
OR 
[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)

Posted by at No comments:

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

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

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

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

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
---------- ---------- ---------- ---------- ---------- ---
Posted by at No comments:

Wednesday, September 7, 2022

Silent Installation- Oracle 19c Database

    Install Database Software only

    1. Install preinstall package for Oracle 19c
      2. yum install -y oracle-database-preinstall-19c
    2. If you using RHEL8, then retreive RPM's from repository as follows
      3. 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
    3. Download oracle 19c database software
      4. https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html
    4. Copy downloaded file to the server and unzip under oracle_home
      5. cd /u01/app/oracle/product/19.0.0/dbhome_1
cp ~/V982063-01.zip .
unzip V982063-01.zip
    5. Backup existing response file
      6. cd /u01/app/oracle/product/19.0.0/db_1/install/response
cp db_install.rsp db_install.rsp_bkp
    6. Modify Response file as below
      7. [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
    7. To avoid bug '[INS-08101] Unexpected error while executing the action at state: 'supportedOSCheck'' in Oracle 19c on RHEL8
      8. export CV_ASSUME_DISTID=OEL7.8
    8. Install Pre-requisites to check any errors
      9. [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
    9. Install DB software as silent mode
      10. [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
    10. Execute root.sh from root user
      11. [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
    11. Verify the Installation

    Create Database

    1. Modify Response file accordingly
      2. vi /u01/app/oracle/product/19.0.0/dbhome_1/assistants/dbca/dbca.rsp
    2. Modify template file as per the requirement
      3. vi /u01/app/oracle/product/19.0.0/dbhome_1/assistants/dbca/templates/New_Database.dbt
    3. Create Database using response and template (template file use within response file)
      4.  cd /u01/app/oracle/product/19.0.0/dbhome_1/assistants/dbca
       dbca -createDatabase -silent -responseFile ./dbca.rsp
    4. Configure Listener (listener and tns entries) after the DB creation
      5.  cd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin
       vi tnsnames.ora
       vi listener.ora
Posted by at No comments:
Subscribe to: Posts (Atom)