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](Best Practices.md) section
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.
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.
-
Using sql*plus or SQL Developer, connect to the database as system or a user with the DBA role.
-
Run:
@create_user.sql-
Enter the username, tablespace, temporary tablespace and password for the new schema.
-
Connect to the database as the newly created user.
-
Follow the steps to install into an existing schema (below).
- 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;- Run:
@logger_install.sql- Once installed, Logger is automatically set to DEBUG level. View the configurations section to modify its settings.
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](Logger API.md#procedure-set_level) to ERROR as the performance hit is exceptionally small.
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 SYSTo uninstall Logger simple run the following script in the schema that Logger was installed in:
@drop_logger.sqlYou may want to install Logger into it's own schema for various reasons. Some of the most common ones are:
- DBA does not want to give
CREATE ANY CONTEXTaccess to your user.- If this is the case, the DBA can then lock the Logger schema after running the grant scripts (below) to prevent any access to the privileged user.
- Restrict Logger to never be able to access your data. Note: Logger does not try to reference any of your data. Some security policies require that 3rd party solutions can not reside in the same schema as your data. This follows the concept that Logger doesn't need to see your data, your schema just needs access to Logger.
Once you have installed Logger into it's own schema they're two additional scripts that need to be run. The first grants the appropriate privileges to your schema and the second will create synonyms in your schema.
Run as the user with Logger installed:
@scripts/grant_logger_to_user.sql <grant_to_username>If you want to restrict access to the "Logger Schema" (since it has CREATE ANY CONTEXT privilege) you can simple lock it as SYSTEM:
alter user <username> account lock;Run as the user that needs to access Logger:
@scripts/create_logger_synonyms.sql <from_username>They're various logger levels. To see the complete list, go to the [Constants](Logger API.md#constants) section in the Logger API.
To enable logging for the entire schema:
exec logger.set_level(logger.g_debug);To disable logging:
exec logger.set_level(logger.g_off);Instead of disabling all logging, setting the level to "ERROR" might be a better approach:
exec logger.set_level(logger.g_error);If you never want logger to run in an environment you can install the NO-OP version.
Logger now supports client specific configuration. For more information and examples view the [Set Logging Level](Logger API.md#set-logging-level) section in the Logger API documentation.
To view the status/configuration of the Logger:
set serveroutput on
exec logger.status
Project Home Page : https://github.com/oraopensource/logger/
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.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 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. |
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;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;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.
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.