Moving to WordPress...

Well, so many friends and well wishers asked about bijoos.com - so put the site back [though most of the stuff is obsolete] and moved the blog to http://bijoos.com/oraclenotes

I will try to keep the blog active...

Thanks for visiting...

Have a great day!

Active or Recently Used Printers in Oracle EBS

Recently had a request from one of the admins for list of printers used in the EBS application and how many jobs are directed to a printer. The following SQL provided what the admin needed, thought a good one to share...

select distinct fcr.printer, fu.user_name, 

       fu.description, count(*) jobs

from apps.fnd_concurrent_requests fcr, apps.fnd_user fu

where fcr.requested_by = fu.user_id

and (fcr.printer is not null and fcr.printer not in  ('noprint','LOCAL_PRINT','LOCAL','LOCAL_PRINTTO', 'LOCAL_PREVIEW'))

group by fcr.printer, fu.user_name, fu.description;

The caveat is that if you purge concurrent requests, the information is dependent on the records available in concurrent requests table...

Hope this helps someone, or if there is a better way to find the printer usage, please let me know... have a great day!

Using Flashback during repeated load tests

We are in the middle of migrating several databases to a new data center. One of the requirement is to do rigorous load and stress tests on the new servers to make sure they can handle the load and the configuration is correct. For this purpose, there are several load/stress test scripts are written and tested using OATS [Oracle Application Testing Suite].

Since the load/stress tests are done repeatedly, the test data [database] need to be restored to its pre-test state before doing the next test. As these database sizes are large, it will take a while to do the restore. But, the flashback feature of the database comes handy.

Enable Flashback

To enable flashback, from 11gR2 onward there is no need to restart the database in mount mode. Flashback can be enabled and disabled while the database is online.

Before you enable flashback, set the following parameters.


SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +ORAARCH
db_recovery_file_dest_size           big integer 50000M

Enable flashback using:

SQL> ALTER DATABASE FLASHBACK ON;

You can verify if flashback is enabled by querying v$database.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

Flashback logs are written to the directory speficied by the DB_RECOVERY_FILE_DEST parameter. The retention of these files depends on the setting of parameter:

SQL> show parameter db_flashback

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target        integer     1440

SQL>

The database must be in ARCHIVELOG mode.

Create Restore Point

During load/stress tests we like to make sure the database is flashbackable to pre-test state. We ensure this by creating a guaranteed restore point.

SQL> create restore point BEFORE_LOAD_TEST_G GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>

Restore points created in the database can be viewed from v$restore_point view.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

       GUARANTEE_FLASHBACK_DATABASE

      FROM   V$RESTORE_POINT ;

NAME                         SCN                             TIME DATABASE_INCARNATION# GUA

---------------------- ---------- ------------------------------ --------------------- ---

BEFORE_LOAD_TEST_G       51274755 26-NOV-12 02.52.04.000000000 PM                     2 YES

You will have to keep track of the space usage on the flash recovery area to make sure you have enough free space. 

SQL>  select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

-------------------- ------------------ ------------------------- ---------------

CONTROL FILE                          0                         0              0

REDO LOG                              0                         0              0

ARCHIVED LOG                      18.47                         0            218

BACKUP PIECE                          0                         0              0

IMAGE COPY                            0                         0              0

FLASHBACK LOG                     12.24                         0             15

FOREIGN ARCHIVED LOG                  0                         0              0

7 rows selected.

SQL>

After the test is done, it is time to flashback!

Flashback the Database

To flashback, the database has to be restarted in mount state. Here the example shows a RAC database shutdown and startup.

$ srvctl stop database -d mydb

$ srvctl start instance -d mydb -n mynode1 -o mount

Use RMAN to do the flashback, the advantage is that if any archivelog that is not present in the disk is required for flashback recovery, RMAN can automatically restore the archivelogs from the backup media.

$ rman target /

RMAN has commands to list the restore points in DB.

RMAN> list restore point BEFORE_LOAD_TEST_G;

using target database control file instead of recovery catalog

SCN              RSP Time  Type       Time      Name

---------------- --------- ---------- --------- -------------------

51274755                   GUARANTEED 26-NOV-12 BEFORE_LOAD_TEST_G

Now ready to flashback the database...

RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_LOAD_TEST_G;

Starting flashback at 27-NOV-12

using target database control file instead of recovery catalog

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=997 instance=prod2 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.5 (2012091607)

allocated channel: ORA_SBT_TAPE_2

channel ORA_SBT_TAPE_2: SID=1139 instance=prod2 device type=SBT_TAPE

channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 7.5 (2012091607)

allocated channel: ORA_SBT_TAPE_3

channel ORA_SBT_TAPE_3: SID=1281 instance=prod2 device type=SBT_TAPE

channel ORA_SBT_TAPE_3: Veritas NetBackup for Oracle - Release 7.5 (2012091607)

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1424 instance=prod2 device type=DISK

starting media recovery

archived log for thread 1 with sequence 169 is already on disk as file +ORAARCH/prod/archivelog/2012_11_26/thread_1_seq_169.482.800375559

archived log for thread 2 with sequence 500 is already on disk as file +ORAARCH/prod/archivelog/2012_11_26/thread_2_seq_500.480.800373637

media recovery complete, elapsed time: 00:00:04

Finished flashback at 27-NOV-12

RMAN>

RMAN> alter database open resetlogs;

database opened

RMAN>

Database flashback is done!

You may now start the other instances in the cluster, if applicable.

$ srvctl start instance -d mydb -n mynode2

Flashback using time, instead of restore point.

Sometimes, it is required to flashback a database without a restore point - to a time. It can be done using the syntax:

If you are restoring to a time, instead of restore point, use the below syntax.

RMAN> flashback database to time "to_timestamp('2012-11-29 12:00:00', 'YYYY-MM-DD HH24:MI:SS')";

Starting flashback at 30-NOV-12

using channel ORA_SBT_TAPE_1

using channel ORA_SBT_TAPE_2

using channel ORA_SBT_TAPE_3

using channel ORA_DISK_1

starting media recovery

channel ORA_SBT_TAPE_1: starting archived log restore to default destination

channel ORA_SBT_TAPE_1: restoring archived log

archived log thread=2 sequence=56

channel ORA_SBT_TAPE_1: restoring archived log

archived log thread=1 sequence=36

channel ORA_SBT_TAPE_1: reading from backup piece PROD_1211301159_AL0_501_1_1_800714350

channel ORA_SBT_TAPE_1: piece handle=PROD_1211301159_AL0_501_1_1_800714350 tag=DS1PROD2_1211301159_AL0

channel ORA_SBT_TAPE_1: restored backup piece 1

channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:45

channel ORA_SBT_TAPE_1: deleting archived log(s)

archived log file name=+ORAARCH/prod/archivelog/2012_11_30/thread_1_seq_36.794.800732539 RECID=652 STAMP=800736143

channel ORA_SBT_TAPE_1: deleting archived log(s)

archived log file name=+ORAARCH/prod/archivelog/2012_11_30/thread_2_seq_56.792.800732539 RECID=651 STAMP=800736142

media recovery complete, elapsed time: 00:00:04

Finished flashback at 30-NOV-12

RMAN>

RMAN> alter database open resetlogs;

database opened

RMAN>

Disk Array Migration for ASM Databases

This blog identifies the DBA steps required to migrate databases from one disk array to another.

Identify the ASM diskgroups and their disk (LUN) sizes

Login to the ASM instance and identify the LUNs allocated to server.

SQL> SET PAGES 999

SQL> col name format a15

For 11g ASM:

SQL> select d.group_number, g.name, d.os_mb

from v$asm_disk d left outer join v$asm_diskgroup g

on (d.group_number = g.group_number)

order by g.group_number, d.os_mb;

For 10g ASM:

SQL> select d.group_number, g.name, d.total_mb

from v$asm_disk d left outer join v$asm_diskgroup g

on (d.group_number = g.group_number)

order by g.group_number, d.total_mb;

You may discard LUNs with group number 0, as they are not part of any diskgroup. Request Sysadmin team to provision the new LUNs to the container, same LUN size or total of multiple LUNs in the group is equal or more to what is allocated to the group. 

 To find out what is allocated and free in each diskgroup, you may use:

SQL> select name, total_mb, free_mb from v$asm_diskgroup;

Once LUNs are provisioned by Unix team, use OEM or SQL to add the LUNs to respective diskgroups [existing diskgroup]. NO outage required for this step. Remember to login to 11g ASM using the SYSASM privilege, for 10g login using SYSDBA privilege.

SQL> ALTER DISKGROUP ADD DISK ‘/full_path_of_device’;

To find out which LUNs are available to add, you may use SQL:

SQL> col path format a40

SQL> select path, os_mb from v$asm_disk where group_number = 0;

{for 10g ASM, please substitute os_mb with total_mb}

Then, go ahead and drop the old LUNs. No need to wait for the add operation to complete.

SQL> ALTER DISKGROUP DROP DISK name_of_old_LUN;

The name of the LUN can be found by querying the V$ASM_DISK...

SQL> select path,name from v$asm_disk where group_number=3;

Keep the rebalance power low, so that the migration activity does not impact database performance.

You may check the progress of rebalance periodically using V$ASM_OPERATION, once no rows are in this view, you may advice SYSADMIN team to pull out the old LUNs from the server. V$ASM_OPERATION also shows the work so far completed and how long it will take to complete the work.

SYSAUX Tablespace Growth - Review and Fix

Received an alert from one of the databases that SYSAUX tablespace is nearing its allocated 24GB space. 24GB is too much for SYSAUX, especially on a development database... so need to make sure the contents are reviewed before adding more space...

Oracle10g+ versions have a view V$SYSAUX_OCCUPANTS, showing what components are in SYSAUX tablespace and how much space is used. It also shows the procedure to use if you decide to move this component from SYSAUX to another tablespace.



SQL> col OCCUPANT_NAME format a20

SQL> select occupant_name, space_usage_kbytes

     from v$sysaux_occupants
     where space_usage_kbytes > 10240
     order by space_usage_kbytes desc;

OCCUPANT_NAME        SPACE_USAGE_KBYTES
-------------------- ------------------
SM/AWR                          2875776
LOGMNR                           573184
SM/ADVISOR                       448384
XDB                              177216
SDO                               79936
STREAMS                           76928
AO                                49536
XSOQHIST                          49536
XSAMD                             31872
SM/OTHER                          17984
TEXT                              14720

11 rows selected.

SQL>

So, it really does not add up anywhere near 24GB, the largest component is AWR and is only occupying less than 3GB... What else is in SYSAUX, that does no belong to the standard Oracle components?


SQL> col SEGMENT_NAME format a25
SQL> col segment_type format a10
SQL> col bytes format "999,999,999,999"
SQL> select segment_name, bytes 
     from dba_segments 
     where tablespace_name = 'SYSAUX'
     and bytes > 1048576000 order by bytes;

SEGMENT_NAME                         BYTES
------------------------- ----------------
SYS_LOB0002516712C00008$$   20,408,434,688

1 row selected.

SQL>

It's a LOB segment, let us find out which table...

SQL> select table_name, column_name

  2  from dba_lobs

  3  where segment_name = 'SYS_LOB0002516712C00008$$';

TABLE_NAME

------------------------------

COLUMN_NAME

---------------------------------------------

STREAMS$_APPLY_SPILL_MSGS_PART

MESSAGE

Aaaha! We recently enabled Change Data Capture feature on this database, and it is using STREAMS... Now, need to see what's causing it to grow and how to fix...!

Also, shows that V$SYSAUX_OCCUPANTS do not consider the LOB segments, when calculating space occupied  :-(

Time Flies!

Just thought that I have not updated the blog for sometime, but did not realize it is almost 10 months since the last update... Time really flies...

Oracle Openworld is here, though this is my sixth or seventh OOW at SFO, 2012 is the first OOW where I am part of the event outside of participation...

Will be presenting on Thu 10/4 - EBS performance improvement tips... I know it is the last day of the conference and many may not stay back to attend the afternoon sessions in particular... If you are around and you are supporting an Oracle EBS instance [11i or R12], I think you would learn a thing or two from this presentation. As the title of the presentation "Improving Performance of E-Business Suite Application - Practical Tips from a DBA's Diary..." says, the presentation is full of proven tips and techniques. If you happen to attend, please provide me feedback...

I will be at the Oracle Certification Lounge on Wed 10/3 at 1PM for a meet the author session. If you have any certification related questions or need guidance, don't forget to stop by... you may also discuss the contents of  my 11g OCA certification exam study guide, and how it can help you get certified...

Also, I will be available most of the Exhibition hall hours at the OneNeck booth [415 Moscone South]... Stop by to say hello, or if you need to have a quick chat on architecture, tuning,  upgrades, administration or any topic related to EBS system administration or Oracle administration... No Exadata or EM12c yet :-(

Have a great day!

Sequences Nearing Limit

Happy New Year 2012!

During the holidays we hit a production issue on the EBS instance. It was one of the Sequences reaching the maximum limit, but took a lot of time for our admins and Oracle support to figure out where the issue was... If we had the following SQL as part of the monitoring jobs, could have avoided the issue altogether...

SELECT sequence_owner,

       sequence_name,

       last_number,

       max_value,

       cache_size

FROM   dba_sequences

WHERE  last_number > max_value - CASE

                                   WHEN max_value > 50000 THEN 10000

                                   ELSE 200

                                 END

       AND cycle_flag = 'N'

       AND max_value != -1; 

This was added to monitoring tool immediately... :-)

Oracle EBS Housekeeping Jobs

For a long time, I searched for a comprehensive list of housekeeping jobs [Standard EBS jobs provided by Oracle] that should be scheduled periodically on the EBS instance. Since I could not find a single source, came up with my own after a lot of reading... There may be more, but these should be scheduled on the EBS to keep the instance performing healthy... The arguments and run interval may need to be modified to suit the workload and business requirement... but this will get you started... Few jobs may need multiple scheduling with different parameters...

Job       : Gather Schema Statistics 
Executable: FNDGSCST 
Frequency : Daily 
Parameters: ALL, 10, , NOBACKUP, , LASTRUN, GATHER AUTO, , N 
Frequency : Weekly 
Parameters: ALL, 10, 4, NOBACKUP, , LASTRUN, GATHER, , Y  


Job       : JTF Item InterMedia Index Optimizing operation
Executable: JTFOPTI 
Frequency : Weekly 
Parameters: FULL, 2 


Job       : JTF Item InterMedia Index Sync Operation
Executable: JTFSYNC 
Frequency : Weekly 
Parameters: (None) 


Job       : MES InterMedia Index Sync Operation
Executable: AMVSYNC 
Frequency : Weekly 
Parameters: (None) 


Job       : OCM InterMedia Index Synchronizing for Attribute Bundles 
Executable: IBCSYNCATTR 
Frequency : Weekly 
Parameters: (None) 


Job       : Purge Debug Log and System Alerts FNDLGPRG 
Frequency : Weekly 
Parameters: (Date - 30 days)


Job       : Purge FND_STATS History Records 
Executable: FNDPGHST 
Frequency : Weekly 
Parameters: DATE, 01-Jan-00, 30-JUN-11 


Job       : Purge Obsolete Generic File Manager Data 
Executable: FNDGFMPR Daily 
Parameters: Y, , 


Job       : Purge Obsolete Workflow Runtime Data FNDWFPR 
Frequency : Weekly
Parameters: , , 60, PERM, N, 500, N Weekly , , 60, TEMP, N, 500, N 


Job       : Purge Signon Audit data FNDSCPRG 
Frequency : Weekly 
Parameters: (Date - 60 days)


Job       : Workflow Agent Activity Statistics Concurrent Program  
Executable: FNDWFAASTATCC 
Frequency : Daily 
Parameters: (None) 


Job       : Workflow Background Process 
Executable: FNDWFBG 
Frequency : 12 HOURS 
Parameters: , , , N, N, Y 
Frequency : 1 HOUR
Parameters: , , , N, Y, N 
Frequency : 5 MINUTES 
Parameters: , , , Y, N, N 


Job       : Workflow Control Queue Cleanup 
Executable: FNDWFBES_CONTROL_QUEUE_CLEANUP 
Frequency :  12 HOURS 
Parameters: (None) 


Job       : Workflow Directory Services User/Role Validation
Executable: FNDWFDSURV 
Frequency : Daily 
Parameters: 10000, Y, Y, Y 


Job       : Workflow Mailer Statistics Concurrent Program
Executable: FNDWFMLRSTATCC 
Frequency : Daily 
Parameters: (None) 


Job       : Workflow Work Items Statistics Concurrent Program
Executable: FNDWFWITSTATCC 
Frequency : Daily 
Parameters: (None)  


Enjoy!

Tablespace Free Space and Max Size

Earlier, provided a small script to find the maximum growable size of a tablespace... Here is little more comprehensive script that shows more tablespace space info. Current size, growable size based on the autoextensible data files, free space, biggest chunk...All size is in MB.


SELECT a.tablespace_name TSNAME, 
        ROUND(SUM(a.growable)/1048576) growable, 
        ROUND(SUM(a.tots)/1048576) Tot_Size,
        ROUND(SUM(a.sumb)/1048576) Tot_Free,
        ROUND(SUM(a.sumb)*100/sum(a.tots)) Pct_Free,
        ROUND(SUM(a.largest)/1048576) Large_Ext
FROM   (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,
               MAX(bytes) largest, 0 growable
        FROM   dba_free_space a
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_data_files
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_temp_files
        GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY rollup(a.tablespace_name)
ORDER by a.tablespace_name

Enjoy!

SQL Script to find the top 50 tables by size

Here is a SQL to identify the top 50 tables. When I say table, it could be a table, partitioned table or materialized view. Takes into consideration the indexes defined on the table and lob segments in the table, thus gives a complete picture of the table size. When you truncate the table, this much would be the space released...


select top50.owner, top50.table_name, meg, a.num_rows 
from dba_tables a, 
   (Select * from (
SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type like 'TABLE%'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type like 'INDEX%'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore small tables */
ORDER BY SUM(bytes) desc
) where rownum < 51) top50
where top50.owner =a.owner
and top50.table_name = a.table_name
order by meg desc, num_rows desc;

Enjoy!

Script to Grant Privileges and Create Synonyms

Extending the previous post Granting privileges on all or multiple objects to user/role , here is another script.

Often we have a requirement to create read only accounts for a schema. Typically these accounts are named _QUERY. The script when executed will prompt for the user name of the read only account, and the schema name where read privileges on the objects to be granted. It produces output to the screen and writes to file named tmpgrants.sql. Execute the tmpgrants.sql to grant SELECT privilege and to create a synonym under the read only account, so that the query account need not worry about qualifying the table with schema name.

First create the read only user account using similar syntax as below...

create user tdmaa_query identified by tdm123q default tablespace users;
grant create session to tdmaa_query;

Be sure to save all the below lines to a file and run the file in SQL*Plus... Executing this script will produce a script file named tmpgrants.sql. Run that script to grant privileges and create synonyms.


set pages 0 lines 200 trims on verify off feedback off


accept grants_to  prompt 'Enter user to grant privileges: '
accept schema     prompt 'Enter schema on which to grant: '


spool tmpgrants.sql


select 'grant select on '||owner||'.'||table_name ||' to &grants_to;', chr(10),
       'create synonym &grants_to..'||table_name ||' for '||owner||'.'||table_name||';', chr(10)
from  dba_tables
where owner = upper('&schema')
union all
select 'grant select on '||owner||'.'||view_name ||' to &grants_to;', chr(10),
       'create synonym &grants_to..'||view_name ||' for '||owner||'.'||view_name||';', chr(10)
from  dba_views
where owner = upper('&schema')
;


spool off


set pages 99 lines 80 verify on feedback on


prompt "Run tmpgrants.sql if you are satistified with the script..."



Enjoy!

Granting privileges on all or multiple objects to user/role

Granting privileges on all or multiple objects to user/role

Question:
I need to grant SELECT, UPDATE privilege on all tables owned by schema XXVMX to users MSUBBU, SMARTIN. Is there a command in Oracle to grant a privilege on all objects in schema to user?

Answer:
No such privilege in Oracle. You will have to write a script to grant the privilege on individual objects to the users.

If you have to repeat the same to more users, it may be better to create a role and grant the privileges to the role. Then assign the role to the users that need the privilege. Thus when new tables are created under XXVMX, the privilege need to be added to the role once only, do not have to do grant to all the individual users.

Create Role:

CREATE ROLE XXVMX_UPDATE;

Grant Role to Users:

GRANT XXVMX_UPDATE to MSUBBU, SMARTIN;

SQL Script:

set pages 0
set lines 300 trims on feedback on echo off


spool grants.sql


SELECT 'grant select, update on ' || owner ||'.'||table_name|| ' to XXVMX_UPDATE;'
FROM dba_tables
WHERE owner = 'XXVMX';


spool off


set pages 99 lines 80
set feedback on echo on

Execute the script file created to grant the privileges.

@grants.sql

You may replace the "dba_tables" in the query with "dba_objects" and change the WHERE clause appropriately to filter different sets of objects...

Script to Refresh Materialized Views

The following script can be used to refresh materialized views that are capable of FAST (incremental) refresh automatically. Can be used on EBS database as well if you un-comment the commented (REM) lines. The refresh criteria used is any fast refresh-able materialized view that has not been refreshed in the past 24 hours, but was refreshed in the last one month...

REM Uncomment below line if EBS database

REM alter session set current_schema=apps;

set serveroutput on size 9999

Declare

cursor mv2ref is select owner ||'.'||  mview_name mview

       from dba_mviews

       where last_refresh_date between sysdate -30 and sysdate -1

       and fast_refreshable = 'DML';

dummy pls_integer;

begin

REM Uncomment below line if EBS database

REM select MRP_AP_REFRESH_S.NEXTVAL into dummy from dual;

for rmv2ref in mv2ref loop

begin

    dbms_mview.refresh(rmv2ref.mview, 'F');

    dbms_output.put_line(rmv2ref.mview ||' Refreshed at '|| to_char(systimestamp));

exception

when others then

    dbms_output.put_line(rmv2ref.mview ||' Errored at '|| to_char(systimestamp));

    dbms_output.put_line('    Error: '|| sqlcode ||':'||sqlerrm);

end;

end loop;

end;

/

Enjoy!

Query - Tablespace Max Size

I sometimes see concerned emails from developers that they are running out of space in tablespace and the DBA need to do something immediately. The DBA will start wondering we have monitoring in place, then how come it did not catch an out of space situation, but a user/developer did...

For you developer/user, if you are wondering if your database/tablespace is running out of space, you may need to check if the tablespace has autoextensible data files...

Here is a simple query, that could be used to find the current size of the tablespace and how much it can grow using the autoextensible feature of data file.


  SELECT tablespace_name,
         ROUND (SUM (bytes) / 1048576) curr_size,
         ROUND (SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0)))/ 1048576)  growable
    FROM dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name;

Rename Diskgroup in ASM

Rename ASM Diskgroup [11gR2]

In 11gR2 it is possible to rename an ASM diskgroup. This is especially useful when performing database copy using OS level LUN mirroring technologies.

The renamedg command is used to rename diskgroups. It has the following options:

$ renamedg -help

Parsing parameters..

phase                           Phase to execute,

                                (phase=ONE|TWO|BOTH), default BOTH

dgname                          Diskgroup to be renamed

newdgname                       New name for the diskgroup

config                          intermediate config file

check                           just check-do not perform actual operation,

                                (check=TRUE/FALSE), default FALSE

confirm                         confirm before committing changes to disks,

                                (confirm=TRUE/FALSE), default FALSE

clean                           ignore errors,

                                (clean=TRUE/FALSE), default TRUE

asm_diskstring                  ASM Diskstring (asm_diskstring='discoverystring',

                                'discoverystring1' ...)

verbose                         verbose execution,

                                (verbose=TRUE|FALSE), default FALSE

keep_voting_files               Voting file attribute,

                                (keep_voting_files=TRUE|FALSE), default FALSE

To rename a diskgroup, it must be stopped [or unmounted].

$ srvctl stop diskgroup -g oops1

$ renamedg dgname=oops1 newdgname=good1 verbose=true

$ srvctl start diskgroup –g good1

You must manually rename all database files on the diskgroup to reflect the new name using ALTER DATABSE RENAME FILE command.

Even after successful rename command, old diskgroup resources in Oracle Restart must be manually removed using the srvctl command.

To Read More:  http://download.oracle.com/docs/cd/E11882_01/server.112/e16102/asmdiskgrps.htm#CHDJAGHE

Oracle11gR1 - To Read...

 Database Replay - OTN  Documentation

 Interval and Reference Partitions – Paper   Documentation

 SQL Query Result Cache - OTN   Documentation

SQL Performance Analyzer – OTN  Documentation

 SQL Plan Baseline – OTN  Documentation

Automatic SQL Tuning Enhancements - Documentation

Snapshot Standby - Documentation

Active Data Guard (Real-time query) - Documentation

OLTP Table Compression – OTN-OOW   Documentation

AutoTask Infrastructure - Documentation

Virtual Columns - Documentation

Flashback Data Archive (Oracle Total Recall) - OTN Documentation

Flashback Transaction - OTN  Documentation

Automatic Diagnostic Repository & ADRCI - Documentation

Data Recovery Advisor - OTN  Documentation

Active DB Duplication - Documentation

Multi-section backups - Documentation

Lightweight Jobs in Scheduler - Documentation

Fine grained dependency tracking - Documentation