ORA-01555: snapshot too old during datapump export

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

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

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

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

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

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)

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

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

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

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

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

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 )

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

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

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

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:

1. 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
2. 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

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

Script to find duplicates for primary key or Unique Index

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

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

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:

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

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

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

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.

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

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