EBS Configurator version

The Configurator version in 11i can be obtained using the following URL:
http://hostname:portnum/configurator/oracle.apps.cz.servlet.UiServlet?test=version

the values "hostname:portnum" can be found in "BOM:Configurator URL of UI Manager" profile option.

Reference: Note 419300.1

For EBS R12, the URL is

http://hostname:portnum/OA_HTML/configurator/UiServlet?test=version

List of Scheduled Concurrent Requests

One of concurrent programs stopped to run. I am not sure if it is not scheduled to run any more or it is just cancelled temporarily.

Below code (Note 170107.1) helps me to get the answer. There is another Note (602162.1) on the same subject.

select b.concurrent_program_name, a.*
from apps.fnd_concurrent_requests a , apps.fnd_concurrent_programs b
where a.concurrent_program_id = b.concurrent_program_id
and status_code in ('Q','I')
and requested_start_date > SYSDATE
and hold_flag = 'N'
order by b.concurrent_program_name;
-- 'I' indicates a status of Normal

Change EBS 11i password

In Oracle E-business Suite, the commands for changing APPS passwors and for changing SYSADMIN password are different. It is very important to use the right syntax when making the change.

Make sure you can log onto the database as APPS and SYSTEM by using Sql*plus on the apps server BEFORE you run below lines.

- To change APPS password
$FND_TOP/bin/FNDCPASS apps/old_apps_pwd 0 Y system/system_pwd SYSTEM APPLSYS new_apps_pwd

- To change SYSADMIN password
$FND_TOP/bin/FNDCPASS apps/current_apps_pwd 0 Y system/system_pwd USER SYSADMIN new_sysadmin_pwd

- to change schema owner password
FNDCPASS apps/current_apps_pwd 0 Y system/system_pwd ORACLE OE new_oe_pwd

UPDATE on R12:

Below statement by APPS will change SYSADMIN password:
SQL> select fnd_web_sec.change_password('sysadmin','newPwd4U') from dual;

Belo line to check if a user's password is right or not:
SQL> select fnd_web_sec.validate_login('ebs_userID', 'userPWD') from dual;
FND_WEB_SEC.VALIDATE_LOGIN('EBS_USERID','USERPWD')
--------------------------------------------------------------------------------
Y

Enable remote SYSDBA login

UNIX OS user who is in the same Group as the user who installed Oracle on the server can login as SYS from the server locally. The key to log onto Oracle as SYS from a remote client is to set init.ora parameter REMOTE_LOGIN_PASSWORDFILE not equal to NONE (the default?).

The steps to make it work:

Step 1: Set the init parameter, and then bounce the database:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

EXCLUSIVE forces the password file to be tied exclusively to a single instance. In 10gR2 release, the value EXCLUSIVE is supported for backward compatibility and now has the same behavior as the value SHARED (Reference B14237-04).

Step 2: Create a passwor file:
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=xxxxx

ORAPWD has an option "entries=", which is not mandatory. I do not know what is the default number. In most cases, just do not use this option.

Step 3: Verify below view returns at least one row (username SYS):
SQL> select * from v$pwfile_users;

If yes, then everything works and the new passwordfile is in use by the username on the list. Now, SYSDBA can logon to the database from a remote client.

Additional notes:
1. If "grant sysdba to user_A;" is run by SYS, the user_A will show up in view v$pwfile_users. And user_A can login as SYSDBA useing its own password (not the one in passwordfile).
2. Without Step 1, Step 2 really does not do anything.
3. The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect as SYSDBA or SYSOPER from a remote machine. This parameter was deprecated in 11g and should not be used, it is for 'normal' users that use OS authentication (Note 50507.1).
4. If REMOTE_OS_AUTHENT is set to FALSE, OS_AUTHENT_PREFIX does not take any effect.

datapump export / import tables

Scripts use datapump to export and import data on table level.

~~~~~~~~~~~~~~~~~ export data from source db ~~~~~~~~~~~~~~~~
-- exp_init.par to export data with filters on tables (for Golden Gate initial load)
DIRECTORY=EXP_DIR
DUMPFILE=exp_init.dmp
LOGFILE=exp_init.log
PARALLEL=8
EXCLUDE=INDEX
EXCLUDE=CONSTRAINT
EXCLUDE=GRANT
EXCLUDE=TRIGGER
EXCLUDE=SYNONYM
TABLES=BUS_UNIT,BUS_UNIT_COST,CLAIM
QUERY=BUS_UNIT_COST:"WHERE BE_ID <> 250681 and BE_ID <> 251013",
   CLAIM:"WHERE BE_ID <> 250681 and BE_ID <> 251013"

-- Shell script to call the .par file. Use "nohup" to run it.
export ORACLE_SID=SOURCEDB
PWD=`/path/XXXX`
$ORACLE_HOME/bin/expdp userID/$PWD parfile=exp_init.par

~~~~~~~~~~~~~~~~~~~~ import ~~~~~~~~~~~~~~~~~~~~~
-- optional: drop indexes first (and disable triggers if any)
SQL> alter table userid.CLAIM drop PRIMARY KEY drop INDEX;
SQL> drop INDEX userid.clm_INDX1;

-- imp_init.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp_init.dmp
LOGFILE=imp_init.log
PARALLEL=8
TABLE_EXISTS_ACTION=TRUNCATE
TABLES=BUS_UNIT,BUS_UNIT_COST,CLAIM

-- Shell script for importing
export ORACLE_SID=TARGETDB
echo $ORACLE_SID
PWD=`/path/XXXX`
$ORACLE_HOME/bin/impdp userID/${PWD} parfile=imp_init.par

-- after import, re-create indexes, such as
SQL> CREATE UNIQUE INDEX userid.CLM_PK ON userid.CLAIM
  (CLM_ID, BE_ID)
  parallel 4 nologging
  TABLESPACE CLM_INDEX01;
SQL> ALTER TABLE userid.CLAIM ADD (
  CONSTRAINT CLM_PK
  PRIMARY KEY (CLM_ID, BE_ID)
  USING INDEX userid.CLM_PK
  ENABLE VALIDATE);

SQL> grant select on userid.CLAIM to READONLY_RO;
... ... ...

How to find conflicting patches with opatch

You can use OPatch from the $ORACLE_HOME/OPatch folder (%ORACLE_HOME%/OPatch for Windows), to find out if installing a certain patch will conflict or not. The steps are as follows:

1. Download the patch which you intend to apply

2. Unzip the patch file

3. Change to the directory where the patch has been downloaded

4. Run the following command:

> opatch query -all

This will simulate the patch application procedure and you will find out if it is going to conflict with any of the existing patches.

You can read on more such OPatch options from the ../OPatch/docs/Users_Guide.txt file.

Note 459556.1

307706.1 (How To Determine Patch Is Superset/Subset Patch)

Install standalone Discoverer for 11i

There are several installations involved to make Discoverer work with Oracle EBS 11i. Here are the steps I went through on a SuSe 10 server.

1. OS level
$ cat /etc/issue
Welcome to SUSE Linux Enterprise Server 10 SP2 (i586) - Kernel \r (\l).
$ uname -a
Linux ebsapp1d 2.6.16.60-0.21-bigsmp #1 SMP Tue May 6 12:41:02 UTC 2008 i686 i686 i386 GNU/Linux

2. Download the right CDs from Oracle.
The Oracle 10.1.2.0.2 BI standalond is in two Zip files (for Linux). It does not install the Applications Server (AS) 10g Infrastructure tier!

3. Read the "Oracle BI Installation Guide" coming with the installation Zip files.
Make sure rpm OPENMOTIF release meets the requirement. These worked well on my Prod server:
$ rpm -aq | grep openmotif
openmotif21-libs-2.1.30MLI4-143.9
openmotif-libs-2.2.4-21.12

If it is not, Reports service will have issues (see Note 762394.1). I had to follow Note 726970.1 to stop growing 200+ Reports sessions on OS level on my Dev server.

4. Install Oracle Buiness Intelligence 10g (10.1.2.0.2)
During the installation, two warnings pop up. In the firsst one, go to the command line and manually run "opmnctl startall". In the second one, apply patch 6690831.

This installation puts Discoverer in version 10.1.2.48.18

5. Install 10gAS Upgrade (patch 5983622) to upgrade BI standalone from 10.1.2.0.2 to 10.1.2.3
This is reqiured to make Discoverer work on SuSe 10. It upgrades Discoverer to 10.1.2.55.26

6. Apply patch 7595032 (Discoverer CP4).
Discoverer will not work with EBS 11i without this CP. (I hit issue described in Note 761287.1). Before patch 7595032 installation worked, patche 680880 (for opatch) and patch 4398431(for JDBC) were applied.

Create and fast refresh materialized view

In 11G, I used below statements to create a materialized view and refresh it.

WHENEVER SQLERROR CONTINUE
drop materialized view erp.db1_CLNCL_INDEX;
--
-- create MVIEW basing on a remote table
--
create materialized view erp.db1_CLNCL_INDEX
tablespace mat_view_data01
build deferred
using index tablespace mat_view_indx01
refresh fast on demand with primary key
as select * from user1.CLNCL_INDEX@db1
  -- where TRUNC("CREATE_TS") > sysdate - 1100
                         -- FAST REFRESH does not like SYSDATE. Error ORA-12015
  where TRUNC("CREATE_TS")  > to_date('01/01/2009', 'MM/DD/YYYY')
;

-- Run "complete" refresh one time, before "fast" refresh. It takes hours.
EXEC DBMS_MVIEW.REFRESH(LIST => 'ERP.DB1_CLNCL_INDEX', METHOD => 'C', ATOMIC_REFRESH => FALSE, PARALLELISM =>'6');
-- Run FAST refresh. It can be put into a shell script to run daily.
EXEC DBMS_MVIEW.REFRESH(LIST => '"ERP"."DB1_CLNCL_INDEX"', METHOD => 'F', PARALLELISM =>'6');

~~~~~~~~~~~~~~~~  Shell script for crontab ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#!/bin/ksh
## Call sql script to refresh MVs 
# Please specify the variables
export ORACLE_SID=DBNAME
export ORACLE_HOME=/app/oracle/product/11.2.0.3
export PATH=$ORACLE_HOME/bin:${PATH}
export script_dir=/path/to/dba   # Where you save the text log files

Passwd=`XXXXX`
current_dir=`pwd`
cd $script_dir
DT=`date +"%h %d,%y %H:%M"`
BKUPLOG="$script_dir/DBNAME_mvFresh_out.txt"
BKUPLOG_ERR="$script_dir/DBNAME_mvFresh_out_err.txt"

if [ -f $BKUPLOG ]; then
   mv $BKUPLOG ${BKUPLOG}_old
fi
if [ -f $BKUPLOG_ERR ]; then
   mv $BKUPLOG_ERR ${BKUPLOG_ERR}_old
fi

exec 1>$BKUPLOG
exec 2>$BKUPLOG_ERR
echo "Start at $DT"
echo
if [ -f DBNAME_fast_refresh.sql ]; then
   echo "file DBNAME_fast_refresh.sql does exist!!"
else
   echo "file DBNAME_fast_refresh.sql does not exist" |  mailx -s "$script_dir" a1@gmail.com,a2@gmail.com
   exit 1;
fi

$ORACLE_HOME/bin/sqlplus -S /nolog <<-ENDSQL
connect ${Passwd}
show user
@DBNAME_fast_refresh.sql
exit;
ENDSQL

DT=`date +"%h %d,%y %H:%M"`
echo
echo "finish at $DT"
if [ -n "`grep "ORA-" $BKUPLOG`" ] || [ -s $BKUPLOG_ERR ]; then
    cat $BKUPLOG | mailx -s "Error in $script_dir" a1@gmail.com,a2@gmail.com
fi
cd $current_dir
exit
~~~~~~~~~~~~
$ view DBNAME_fast_refresh.sql
spool DBNAME_MVrefresh.lst
EXEC DBMS_MVIEW.REFRESH(LIST => '"ERP"."DB1_CLNCL_INDEX"', METHOD => 'F', PARALLELISM =>'6');

select '1', to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') datetime from dual;
-- put more statements here to refresh more MVWs 
spool off

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In 10G, below SQL is helpful for finding the materialized view and the master table from the MLOG name.

SQL> select distinct
ds.master_owner "MasterTable owner",
ds.master "MasterTable name",
ds.owner "MView owner", ds.name "Snapshot/Mview name",
dsl.log_owner "Log owner", dsl.log_table "MLog name"
FROM dba_snapshot_logs dsl, dba_snapshots ds
WHERE dsl.log_table like '%MLOG$_BOM_OPERATION_SEQUEN%'
and ds.master = dsl.master
--and ds.owner = dsl.log_owner
order by 3;

Note that the information in DBA_SNAPSHOT may not be accurate. For example, Materialized view ENI_DBI_MFG_STEPS_JOIN_MV is created by a join of bom.bom_operational_routings and bom.bom_operation_sequences. But, DBA_SNAPSHOT only reports table BOM_OPERATION_SEQUENCES as the master.

DBA_MVIEWS does not have the MASTER column.

Create database (10.2.0.4) for moving an 11i instance

This task is part of moving an EBS 11i instance from outside hosting Linux servers to in-house servers. There are certain steps for creating a new database for using datapump Export / Import to move an Oracle EBS (11i) database.

1. Install 10gR2 software (note 362203.1)
- Install the base 10.2.0.1 software
- Install 10g products from the 10g Companion CD (Guide B19079-01, p. 3-6)
- Perform 10.2.0.4 patch set (6810189) installation
- Create nls/data/9idata directory by a Perl script

2. Download two EBS 11i patches (note 362205.1)
- 4873830. It includes a script for generating the SQL of creating the target database and tablespaces.
- 7225862. It provides necessary SQL scripts for target database preparation.

3. Get the init file from the source database server
- Comment out UNDO_TABLESPACES and UNDO_MANAGEMENT
- Do not use below PLSQL parameters (so they will be the defaults)
PLSQL_V2_COMPATIBILITY
PLSQL_CODE_TYPE
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT

4. Modify adcrdb.sql to meet the file system layout. Below sample does not include all tablespace statements.

CREATE DATABASE
MAXDATAFILES 1301
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1 (
'/u03/oracle/oradata/dpulti/log01a.log' , '/u02/oracle/oradata/dpulti/log01b.log'
) SIZE 300M ,
GROUP 2 (
'/u03/oracle/oradata/dpulti/log02a.log' , '/u02/oracle/oradata/dpulti/log02b.log'
) SIZE 300M ,
GROUP 3 (
'/u03/oracle/oradata/dpulti/log03a.log' , '/u02/oracle/oradata/dpulti/log03b.log'
) SIZE 300M ,
GROUP 4 (
'/u03/oracle/oradata/dpulti/log04a.log' , '/u02/oracle/oradata/dpulti/log04b.log'
) SIZE 300M
DATAFILE
'/u03/oracle/path/to/system01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M,
'/u03/oracle/path/to/system02.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M
SYSAUX DATAFILE
'/u03/oracle/path/to/sysaux01.dbf' SIZE 1800M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M
/

5. Restart the new database after un-commenting out UNDO_TABLESPACES and UNDO_MANAGEMENT

6. To insure the database will have all necessary components for EBS 11i apps, run a series of SQL scripts detailed in 362205.1
- if get issue on adpostcrdb.sql, check note 236583.1

7. Make sure temp tablespace has temp datafiles.
8. Run @?/rdbms/admin/utlrp.sql to compile any invalid objects. Here are the counts by each owner in 10.2.0.4 after all finish:

SQL> select owner, count(*) from all_objects group by owner order by owner;

OWNER COUNT(*)
------------------------------ ----------
CTXSYS   312
DBSNMP   46
DMSYS   189
EXFSYS    176
MDSYS   906
OLAPSYS   718
ORACLE_OCM    8
ORDPLUGINS   10
ORDSYS    1721
OUTLN    8
PUBLIC   19889
SI_INFORMTN_SCHEMA    8
SYS    23047
SYSTEM   426
TSMSYS    2
XDB    338

16 rows selected.

SQL> select count(*) from all_objects where status = 'INVALID';
COUNT(*)
----------
0

9. Back up the database.
10. If PLSQL_NATIVE_LIBRARY_DIR will be used, create subdirectories.

After the the database is ready, run datapump to import the database.

~~~~~~~~~~~~ export from source database in hosting Linux server ~~~~~~~~~~~~~~
1. Identify and purge some materialized view logs. They can be very big and waste time in exporting and importing.
2. Remove rebuild index parameter in spatial indexes (See Note 362205.1, Section 1).
Login as MST, and execute:
SQL> alter index MST_MD_HYDROS_N1 rebuild parameters 'sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX';
SQL> alter index MST_MD_ADM_BNDS_N1 rebuild parameters 'sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX';
3. Connect to the database as SYSDBA and run the following command:
SQL> grant EXEMPT ACCESS POLICY to system;
4. Create directory dpump_dir1 as SYSTEM
SQL> connect system/*******
SQL> CREATE OR REPLACE DIRECTORY DPUMP_DIR1 AS '/path/to/backup/export';

5. Shutdown all 11i Application servers
6. Run export

$ cat expfile.dat
directory=dpump_dir1
PARALLEL=2
dumpfile=dba%U.dmp
full=y
exclude=SCHEMA:"='MDDATA'"
exclude=SCHEMA:"='OLAPSYS'"
exclude=SCHEMA:"='ORDSYS'"
exclude=SCHEMA:"='DMSYS'"
exclude=SCHEMA:"='OUTLN'"
exclude=SCHEMA:"='ORDPLUGINS'"
logfile=expdpapps.log

$ nohup expdp system/****** parfile=expfile.dat > ${LOGFILE} 2>&1 &

~~~~~~~~~~ import to the new database in in-house AIX server~~~~~~~~~~~~~
$ export ORACLE_SID=db_id
$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH

$ sqlplus system/PWD
SQL> create directory dmpdir as '/mnt/usb/export';

$ cat impdp.dat
#$Header: auexpdp.dat 115.4 2005/12/20 16:02:25 apydimar noship $
directory=dmpdir
parallel=2
dumpfile=dba%U.dmp
# full=y
transform=oid:n
logfile=impdpapps.log

$ nohup impdp system/PWD parfile=impdp.dat &

Patch number (or patchset) for upgrading Oracle database version

There is a summary document on database Server patchsets from 8.1.7.4 and after. It is a good resource for identifying patch numbers of RDBMS PSU, SPU(CPU), Bundle patches and Patchsets.

The Metalink note number is 438049.1. Seems Oracle updates this document all the time.

Select MAX from a column

I tried using below function to get a sequence value for a coulmn, but kept getting NULL when when the table is empty.

begin
select max(nvl(id, 0)) + 1 INTO t_id FROM table_name;
exception when NO_DATA_FOUND then
t_id := 1;
end;

The real trick is that "select max(id) FROM table_name" always returns NULL, and does not raise NO_DATA_FOUND error.

The fix is to use below statement:

select nvl(max(id), 0) + 1 INTO t_id FROM table_name;

Note "select count(id) from table_name" always returns 0 when the table is empty.

Obfuscate (or hide) DAD's password

In Apache configuration, a password can be saved in dads.conf file to let users to view webpages with a password. dataTool.pl is the tool to hide the real password. There are a few of environment variables need to be specified before the execution on dadTool.pl works.

1. Make sure $ORACLE_HOME points to the OMS Home. Such as
export ORACLE_HOME=/app/oracle/product/oms10g

2. Here are variables I specified in Grid Control (10.2.0.1 & 10.2.0.4) installations:
export PATH=$ORACLE_HOME/Apache/modplsql/conf:$PATH
export PATH=$ORACLE_HOME/perl/bin:$PATH
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
export LIBPATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Note: on AIX (64-bit) server, ponit LIBPATH to $ORACLE_HOME/lib32 (not to the 64 bit libraries):
export LIBPATH=$ORACLE_HOME/lib32

3. The result should be

$ cd $ORACLE_HOME/Apache/modplsql/conf
$ perl dadTool.pl -o
Information
-------------------------------------------------------------
Backed up older dads.conf as /app/oracle/product/oms10g/Apache/modplsql/conf/dads.conf.orig.2009-05-26_14-12

All passwords successfully obfuscated. New obfuscations : 1

Logon trigger to change CURSOR_SHARING=FORCE

Bind variable placeholders are used to calculate the SQL hash value for looking up an existing cursor in library cache. This should reduce the amount of hard parsing and shared pool garbage. They can also be used for enforcing a SQL profile for SQL statement.

Use a trigger to change CURSOR_SHARING to FORCE in a session while a user is logging onto 10G database and its ID and its program match the record in LOGIN_CURSOR_CTL table. This is a solution for those legacy or 3rd-party applications which use literal values in queries and are not easy to change their code for using binding variable.

CREATE TABLE ORADBA.LOGIN_CURSOR_CTL
( USER_ID      VARCHAR2(30)                 NOT NULL,
  MACHINE     VARCHAR2(200),
  PROGRAM    VARCHAR2(200),
  CREATE_DT  DATE                            DEFAULT sysdate
)
TABLESPACE USER01;

CREATE OR REPLACE TRIGGER ORADBA.LOGIN_PARSE
AFTER LOGON ON DATABASE
DECLARE
  v_db_user      VARCHAR2(30);
  v_machine     VARCHAR2(200);
  v_program     VARCHAR2(200);
  v_os_user      VARCHAR2(30);
  v_count          INTEGER;
BEGIN
  v_db_username := SYS_CONTEXT('USERENV','SESSION_USER');
  v_machine  := SYS_CONTEXT('USERENV', 'HOST');
  v_program   := SYS_CONTEXT('USERENV', 'MODULE');
  v_os_user := SYS_CONTEXT('USERENV','OS_USER');

  SELECT COUNT(*) INTO v_count FROM LOGIN_CURSOR_CTL
  WHERE USER_ID=v_db_user AND NVL(machine,v_machine)=v_machine
  AND LOWER(v_program) LIKE LOWER(NVL(PROGRAM,'%'))
  ;  -- Note: NULL values of program/machine of LOGIN_CURSOR_CTL means "ANY" cases

  IF v_count >=1 THEN
     EXECUTE IMMEDIATE ('ALTER SESSION SET CURSOR_SHARING=''FORCE''');
     INSERT INTO LOGIN_AUDIT (username, machine, PROGRAM, osuser, login_time )
               VALUES (v_db_user ,v_machine, v_program, v_os_user, SYSDATE );
  END IF;   -- LOGIN_AUDIT is another table.
 END;
/

Check bind variable is used or not:
SQL> select /* WXYZ */ * from dba_objects where object_name = 'ABCD';
SQL> select sql_text, executions, parse_calls, last_active_time FROM v$sql where sql_text like '%WXYZ%' ;

Re-install Grid Control Agent (10.2.0.4)

Here are steps to re-install Grid Control agent on db_server:

0. Have a blackout on the host level to avoid all kinds of GC alerts.
1. Remove Host db_server from the GC site at http://gc_server:7777/em/console/logon/logon , use "Super Administrator" account.
Note: remove the listerner, databases first, then remove the Agent and Host as the last step after the Agent is stopped.

2. On db_server, login as oracle. Stop the Agent and make sure "ps -ef | grep emagent" does not return anything. Then run .../runInstaller to just remove agent10g Home from the server.

3. Remove or rename folder $ORACLE_BASE/product/agent10g. Create a new folder, say, $ORACLE_BASE/product/Agent204download to hold the script.

4. Go to the new folder, and run wget http://gc_server:4889/agent_download/10.2.0.4.0/aix/agentDownload.aix

5. The easiest way is to make a shell script to launch the installation. Here is the content of my file agentinstall.sh

#!/bin/sh
export ORACLE_BASE=/u01/app/oracle

export PATH=$PWD:$PATH
export ORACLE_HOME=$ORACLE_BASE/product/agent10g
export AGENT_HOME=$ORACLE_BASE/product/agent10

./agentDownload.aix -b $ORACLE_BASE/product > agentdownload.log

6. Run the script to fire the installation process
./agentinstall.sh

If the screen is not moving (hanging), hit “Enter” key once during the entire installation!! You may open a different Putty (OS) session to view the log for monitoring the process.

Troubleshootings:

1. Make sure the db_server name is defined in /etc/hosts file.
2. "$ nslookup db_server" to check the IP address and hostname.
3. "$ netstat -an | grep 3872" to verify port is not used (Note 443524.1)
4. On the server where the entire $ORACLE_BASE (Oracle binary) was copied from a different server by SAN COPY on disk level, the Agent installation keeps getting the wrong hostname. I have to add ORACLE_HOSTNAME=db_server to the end of the "InstallCmd=" line in the agentDownload.aix script to force the right hostname (after reading Note 370300.1).

Errors from Using DATAPUMP

Datapump moves data on block level and is faster than Export/Import. It took about 4 hours to export 360GB dump data, and took 13 and half hours to import it on AIX servers with 4 physical CUPs.

Here are some errors I ran into, and the fixes:

- The OS folder can not be recognized by datapump:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/images/DB_full.dmp"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

Fix (Note 370513.1): Mount the disk by using the recommended mounting options which are: (hard,bg,proto=tcp,suid,rsize=32768,wsize=32768,noac)

- Datapump can not locate the directory:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

Fix: Make sure to include "/" at the beginning (or the end) of the path, such as '/home/oracle/dumdir' when creating the DIRECTORY in the database.

- On a RAC environment, got errors when running datapump on one of the nodes:

ORA-31626: job does not exist
ORA-31650: timeout waiting for master process response

Wed Mar 18 19:58:06 2009
kwqbcsrmsg: Timeout contacting inst 2 for buffered oper 1,
IPC ret status=36
queue = SYS KUPC$S_1_20090318195629
kupprdp: master process DM00 started with pid=38, OS id=799160
to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_01', 'SYSTEM', 'KUPC$C_1_20090318195629', 'KUPC$S_1_20090318195629', 0);
Wed Mar 18 19:59:00 2009

Fix (Note 454639.1): "alter system set aq_tm_processes=1"

SQL> select value from v$parameter
where name = 'aq_tm_processes';
VALUE
------
0
SQL> alter system set aq_tm_processes=1 scope=both;
SQL> select value from v$parameter
where name = 'aq_tm_processes';
VALUE
------
1

- Got below errors from datapump, and ORA-600 in alert log:

UDE-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2745
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3712
ORA-06512: at line 1

ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []

Fix (Note 754401.1): Re-create the SYS.KUPC$DATAPUMP_QUETAB queue.

SQL> startup restrict
SQL> exec dbms_aqadm.drop_queue_table(queue_table=>'SYS.KUPC$DATAPUMP_QUETAB',force=>TRUE);

PL/SQL procedure successfully completed.

SQL> BEGIN
2 dbms_aqadm.create_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
3 multiple_consumers => TRUE,
4 queue_payload_type =>'SYS.KUPC$_MESSAGE',
5 comment => 'DataPump Queue Table',
6 compatible=>'8.1.3');
7 EXCEPTION
8 WHEN OTHERS THEN
9 IF SQLCODE = -24001 THEN NULL;
10 ELSE RAISE;
11 END IF;
12 END;
13 /

PL/SQL procedure successfully completed.

Paralle Degree in Creating MVW

When using below statement to create a materialized view, I find that it not only creates the materialized view with a DEFAULT degree, but also uses parallelism of DEFAULT degree to create the materialized view.

CREATE MATERIALIZED VIEW DSS_DIM_MVW
TABLESPACE PDW_DATA
NOCACHE
NOLOGGING
NOCOMPRESS
PARALLEL ( DEGREE DEFAULT INSTANCES 1 )
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS
SELECT "......query from multiple tables of DOP 1"
;

On the server with 4 LCPU, the database uses 2 slave sets of each 8 (4 * 2, default) parallel processes to run the statement. So totally, 16 slaves are recuited to run the creation. The two slaves in the first slave set could be scanning a table and passing the results to the slaves in slave set 2 for sorting.

After the query finishes, the materialized view gets DEFAULT as its DOP.

GC Agent Installation Folder

After the Grid Control server 10.2.0.4.0 is installed, it creates two folders under $OMS_HOME/sysman/agent_download:
10.2.0.1.0
10.2.0.4.0

The Agent installation file AIX_xxxx_10_2_0_4_0.zip file should be downloaded from Oracle site and saved to folder 10.2.0.4.0. After the file is upzipped, it creates folder aix with all installation files there.

The needed modifications are

1. In /10.2.0.4.0/agent_download.rsp:

s_OMSHost="gc_server"
s_OMSPort="4889"
# s_encrSecurePwd=""

2. In /10.2.0.4.0/aix/agentDownload.aix

s_OMSHost="gc_server"
s_OMSPort="4889"

Then, run below line on the database server to start the Agent installation:

wget http://gc_server:4889/agent_download/10.2.0.4.0/aix/agentDownload.aix

Grid Control 10.2.0.4 Installation

Make sure environment variables $ORACLE_BASE and $ORACLE_HOME do not exist in the OS account before any installation. Steps on installing Grid Control 10.2.0.4 on AIX (OS level 5300-08-03-0831):

1. Install Oracle 11g (11.1.0.6) database on the server for the repository.

- Do not include anything related to Emterprise Manager in the database. (No SYSMAN account exists).
- Temporarily set the SYS password to be "default".

2. Make sure the initialization parameters meet the requirements, such as 8k block size.

3. Run @?/rdbms/admin/dbmspool.sql as SYSDBA to create a package in the database.

4. Edit the em_using_existing_db.rsp file.

- Note 763347.1 provides the best sample on necessary parameters.
- Note 602750.1 describes the worst "Invalid Username/Password" bug. The smart way is to have parameter s_reposDBAPwd="default" in the file. I believe this parameter is only used by ConfigureGC.pl

5. Install Grid Control 10.2.0.1 on the server with "software-only" option.

./runInstaller -noconfig -silent -responseFile /path_to_res_filr/response/em_using_existing_db.rsp -force

6. Stop all the OPMN processes, plus "ps -ef | grep opmn".

$OMS_HOME/opmn/bin/opmnctl stopall

7. Apply the 10.2.0.4 patch set (patch 3731593) to OMS. First, edit the patchset.res with

FROM_LOCATION="/file_path/Disk1/stage/products.xml"
ORACLE_HOME="/oracle_base/product/oms10g"
b_softwareonly=true
s_sysPassword="default"

8. Run command to apply the patch set to the OMS home.

./runInstaller -noconfig -silent -responseFile /file_path/Disk1/response/patchset.rsp -force

9. Apply the 10.2.0.4 patch set (patch 3731593) to Agent. First, backup and edit the patchset.res with

FROM_LOCATION="/file_path/Disk1/stage/products.xml"
ORACLE_HOME="/oracle_base/product/agent10g"
b_softwareonly=true
s_sysPassword="default"

10. Run command to apply the patch to the Agent home.

./runInstaller -noconfig -silent -responseFile /file_path/Disk1/response/patchset.rsp -force

11. Make PERL5LIB to the environment variable.

export PERL5LIB=/oracle_base/product/oms10g/perl/lib/5.6.1

12. Run the perl script to configure Grid Control.

$ cd /oracle_base/product/oms10g/perl/bin
$ ./perl /oracle_base/product/oms10g/sysman/install/ConfigureGC.pl /oracle_base/oracle/product

13. After the Agent is started and the some targets on the Host do not show up on the page http://gc_server:7777/em/console/home, run agentca in the Agent home (/oracle_base/product/agent10g/bin) and then remove/add them on the page if necessary.

./agentca -d

14. Run asked root.sh .

Troubleshooting:

Note 434858.1: Remove a failed GC installation.
Note 435329.1: Agent does not start: Malformed URL: http://:%EM_SERVLET_PORT%/emd/main/
Note 730308.1: How To Troubleshoot Oms Startup Issue. (Not really helps).

UPDATES:

To turn off  Performance Tuning Pack in 11G database to make it unavailable in Grid Control:


SQL> alter system SET CONTROL_MANAGEMENT_PACK_ACCESS='NONE' SCOPE=BOTH sid='*';


To turn it on, run below statement and then wait about one or two minutes in Grid Control:


SQL> alter system SET ONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' SCOPE=BOTH;


View DBA_FEATURE_USAGE_STATISTICS gives more information on the status, such as


SQL> select * from DBA_FEATURE_USAGE_STATISTICS where lower(name) like '%sql%';

Change Database Name

There are two parts on changing DB_NAME: modify the db_name parameter in initialization file and re-create the control file.

1. Check the current DB_NAME, and shut down the database clearly

SQL> select database_name from dual;
SQL> select * from props$ where name = 'GLOBAL_DB_NAME';
SQL> alter database backup controlfile to trace;
SQL> show parameter db_name;
SQL> alter system switch logfile;
SQL> shutdown immediate;

2. Make sure there is no database process (such as pmon) running on OS.

3. Edit the control file saved in trace and make a script for creating control file. The key change is

FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS ...
TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ...

4. Rename the existing control files.

5. Change the db_name parameter in SPFILE:

SQL> startup nomount;
SQL> alter system set db_name=newdbname scope=spfile;
SQL> shutdown immediate;
SQL> startup nomount;
SQL> show parameter db_name;
SQL> Run the script to create the control file.

7. Open the database
SQL> alter database open resetlogs;

8. Add temp files and verify their Status by v$tempfile.

9. Check the global_name has been changed (by "select database_name from dual;"). If necessary, run
SQL> alter database rename global_name to newdbname.domain ;

Change SID / Instance ID

It is eazy to change ORACLE_SID / SID. Really, only two files are involved.

1. Identify the current SID:

SQL> select instance from v$thread;

INSTANCE
---------
pdb

SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/spfilepdb.ora

SQL> select value from v$parameter where name = 'control_files';

VALUE
-------------------------------------------------------------------------
/u08/app/oracle/oradata/pdb/control01.ctl, /u09/app/oracle/oradata/pdb/control02.ctl

SQL> alter database backup controlfile to trace;
SQL> shutdown immediate;

2. Rename SPFILE and password file:

$ cd /u01/app/oracle/product/10.2.0/db_1/dbs
$ cp spfilepdb.ora spfilepdb.ora_bk
$ mv spfilepdb.ora spfilepdbs.ora
$ cp orapwpdb orapwpdb_bk
$ mv orapwpdb orapwpdbs

3. Optional: back up the control files. Once the instance is started, the control file will be updated with the new SID.

4. Start the instance and see the new SID:

$ export ORACLE_SID=pdbs
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup open
SQL> select instance from v$thread;

INSTANCE
--------
pdbs

SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/spfilepdbs.ora

SQL> select value from v$parameter where name = 'control_files';

VALUE
---------------------------------------------------------------------------
/u08/app/oracle/oradata/pdb/control01.ctl, /u09/app/oracle/oradata/pdb/control02.ctl

5. Update oratab, tnsnames.ora, .profile with the new SID
6. If necessary, all folders for the instance on the file system can be changed to reflect the new SID.

Identify master node in RAC

1. Check the ocssd.log file.

db1p:$CRS_HOME/log/db1p/cssd$grep -i "master node" ocssd.log | tail -1
[ CSSD]CLSS-3001: local node number 1, master node number 2

or

db2p:$CRS_HOME/log/db2p/cssd$grep -i "master node" ocssd.log | tail -1
[ CSSD]CLSS-3001: local node number 2, master node number 2

2. "ocrconfig -showbackup" show you the last backup on master node.

DDL: create statements

Since 9i, Oracle provides a package to get the DDL statements, such as for creating tablespace.

SQL> set long 50000
SQL> set heading off
SQL> select dbms_metadata.get_ddl('TABLESPACE', 'USERS') from dual;

In 11.2.0.3, following lines will get the statements for creating indexes and creating user:

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off

SQL> select dbms_metadata.get_ddl('INDEX', index_name, 'OWNER_ID')
            from dba_indexes where owner = 'OWNER_ID' and table_name in (......) ;


SQL> select dbms_metadata.get_ddl('USER', 'OWNER_ID') from dual;


In 8i, you have to use Export to extract the same type of statement.

Grid Control: Status Pending

On one Host's homepage in Grid Control, it says "Status Pending" even Grid Control still monitors all databases on the host.

I ran agentca on the database host while the agent is up and running. After this action, the host's status is changed to "Up". If there are database and instance on the host but missing from Grid Control, it will also find and add them to Grid Control.

It seems it stopped the agent during running time, but I did not get notifications saying the agent is down. Here is the log:


/u01/oracle/product/agent10g/bin$./agentca -d

Stopping the agent using /u01/oracle/product/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
Running agentca using /u01/oracle/product/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/u01/oracle/product/agent10g ACTION=Configure MODE=Perform RESPONSE_FILE=/u01/oracle/product/agent10g/response_file RERUN=TRUE INV_PTR_LOC=/etc/oraInst.loc
Perform - mode is starting for action: Configure

Perform - mode finished for action: Configure

You can see the log file: /u01/oracle/product/agent10g/cfgtoollogs/oui/configActions2009-02-03_09-14-00-AM.log

AUTHID current_user

An anonymous block works, but same code in a named block may not work, because roles used in named PL/SQL blocks and anonymous PL/SQL blocks act differently. The workaround is to define the procedure as "AUTHID current_user":

CREATE or REPLACE procedure test_proc
AUTHID CURRENT_USER as
......

Anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.

If the user was granted all the necessary privileges directly (not through a role), it won't matter if roles are enabled or disabled, nor will you need "authid current_user".

You can check what system privileges are granted with roles currently enabled, then you can disable roles and see what privileges are different.
SQL> select * from session_privs;
SQL> set role none;
SQL> select * from session_privs;

The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer's rights queries SESSION_ROLES, then the query does not return any rows.

-- check current enabled roles
SQL> SELECT * FROM session_roles;
-- turn off roles
SQL> set role none;
-- re-query:
SQL> SELECT * FROM session_roles;

Restore database from hot backups

There are different ways to specify the time point during incomplete recovery.

1. set until scn
set until scn 82189;
restore database;
recover database;

You can run a "preview" to find the highest SCN. Such as
RMAN> run {
2> set until time='sysdate-2';
3> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
4> restore database preview;
5> RELEASE CHANNEL ch00;
6> }

2. set until sequence
set until sequence = 58 thread 1;
restore database;
recover database;

You can use LIST to find checkpoint SCN of the backup you need:
RMAN> list backup of database completed between date_1 and date_2;
RMAN> list backup of database completed after date_2;

Then use LIST again to check the archivelogs needed for recovery have been backed up. It will report the sequence numbers:
RMAN> list backup of archivelog from scn=82189;

You can also run a query on the database to find the archivelog sequence number:
SQL> select * from V$LOG_HISTORY
where FIRST_CHANGE# < 82189 and NEXT_CHANGE# >=82189;

3. SET UNTIL TIME
SET UNTIL TIME "to_date('30-JUL-2008:11:59:00', 'DD-MON-YYYY:HH24:MI:SS')" ;
RESTORE DATABASE;
RECOVER DATABASE;

If there is only one backup available, I usually put the time to be the point right after RMAN finished backing up all data files and before RMAN was going to back up archive logs.


If you lost the control file, you can't run a SET UNTIL command if the controlfile isn't mounted. So, if you haven't made any structural changes to your database between the PITR and last autobackup, you can restore the control file fist (see Note 372996.1).

RMAN> set dbid=123456;
startup nomount;
set controlfile autobackup format for device type disk to 'diskPath/%F';
restore controlfile from autobackup until time "to_timestamp('30-JUL-2008:11:59:00', 'DD-MON-YYYY:HH24:MI:SS')";
startup mount;
run{ set until time "to_timestamp('30-JUL-2008:11:59:00', 'DD-MON-YYYY:HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
}

Restore archivelogs to disk from ASM

During recovering a database, usually Oracle tells which archivelog is needed by specifying the sequence number, such as:
ORA-00279: change 39138275307 generated at 01/02/2009 00:12:07 needed for thread 1
ORA-00280: change 39138275307 for thread 1 is in sequence #78240

Here are the steps to get the necessary logs from the tape or from the ASM:

1. Make sure the status of archivelog is "AVAILABLE" on the tape, and the tape is also available on the tape drive.

RMAN> list backup of archivelog sequence 78240 thread 1;

2. Restore the archivelogs from the tape to the disk. But it may say the logs are already on the disk.

RMAN> run {
2> set archivelog destination to '/images';
3> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
4> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
5> restore archivelog from logseq=78240 until logseq=78241;
6> RELEASE CHANNEL ch00;
7> RELEASE CHANNEL ch01;
8> }

executing command: SET ARCHIVELOG DESTINATION

allocated channel: ch00
channel ch00: sid=1017 instance=pdw1 devtype=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

allocated channel: ch01
channel ch01: sid=1003 instance=pdw1 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

Starting restore at 12-JAN-09:06:50:05

archive log thread 1 sequence 78240 is already on disk as file +PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78240.11985.675049215
archive log thread 1 sequence 78241 is already on disk as file +PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78241.14012.675049273
restore not done; all files readonly, offline, or already restored
Finished restore at 12-JAN-09:06:50:06

released channel: ch00
released channel: ch01

3. Use RMAN "COPY" command to copy the logs from ASM to disk. Make sure the disk folder is writable.

RMAN> connect target
connected to target database: PDB (DBID=1190101294)

RMAN> copy archivelog '+PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78240.11985.675049215' to '/images/archive_78240.arc';

Starting backup at 12-JAN-09:06:57:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=973 instance=pdb1 devtype=DISK
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=78240 recid=3055 stamp=675049214
output filename=/images/archive_78240.arc recid=3088 stamp=675932248
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-JAN-09:06:57:29

Starting Control File and SPFILE Autobackup at 12-JAN-09:06:57:29
piece handle=+PFRA/pdb/autobackup/2009_01_12/s_675932297.12505.675932301 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JAN-09:06:58:25

RMAN> copy archivelog '+PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78241.14012.675049273' to '/images/archive_78241.arc';

Starting backup at 12-JAN-09:06:59:28
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=78241 recid=3056 stamp=675049272
output filename=/images/archive_78241.arc recid=3089 stamp=675932369
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-JAN-09:06:59:30

Starting Control File and SPFILE Autobackup at 12-JAN-09:06:59:30
piece handle=+PFRA/pdb/autobackup/2009_01_12/s_675932372.12504.675932373 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JAN-09:06:59:34

RMAN> exit

That also shows a good way to copy files out from ASM.

Monitor RMAN jobs

After you start a RMAN restore or backup session, RMAN needs to reach the tape first and you may have to wait for a while before you see the progress. In Veritas Netbackup client, there is a folder (on database server) holding Veritas logs. You can check them for any access errors or tape availability. The location could be

/usr/openv/netbackup/logs/user_ops/dbext/logs/

To find the Netbackup client location, try "ps -ef | grep netbackup" or "ps -ef |grep bpcd" when the RMAN job is running. Note the "dpcd" process may not be running all the time, and just be in and out when RMAN runs.

Sometimes, no news is good news. Once the session passes the tape level, Note 1444640.1 gives ways to monitor RMAN jobs from the database.

Manage DBSNMP account

To change the password for DBSNMP accout, you have to stop the Agent first. Here is a way you do not need to edit the targets.xml file under $AGENT_HOME/sysman/emd (as described in note 748668.1) on the EM client and Grid Control automatically reloads the new information to the agent on the client:
1. Stop EM agent on the server $AGENT_HOME/bin/emctl stop agent.
2. Use Sql*plus to change dbsnmp paaword.
3. Start the agent on the DB server.
4. In Grid Contrl, go the database and then click on "Monitoring configuration"
5. You may see "ORA-01017: invalid username/password; logon denied".
6a. You can enter the new password, and proceed to next. It seems to me that Grid Control saves the new encrypted password to the targets.xml file on the client. If this does not work, you can go to 6b.
6b. Click on "Change dbsnmp Password" to change its password again. After that, you should see "The dbsnmp passsword has been successfully changed for both the datbase and the agent. This new password has also been set below as the Monitor Password. The dbsnmp user is unlocked in the database. You may now proceed with any additional configuration. Otherwise, to exit the wizard, click Cancel."
7. Clieck on "Next" and then "OK" to re-connect.

Sometimes, you need to re-create DBSNMP account. You have to stop the agent first to terminate all DBSNMP connections to the database. Otherwise, you will get "ORA-01940: cannot drop a user that is currently connected" error when try to drop it. The steps are
1. Stop the EM agent.
2. SQL> @?/rdbms/admin/catnsnmp.sql -- drop user
3. SQL> @?/rdbms/admin/catsnmp.sql -- create user
It also creates 46 objects in DBSNMP schema, but keeps the account in LOCKED status.
4. Start the EM agent.

Use srvctl to remove service or disable service

Before using srvctl to reomve service or database from the Oracle clusterware (OCR), you need to use 'crs_stat -t' to check if they are OFFLINE or not in Target and State columns. If they are not, you should stop them first to make them OFFLINE.

. Stop and remove the service using the below srvctl command (Guide B14197-08):
$ srvctl stop service -d db_name -s service_name_list
$ srvctl remove service -d db_name -s service_name

. Stop and remove the database using the below srvctl command:
$ srvctl stop instance -d db_name -i inst_name_list
$ srvctl stop database -d db_name
$ srvctl remove instance -d db_name -i inst_name
$ srvctl remove database -d db_name

. To disable the database from automatic startup by clusterware, use the below srvctl command when it is OFFLINE status:
$ srvctl disable database –d db_name