You can use the following script to purge your Automatic Diagnostic Repository. The script provided will delete everything older than 1 day (1440 minutes). If you would like to maintain a week worth of information, set the -age option to be 10080.

 $ cat adr.ksh export CTL=adrci.ctl echo "show homes" |adrci |grep ^diag> $CTL 

The algorithm is simple. You determine what all your ADR homes are and loop through each one of them and purge log and trace files.


cat $CTL |while read HP do echo “Purging for HOMEPATH: $HP” adrci < [/code] Posted by Charles Kim, Oracle ACE Director


Starting from Oracle Database 11g, the DBA_USERS.PASSWORD column no longer contains the encrypted password. Now, you must query the SYS.USER$ table to access the information that you are looking for:

  1* select name, password from sys.user$ where name='RODBA'
SQL> /

NAME                           PASSWORD
------------------------------ ------------------------------
RODBA                          10AD051DA4653404

Once you find the encrypted password, everything is same as it was before. On a separate window, change the password:

SQL> alter user RODBA identified by RODBA123;
User altered.

On another window, login:

SQL> conn RODBA/RODBA123
Connected.

Reset the password back ASAP:
SQL> alter user RODBA identified by values '10AD051EC4653404';

User altered.

Posted by Charles Kim, Oracle ACE Director


In the following example, we will create a 6 node RAC database on the clustered nodes viscosityoradb1 to viscosityoradb6. We will create a general purpose database with 500MB redo log files and the database will be created in the DATA disk group. We will also allocate 2.4GB for the SGA and create the database with AL32UTF8 character set.

dbca -silent \
-createDatabase \
-templateName General_Purpose.dbc \
-gdbName DBATOOLS \
-sid DBATOOLS \
-SysPassword dba123 \
-SystemPassword dba123 \
-emConfiguration NONE \
-redoLogFileSize 500 \
-recoveryAreaDestination FRA \
-storageType ASM \
-asmSysPassword dba123 \
-diskGroupName DATA \
-characterSet AL32UTF8 \
-nationalCharacterSet AL16UTF16 \
-totalMemory 2400 \
-databaseType MULTIPURPOSE \
-nodelist viscosityoradb1,viscosityoradb2,viscosityoradb3,viscosityoradb4,viscosityoradb5,viscosityoradb6

Executing the dbca script yields the following output:

Copying database files
1% complete
3% complete
30% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
44% complete
45% complete
48% complete
50% complete
Creating cluster database views
52% complete
70% complete
Completing Database Creation
73% complete
76% complete
79% complete
82% complete
91% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/DBATOOLS/DBATOOLS.log" for further details.

As a follow-up step, you should shutdown the database, mount the database and enable archive logging.

Within the Database Cloud offerings, we can provide database as a service quickly and efficiently by leveraging dbca in silent mode. Provisioning databases to customers can be done in within 1/2 hour rather than 1/2 day.

Created by Charles Kim, Oracle ACE Director

Posted in RAC