Using msdb.dbo.sp_update_job one can disable or enable jobs, the job_id can be retrieved from msdb.dbo.sysjobs
To enable
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 1
To disable
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
———————————— ———– ——————————
sec_case_sensitive_logon boolean TRUE
SQL>
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
System altered.
Using the package DBMS_SCHEDULER one can enable/disable jobs.
To disable job: This disables the job from running
SQL> exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);
PL/SQL procedure successfully completed.
– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;
JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB FALSE
To enable job:
SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);
PL/SQL procedure successfully completed.
– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;
JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB TRUE
Using Oracle SID and SERIAL# can turn trace on/off a Oracle session that has already started. The values of SID and serial# can be obtained from GV$SESSION. This will create trace file in directory set by the parameter user_dump_dest. To find the SID and SERIAL# you want to debug you can query GV$session to query by program, username, machine, terminal.
To start trace:
SQL> exec dbms_support.start_trace_in_session(<SID>, <Serial#>);
To stop trace:
SQL> exec dbms_support.stop_trace_in_session(<SID>, <Serial#>);
To start trace with Wait Event data with SQL trace
exec sys.dbms_support.start_trace(<SID>, <Serial#>,waits => TRUE, binds=> TRUE);
If one needs to turn timed statistics on for another session, one can execute it through the package dbms_system.
SQL> exec sys.dbms_system.set_bool_param_in_session(sid => <sid>, serial# => <seral#>, parnam => ‘TIMED_STATISTICS’, bval => true);
To install the DBMS_SUPPORT package if it doesn’t exists in the database run the following script to create/install the package and setup access for other users other than sysdba.
SQL> connect / AS SYSDBA
SQL> @?/rdbms/admin/dbmssupp.sql
SQL> GRANT execute ON dbms_support TO SCOTT_DBA;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
In 10g there is a new utility DBMS_MONITOR which you can use to achieve the, eg:
exec dbms_monitor.session_trace_enable (session_id=>139, serial_num=>53, waits=>true, binds=>false); For additional information you can the following article by Oracle http://www.oracle.com/technology/oramag/oracle/04-sep/o54talking.html
One can also turn trace on an already running session using oradebug, once session id (SID) has been identified.
SQL> select p.PID, p.SPID, s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = 62;
PID SPID SID
———- ———— ———-
68 5243378 62
1 row selected.
SQL> connect /as sysdba
– attach to session by OS process id
SQL> oradebug setospid 5243378;
Oracle pid: 68, Unix process pid: 5243378, image: oracle@hostname (TNS V1-V3)
– sets maximum dump file size to be unlimited
SQL> oradebug unlimit
Statement processed.
– turn trace on
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
– after gathering information from the same session which is already attached to ospid 5243378 turn off the trace
SQL> oradebug event 10046 trace name context off
Statement processed.
The Folowing are the requirments for enabling Flashback Database:
- Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
- You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
- For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.
To enable logging for Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. Follow the process outlined here:
Start SQL*Plus and ensure that the database is mounted, but not open. For example:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
By default DB_FLASHBACK_RETENTION_TARGET is set to one day (1440 minutes).
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=536870912;
Enable the Flashback Database feature for the whole database:
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
SQL> SELECT flashback_on FROM v$database;
FLASHBACK_ON
——————
YES
Most of the Oracle production database require you to enable ARCHIVELOG mode for your database. When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system. Archive logs are utilized by RMAN, Data Guard, Flashback and many others.
Enabling archive mode is simple, just connect to your database in mounted but closed mode (startup mount) and alter the database. But if you don’t tune alittle you’ll run into problems down the road, so lets specify some parameters too. Namely, consider LOG_ARCHIVE_DEST.
Lets start by checking the current archive mode.
[oracle@calora01]$sqlplus “/as sysdba”
SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
————
NOARCHIVELOG
So we’re in NOARCHIVELOG mode and we need to change.
We can use a database alter statement.
SQL>create spfile from pfile;
SQL>shutdown immediate;
SQL>startup;
SQL>alter system set log_archive_dest_1=’location=/u02/oradata/ATT/arch’ scope=spfile;
SQL>alter system set log_archive_start=TRUE scope=spfile;
SQL>shutdown immediate;
Remember, if you run out of space in your archive log destination the database will shut down!
Now we can startup the database in mount mode and put it in archivelog mode.
[oracle@calora01]$sqlplus “/as sysdba”
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
SQL>alter database mount;
Database mounted
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
You can see here that we put the database in ARCHIVELOG mode by using the SQL statement “alter database archivelog”, but Oracle won’t let us do this unless the instance is mounted but not open. To make the change we shutdown the instance, and then startup the instance again but this time with the “mount” option which will mount the instance but not open it. Then we can enable ARCHIVELOG mode and open the database fully with the “alter database open” statement.
There are several system views that can provide us with information reguarding archives, such as:
V$DATABASE
Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.
V$ARCHIVED_LOG
Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST
Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES
Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG
Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG
Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range for each archived log.
Using these tables we can verify that we are infact in ARCHIVELOG mode:
SQL> select log_mode from v$database;
LOG_MODE
————
ARCHIVELOG