What happened so far on my Journey to the Cloud?
- Part I – Push a Button (Dec 3, 2015)
- You are here! ==> Part II – Switch On/Off and Remove (Dec 4, 2015)
- Part III – Patch, patch, patch (Dec 22, 2015)
- Part IV – Clean Up APEX (Jan 19, 2016)
- Part V – TDE is wonderful (Jan 28, 2016)
- Part VI – Patching does not work (Apr 18, 2016)
- Part VII – APEX is in CDB$ROOT again (Dec 21, 2016)
Day 2 of my Cloud exploration journey.
In my blog posts about my DBaaS Cloud journey I won’t explain a lot about how to login to the Oracle Cloud or how to setup a simple database as others have done this already including the SSH key topic:
- Generating an SSH Key Pair on Windows using PuTTYgen
(Oracle Documentation)- One important thing to mention regarding the PRIVATE KEY:
The public key will be placed in /home/opc/.ssh/authorized_keys but
that is just the public key.
Only the customer has the private key and
if loose it they will have to delete and recreate the instance.
- One important thing to mention regarding the PRIVATE KEY:
- Connection to a Database Cloud Service (DBaaS) Instance through an SSH Tunnel
(Oracle Tutorial) - Oracle Cloud – Database as a Service (DBaaS) – Create a Service
(by Tim Hall – Oracle Base) - Creating an Oracle Cloud Service
(by Rene Antunez – Oracle ACE - Oracle Cloud – Database as a Service (DBaaS) – Patch Service
(by Tim Hall – Oracle Base)
But let me show you some findings I’ve had when logging in via SSH to my DBaaS environment. In the following article I will describe how to:
- Switch off Flashback Database
- Clean up expired Backups and Archivelogs
- Clean up traces and set the retention policy
- Remove APEX from the CDB$ROOT
Flashback Database is ON by default
After playing around with my DBaaS environment for a few days all got stuck. The database hung. A quick look into the alert.log revealed the simple reason: my database had no space anymore on the Flash Recovery Area mount point. “Why that?” I did ask myself.
Well,
SQL> select FLASHBACK_ON from V$DATABASE; YES
told me the answer. By default FLASHBACK DATABASE is ON. Great idea but for playground-mode you may or may not switch it OFF. Furthermore in my (developers) environment the logs will be written to /u03/app/oracle/flash_recovery_area/flashback (DB_RECOVERY_FILE_DEST) – and DB_RECOVERY_FILE_DEST_SIZE is defined at only 7.5GB. That got consumed quickly.
You may switch it OFF if you don’t need FLASHBACK DATABASE at the moment. Or check at least increase the DB_RECOVERY_FILE_DEST_SIZE.
SQL> alter database flashback off; SQL> exit $ rm /u03/app/oracle/flash_recovery_area/flashback/*
And just for the records:
You don’t need to have FLASHBACK DATABASE switched ON as a feature in order to use GUARANTEED RESTORE points (which is one of the best things – I used it almost daily before I “destroy” something in my databases).
Quick check:
SQL> select FLASHBACK_ON from V$DATABASE; NO SQL> create restore point DESTROY_ME guarantee flashback database;
If you’d check now:
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ RESTORE POINT ONLY
Don’t forget to drop your GUARANTEED RESTORE POINT later on.
SQL> drop restore point DESTROY_ME;
Clean up your expired BACKUPS and ARCHIVE LOGS
Every database should be run in archivelog mode – no doubt. But if you are short on disk space for the archive logs you may need to clean up from time to time. Furthermore archivelog mode is necessary in order to support certain actions in Oracle Multitenant.
Keep in mind:
Backups and Archivelogs don’t clean up themselves.
Clean up expired backups:
$ rman target / RMAN> crosscheck backup; RMAN> delete noprompt obsolete; RMAN> delete expired backup;
Clean up archivelogs:
$ rman target / RMAN> crosscheck archivelog all; RMAN> delete noprompt expired archivelog all;
Clean up Trace and Log Files and set the Retention
The default retention for traces has been set to standard values in the cloud as well. You may change this to get a more frequent purging of traces. The interface to tweak settings is ADRCI. Find a nice ADRCI Survival Guide for DBAs here written by my colleague Uwe Hesse,
adrci> show control
To solve DIA-48448 you’ll have to set the correct diag directory you want to look at:
adrci> show homes ADR Homes: diag/rdbms/mike/MIKE diag/tnslsnr/slc01qnv/listener diag/tnslsnr/MIKEVM/listener adrci> set home diag/rdbms/mike/MIKE adrci> show control ADR Home = /u01/app/oracle/diag/rdbms/mike/MIKE: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 4162796853 720 8760 2015-09-17 13:51:04.699486 +00:00 2015-11-30 17:53:33.062134 +00:00 1 2 82 1 2015-09-17 13:51:04.699486 +00:00 1 rows fetched
This means SHORTP_POLICY and LONGP_POLICY are set to 30 days and 365 days (~1 year).
I’d usually change it (that is pretty up to your personal preferences) and purge whatever is older than a few days for a start.
adrci> set control (SHORTP_POLICY=96, LONGP_POLICY=720)
switching the short term policy to 4 days, the long term policy (alert.log, incidents) to 30 days. And finally cleaning up also older traces for a fresh start:
adrci> purge -age 24
This tool a while (~ 10 minutes) in my environment.
Remove Oracle Application Express (APEX) from the CDB$ROOT
As I wrote a few weeks ago Oracle APEX is a fantastic – and unfortunately still the most undervalued – Oracle tool. The only real treatment you should do to your Cloud database first is removing APEX from the CDB$ROOT before either plugging something in or creating pluggable database. If you have created pluggable databases (PDBs) already please make sure that APEX is not used in any of them as removing APEX from the CDB$ROOT will remove it from all existing PDBs as well.
See: https://blogs.oracle.com/UPGRADE/entry/apex_in_pdb_does_not
on Why and How to remove Oracle APEX from the CDB$ROOT.
In short:
- Make sure to change into the local $ORACLE_HOME/apex directory first before starting SQL*Plus:
cd $ORACLE_HOME/apex - Connect to your CDB$ROOT:
sqlplus / as sysdba - Run the “Remove APEX from the CDB$ROOT” script:
SQL> @apxremov_con.sql
- Recompile to get rid of any invalid objects left over by the removal script:
SQL> @?/rdbms/admin/utlrp.sql
That sounds simple, doesn’t it?
Well, but my journey ends here for today as I’m getting 28 invalid objects (which don’t compile) afterwards – and plenty of nice ORA-600 errors in the alert.log looking fantastically similar to Data Dictionary corruptions. I think my Part 3 of the journey will be a patching cycle.
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [1], [4], [71210] ORA-00600: internal error code, arguments: [6711], [4228381], [1], [4228380], [3]
A look into the list of INVALID objects give me this result – from the Cloud documentation I see that C##PDBMGR is a self-provisioning user env:
The Oracle Pluggable Database Self-Service Provisioning application provides an interface to Oracle Database 12c Multitenant option and allows for the provisioning of Pluggable Databases (PDBs). You can perform PDB operations including create, clone, open/close, plug/unplug, and drop. Additionally, you can grant others access to the application, giving them rights to create and manage their own PDBs within the quota limits you set.
So maybe it is not a good idea to remove APEX from the CDB$ROOT in our Oracle DBaaS Cloud environment? I will find out next week …
SQL> column object_type heading "Type" Format a15 SQL> column object_name heading "Name" format a30 SQL> column owner heading "Owner" format a20 SQL> set pagesize 100 SQL> select owner, object_type, object_name from dba_objects where status='INVALID'; Owner Type Name -------------------- --------------- ------------------------------ SYS PACKAGE BODY WWV_DBMS_SQL FLOWS_FILES TRIGGER WWV_BIU_FLOW_FILE_OBJECTS APEX_040200 TRIGGER APEX$_WS_ROWS_T1 C##DBAAS_MONITOR FUNCTION DBM_IS_CDB C##PDBMGR PACKAGE BODY PDBSS_PDB_MGR C##PDBMGR TRIGGER PDBSS_APPLICATION_LOG_BI C##PDBMGR TRIGGER PDBSS_ERROR_LOG_BI C##PDBMGR TRIGGER PDBSS_PREFERENCES_BIU C##PDBMGR TRIGGER PDBSS_RESERVED_DB_NAMES_BIU C##PDBMGR TRIGGER PDBSS_DB_SIZES_BIU C##PDBMGR TRIGGER PDBSS_RESOURCE_PLANS_BIU C##PDBMGR TRIGGER PDBSS_USERS_BIU C##PDBMGR TRIGGER PDBSS_USER_LOG_BI C##PDBMGR TRIGGER PDBSS_ACCESS_REQUESTS_BIU C##PDBMGR TRIGGER PDBSS_DATABASES_BIU C##PDBMGR TRIGGER PDBSS_DATABASE_LOG_BI C##PDBMGR TRIGGER PDBSS_DATABASE_ADMINS_BIU C##PDBMGR TRIGGER BIU_PDBSS_EMAIL_TEMP_TYPES C##PDBMGR TRIGGER BIU_PDBSS_EMAIL_TEMPLATES C##PDBMGR PACKAGE PDBSS_FILTER_FW C##PDBMGR PACKAGE PDBSS_FW C##PDBMGR TRIGGER PDBSS_HISTORY_BIU C##PDBMGR TRIGGER PDBSS_NOTIF_BIU C##PDBMGR TRIGGER PDBSS_TZ_PREF_BIU C##PDBMGR PACKAGE BODY PDBSS C##PDBMGR PACKAGE BODY PDBSS_FILTER_FW C##PDBMGR PACKAGE BODY PDBSS_FW C##PDBMGR PACKAGE BODY PDBSS_EMAIL_API 28 rows selected.
I certainly can drop the user C##PDBMGR – but removing the Database Monitor user C##DBAAS_MONITOR may cause some trouble as the DBaaS Monitor won’t work anymore. And furthermore still three APEX objects remain INVALID.
I’ll update you as soon as I have my environment patched and refreshed sometime next week after UKOUG Conference.
–Mike
6 Responses
[…] Part II – Switch On/Off and Remove (Dec 4, 2015) […]
[…] Part II – Switch On/Off and Remove (Dec 4, 2015) […]
[…] Part II – Switch On/Off and Remove (Dec 4, 2015) […]
[…] Part II – Switch On/Off and Remove (Dec 4, 2015) […]
[…] Part II – Switch On/Off and Remove (Dec 4, 2015) […]
[…] Part II – Switch On/Off and Remove (Dec 4, 2015) […]