Data Pump, SYS account and non working jobs

Lately I have had an issue with a load framework, which stopped working after moving the whole thing to a test environment. From the beginning it was not clear why it does not work. The reason provided in the DBA_SCHEDULER_JOB_RUN_DETAILS was exactly that the job was stopped because it was terminated, so not really helpful :-).
However, every trace indicated that jobs throw the ORA-28031 exception (maximum of 148 enabled roles exceeded).

The only role with such a number of roles was the SYS account. Apparently a job process during a piece of its lifetime is run on the SYS privileges. So the processes had been started and almost immediately terminated. And this actually explains everything. The only mystery left was why the SYS account get so many roles.
I am not sure of the exact scenario, but:

• we have on these databases the quite numerous quantity of custom roles and exactly those roles were assigned to the SYS account
• the migration was done with the Data Pump tools
• the import was done on the SYS privileges and apparently then all those roles were assigned
• there was no explicit operation of assigning those roles to the SYS schema
• it is possible in the impdp parameters there was added the exclusion of default roles, which are enabled on the SYS by default

The main solution was to revoke all those unnecessary roles. To make the framework work other adjustments were also required, but rather obvious.

The fun with names and PLS-00302

Quite often it turns out that the more mysterious the problem is the more trivial is the solution.
This is a short note about such a situation.

There is a package, which prepares a report. The package has the VALID status, no problems with the compilation or so. However, the call to the procedure ended with throwing the exception PLS-00302 (component zzz must be declared). The call from the owner schema, so not problems with the visibility of the package, the DESCRIBE command works as expected.
To make the long story short I found out that the exception had been risen, when the call was fully qualified with the schema name. If there was no schema specification, the code had been working properly. The reason was the existence of another package with the same name as the schema name, and the problem was with the context resolution - in the first place there was a search within the package and not within the schema namespace.

The workaround is simple - not to call the package prefixed with the schema name. The true solution is clear (though not necessarily simple due to possible dependencies) - to change the package name to different than the schema name and of course to avoid naming the packages with names of schemas.

The listing of file names in a directory through PL/SQL

There is no straightforward way to enlist file names in a file system directory from PL/SQL level. The UTL_FILE package allows for many operations on the files, yet there is no equivalent to command ls. There is, however, a way to do it from within the database engine.

The first idea is to use Java or extproc. Tom Kyte describes such solution on AskTom.
The second idea is to use the DBMS_BACKUP_RESTORE package. This way is a little tricky and may change between different versions of the Oracle RDBMS binaries, so one has to be careful, when using it. As this is quite interesting I place here a short description.

connect / as sysdba
declare
p_dir varchar2(64) := '/oracle';
p_nul varchar2(1024);
begin
sys.dbms_backup_restore.searchFiles(p_dir, p_null);
dbms_output.put_line(p_nul);
for f in (select fname_krbmsft fname from x$krbmsft) loop
dbms_output.put_line(f.fname);
end loop;
end;
/

I run it on the 11.2.0.2 version and the whole thing works as advertised, however:

• does not follow symlinks
• returns all the file names recursively, so enlists also subdirectories' files

Possibly there are other features, I did not spot in the short test.

A very good article on the subject one may find at http://www.morganslibrary.com/hci/hci002.html. The author suggests some security measures, which in this case are obligatory like wrapping the x$krbmsft in a read only view and the searchFiles call in a procedure. Of course this is not only for security - possible changes in the DBMS_BACKUP_RESTORE package may bring an end to this procedure and then it is easier to change the code in custom procedure.

The orphaned mview registration on a mview log

Some time ago we migrated a certain application between 2 databases. This operation was quite long-term and went in few steps. The replication based upon mviews was involved for the transition period. The one of the final steps was a drop of a few schemas on the source database. So far, so good...

After several months we realized that one of the mview logs on a table from the migrated schemas had become quite large despite the fact that the mview based upon it had been refreshed regularly and that was the only mview bound with this mview log.

What is behind this tale?
It is quite easy to find the solution by googling or at least some clues to what happened with our log - for example on the blog by A. Prakesh or the blog by D.Vadas. Finally on the MOS there are a few articles describing the whole mview refresh mechanism (236233.1, 258634.1) and dealing by the way with this particular issue.

In our particular case the scenario is the following. The schemas drop operation on the source database affected also the mviews, which were registered with the mview logs on the destination database. Such situation is one of the few possible, when there is performed no explicit unregistration of the mview. In the catalog of the destination database it is seen after performing the following query:

select s.mowner, s.master, s.snaptime, 'exec dbms_mview.purge_mview_from_log('||s.snapid||');' fix1
from sys.slog$ s 
where not exists(select 1 from dba_registered_snapshots r where s.snapid=r.snapshot_id) 
order by mowner, master;

While there are no more entries in the dba_registered_snapshots view, there still exist entries in the sys.slog$, which indicate the database engine, how much data have to be stored to allow for the fast refresh on the registered mviews. The snaptime value indicates the last fast refresh on the registered mviews. In our case we had 2 entries per mview bound with this issue. One of them was the "healthy" one, which has been refreshed frequently, while the second entry was the one without a corresponding entry in the dba_registered_snapshots view with the snaptime far in the past (which corresponded to the oldest rows in the mview log).

So we found the guilty, what's next?
Here is a time for the fix1 column from the query above. The call to the purge_mview_from_log procedure from the dbms_mview package purges all the entries, which are held for a once-in-time existing mviews, which we implicitly dropped with the migrated schemas. The required argument is the snapid column value from s.slog$.
After the purging operation we are left possibly with an enormously grown segment of the mview log, so we need 2 more operations. One is to enable the row movement on the table storing the mview log entries (the system-create name with the pattern MLOG$_{the master table name}) and the next one is the shrink of course, but called this time on the mview log object and not the table (all the issues with shrinking apply .

alter table MLOG$_{the master table name} enable row movement;
alter materialized view log on {the master table name} shrink space [compact];
alter materialized view log on {the master table name} shrink space; -- if previous command with compact

At least in our case the purge even on small tables enabled high waits on the log sync event (though this was not the case on the test environment), so this is important to do the purge in the period of the low database load - for example at night or for weekend. I suppose the reason behind this is that the purge is done by deleting every entry in the mlog. It seems there is a one commit per purge but at least in our case there were lots of commit cleanouts on the production site.

ORADEBUG session for AQ

SQL> select owner, job_name from dba_scheduler_jobs 
SQL> where lower(job_action) like '%register_driver%'
OWNER                          JOB_NAME                     
------------------------------ ------------------------------
SYS                            AQ$_PLSQL_NTFN21               

SQL> select SESSION_ID, SLAVE_PROCESS_ID, SLAVE_OS_PROCESS_ID 
SQL> from DBA_SCHEDULER_RUNNING_JOBS where job_name='AQ$_PLSQL_NTFN21'
SESSION_ID SLAVE_PROCESS_ID SLAVE_OS_PROCESS_ID
---------- ---------------- -------------------
        16               26 26823               

SQL> select sid, serial#, paddr, program from v$session where sid=16
SID SERIAL# PADDR            PROGRAM                                        
--- ------- ---------------- ------------------------------------------------
 16       1 000000009F67D7A8 oracle@dt-bpss-db-04-2 (J000)                    

SQL> select spid from v$process where addr='000000009F67D7A8'
SPID                   
------------------------
26823                    
-------------------
SQL> oradebug setospid 26823
Oracle pid: 26, Unix process pid: 26823, image: oracle@dt-bpss-db-04-2 (J000)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug Event 10046 trace name context forever, level 12
Statement processed.
SQL> select sysdate from dual;

SYSDATE
-------------------
2013-07-18 13:57:50

SQL> oradebug Event 10046 trace name context off
Statement processed.

The crash with 11.2.0.2.9 binaries

I have run a test database and after short time have been informed it crashed. Today's investigation shows that it is bound with parallel recovery done automatically after/during startup. Here IMHO the excellent article on the subject.

Short way to enable one SQL plan from many

Assuming one has a SQL cursor on a database, for which exist many plans and one wants to get rid of all of them but one here is the quickest way I find to do that:

declare
  n number;
begin
  n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'gzwhs3zc68ju5', plan_hash_value=>156135624, fixed =>'NO', enabled=>'YES');
end;
/

• sql_id is an identifier of a SQL we want to create SQL plan baseline for
• plan_hash_value is a hash value for the plan wa want to use
• fixed is an attribute, which provides some priority for fixed plans over non-fixed and disables automatic SQL tuning to implement new findings at once (findings are stored as non-fixed plan baselines) - I would say this is reasonable to use it, though in the example it is set to NO
• enabled is self-explained