Skip to content

Installation

Jump to bottom Edit New page
Martin D'Souza edited this page Jul 26, 2013 · 4 revisions

Installation
Configuration
Maintenance #Installation

If you're new to Logger it's recommended you simply install into an existing schema on a development environment to get up and running as quickly as possible. You are encouraged to review the rest of the installation sections after you're more familiar with Logger. Once you are comfortable using Logger it is recommended that you read the Best Practices section

##Important Notes

###Previous Installations Version 2.0.0 build scripts were completely re-written to make it easier for future development. The new build scripts were built off Logger 1.4.0. As such, if your current version is lower than 1.4.0 you need to run the uninstall script for your specific version. If you're currently 1.4.0 or above the installation script will automatically update your current version. The following query will identify your current version.

select pref_value
from logger_prefs
where pref_name = 'LOGGER_VERSION';

To uninstall an older version of logger, see the Uninstall instructions. If necessary, you can download the correct version from the releases folder.

###Install Through APEX Logger is no longer supported from a web-only installation if the schema was provisioned by APEX. Essentially the APEX team removed the "create any context" privilege when provisioning a new workspace, likely for security reasons. I agree with their choice, it unfortunately impacts logger.

##Install into a new schema

  1. Using sql*plus or SQL Developer, connect to the database as system or a user with the DBA role.

  2. Run:

@create_user.sql

  1. Enter the username, tablespace, temporary tablespace and password for the new schema.

  2. Connect to the database as the newly created user.

  3. Follow the steps to install into an existing schema (below).

##Install into an existing schema:

  1. If possible, connect as a privileged user and issue the following grants to your "existing_user":
grant connect,create view, create job, create table, create sequence,
create trigger, create procedure, create any context to existing_user;
  1. Run:
@logger_install.sql
  1. Once installed, Logger is automatically set to DEBUG level. View the configurations section to modify its settings.

##NO-OP Option for Production Environments To make sure there is no fear of leaving debug statements in production code, Logger comes with a NO-OP (No Operation) installation file (logger_no_op.sql). This installs only a shell of the Logger package. All procedures are essentially NO-OPs. It does not even create the tables so there is absolutely no chance it is doing any logging. It is recommended that you leave the full version installed and simply set the Logger level to ERROR as the performance hit is exceptionally small.

##Objects The following database objects are installed with Logger:

OBJECT_TYPE         OBJECT_NAME
------------------- ------------------------------
JOB                 LOGGER_PURGE_JOB
                    LOGGER_UNSET_PREFS_BY_CLIENT
PACKAGE             LOGGER
PROCEDURE           LOGGER_CONFIGURE
SEQUENCE            LOGGER_APX_ITEMS_SEQ
                    LOGGER_LOGS_SEQ
TABLE               LOGGER_LOGS
                    LOGGER_LOGS_APEX_ITEMS
                    LOGGER_PREFS
                    LOGGER_PREFS_BY_CLIENT_ID
VIEW                LOGGER_LOGS_5_MIN
                    LOGGER_LOGS_60_MIN
                    LOGGER_LOGS_TERSE
LOGGER_GLOBAL_CTX   CONTEXT -- Global Application Contexts are owned by SYS

##Uninstall To uninstall Logger simple run the following script in the schema that Logger was installed in:

@drop_logger.sql

#Configuration

###Logger Levels They're various logger levels. To see the complete list, go to the Constants section in the Logger API.

###Enable To enable logging for the entire schema:

exec logger.set_level('DEBUG');

###Disable To disable logging:

exec logger.set_level('OFF');

Instead of disabling all logging, setting the level to "ERROR" might be a better approach:

exec logger.set_level('ERROR');

If you never want logger to run in an environment you can install the NO-OP version.

###Client Specific Configuration Logger now supports client specific configuration. For more information and examples view the Set Logging Level section in the Logger API documentation.

###Status To view the status/configuration of the Logger:

set serveroutput on
exec logger.status

Project Home Page	 	 : https://github.com/tmuth/Logger---A-PL-SQL-Logging-Utility/
Logger Version		 	 : 2.0.0.a01
Debug Level		  	 	 : DEBUG
Capture Call Stack	 	 : TRUE
Protect Admin Procedures : TRUE
APEX Tracing		 	 : Disabled
SCN Capture		 		 : Disabled
Min. Purge Level	 	 : DEBUG
Purge Older Than	 	 : 7 days
Pref by client_id expire : 12 hours
For all client info see  : logger_prefs_by_client_id

PL/SQL procedure successfully completed.

###Preferences Logger stores its configuration settings in LOGGER_PREFS. These are the following preferences:

Preference Description
GLOBAL_CONTEXT_NAME Context that Logger uses to save values. It is not recommended to modify this setting.
INCLUDE_CALL_STACK Store the call stack. Note [client specific settings](Logger-API#wiki-set-logging-level) can override this.
INSTALL_SCHEMA Schema that Logger is installed in. Do not modify.
LEVEL The current schema Logger level.
LOGGER_VERSION Current version of Logger. Do no modify this as it may affect future migrations.
PREF_BY_CLIENT_ID_EXPIRE_HOURS Default time (in hours) that client specific logging levels are set for.
PROTECT_ADMIN_PROCS If TRUE then only user, defined in INSTALL_SCHEMA, can run privilidged procedures.
PURGE_AFTER_DAYS Purge logs, equal to or higher than PURGE_MIN_LEVEL, after this many days. A purge job is run each night to clean up logger.
PURGE_MIN_LEVEL Min level to purge logs used in auto Logger cleanup job.

###Other Options

Once you perform the following described steps for the Flashback or APEX option, simply run the logger_configure procedure, then run logger.status to check validate your changes.

exec logger_configure;
exec logger.status;

####Flashback To enable this option, grant execute on dbms_flashback to the user that owns the logger packages. Every insert into logger_logs will include the SCN (System Commit Number). This allows you to flashback a session to the time when the error occurred to help debug it or even undo any data corruption. As SYS from sql*plus:

grant execute on dbms_flashback to logger;

####APEX This option allows you to call logger.log_apex_items which grabs the names and values of all APEX items from the current session and stores them in the logger_logs_apex_items table. This is extremely useful in debugging APEX issues. This option is enabled automatically by logger_configure if APEX is installed in the database.

#Maintenance

By default, the DBMS_SCHEDULER job "LOGGER_PURGE_JOB" runs every night at 1:00am and deletes any logs older than 7 days that are of error level g_debug or higher which includes g_debug and g_timing. This means logs with any lower level such as g_error or g_permanent will never be purged. You can also manually purge all logs using logger.purge_all, but this will not delete logs of error level g_permanent.

Starting in 2.0.0 a new job was LOGGER_UNSET_PREFS_BY_CLIENT introduced to remove client specific logging. By default this job is run every hour on the hour.

Add a custom footer
Add a custom sidebar
Clone this wiki locally