ORA-BLOG

ORA-01555: snapshot too old during datapump export

2011-10-05T20:29:00.000-07:00

Datapump Export can fail with the following error when the parameter FLASHBACK_TIME is used

ORA-31693: Table data object "SCOTT"."EMPLOYEES" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 271 with name "_SYSSMU271$" too small
ORA-00604: error occurred at recursive SQL level 3
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1^@
ORA-00604: error occurred at recursive SQL level 3
ORA-08180: no snapshot found based on specified ti

The reason for this is the database parameter UNDO_RETENTION is set to low compared to the time set with FLASHBACK_TIME.

Example:

- export starts at 10:00 pm and ends at 00:00 am. Flashback time start at 10:00 pm of the same days when export starts.

Expdp may need 2 hours to complete and undo_retention is set to 2040 that means 34 minutes. So the information in undo is recycled.

Solution
Set UNDO_RETENTION to a value sufficient to get all info required by the flashback time.
Also consider increasing undo tablespace size.

Trigger on database to capture ORA errors

2011-10-04T09:06:00.001-07:00

You might want to track ORA errors on your database so that whenever an oracle error comes,you may want the error code and error description to be logged into some table. This will be required when you see your application just exists saying some error but you did not see any ORA error in application log file or console.

In this cases, what you can do is write a trigger on the database server to log the error code, error statement and error description to a table when ORA error comes.

To implement this, first create a table which will hold the ORA error,statement and error description. This can be created in SYS schema.

CREATE TABLE stats$error_log (
        err_dt          TIMESTAMP,
        db_user         VARCHAR2(30),
        msg_stack       VARCHAR2(2000),
        sqltxt          VARCHAR2(1000))
tablespace users;

Now, create a trigger on the database server error.

CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
          v_sqltext VARCHAR2(1000);
          nl_sqltext ora_name_list_t;
BEGIN
          -- Capture entire error text
          FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP
            v_sqltext := v_sqltext || nl_sqltext(i);
          END LOOP;
          INSERT INTO STATS$ERROR_LOG
          (err_dt, db_user, msg_stack, sqltxt)
          VALUES
          (systimestamp,
           sys.login_user,
           dbms_utility.format_error_stack, v_sqltext);
END log_server_errors;
/

For further reference you can find.
Simple version:
http://www.adp-gmbh.ch/ora/sql/trigger/servererror.html

Slightly more fancy version:
http://blog.sydoracle.com/2011/02/using-oracles-servererror-to-catch.html

How to pin nodes in RAC

2011-09-30T10:30:00.001-07:00

The following steps can be used to pin nodes in RAC.
Pinning a node means that the association of a node name with a node number is fixed. If a node is not pinned,node number may change if the lease expires while it is down. The lease of a pinned node never expires.

To find whether a node in a cluster is pinned,use the olsnodes command.

[grid@raclinux1 stage10g]$ olsnodes -t
raclinux1 Unpinned
raclinux2 Unpinned

Use the pin command to pin the nodes

[root@raclinux1 bin]# ./crsctl pin css -n raclinux1
CRS-4664: Node raclinux1 successfully pinned.
[root@raclinux1 bin]#

[root@raclinux2 bin]# ./crsctl pin css -n raclinux2
CRS-4664: Node raclinux2 successfully pinned.
[root@raclinux2 bin]#

[grid@raclinux1 stage10g]$ olsnodes -t
raclinux1 Pinned
raclinux2 Pinned

Using SYSDBA for datapump export and import

2011-09-27T18:37:00.001-07:00

Here is a quote from MOS on the usage oof SYSDBA for export and import

*How to Connect AS SYSDBA when Using Export or Import

[ID 277237.1]*

SYSDBA is used internally in the Oracle database and has specialized functions. Its behavior is not the same as for generalized users. For example, the SYS user cannot do a transaction level consistent read (read-only transaction). Queries by SYS will return changes made during the

transaction even if SYS has set the transaction to be READ ONLY.

Therefore export parameters like CONSISTENT, OBJECT_CONSISTENT, FLASHBACK_SCN, and FLASHBACK_TIME cannot be used. Starting with Oracle10g, the export shows a warning that the export is not

consistent when the export is started with CONSISTENT=Y and connects to the database with the user SYS (or as SYSDBA):

EXP-00105: parameter CONSISTENT is not supported for this user

Note that Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). For export this means that the export of

table data is consistent. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. And if a table is partitioned, each partition is exported as a separate transaction. If a nested table or a partitioned table was updated during the

export, the data that is exported while connected as the SYS schema could be inconsistent.

This could be reason when you see 'duplicate key on index' and related errors when you perform datapump import using impdp. For eg :- There were some duplicate value on index of some tables which were taken using datapump export as sysdba with flashback parameters. The table does not have duplicate values in production.

Typically, there is no need to invoke Export or Import as SYSDBA, except in the following situations:

- at the request of Oracle technical support;

- when exporting a transportable tablespace set with the old style export utility (Oracle9i and Oracle8i);

- when importing a transportable tablespace set with the old-style import utility (Oracle10g, Oracle9i, and Oracle8i)."

Privileges required for generating AWR reports

2011-09-26T19:04:00.000-07:00

Some times you may have a requirment to generate AWR reports using non - administrator schemas.

The following privileges are required for a SCHEMA to generate AWR reports.

grant SELECT ON SYS.V_$DATABASE to SCHEMA;
SELECT ON SYS.V_$INSTANCE to SCHEMA;
EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY to SCHEMA;
SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE to SCHEMA;
SELECT ON SYS.DBA_HIST_SNAPSHOT to SCHEMA;
GRANT ADVISOR to SCHEMA;

Change oracle listener log file

2011-09-26T18:36:00.001-07:00

Listener log files may grow very big in size and you may sometimes want to delete the existing log file to free up some space. When you try to delete the log file in windows, you will get file already in use message.

To solve this, you can change the listener log file using the below commands

lsnrctl

set log_file listener1.log

Where listener1.log is the new log file, now you can delete the old log file.

You can also turn off listener logging as below

lsnrctl

set log_status off

Create a Local Listener on non default port ( ports other than 1521)

2011-09-25T07:01:00.000-07:00

On some circumstances you may need to have separate listeners running on seperate ports for your databases.When you create a database, the default listener will be created and listens on port 1521. There may be cases when you want to run multiple listeners for your databases.

If you want PMON to register with a local listener that does not use TCP/IP, port 1521, configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.

For a shared server environment, you can alternatively use the LISTENER attribute of the DISPATCHERS parameter in the initialization parameter file to register the dispatchers with a nondefault local listener. Because both the LOCAL_LISTENER parameter and the LISTENER attribute enable PMON to register dispatcher information with the listener, it is not necessary to specify both the parameter and the attribute if the listener values are the same.

The steps are as follows.

1. Create new non default listener
    Create a new listener using $ORACLE_HOME/netca for this.

2.Set the LOCAL_LISTENER parameter for your database. You can set this in two ways.
a ) Initialization parameter file (PFILE)
     a ) Ensure you have ORACLE_HOME and ORACLE_SID environment variable set
     b) sqlplus / as sysdba
     c) create pfile='$ORACLE_HOME/dbs/init.ora from spfile;
     d) Open $ORACLE_HOME/dbs/init.ora and add the following entry
            *.local_listener=listener1
                   where listener1 is the name you want to give for your new listener.
   e) shut immediate;
     f) startup pfile='$ORACLE_HOME/dbs/init.ora'

b) Server parameter file ( SPFILE )
     a ) Ensure you have ORACLE_HOME and ORACLE_SID environment variable set
     b) sqlplus / as sysdba
   c) alter system set local_listener=listener1 scope=spfile sid='*';
     d) shut immediate;
     e) startup;

2.Resolve listener name with tnsnames.ora
listener name ( in this case listener1) is then resolved to the listener protocol addresses through a naming method, such as a tnsnames.ora file on the database server.

Add the following entry in your database server tnsnames.ora

listener1=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST= )(PORT=<port you wish to configure>)))

3.Restart the database

a ) Ensure you have ORACLE_HOME and ORACLE_SID environment variable set
     a) sqlplus / as sysdba
   b) shut immediate;
     c) startup;

You will see that database is registered with new listener.

Refer : http://download.oracle.com/docs/cd/B13789_01/network.101/b10775/listenercfg.htm

Change SYSMAN account password Oracle RAC / Single Instance database

2011-09-24T07:56:00.000-07:00

Following steps can be used to change SYSMAN account password for Oracle RAC / single instance database. This can be done online.

Login to the server where target database runs as oracle software owner os user.

ibs-ash-sr133 oraaires [MYDBSTG2]:emctl status dbconsole
Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.
ibs-ash-sr133 oraaires [MYDBSTG2]:sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 12 11:17:08 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------------------------
db_unique_name string MYDBSTG
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

ibs-ash-sr133 oraaires [MYDBSTG2]:export ORACLE_UNQNAME=MYDBSTG
ibs-ash-sr133 oraaires [MYDBSTG2]:emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /projects/aires/product/11.2.0/aires_stg/ibs-ash-sr133_MYDBSTG/sysman/log

ibs-ash-sr133 oraaires [MYDBSTG2]:emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
... Stopped.
ibs-ash-sr133 oraaires [MYDBSTG2]:emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.

ibs-ash-sr133 oraaires [MYDBSTG2]:sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 12 11:19:42 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter user sysman identified by we#t04q;

User altered.

SQL> conn sysman/we#t04q
Connected.
SQL> show user
USER is "SYSMAN"
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

ibs-ash-sr133 oraaires [MYDBSTG2]:emctl setpasswd dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Please enter new repository password:
Repository password successfully updated.

ibs-ash-sr133 oraaires [MYDBSTG2]:emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ....... started.
------------------------------------------------------------------
Logs are generated in directory /projects/aires/product/11.2.0/aires_stg/ibs-ash-sr133_MYDBSTG/sysman/log
ibs-ash-sr133 oraaires [MYDBSTG2]:emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.2.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
https://ibs-ash-sr133.ibsdc.com:5500/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /projects/aires/product/11.2.0/aires_stg/ibs-ash-sr133_MYDBSTG/sysman/log
ibs-ash-sr133 oraaires [MYDBSTG2]:

Reference : How To Change the Password of the Database User Sysman (DB Control Repository Schema) [ID 259379.1]

Unix : Split / Print / Grep lines before and after search pattern

2011-09-24T07:39:00.000-07:00

You can use ggrep command to print or grep a certain number of lines before or after a particular search string / pattern from a file.
This will be quite useful when you know a search string / pattern, for eg : ORA-00257: archiver error. Connect internal only. You might want to see from when onwards this error has been reported in alert log. The log might be very huge , a vi / tail might be time consuming in this case. Use the below command.

/usr/sfw/bin/ggrep -A 5 -B 5 'ORA-19815:' alert_idckf.log > 1

Here -A 5 and -B 5 , where 5 stands for number of lines before and after the search string "ORA-19815:'"

/usr/sfw/bin/ggrep -B 10 'ORA-19815: WARNING: db_recovery_file_dest_size of 26754416640 bytes is 100.00% used' alert_idckf.log > 1

Here , the search is only for printing 10 lines after the search string 'ORA-19815: WARNING: db_recovery_file_dest_size of 26754416640 bytes is 100.00% used' from the file alert_idckf.log. The output is redirected to 1, which can be viewed.

Reboot-less node fencing in Oracle Clusterware 11g Release 2

2011-09-22T19:55:00.000-07:00

There has been several improvements in the node eviction and split brain scenarios with Oralce 11G R2 RAC.

Oracle Clusterware uses a STONITH (Shoot The Other Node In The Head) comparable fencing algorithm to ensure data integrity in cases, in which cluster integrity is
endangered and split-brain scenarios need to be prevented. In case of Oracle Clusterware, this means that a local process enforces the removal of one or more nodes from the cluster (fencing).
Until Oracle Clusterware 11g Release 2, Patch Set One (11.2.0.2) the fencing of a node was performed by a “fast reboot” of the respective server. A “fast reboot” in this context summarizes a shutdown and restart procedure that does not wait for any IO to finish or for file systems to synchronize on shutdown. With Oracle Clusterware 11g Release 2, Patch Set One (11.2.0.2) this mechanism has been changed in order to prevent such a reboot as much as possible.

Already with Oracle Clusterware 11g Release 2 this algorithm was improved so that failures of certain, Oracle RAC-required subcomponents in the cluster do not necessarily cause an
immediate fencing (reboot) of a node. Instead, an attempt is made to clean up the failure within the cluster and to restart the failed subcomponent. Only, if a cleanup of the failed component appears to be unsuccessful, a node reboot is performed in order to force a cleanup.

With Oracle Clusterware 11g Release 2, Patch Set One (11.2.0.2) further improvements were made so that Oracle Clusterware will try to prevent a split-brain without rebooting the node. It thereby implements a standing requirement from those customers, who were requesting to preserve the node and to prevent a reboot, since the node runs applications not managed by Oracle Clusterware, which would otherwise be forcibly shut down by the reboot of a node.

With the new algorithm and when a decision is made to evict a node from the cluster, Oracle Clusterware will first attempt to shutdown all resources on the machine that was chosen to be the subject of an eviction. Especially IO generating processes are killed and it is ensured that those processes are completely stopped before continuing. If, for some reason, not all resources can be stopped or IO generating processes cannot be stopped completely, Oracle Clusterware will still perform a reboot or use IPMI to forcibly evict the node from the cluster.
If all resources can be stopped and all IO generating processes can be killed, Oracle Clusterware will shut itself down on the respective node, but will attempt to restart after the stack has been stopped. The restart is initiated by the Oracle High Availability Services Daemon, which has been introduced with Oracle Clusterware 11g Release 2.

Redundant Interconnect Usage in 11G R2 RAC

2011-09-22T19:52:00.000-07:00

We have thought of implementing this new feature with Oracle 11G as we have frequent private NIC down events which further results in node evictions.

Redundant Interconnect without any 3rd-party IP failover technology (bond, IPMP or similar) is supported natively by Grid Infrastructure starting from 11.2.0.2. Multiple private network adapters can be defined either during the installation phase or afterward using the oifcfg. Oracle Database, CSS, OCR, CRS, CTSS, and EVM components in 11.2.0.2 employ it automatically.

Grid Infrastructure can activate a maximum of four private network adapters at a time even if more are defined. The ora.cluster_interconnect.haip resource will start one to four link local HAIP on private network adapters for interconnect communication for Oracle RAC, Oracle ASM, and Oracle ACFS etc.

Grid automatically picks link local addresses from reserved 169.254.*.* subnet for HAIP, and it will not attempt to use any 169.254.*.* address if it's already in use for another purpose. With HAIP, by default, interconnect traffic will be load balanced across all active interconnect interfaces, and corresponding HAIP address will be failed over transparently to other adapters if one fails or becomes non-communicative. .

The number of HAIP addresses is decided by how many private network adapters are active when Grid comes up on the first node in the cluster . If there's only one active private network, Grid will create one; if two, Grid will create two; and if more than two, Grid will create four HAIPs. The number of HAIPs won't change even if more private network adapters are activated later, a restart of clusterware on all nodes is required for new adapters to become effective

New HAIP reource

--------------------------------------------------------------------------------
NAME           TARGET STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE ONLINE       ibs-ash-sr118        Started
ora.cluster_interconnect.haip
      1        ONLINE ONLINE       ibs-ash-sr118
ora.crf
      1        ONLINE ONLINE       ibs-ash-sr118
ora.crsd
      1        ONLINE ONLINE       ibs-ash-sr118
ora.cssd
      1        ONLINE ONLINE       ibs-ash-sr118
ora.cssdmonitor
      1        ONLINE ONLINE       ibs-ash-sr118
ora.ctssd
      1        ONLINE ONLINE       ibs-ash-sr118        OBSERVER
ora.diskmon
      1        ONLINE ONLINE       ibs-ash-sr118
ora.drivers.acfs
      1        ONLINE ONLINE       ibs-ash-sr118
ora.evmd
      1        ONLINE ONLINE       ibs-ash-sr118
ora.gipcd
      1        ONLINE ONLINE       ibs-ash-sr118
ora.gpnpd
      1        ONLINE ONLINE       ibs-ash-sr118
ora.mdnsd
      1        ONLINE ONLINE       ibs-ash-sr118

While in previous releases bonding, trunking, teaming, or similar technology was required to make use of redundant network connections between the nodes to be used as redundant,dedicated, private communication channels or “interconnect”, Oracle Clusterware now provides an integrated solution to ensure “Redundant Interconnect Usage”. This functionality is available starting with Oracle Database 11g Release 2, Patch Set One (11.2.0.2).

The Redundant Interconnect Usage feature does not operate on the network interfaces directly. Instead, it is based on a multiple-listening-endpoint architecture, in which a highly available virtual IP (the HAIP) is assigned to each private network (up to a total number of 4 interfaces).By default, Oracle Real Application Clusters (RAC) software uses all of the HAIP addresses for private network communication, providing load balancing across the set of interfaces identified as the private network. If a private interconnect interface fails or becomes non-communicative,then Oracle Clusterware transparently moves the corresponding HAIP address to one of the remaining functional interfaces

Oracle RAC Databases, Oracle Automatic Storage Management (clustered ASM), and Oracle Clusterware components such as CSS, OCR, CRS, CTSS, and EVM components employ
Redundant Interconnect Usage starting with Oracle Database 11g Release 2, Patch Set One (11.2.0.2)

Steps
=============

# $GRID_HOME/bin/oifcfg getif
eth0 10.2.156.0 global public
eth1 192.168.12.0 global cluster_interconnect

The interfaces that are currently stored in the GPnP profile, their subnets, and their role (public or cluster_interconnect) are displayed.

2. Add the remaining LLT links to the GPnP profile:
# $GRID_HOME/bin/oifcfg setif -global \
eth2/192.168.12.0:cluster_interconnect

3. Verify that the correct interface subnet is in use:
# $GRID_HOME/bin/oifcfg getif
eth0 10.2.156.0 global public
eth1 192.168.12.0 global cluster_interconnect
eth2 192.168.2.0 global cluster_interconnect

4.You must restart Oracle Clusterware on all members of the cluster when you make global changes. For local changes, you only need to perform a node restart.

Interconnect changes for the database occur at instance startup. However, the interconnect for Oracle Clusterware might be different.

11gR2 Grid Infrastructure Redundant Interconnect and ora.cluster_interconnect.haip [ID 1210883.1]
How to Modify Private Network Interface in 11.2 Grid Infrastructure [ID 1073502.1]
http://download.oracle.com/docs/cd/B28359_01/rac.111/b28255/oifcfg.htm
How to Change Interconnect/Public Network (Interface or Subnet) in Oracle Clusterware [ID 283684.1]
[11gR2 Grid Infrastructure Redundant Interconnect and ora.cluster_interconnect.haip [ID 1210883.1]]

Known Issues
================

A few bug fixes for haip has come with 11.2.0.3

Drop and Create enterprise manager database control ( emca) for 11G R2 RAC

2011-09-22T09:38:00.000-07:00

With 11G R2 , drop ,creation, de-config,re creation of enterprise manager database console does not put the database in quiesce mode.
The followed to drop , create , de config and re create emca for 11G R2 single instance or RAC.

I have tried ./bin/emca -config dbcontrol db -repos drop to drop enterprise manager repository objects, but it got hung at some point. Finally, I have tried the manual drop option which is very simple and you are almost sure that it will work !

1.Drop db control repository manually

1. stop dbconsole , if running and the following process from ORACLE_HOME (from each node)

$ps -ef | grep console
$ps -ef| grep emwd
$ps -ef | grep emagent
$ps -ef |grep java

2. Set environment variables

SET ORACLE_HOSTNAME=< Hostname of your machine "
SET ORACLE_HOME=< Location where your database is installed >
SET ORACLE_SID=< name of your database "
SET PATH=%ORACLE_HOME%\bin;%PATH%

Note : You will have to replace SET command with EXPORT for *nix environments

3. Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:

a. drop user sysman cascade;
b. drop role MGMT_USER;
c. drop user MGMT_VIEW cascade;
d. drop public synonym MGMT_TARGET_BLACKOUTS;
e. drop public synonym SETEMVIEWUSERCONTEXT;
f. drop public synonym MGMT_target_blackouts;
g. drop public synonym mgmt_severity_array;
h. drop public synonym mgmt_guid_obj;

Verify any left over object owned by SYSMAN

SQL> SELECT owner,TABLE_NAME, synonym_name name FROM dba_synonyms WHERE table_owner = 'SYSMAN';

if yo notice any public synonym or any rows returned by the above query .

SQL>

DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/

4.Move or rename config folders on each node :

$rm -rf $ORACLE_HOME/
$rm -rf $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole__

You would replace the values in the <> with the correct values.

5.Create DBconsole repos and configure dbconsole : (Use -cluster when its RAC DB)

$export ORACLE_HOME= (Please give the physical path avoid symbolic links)
$

2.Create emca repository objects

Execute the below command to create emca db console for RAC
./bin/emca -config dbcontrol db -repos create -cluster

-bash-3.00$ ./bin/emca -config dbcontrol db -repos create -cluster

STARTED EMCA at Aug 19, 2011 8:47:46 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database unique name: bvnprd
Service name: bvnprd
Listener ORACLE_HOME [ /project/11.2.0/grid ]:
Password for SYS user: ^R < Give SYS password in single quotes if there are special characters >
Password for SYS user: ^R
Password for SYS user: ^R
Password for SYS user:
Database Control is already configured for the database bvnprd
You have chosen to configure Database Control for managing the database bvnprd
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: yes
Password for DBSNMP user:
Password for SYSMAN user:
Cluster name: crs
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /project/11.2.0/grid ]:
ASM port [ 1521 ]:
ASM username [ ASMSNMP ]: SYS < You can also give ASMSNMP user and password below >
ASM user password:
Invalid username/password or database/scan listener not up or database service is not registered with scan listener.
ASM user password:
Invalid username/password or database/scan listener not up or database service is not registered with scan listener.
ASM user password: 3LHj*5x
Invalid username/password or database/scan listener not up or database service is not registered with scan listener.
ASM user password:
Invalid username/password or database/scan listener not up or database service is not registered with scan listener.
ASM user password: LHj*5x
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /project/oracle/orabase/product/11.2.0/dbhome_1

Database instance hostname ................ dom-ash-sr147.domdc.com
Listener ORACLE_HOME ................ /project/11.2.0/grid
Listener port number ................ 1521
Cluster name ................ crs
Database unique name ................ bvnprd
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /project/11.2.0/grid
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ SYS

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: yes
Aug 19, 2011 9:19:52 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /project/oracle/orabase/cfgtoollogs/emca/bvnprd/emca_2011_08_19_08_47_45.log.
Aug 19, 2011 9:19:55 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Aug 19, 2011 9:20:02 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Aug 19, 2011 9:30:49 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Aug 19, 2011 9:31:06 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Aug 19, 2011 9:31:49 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Aug 19, 2011 9:31:50 AM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /project/oracle/orabase/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_dom-ash-sr147_bvnprd to remote nodes ...
Aug 19, 2011 9:31:53 AM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /project/oracle/orabase/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_dom-ash-sr149_bvnprd to remote nodes ...
Aug 19, 2011 9:32:02 AM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /project/oracle/orabase/product/11.2.0/dbhome_1/dom-ash-sr147_bvnprd to remote nodes ...
Aug 19, 2011 9:32:11 AM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /project/oracle/orabase/product/11.2.0/dbhome_1/dom-ash-sr149_bvnprd to remote nodes ...
Aug 19, 2011 9:32:16 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Location /project/oracle/orabase/product/11.2.0/dbhome_1/EMStagePatches_bvnprd is not shared. Software library could not be configured. Provisioning archives will not be deployed. Please configure it manually.
Aug 19, 2011 9:32:16 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Aug 19, 2011 9:32:32 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Aug 19, 2011 9:33:10 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Aug 19, 2011 9:33:10 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://dom-ash-sr147.domdc.com:1158/em <<<<<<<<<<<
Aug 19, 2011 9:33:12 AM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
**************** Current Configuration ****************
INSTANCE            NODE           DBCONTROL_UPLOAD_HOST
----------        ----------        ---------------------

bvnprd            dom-ash-sr147     dom-ash-sr147.domdc.com
bvnprd            dom-ash-sr149     dom-ash-sr147.domdc.com

Aug 19, 2011 9:33:12 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /project/oracle/orabase/product/11.2.0/dbhome_1/dom-ash-sr147_bvnprd/sysman/config/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Aug 19, 2011 9:33:12 AM
-bash-3.00$

Calculate next run time in dbms_scheduler.create_job (job_interval )

2011-09-22T09:10:00.000-07:00

You control when and how often a job repeats by setting the repeat_interval attribute of the job itself or of the named schedule that the job references. You can set repeat_interval with DBMS_SCHEDULER package procedures or with Enterprise Manager.

The question I had was what will be the next execution date of a job ? Will it be based on start time of one instance of job or completion time of one instance of job ?

According to Oracle Documentation http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/scheduse004.htm#i1023312

The result of evaluating the repeat_interval is a set of timestamps.The Scheduler runs the job at each timestamp. Note that the start date from the job or schedule also helps determine the resulting set of timestamps. (See Oracle Database PL/SQL Packages and Types Reference for more information about repeat_interval evaluation.) If no value for repeat_interval is specified, the job runs only once at the specified start date.

Immediately after a job is started, the repeat_interval is evaluated to determine the next scheduled execution time of the job. It is possible that the next scheduled execution time arrives while the job is still running. A new instance of the job, however, will not be started until the current one completes.

This is something that I took sometime to figure out !

ASM DiskGroup NOT Mounted in 11G R2

2011-09-17T23:09:00.000-07:00

I was just going to create a 11G R2 RAC database on my RHEL 5.4 two node cluster. But dbca exists stating that ASM diskgroup where I am planning to put my database files are not mounted.

When I checked status using GRID_HOME/asmca,it shows the disks as mounted. But still dbca reports the same problem

I wanted to login to ASM instance and query the V$ASM_DISKGROUP.

On Node 1, I saw that ASM instance is up.

n310 oracle [+ASM1]:ps -ef | grep smon
    oracle 355 30041 0 15:06 pts/2 00:00:00 grep smon
    oracle 6730 1 0 12:51 ? 00:00:00 asm_smon_+ASM1
    oracle 14649 1 0 14:30 ? 00:00:00 ora_smon_nfr1
    oracle 14968 1 0 14:31 ? 00:00:00 ora_smon_nfr3

I thought I will query the V$ASM_DISKGROUP dynamic view to see the state of my ASM diskgroups.

Check the ORACLE_HOME variable from which ASM instance was started.

From the above grep command , I got the PID 6730 for ASM instance.
Go to /proc/PID/environ and check the environment variables with which the ASM instance was started.

n310 oracle [+ASM1]:cat /proc/6730/environ
Here I can see the ORACLE_HOME as /app/oracle/grid.

export ORACLE_HOME=app/oracle/grid

Since now I got the ORACLE_HOME, Lets see what V$ASM_DISKGROUP shows

n310 oracle [+ASM1]:export ORACLE_SID=+ASM1
    n310 oracle [+ASM1]:./bin/sqlplus / as sysasm

    SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 15 16:12:43 2011
    Copyright (c) 1982, 2009, Oracle. All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
    With the Real Application Clusters and Automatic Storage Management options

    SQL> select NAME,TOTAL_MB,FREE_MB,STATE from v$ASM_DISKgroup;

    NAME TOTAL_MB FREE_MB STATE
    —————————— ———- ———- ———–
    DATA 8610 7684 MOUNTED
    VOL 0 0 DISMOUNTED

    SQL> alter diskgroup VOL mount;

    Diskgroup altered.

    SQL> select NAME,TOTAL_MB,FREE_MB,STATE from v$ASM_DISKgroup;

    NAME TOTAL_MB FREE_MB STATE
    —————————— ———- ———- ———–
    DATA 8610 7684 MOUNTED
    VOL 38170 37980 MOUNTED

Here, When I logged in as sysdba,I got the following error.

    SQL> alter diskgroup VOL mount;
    alter diskgroup VOL mount
    *
    ERROR at line 1:
    ORA-15032: not all alterations performed
    ORA-15260: permission denied on ASM disk group

So, I used SYSASM to do ASM management operations.

When I quered the DISKGROUP status in the second node, it shows as MOUNTED.

Find files created in last 24 hours in Unix

2011-09-17T23:01:00.000-07:00

I just had a requirement recently to start a process and see which all files it write when it is started.
I used a ‘find’ command from the home directory for this.

find . -mtime -1 -print

n310 oracle [CHANGEME]:find . -mtime -1 -print
./bin
./dbs
./install
./install/.ee

Table not present in datapump export 11G R2 - Deferred Segment Creation

2011-09-17T22:57:00.000-07:00

After upgrading database to 11G R2 developers keep complaining that some tables doesn’t come in the export file.They noticed it when they imported this and found that some objects were not in a compiled state.When they tried to compile, they came to know that tables was not imported. There is no error in the export / import log, but some tables are not exported.
BTW, they used imp /exp utilities.
The tables which were not exported were empty- which means they had zero rows in it.

Understand Deferred Segment Creation

Beginning with Oracle Database 11g Release 2, when you create heap-organized tables in a locally managed tablespace, the database defers table segment creation until the first row is inserted. In addition, segment creation is deferred for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table.This means that,no segments are allocated until a row is inserted to these table.You can verify this by querying user_tables

SQL> SELECT table_name, segment_created FROM user_tables where rownum < 4;

The advantages of this space allocation method are the following:

It saves a significant amount of disk space in applications that create hundreds or thousands of tables upon installation, many of which might never be populated
It reduces application installation time. There is a small performance penalty when the first row is inserted, because the new segment must be created at that time.

Here is how you can solve this problem

Set the parameter DEFERRED_SEGMENT_CREATION initialization parameter to FALSE, which is TRUE by default
ALTER table MOVE for the tables which are returned from the above query
INSERT a record to these tables and then rollback

Script to find duplicates for primary key or Unique Index

2011-09-17T21:42:00.000-07:00

This script will help you to find duplicate values coming in any primary key or unique key or unique index fields.
This will be very useful when you encounter any issues during datapump export or import, when a unique index creation fails with "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found"

REM This is an example SQL*Plus Script to delete duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt 'Table: '
prompt
select 'Table '||upper('&&t') from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If more than
prompt one column is specified , you MUST separate with commas.
prompt
accept c prompt 'Column(s): '
prompt
delete from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/

Find Explain Plan for a given sql_id

2011-09-17T08:40:00.000-07:00

You can check the explain plan for a given sql_id using following commands.

Suppose the sql_id given was ’6rata4x2qurst’.
The source of sql_id can be a badly performing query from statspack, awr etc.

SQL> select sql_fulltext from gv$sql where sql_id=’6rata4x2qurst’;
SQL_FULLTEXT
——————————————————————————–——————–——————–——————–——————–
select empno from emp where sal > 1000

SQL> explain plan for select empno from emp where sal > 1000;
Explained.
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
—————————————————————————–——–——–——–——–——————–——————–——————–

Plan hash value: 3956160932
————————————————————————–——–——–——————–——————–——————–——————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–——–——–——–——————–——————–——————–——————–
| 0 | SELECT STATEMENT | | 13 | 104 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 13 | 104 | 3 (0)| 00:00:01 |
————————————————————————–——————–——————–——————–——————–——————–
Predicate Information (identified by operation id):
———————————————————————–——————–——————–——————–——————–——————–
PLAN_TABLE_OUTPUT
—————————————————————————–——————–——————–——————–——————–——————–
1 – filter(“SAL”>1000)
13 rows selected.

Find Cluster Name in 11g R2 RAC

2011-09-17T06:21:00.000-07:00

When you configure enterprise manager db control in RAC, using emca command, it asks for cluster name . You can find the cluster name using cemutlo command.

You should change your path to GRID_HOME/bin directory and execute the following command.

-bash-3.00$ ./cemutlo -n
crs

ORA-00214 : Control file version inconsistent and ORA-01033:

2011-05-13T10:12:00.001-07:00

One fine morning , when I tried too connect to test database which is running in my local windows machine, I got the following error.

 
 sqlplus scott/tiger@TEST 
 ERROR: 
 ORA-01033: ORACLE initialization or shutdown in progress

Then I tried to login to the database as SYS and I found that the database is in mount state. So why is it not open ? I knew when the windows machine was restarted the database might have come up and went into mount state. Then why it did not open. Let's see.

Let me shutdown and startup it manually and see what went wrong.

 
 SQL> startup; 
 ORACLE instance started. 
  
 Total System Global Area  612368384 bytes 
 Fixed Size                  1250428 bytes 
 Variable Size             188746628 bytes 
 Database Buffers          415236096 bytes 
 Redo Buffers                7135232 bytes 
 ORA-00214: control file 'D:\APPLNS\ORACLE\ORADATA\TEST\CONTROL02.CTL' version 
 521 inconsistent with file 'D:\APPLNS\ORACLE\ORADATA\TEST\CONTROL01.CTL' 
 version 519

So this is the problem. One or more copy of control files are inconsistent. As you know,control files are multiplexed and here somehow the copy of control file inconsistent. So let's where are these control files. Remember my db is in mount state.

 
 SQL> show parameter control; 
  
 NAME                                 TYPE        VALUE 
 ------------------------------------ ----------- ------------------------------ 
 control_file_record_keep_time        integer     7 
 control_files                        string      D:\APPLNS\ORACLE\ORADATA\TEST\ 
                                                  CONTROL01.CTL, D:\APPLNS\ORACL 
                                                  E\ORADATA\TEST\CONTROL02.CTL, 
                                                  D:\APPLNS\ORACLE\ORADATA\TEST\ 
                                                  CONTROL03.CTL

Now what we have to do is startup the database with a single control file which is consistent.Remove the control file copies and start with only one control file.
If you use pfile then edit the CONTROL_FILES parameter from init.ora and modify it to include just one copy of control file.

If you use spfile then after issuing startup nomount use show control_files to see existing controlfiles inside spfile and then you can use ALTER SYSTEM SET CONTROL_FILES=file_name; in order to point just one copy of control file.

Here is my control_files parameter in init.ora

 
 *.control_files='D:\Applns\oracle\oradata\TEST\control01.ctl','D:\Applns\oracle\oradata\TEST\control02.ctl','D:\Applns\oracle\oradata\TEST\control03.ctl'

I have modified it to include just only one.

 
 *.control_files='D:\Applns\oracle\oradata\TEST\control01.ctl'

Now let's startup the instance with new pfile.

 
 SQL> startup pfile='D:\Applns\oracle\product\10.2.0\db_1\database\initTEST.ora' 
 ORACLE instance started. 
  
 Total System Global Area  612368384 bytes 
 Fixed Size                  1250428 bytes 
 Variable Size             188746628 bytes 
 Database Buffers          415236096 bytes 
 Redo Buffers                7135232 bytes 
 Database mounted. 
 Database opened. 
 SQL>

Bravo !!!!
You can see my database came up and is open.
Now, let's copy the good control file and make two copies.

 
 D:\Applns\oracle\oradata\TEST>dir CO* 
  Volume in drive D is Applns 
  Volume Serial Number is 876D-EFDD 
  
 Directory of D:\Applns\oracle\oradata\TEST 
  
 05/10/2011  10:54 AM         7,061,504 CONTROL01.CTL 
 05/05/2011  08:03 AM         7,061,504 CONTROL02.CTL 
 05/05/2011  08:03 AM         7,061,504 CONTROL03.CTL 
                3 File(s)     21,184,512 bytes 
                0 Dir(s)  45,936,365,568 bytes free 
  
 D:\Applns\oracle\oradata\TEST>del CONTROL02.CTL 
  
 D:\Applns\oracle\oradata\TEST>del CONTROL03.CTL 
  
 D:\Applns\oracle\oradata\TEST>copy CONTROL01.CTL CONTROL02.CTL 
         1 file(s) copied. 
  
 D:\Applns\oracle\oradata\TEST>copy CONTROL01.CTL CONTROL03.CTL 
         1 file(s) copied.

Don't forget to put back these entries in your pfile.

 
 *.control_files='D:\Applns\oracle\oradata\TEST\control01.ctl','D:\Applns\oracle\oradata\TEST\control02.ctl','D:\Applns\oracle\oradata\TEST\control03.ctl'

Let's shutdown and start database with new pfile and create spfile.

 
 SQL> shutdown immediate; 
 Database closed. 
 Database dismounted. 
 ORACLE instance shut down. 
 SQL> startup pfile='D:\Applns\oracle\product\10.2.0\db_1\database\initTEST.ora'; 
 ORACLE instance started. 
  
 Total System Global Area  612368384 bytes 
 Fixed Size                  1250428 bytes 
 Variable Size             188746628 bytes 
 Database Buffers          415236096 bytes 
 Redo Buffers                7135232 bytes 
 Database mounted. 
 Database opened. 
 SQL> show parameter control_files; 
  
 NAME                                 TYPE        VALUE 
 ------------------------------------ ----------- ------------------------------ 
 control_files                        string      D:\APPLNS\ORACLE\ORADATA\TEST\ 
                                                  CONTROL01.CTL, D:\APPLNS\ORACL 
                                                  E\ORADATA\TEST\CONTROL02.CTL, 
                                                  D:\APPLNS\ORACLE\ORADATA\TEST\ 
                                                  CONTROL03.CTL 
 SQL> create spfile from pfile; 
  
 File created. 
  
 SQL> shutdown immediate; 
 Database closed. 
 Database dismounted. 
 ORACLE instance shut down. 
 SQL> startup; 
 ORACLE instance started. 
  
 Total System Global Area  612368384 bytes 
 Fixed Size                  1250428 bytes 
 Variable Size             188746628 bytes 
 Database Buffers          415236096 bytes 
 Redo Buffers                7135232 bytes 
 Database mounted. 
 Database opened.

So that was it !!! . Let's try a new connection.

 
 D:\Applns\oracle\oradata\TEST>sqlplus scott/tiger@test 
  
 SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 10 11:01:59 2011 
  
 Copyright (c) 1982, 2005, Oracle.  All rights reserved. 
  
 ERROR: 
 ORA-01017: invalid username/password; logon denied 
  
  
 SQL> alter user scott identified by tiger; 
  
 SQL> alter user scott account unlock; 
  
 D:\Applns\oracle\oradata\TEST>sqlplus scott/tiger@test 
  
 SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 10 11:03:33 2011 
  
 Copyright (c) 1982, 2005, Oracle.  All rights reserved. 
  
  
 Connected to: 
 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production 
 With the Partitioning, OLAP and Data Mining options 
  
 SQL>

It worked !!! . Happy Reading...

Published with Blogger-droid v1.6.9

Maximum number of datafiles for an Oracle database

2011-05-13T09:44:00.001-07:00

The maximum number of datafiles an Oracle database can have is controlled by the parameter , 'db_files'. You can find the maximum number of datafiles for a database as shown below.

 
 SQL> show parameter db_files 
  
 NAME                                 TYPE        VALUE 
 ------------------------------------------------- ----------- ------------------------------ 
 db_files.                              integer     200 
 SQL>

You can change or increase the value of this using ALTER SYSTEM command.

Published with Blogger-droid v1.6.9

Delete / Drop Database using dbca silent option

2011-05-05T20:44:00.001-07:00

You can delete or drop a databse using dbca in silent or gui mode. This is how you can use dbca silent mode to delete a database.
SourceDB - ORACLE_SID of the target databse which has to be dropped.

 
 Oracle@N880 ~]$ ./bin/dbca -silent -deleteDatabase -sourceDB M9W1 
 Connecting to database 
 4% complete 
 9% complete 
 14% complete 
 47% complete 
 Updating network configuration files 
 48% complete 
 52% complete 
 Deleting instance and datafiles 
 76% complete 
 100% complete 
 Look at the log file "/applns/oracle/cfgtoollogs/dbca/M9W1.log" for further details.

Published with Blogger-droid v1.6.8

2011-05-05T01:14:00.001-07:00

These two roles are defined automatically for Oracle databases.The privileges associated with these role changed from 9i,10g R1 and 10g R2.Before you assign this role to a new user, you can see what all privileges a user granted with this role can get, using the below queries.

 
 SQL> select banner from v$version; 
  
 BANNER 
 ---------------------------------------------------------------- 
 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod 
 PL/SQL Release 10.2.0.4.0 - Production 
 CORE    10.2.0.4.0      Production 
 TNS for Solaris: Version 10.2.0.4.0 - Production 
 NLSRTL Version 10.2.0.4.0 - Production 
  
 SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT'; 
  
 GRANTEE                        PRIVILEGE                                ADM 
 ------------------------------ ---------------------------------------- --- 
 CONNECT                        CREATE SESSION                           NO 
  
 SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='RESOURCE'; 
  
 GRANTEE                        PRIVILEGE                                ADM 
 ------------------------------ ---------------------------------------- --- 
 RESOURCE                       CREATE TRIGGER                           NO 
 RESOURCE                       CREATE SEQUENCE                          NO 
 RESOURCE                       CREATE TYPE                              NO 
 RESOURCE                       CREATE PROCEDURE                         NO 
 RESOURCE                       CREATE CLUSTER                           NO 
 RESOURCE                       CREATE OPERATOR                          NO 
 RESOURCE                       CREATE INDEXTYPE                         NO 
 RESOURCE                       CREATE TABLE                             NO

Published with Blogger-droid v1.6.8

Frequency of archive log generation.

2011-04-26T15:47:00.000-07:00

Find the frequency in which archive logs are generated for a database.

This will give the number of archive logs generated for each day.

SQL> select count(*),trunc(first_time) from v$log_history group by trunc(first_time) order by trunc(first_time);

COUNT(*) TRUNC(FIR
---------- ---------
         2 05-APR-11
        44 06-APR-11
        42 07-APR-11
        42 08-APR-11
        43 09-APR-11
        43 10-APR-11
        42 11-APR-11
        45 12-APR-11
        43 13-APR-11
        43 14-APR-11
        43 15-APR-11

COUNT(*) TRUNC(FIR
---------- ---------
        43 16-APR-11
        49 17-APR-11
        46 18-APR-11
        14 19-APR-11

15 rows selected.

If you want to know at what time log switches happened for each day, you can use the below query.

SQL> SELECT TO_CHAR(first_time,'YYYY-MON-DD') DAY,
2    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'00',1,0)),'99') "00",
3    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'01',1,0)),'99') "01",
4    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'02',1,0)),'99') "02",
5    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'03',1,0)),'99') "03",
6    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'04',1,0)),'99') "04",
7    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'05',1,0)),'99') "05",
8    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'06',1,0)),'99') "06",
9    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'07',1,0)),'99') "07",
10    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'08',1,0)),'99') "08",
11    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'09',1,0)),'99') "09",
12    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'10',1,0)),'99') "10",
13    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'11',1,0)),'99') "11",
14    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'12',1,0)),'99') "12",
15    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'13',1,0)),'99') "13",
16    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'14',1,0)),'99') "14",
17    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'15',1,0)),'99') "15",
18    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'16',1,0)),'99') "16",
19    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'17',1,0)),'99') "17",
20    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'18',1,0)),'99') "18",
21    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'19',1,0)),'99') "19",
22    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'20',1,0)),'99') "20",
23    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'22',1,0)),'99') "22",
24    TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'23',1,0)),'99') "23"
25 FROM v$log_history
26 GROUP BY TO_CHAR(first_time,'YYYY-MON-DD')
27 ORDER BY TO_CHAR(first_time,'YYYY-MON-DD') ;

DAY         00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 22 23
----------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2011-APR-05   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   2
2011-APR-06   5   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 16
2011-APR-07   4   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 15
2011-APR-08   4   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 15
2011-APR-09   5   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 15
2011-APR-10   5   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 15
2011-APR-11   5   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 14
2011-APR-12   7   0   3   0   2   0   2   0   2   0   2   0   2   1   2   0   2   0   2   0   2   2 14
2011-APR-13   6   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 14
2011-APR-14   6   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 14
2011-APR-15   6   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 14

DAY         00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 22 23
----------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2011-APR-16   6   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 14
2011-APR-17   7   0   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 19
2011-APR-18   2   2   3   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   0   2   2 19
2011-APR-19   2   0   3   0   2   0   2   0   2   0   2   0   1   0   0   0   0   0   0   0   0   0   0

The retention period or the history of log switches / arvchive log generation in v$log_history is controlled by MAXLOGHISTORY. This is specified during database creation or control file creation.

You can query this as shown below.

SQL> select type,records_used from v$controlfile_record_section where type like 'LOG%'
2 ;

TYPE                         RECORDS_USED
---------------------------- ------------
LOG HISTORY                           584

SQL> select count(*) from v$log_history;

COUNT(*)
----------
       584

Put files in remote machine by FTP in background

2011-04-26T15:40:00.000-07:00

If you have a requirement to put files from one machine to a remote machine where the client in which you are connecting might disconnect after some time out. In this case you would want the file transfer to take place in background ( in server ).Here is a small shell script which can put files in background. This is for solaris.

#!/bin/sh
HOST='remote machine ip'
USER='remote machine user name'
PASSWD='remote machine user password'

ftp -n $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
prompt off
cd /data1/oracle/rman -- source directory where your files are present
mput *
quit
END_SCRIPT
exit 0

You can check whether the process is running as shown below.

-bash-3.00$ ps -ef | grep scriptname