Monday, March 22, 2010

Freeing up db_recovery_file_dest reclaimable space.

I temporarily reduced db_recovery_file_dest_size to force the database to free reclaimable space from recovery_file_dest.

This reduced disk usage of ASM_ORADATA217 by over 320GB.


SYS>show parameter recovery

NAME TYPE VALUE
----------------------------------- ------------------------- ---------------------
db_recovery_file_dest string +ASM_ORADATA217
db_recovery_file_dest_size big integer 375G

SYS>select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 88.7 83.73 1991


SYS>alter system set db_recovery_file_dest_size=20G;

System altered.

SYS>alter system set db_recovery_file_dest_size=375G;

System altered.

SYS>alter system switch logfile;

System altered.

SYS>select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 5.32 .35 135

Tuesday, March 16, 2010

DBMS_SHARED_POOL.PURGE

To clear out a specific cursor from the shared pool.

SYS AS SYSDBA> alter session set events '5614566 trace name context forever';

Session altered.

SYS AS SYSDBA> select sql_text,address, hash_value, executions, loads, version_count, invalidations, parse_calls
from v$sqlarea where version_count > 100;

SQL_TEXT
--------------------------------------------------------------------------------
ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
---------------- ---------- ---------- ---------- ------------- -------------
PARSE_CALLS
-----------
SELECT BID, OFFER, PRICE_TIME FROM PRICE_LATEST WHERE INSTRUMENT_ID = :B1
07000001D275CE98 1404435209 1752 1 473 0
1752


SYS AS SYSDBA> exec sys.dbms_shared_pool.purge('&address, &hash_value','c');
Enter value for address: 07000001D275CE98
Enter value for hash_value: 1404435209

PL/SQL procedure successfully completed.

SYS AS SYSDBA> select sql_text,address, hash_value, executions, loads, version_count, invalidations, parse_calls
from v$sqlarea where version_count > 100;


no rows selected

Friday, March 12, 2010

DBMS_ROWID

TEST @dummy1> insert into test1 values (1);

1 row created.

TEST @dummy1> insert into test1 values (2);

1 row created.

TEST @dummy1> select rowid as therowid, id,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid,1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot
from test1 where id <= 2;

THEROWID ID OBJID FILENUM BLOCKNUM ROWSLOT
------------------ ---------- ---------- ---------- ---------- ----------
AAAM2dAAEAAAABIAAA 1 52637 4 72 0
AAAM2dAAEAAAABIAAB 2 52637 4 72 1


TEST @dummy1> select dbms_rowid.rowid_create(1,52637,4,72,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAM2dAAEAAAABIAAA

TEST @dummy1> select * from test1 where rowid = dbms_rowid.rowid_create(1,52637,4,72,0);

ID
----------
1

Test of parameter NOSYSDBA

# create the password file with nosysdba=n
[dummy1]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password force=y nosysdba=n

# test password file authenticated logon via sqlnet as sysdba
[dummy1]$ sqlplus sys/password@dummy as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:02:59 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYS AS SYSDBA@dummy>

# create the password file with nosysdba=y
[dummy1]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password force=y nosysdba=y

# test password file authenticated logon via sqlnet as sysdba
[dummy1]$ sqlplus sys/password@dummy as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:04:33 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:

# test password file authenticated logon via sqlnet with no sysdba

[dummy1]$ sqlplus sys/password@dummy

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:08 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


Enter user-name:

# test dictionary authenticated logon via sqlnet with no sysdba

[dummy1]$ sqlplus system/password@dummy

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:34 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYSTEM @dummy>

# test local OS authentication as sysdba

[dummy1]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:58 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYS AS SYSDBA@dummy1>

Monday, March 8, 2010

using DBMS_SESSION.SET_CONTEXT to store variables

> CREATE CONTEXT my_context using my_context_proc;

Context created.

> create or replace procedure my_context_proc as
begin
dbms_session.set_context('MY_CONTEXT', 'APP_USER', 'ROBERT');
end;
/

Procedure created.

> exec my_context_proc

PL/SQL procedure successfully completed.

> select sys_context('MY_CONTEXT','APP_USER') from dual;

SYS_CONTEXT('MY_CONTEXT','APP_USER')
-------------------------------------
ROBERT

Tuesday, March 2, 2010

Duplicate database from active

To duplicate an 11g database from active.

1. create the duplicate pfile, including db_file_name_convert, log_file_name_convert
2. startup nomount on the duplicate
3. add tnsnames.ora entries for target and duplicate on both servers
4. copy password file from target to duplicate
5. use rman to run the duplicate

rman target sys/password@target auxiliary sys/password@auxiliary log dup.log
DUPLICATE TARGET DATABASE
TO duplicate
FROM ACTIVE DATABASE
SKIP TABLESPACE 'MYTABLESPACE'
NOFILENAMECHECK;

Create a Dataguard Broker Configuration

To create a dataguard broker standby configuration for physical standby on RAC, with preferred apply instances.

dgmgrl
connect sys/
remove configuration;
create configuration 'PROD' as primary database IS 'prod' connect identifier is prod;
add database 'stby' as connect identifier is stby maintained as physical;
edit database prod set property logxptmode='ASYNC';
edit database stby set property logxptmode='ASYNC';
edit database prod set property PreferredApplyInstance='prod1';
edit database stby set property PreferredApplyInstance='stby1';
edit database prod set property MaxFailure=15;
edit database stby set property MaxFailure=15;
edit database prod set property ReopenSecs=60;
edit database stby set property ReopenSecs=60;
edit database prod set property LogArchiveMaxProcesses=10;
edit database stby set property LogArchiveMaxProcesses=10;
enable configuration;