Sunday, October 24, 2010

Grant ACL to APPS account after database upgraded to 11g

After database for E-Buiness Suite is upgraded to 11g, you have to grant ACL privilege to APPS account for some EBS modules to work. Here phebsapp is the EBS Apps server name.

1. Check if view dba_network_acls has any row. If yes, run below script to add privilege to APPS account:

DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = 'phebsapp' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'APPS','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'resolve');
END IF;
EXCEPTION
WHEN no_data_found THEN
-- below lines will create a ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','ACL description', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('apps.xml','APPS', TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/

SQL> commit;

Please note "commit;" is necessary and very important. After that, you should see the entries in two views:

SQL> select * FROM dba_network_acls;
SQL> select * from dba_network_acl_privileges;

If you do not see rows, something is wrong.

And below two queries should return 1:

SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','connect') from dual;

SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','resolve') from dual;

2. if view dba_network_acls has no data. You can create ACL for the user:

SQL> show user
USER is "SYS"

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','APPS acess for UTL', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apps.xml', principal => 'APPS',is_grant => true, privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/

Sometimes, the domain name makes difference. It does not hurt to add full name to the access list:

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp.domain.com');
END;
/

SQL> commit;

3. If things does not work, you can start over by dropping entries in dba_network_acls.

SQL> begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('apps.xml');
commit;
end;
/

Now dba_network_acls, dba_network_acl_privileges, net$_acl should have nothing on apps.xml and everything no ACL is cleaned up.
SQL> select * from dba_network_acls;
SQL> select * from dba_network_acl_privileges;
SQL> select * from net$_acl;

Then, you can start over again.

Friday, October 15, 2010

Use DBMS_SCHEDULER to schedule jobs

In Oracle 10g and 11g, there are two places holding scheduled jobs:

DBA_SCHEDULER_JOBS
DBA_JOBS

Jobs in DBA_SCHEDULER_JOBS are managed by package DBMS_SCHEDULER, while jobs in DBA_JOBS are managed by package DBA_JOBS.

Here is an example on using DBMS_SCHEDULER to schedule a job:

BEGIN
      DBMS_SCHEDULER.CREATE_JOB(
      job_name=>'FIXED_OBJECTS_STATUS',
      job_type=>'PLSQL_BLOCK',
      job_action=>'BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; DBMS_STATS.GATHER_SCHEMA_STATS(''SYS'');END;',
      REPEAT_INTERVAL=>'FREQ=MONTHLY;BYMONTH=1,7;BYDAY=1 SUN;BYHOUR=14;BYMINUTE=16;BYSECOND=0',
      ENABLED=>TRUE
      );
END;
/

After that, you can see the job:

SQL> SELECT owner, job_name, job_class, enabled, next_run_date
FROM dba_scheduler_jobs WHERE job_name ='FIXED_OBJECTS_STATUS';

And, run it and check the log.
View DBA_SCHEDULER_JOB_RUN_DETAILS holds the execution history for jobs in DBA_SCHEDULER_JOBS.
 
SQL> exec DBMS_SCHEDULER.run_job('FIXED_OBJECTS_STATUS');
SQL> select job_name, actual_start_date, status, job_name
FROM dba_scheduler_job_run_details WHERE job_name = 'FIXED_OBJECTS_STATUS' ;


Below line will disable a job:

SQL> exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');


Recently, a database gave errors in alert log:

Errors in file /u01/app/oracle/admin/dw/bdump/dw_j000_31068352.trc:
ORA-12012: error on auto execute of job 8912
ORA-08103: object no longer exists

I ran query to verify it is a scheduled job and to identify the job name:

SQL> select obj# , object_name FROM sys.scheduler$_job , dba_objects
WHERE obj# = object_id and obj# = 8912;

OBJ#  OBJECT_NAME
--------  -----------------
8912 AUTO_SPACE_ADVISOR_JOB

Then I used dbms_scheduler to disable the job.

NOTES: 
When I query DBA_SCHEDULER_JOBS using Sql*plus on the database server, I do not get error. But I get error when using Sql*plus on my Windows PC:

SQL> select * from DBA_SCHEDULER_JOBS;
ERROR:
ORA-01882: timezone region %s not found
no rows selected

Check document 414590.1 indicating the DST package on my PC needs an update.

Thursday, October 7, 2010

RMAN script for duplicating database

I use below script to refresh our Dev database from Production database monthly, by using the Veritas backup of production database on the tape. It worked well.

ORACLE_SID=devebs ## target database name
export ORACLE_SID
export TNS_ADMIN=$ORACLE_HOME/network/admin
ORACLE_USER=oracle
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss'
export NLS_LANG
export NLS_DATE_FORMAT
NB_ORA_CLASS=ebsdb1p ## server name hosting source database PRODEBS
export NB_ORA_CLASS

DATE=`date +"%h%d%y_%H%M"`

rman catalog rman/rman_pwd@rman_db target sys/source_db_pwd@PRODEBS auxiliary / msglog devEBS_refresh.${DATE}.log << EOF
run {
ALLOCATE AUXILIARY CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch01 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch02 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=ebsdb1p';
# Optional: put Temp in /u04 because of the size
SET NEWNAME FOR TEMPFILE 1 to '/u04/oracle/oradata/devebs/temp01.dbf';
SET NEWNAME FOR TEMPFILE 2 to '/u03/oracle/oradata/devebs/temp02.dbf';
duplicate target database to devebs
UNTIL scn = 5960105731382
;
}
EOF


In the script, statement "UNTIL scn = 5960105731382" is important. Without it, the script will NOT restore necessary archive logs for recovering the database and the refresh will fail (as tested in 10.2.0.4) unless you can manually get the archive logs from production server (which is not easy in ASM environment) and then do a manual recovery on the dev server.

In that case, the script only restores the data files from the latest backup to the target. You need to get all archive logs generated during the period when the latest backup started to the latest backup finished.

If you need to duplicate the target database on the same host with a different database SID, similar script can be used. See Document 73912.1 for more details.