Debugging Procedure using SQL developer

Some days I must using SQL Developer for debugging our procedures. But I face the error ORA-24247: network access denied by access control list (ACL). This error is requested by many people on the internet. That is the reason I decide to write this.

My environment for this article:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Oracle SQL Developer  4.1.0.19

Username: ora41

Database server IP: 192.168.10.200

Client IP(where you are running SQL Developer): 192.168.10.1

Before ora41 user can debug procedures. You must login as dba role and grant privileges:

grant DEBUG CONNECT SESSION to ora41; grant DEBUG ANY PROCEDURE to ora41; BEGIN   DBMS_NETWORK_ACL_ADMIN.append_host_ace (     host       => '192.168.10.1',     lower_port => 40000,     upper_port => 50000,     ace        => xs$ace_type(privilege_list => xs$name_list('connect', 'jdwp'),                               principal_name => 'ora41',                               principal_type => xs_acl.ptype_db)); END; / --review the acl select * from DBA_HOST_ACLS; select * from DBA_HOST_ACES;

After that you must configure SQL Developer on the client to run debug service in a port range as you has granted above. Go to menu Tools --> References…

And then you can open Oracle SQL Developer, connect as ora41 and debug your  procedures.

If your configuration is not correct. You can have the following errors:

Connecting to the database 192.168.10.200 ora41. Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.10.1', '1000' ) ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68 ORA-06512: at line 1 Process exited. Disconnecting from the database 192.168.10.200 ora41.

Bind Variables & Substitution in Oracle

VARIABLE b_salary NUMBER

EXECUTE :b_salary := get_sal(100)

Select client IP of a specific session in Oracle Database

You are an Oracle DBA. The most important questions that you have to answer your self when monitoring your Database: "what is the IP address a session is made from". The following is the solution for you.

--# select v$session for logon_time, username (if the session is living)

SELECT s.status, logon_time, s.sid, s.serial#, s.username, s.osuser,

    p.spid as os_server_process_id, s.port os_client_port, s.machine, p.terminal,

    s.program, s.sql_id, s.prev_sql_id --, s.*

FROM v$session s, v$process p

WHERE s.paddr = p.addr

    and s.username='U1'

    and s.machine='WORKGROUP\HOST-5A81BE'

order by status;

--# If your Server OS is Linux you can stop here by using command netstat -napt. You can using the os_server_process_id return from above SQL as the input for the comment.

# netstat -napt | grep <os_server_process_id>

--# session detail (if the session is living)

select * from v$session where sid=5714 and serial# =48901;

--detail of sql statement

select * from v$sql where sql_id='cyhpma1w9s6v1'

--# select program, module, machine, port column from DBA_HIST_ACTIVE_SESS_HISTORY (if the session is terminated)

select T1.program, T1.module, T1.machine, T1.port ,T1.*

from DBA_HIST_ACTIVE_SESS_HISTORY T1

where session_serial# = 48901 and session_id = 5714

    and user_id=(select user_id from dba_users where username='U1')  ;

--select the IP from COMMENT$TEXT of AUD$ table using PORT number, userid and hostname from above sql

select * from sys.aud$

where userid='U1' and terminal like '%HOST-5A81BE%' and ntimestamp#>trunc(sysdate-1)

    and comment$text like '%PORT=49339%';

RMAN backup command example

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   "/backup/rman/full_devdb_%u_%s_%p" MAXPIECESIZE 2048 M;

RMAN> BACKUP DATABASE;

RMAN> BACKUP ARCHIVELOG ALL;

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

RMAN> BACKUP AS BACKUPSET DATABASE

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;

RMAN> BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

RMAN>  BACKUP FORMAT '/u03/TEST/%d_DB_%T_%u_s%s_p%p' DATABASE PLUS ARCHIVELOG FORMAT '/u03/TEST/%d_ARC_%T_%u_s%s_p%p';

RMAN> BACKUP FORMAT '/u03/TEST/%d_DB_%T_%u_s%s_p%p' DATABASE CURRENT CONTROLFILE FORMAT '/u03/TEST/%d_CTR_%T_%u' SPFILE FORMAT '/u03/TEST/%d_S_%T_%u' PLUS ARCHIVELOG FORMAT '/u03/TEST/%d_ARC_%T_%u_s%s_p%p';

RMAN> BACKUP AS BACKUPSET TABLESPACE PRD01;

RMAN> BACKUP AS BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01;

RMAN> BACKUP AS COMPRESSED BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01;

RMAN> BACKUP DATAFILE '/u01/app/oradata/TEST/users01.dbf';

RMAN> BACKUP DATAFILE 4;

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

RMAN> BACKUP AS COPY DATABASE;

RMAN> BACKUP AS COPY DATAFILE 4 FORMAT '/u01/app/oracle/copy/users01.dbf';

RMAN> CONFIGURE MAXSETSIZE TO 1019M;

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 50M;

RMAN> backup database filesperset 2;

RMAN> backup tablespace sysaux section size 1M;

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK MAXOPENFILES 2 rate 50M;

RMAN> BACKUP AS COPY TAG 'BASE01' INCREMENTAL LEVEL 0 DATABASE;

RMAN> BACKUP TAG 'incr_update' INCREMENTAL LEVEL 1 DATABASE;

RMAN> RECOVER COPY OF DATABASE WITH TAG 'BASE01';

RMAN> BACKUP CURRENT CONTROLFILE;

RMAN> BACKUP SPFILE;

RMAN> LIST BACKUP SUMMARY;

Backup Terminology

Whole database backup: Includes all data files and at least one control file (Remember that all control files in a database are identical.)
Partial database backup: May include zero or more tablespaces and zero or more data files; may or may not include a control file
Full backup: Makes a copy of each data block that contains data and that is within the files being backed up
Incremental backup: Makes a copy of all data blocks that have changed since a previous backup. The Oracle database supports two levels of incremental backup (0 and 1). A level 1 incremental backup can be one of two types: cumulative or differential. A cumulative backup backs up all changes since the last level 0 backup. A differential backup backs up all changes since the last incremental backup (which could be either a level 0 or level 1 backup). Change Tracking with RMAN supports incremental backups.

Default values to current date, month or year on OBIEE 11.1.1.7

To day I show you how to setup the Default value to current date/month/year for prompts on OBIEE 11.1.1.7. A requirement with report/dashboard is that, end user always want to see current date/month/year data when the report/dashboard is opened.
Go into Catalog, open the edit screen for the prompt that you want to setup default value.

In my case, my prompt is Prompt_year_month is used to filter data by Month and Year(Time Dimension). Select the row and click pencial button to open the Edit Prompt window.
To set the current month/year as default value of prompts, you chose “SQL Result” at Default selection. Then enter the select statement that returns the current month/year. Note that “select extract(YEAR from sysdate) from dual” is not a valid SQL statement.
In my demostration, my SQL statement is as below:

Now, how to write the SQL statement. Don’t worry, let’s go.
In the SQL statement you can realize that the table is the Present Layer Name from Repository. And Columns is also from table columns(Time dimension) of the Present Layer Name.
The last one is that the functions. They are not sql functions. They are OBIEE functions. Oh my god, where are they! How to get them! So easy, see below!
In the Edit Column Formula windows you click f(…) button to open Insert Function. All of them are here.

That is all thing you need to write your SQL statement for the “SQL Result Default selection”.

Recovering a deleted datafile using lsof

# rm -f /opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf

# /usr/sbin/lsof | grep dbf
oracle     3545    oracle  261uW     REG     253,10  524296192     491528 /opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf (deleted)
oracle     3547    oracle  264u      REG       253,10  524296192     491528 /opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf (deleted)
oracle     3551    oracle  260u      REG       253,10  524296192     491528 /opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf (deleted)

# ls -l /proc/3545/fd/261
lrwx------ 1 oracle oinstall 64 Jan  4 11:16 /proc/3545/fd/261 -> /opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf (deleted)

# dd if=/proc/3545/fd/261 of=/opt/oracle/LAB/datafile/o1_mf_users_d1mb8zg9_.dbf
1024016+0 records in
1024016+0 records out
524296192 bytes (524 MB) copied, 5.49037 seconds, 95.5 MB/s