Here’s a new version of the database growth report. The new version determines the growth trend for the database for the past one year. Check out this script:

set pages 255 lines 2000 trims on
compute sum of growth_in_gb on report
col week_number for a35
break on report
select to_char(creation_time, 'ww mon yyyy') week_number, round(sum(bytes)/1024/1024/1024,1) growth_in_gb
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'ww mon yyyy')
order by 1
/

Posted by Charles Kim, Oracle ACE Director


 

def S='&1'
def T='&2'
set lines 2000 pages 0 ver off echo off head off feed off
set newpage none
set trimspool on
set long 5000000
col output for a1000 word_wrapped
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);

spool &T..sql
select dbms_metadata.get_ddl('TABLE','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('INDEX','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('TRIGGER','&T','&S') output from dual;

-- Uncomment to generate object level grants
-- select dbms_metadata.get_dependent_ddl('OBJECT_GRANT','&T','&S') output from dual;

spool off;


This script will generate the DDL to re-create a specific table. The script accepts two parameters:
1. Schema name
2. Table name

def S='&1'
def T='&2'
-- def I='&3'
set lines 2000 pages 0 ver off echo off head off feed off
set newpage none
set trimspool on
set long 5000000
col output for a1000 word_wrapped
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);

spool &T..sql
select dbms_metadata.get_ddl('TABLE','&T','&S') output from dual;

spool off;

Created by Charles Kim, Oracle ACE Director


If you notice in Oracle Linux 5.6, Oracle starts to disable NUMA at boot time as shown in the following grub.conf file:

 
[root@rac561 ~]# cat /etc/grub.conf
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You do not have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /, eg.
#          root (hd0,0)
#          kernel /boot/vmlinuz-version ro root=/dev/sda1
#          initrd /boot/initrd-version.img
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,0)/boot/grub/splash.xpm.gz
hiddenmenu
title Oracle Linux Server (2.6.32-100.26.2.el5uek)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.32-100.26.2.el5 ro root=/dev/sda1 rhgb quiet numa=off
        initrd /boot/initrd-2.6.32-100.26.2.el5.img

Looking at /proc/cmdline, you will notice that the numa=off argument was passed at boot:

 
[root@rac561 ~]# cat /proc/cmdline
ro root=/dev/sda1 rhgb quiet numa=off

Posted by Charles Kim, Oracle ACE Director



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