Tag Archives: CDB

Install and Remove APEX from CDB PDB databases Oracle

Install and Remove APEX from CDB PDB databases Oracle

I am getting the following error of APEX while upgrade the PDB database from lower verstion to upper version by unplug and plug.

Error:
ERROR at line 1: ORA-65346: The PDB version is lower and components (APEX) are missing in CDB.

Check the APEX detail with following

select COMP_ID, VERSION, STATUS from CDB_REGISTRY where COMP_ID='APEX' order by CON_ID;

select r.COMP_NAME, r.VERSION, c.NAME, c.CON_ID from CDB_REGISTRY r, V$CONTAINERS c where r.CON_ID=c.CON_ID and r.COMP_ID='APEX' order by CON_ID;

Remove the Apex from CDB
Go to the Oracle home apex directory

cd %ORACLE_HOME%\apex

2. Connect with CDB$ROOT and check all pdb is open state.

sqlplus sys as sysdba
alter pluggable database open all;

3. Remove APEX from the CDB$ROOT and all pluggable database.

sqlplus "sys/syspass as sysdba" @apxremov_con

4. Recompile all invalid objects

@?\rdbms\admin\utlrp.sql
select object_name, status from dba_objects where status='INVALID';

5. Check registry for APEX entry.

select COMP_ID, STATUS from DBA_REGISTRY where COMP_ID='APEX';
No row selected.

Install APEX
1. Go to the Oracle home apex directory

cd %ORACLE_HOME%\apex

2. Connect with CDB$ROOT and check all pdb is open state.

sqlplus sys as sysdba
alter pluggable database open all;

3. Install apex in PDB1 database

Syntex:
@apexins.sql tablespace_apex tablespace_files tablespace_temp images

Example:
alter session set container=PDB1;
@apexins.sql SYSAUX SYSAUX TEMP /i/

4. you can also change password for apex.

SQL> @apxchpwd.sql

5. Create the APEX_LISTENER and APEX_REST_PUBLIC_USER users

SQL> @apex_rest_config.sql

6. Unlock the apex user.

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY Password ACCOUNT UNLOCK;

Advertisements

Downgrading the Oracle Database Non-CDB or CDB

Downgrade the Oracle Database

In Oracle You have option to downgrade the Oracle database. Suppose you upgraded the Oracle database from 11g to 12c database now you want to downgrade.
but if you change the compatibility parameter of your oracle database from 11.2.0 to 12.1.0 then you cannot able to downgrade. only method is logical backup expdp or impdp.

If your database compatibility is same lower than 12.1.0 then you can follows the downgrade steps of the database.

Note:
1. If you are downgrading to Oracle Database 11g Release 2 (11.2.0.2),(11.2.0.3) or (11.2.0.4), then the COMPATIBLE initialization parameter must be set to 11.2.0 or lower.
2. Downgrading to release 11.2.0.1 is not supported.
3. Downgrading to Oracle Database 11g Release 1 (11.1.0.7), then the COMPATIBLE initialization parameter must be set to 11.1.0, its supported.

Following are the steps in three phases
Pre-Downgrade Steps
DOWNGRADE Steps
POSTDOWNGRADE STEPS

Following are the Pre-Downgrade Steps

1. Take a full backup of Oracle Database before downgrading.

Pre-Downgrade Steps for Oracle Database

2. If you are downgrade the CDB or any PDB in oracle 12.1.0.2, you need to apply PSU4 patch. (MOS Note 756671.1)

3. If you are downgrading to Oracle 11.1, took backup of the password of database link.
Note: its better to take the database link in all cases

-- Export database link
expdp system/manager FULL=Y directory=dpump_dir dumpfile=saved_dblinks.dmp INCLUDE=DB_LINK;

-- Import database link
impdp system/manager directory=dpump_dir dumpfile=saved_dblinks.dmp;

4. Disable the Oracle Database vault service if its enabled.

Disable the Oracle Database vault
--Connect with DB owner
sqlplus dv_owner
-- Disable the database vault
EXEC DBMS_MACADM.DISABLE_DV;
--restart the database
shutdown immediate
startup

Enable the database vault
CONNECT dv_owner
EXEC DBMS_MACADM.ENABLE_DV;
--Label security must be enabled of database vault.
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
--Enable label secuirty
EXEC LBACSYS.CONFIGURE_OLS;
EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;
-- Restart the database
shutdown immediate;
startup

5. If your database uses Oracle Label Security

If downgraded to 11.2 or older then run the downgrade script

-- Check its label security is enabled
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
-- if yes then run the following script in case of downgrade to 11.2 or older version.
@ORACLE_HOME/rdbms/admin/olspredowngrade.sql

Note: if you are downgrading release 12.1.0.2 to release 12.1.0.1. then donot do anything

6. If you already enabled unified auditing.

--backup the unified table
CREATE TABLE UA_DATA AS (SELECT * FROM V$UNIFIED_AUDIT_TRAIL);

--clean the table
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);

7. Check the timezone

-- Check the timezone of both the databases. if it matched then fine otherwise you need to copy the time zone from upper to lower version.
select * from V$TIMEZONE_FILE;
-- Copy the time zone files from the current Oracle home to the Orache home to which you are downgrading.
%ORACLE_HOME%\oracore\zoneinfo\timezlrg_18.dat
%ORACLE_HOME%\oracore\zoneinfo\timezone_18.dat

8. If Oracle Enterprise manager is configured. Drop the user SYSMAN

DROP USER sysman CASCADE;

Following are the Downgrading a CDB or Non-CDB Oracle Database Steps

Suppose downgrade the oracle 12c latest version to 12.1.0.1

1. Log in to the system with new oracle home.

2. Go to the directory ORACLE_HOME/rdbms/admin

cd %ORACLE_HOME%\rdbms\admin
sqlplus / as sysdba

3. Start the instance in downgrade mode.

-- For Non CDB
startup downgrade pfile=pfile_name
-- For CDB
startup downgrade pfile=pfile_name
alter pluggable database all open downgrade;

4. Run catdwgrd.sql script for downgrade for CDB or Non CDB database.

-- For Non CDB
SPOOL downgrade.log
SQL> @catdwgrd.sql
spool off

-- For a CDB:
ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d $ORACLE_HOME/rdbms/admin -e -b catdwgrd -l -r catdwgrd.sql

In CDB example catcon.pl is run with catdwgrd.
-d parameter tells catcon.pl where to find catdwgrd.
-l parameter specifies the output directory for log files
-r parameter to run the two scripts together.

5. Shutdown the database after executing catdwgrd.sql script

Shutdown immediate

6. Change the environment variable to old oracle home which you downgraded the database.

ORACLE_HOME
PATH

7. In case of windows system you need to drop the exisiting service and create new from downgraded home of database

-- Stop the ORCL database service
NET STOP OracleServiceORCL
-- Drop the ORCL service
ORADIM -DELETE -SID ORCL
-- Create new ORCL Service
ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS -STARTMODE MANUAL -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

8. Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges.

-- For Non CDB
CONNECT / AS SYSDBA
STARTUP UPGRADE

--For CDB
connect / as sysdba
startup database mount;
alter database open upgrade;
alter pluggable database all open upgrade;

9. Go to ORACLE_HOME/rdbms/admin and connect with database

--For a non-CDB:
SPOOL reload.log
@catrelod.sql
SPOOL off

-- For CDB
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b catrelod -d '''.''' catrelod.sql

10. If you downgrading to Oracle Database 11g Release 1 (11.1.0.7) then you run the @apxrelod.sql script.

@apxrelod.sql

Note: Script used for reload Oracle Application Express.

11. Shut down and restart the instance for normal operation:

SHUTDOWN IMMEDIATE
STARTUP

12. IF Oracle Label Security is configured then copy the script olstrig.sql from upper version oracle home to new downgraded oracle home and run it.

@olstrig.sql

Note: Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies.

13. Run the utlrp.sql script

-- For a non-CDB:
@utlrp.sql;

-- For CDB
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

POSTDOWNGRADE STEPS

1. Recreate the listener from downgraded Oracle home.
Note: Copy paste the network folder from old home to downgrade home.

NETCA

2. If enabled then Re-enabling Oracle Database Vault after Downgrading Oracle Database.

Enable the database vault
CONNECT dv_owner
EXEC DBMS_MACADM.ENABLE_DV;
--Label security must be enabled of database vault.
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
--Enable label secuirty
EXEC LBACSYS.CONFIGURE_OLS;
EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;
-- Restart the database
shutdown immediate;
startup

3. If configured then Restoring Oracle Enterprise Manager after Downgrading Oracle Database.

ORACLE_HOME/bin/emca -restore db