how to setup oracle (11.XX)(using No ASM and ASM) manually...using Oracle Binaries tar'd

Ø  This Doc will help you to install same version of oracle on any new server using the oracle Binaries where oracle is already installed on other servers...

Ø  First we need to take a tar of oracle home binaries of db home and grid home (if using ASM) from the already installed oracle which is in functional use.

Ø  Move the tar copies to the new server.

Ø  Make sure you don’t have any old oracle references on the New machine. Remove all the oracle related files under /etc(ls –ltr ora*).

Ø  Untar the binaries where you want your Oracle Home need to be… DB_home and Grid_Home(if ASM is used).

Ø  Once they are untar’d in respective home.

Ø  If on AIX, run the “SLIBCLEAN” should be run as root user.

Ø  Run the rootpre.sh from the grid_home/clone/rootpre.sh (untar location).

Ø  Run the below command for the GRID_HOME(if asm is used)

Ø  perl ./clone/bin/clone.pl  -silent  ORACLE_BASE=/usr/home/oracle  ORACLE_HOME=/usr/home/oracle/product/11.2.0.4/grid ORACLE_HOME_NAME=Ora11g_gridinfrahome1  INVENTORY_LOCATION=/usr/home/oraInventory

Ø  Once the above cloning process is completed with no issues/errors. Run the below commands as root user.

/usr/home/oraInventory/orainstRoot.sh

/usr/home/oracle/product/11.2.0.4/grid/root.sh

The 2^nd command root.sh will provide you another script to run the generated log file.

/usr/home/oracle/product/11.2.0.4/grid/perl/bin/perl –I /usr/home/oracle/product/11.2.0.4/grid/perl/lib  –I  /usr/home/oracle/product/11.2.0.4/g

rid/crs/install  /usr/home/oracle/product/11.2.0.4/grid/crs/install/roothas.pl

Make sure you don’t have any errors…

Ø  This completes Grid Home setup.

Ø  Now, we need to setup ASM. For this you atleast need one disk. (Not needed if not using ASM).

Ø  Run the below command to do so… with appropriate passwords and disks and diskgroopname.

Ø  asmca -silent -configureASM -sysAsmPassword SYSASMPASSWORD –asmsnmpPassword ASMSNMPPASSWORD -diskString '/dev/rhdisk*' -diskGroupName DISKGROUPNAME -disk DISKNAME -redundancy EXTERNAL

Ø  Create other disk groups now if you have any.

Ø  Make sure you have everything up and if needed create listener also. Create spfile files and other steps you need here.

Ø  Once the above piece is completed. Now we are ready for the Database Piece. Go to the untar’d DB_home location

Ø  perl ./clone/bin/clone.pl -silent ORACLE_BASE=/usr/home/oracle ORACLE_HOME=/usr/home/oracle/product/11.2.0.4/db_1 ORACLE_HOME_NAME=Ora11g_home1 INVENTORY_LOCATION=/usr/home/oraInventory

Ø  once done, run the below command as root user

                                       /usr/home/oracle/product/11.2.0.4/db_1/root.sh

Ø  This completes the setup of DB_Home.

Ø  Now create a database from the backup of any database or you can use dbca to create one…

*************************Hope this Helps*******************************************

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor In Oracle 12C

Just came across about this new listener-to-sid issue when we upgraded the oracle DB to 12c version.
initially when we were using  oracle verison 11 i did not see this issue. As we moved to oracle 12c i saw application is behaving weidly with below error.

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

solution:- 
we need to add an entry in the listener.ora file and reload the listener to take effect.

USE_SID_AS_SERVICE_LISTENER_NAME=ON



Hope this Helps......

how to get table count for all tables in a schema/user using simple methods ......

Getting a table count might be looking simple but if you want to get table count for all tables in the database..which is time consuming...
you can get this by below methods.... use as you think is better..

1) you can get the count of all tables using dba_tables using NUM_ROWS. but this will not give right count as this tables gets updated with the dictionary job/stats job update only....
select table_name,num_rows from dba_tables where owner='ABC' order  by table_name;


2)  you can write a small sql generated sql to get the statements and spool the output.
set heading off;
Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';'
from user_tables
order by table_name;

3) you can use pl/sql query to get the correct count of all tables at any given time....it directly queries the table as you do... you need to be in that specific schema.

set SERVEROUTPUT ON
DECLARE
sql_stmt varchar2(256);
v_b varchar2(100);
cursor c_f is SELECT TABLE_NAME from DBA_TABLES where owner = 'ABC';
  v_a DBA_TABLES.TABLE_NAME%type;

BEGIN
open c_f;
  loop
    fetch c_f into v_a;
    exit when c_f%notfound;

sql_stmt :='SELECT count(*)  from '|| v_a ||'';

execute immediate sql_stmt into v_b;
--execute immediate sql_stmt into tab_count;
---    execute immediate 'SELECT max(v_b) into tab_count from v_a where current of c_f';
--DBMS_OUTPUT.PUT_LINE ( ' || abc.table_name ||  ',' tab_count ');

DBMS_OUTPUT.PUT_LINE ( v_a   || ': ---> ' ||    v_b );

  END LOOP;
END;
/


use below script if above doesnt work. you can run this as sys owner too..



set SERVEROUTPUT ON size 1000000
set feedback on echo off verify off
DECLARE
sql_stmt varchar2(256);
tab_count varchar2(100);

cursor c_f is (SELECT owner,TABLE_NAME from DBA_TABLES where owner = '&owner');
  v_a DBA_TABLES.TABLE_NAME%type;

BEGIN
for abc in c_f
  loop
   
sql_stmt := '(SELECT count(*)  from  '||abc.owner||'.'||abc.table_name||')';
execute immediate sql_stmt into tab_count;

DBMS_OUTPUT.PUT_LINE ( ''||abc.table_name||'' || ': ------>   ' ||  ''||tab_count||'');
  END LOOP;
END;
/






Hope this helps and saves some time....

Setting up ODBC connectivity between Oracle to Sql server using DataDirect ODBC Driver

Just some time back i had to test the setup DataDirect ODBC connection between Oracle to Sql server. here is the complete documentation on how to setup

1)      Basic Overview

2)      Installing the data dictionary tables and views for Heterogeneous services.

3)      Installing DataDirect Connect for ODBC Driver on the Server.

4)      Configuring ODBC data source in the odbc.ini file.

5)      Configuring tnsnames.ora and listener.ora files.

6)      Creating a user in Oracle and in SQL Server side.

7)      Test the Connectivity by (.example) provided by the vendor.

8)      Creating the initialization file and configuring it.

9)      Creating a Database link.

10)  Testing the Connectivity from the oracle to SQL Server with a sample query.

1)      Overview:-

Generic Connectivity provides Oracle Users, the ability to access and integrate non-Oracle data sources, by providing a large range of flexibility in a multi-database environment.

This Document explains the Installation and the use of DataDirect Connect for ODBC with Oracle on an AIX system using Oracle 11g and DG4odbc. Below diagram shows DataDirect Connect 6.1 ODBC drivers to connect Oracle to Microsoft SQL Server or Heterogeneous Services.  

2)      Installing the data dictionary tables and views for Heterogeneous services:

First, select Oracle database and login in to the SqlPlus as sys and try to run the following script which is located in $ORACLE_HOME/rdbms/admin/caths.sql.

For example:

SQL>@ORACLE_HOME/rdbms/admin/caths.sql;

3)      Installing DataDirect Connect for ODBC Driver on the Server:

(i)                              Check the permissions: Log in as a user with full r/w/x

                  permissions recursively on the entire DataDirect Connect                                             for ODBC installation directory.

(i)                              Install the DataDirect Connect for ODBC Driver on the

                  server. From on the options choose SQL Server                                                                                 Wire Protocol.

(ii)                            Set the environment variables for the ODBC.

4)      Configuring ODBC data source in the odbc.ini file:

After installing the ODBC Driver we need to configure odbc.ini file in $ODBC_HOME by setting up some of the parameters.

(i)                              Change the ODBC Data source name

                                Ex:  natodbc =DataDirect 6.1 SQL Server Wire Protocol

(ii)                            Parameters to be looked in order to make connectivity:

                                                Driver=

                                                Database=<name of the target database SQL DB’s>

        disguisewide=1

                                                HostName= <Host name of the SQL Server>

        PortNumber=

5)       Configuring tnsnames.ora and listener.ora files:

                Edit the listener.ora and tnsnames.ora in $ORACLE_HOME/network/admin something like this Ex:

(i)                              Listener.ora:

                                                (SID_DESC=

          (SID_NAME=natodbc)

                                (ORACLE_HOME=/prog/app/oracle/product/11.1.0/

                                                db_2)

      (PROGRAM=dg4odbc)       (ENVS=LIBPATH=/var/tmp/opt/Progress/DataDirect/          Connect64_for_ODBC_61/lib:$ORACLE_HOME/lib)

          )

        

(ii)                            Tnsnames.ora:

                                dg4odbc =

          (DESCRIPTION =

          (ADDRESS =

      (PROTOCOL = TCP)

      (HOST = xx.x.x.x.)

      (PORT = 1521)

          )

          (CONNECT_DATA = (SID = natodbc)

          )

          (HS=OK)

                                                )

                IMP: Before restarting the listener, make sure the SID named in the listener.ora and tnsnames.ora are the same and make sure the  ODBC LIBPATH directory is added to the listener.ora).

6)      Creating a user in Oracle and in SQL Server:

We need to create a user in Oracle and SQL Server in order to connect from Oracle to SQL Server. Grant the user the required privileges to connect.

7)      Test the Connectivity by (.example) provided by the vendor:

After editing all the configuration files and restarting the listener we need to test the connectivity by executing the .example provided by the software (/$ODBC_HOME/samples/example). Test run the .example by giving appropriate values as input and check for the connectivity. Once if it connected, you can query a table from SQL Server for our reference. Ex:-

/var/tmp/opt/Progress/DataDirect/Connect64_for_ODBC_61/samples/example>./example

./example DataDirect Technologies, Inc. ODBC Example Application.

Enter the data source name : natodbc

Enter the user name        : OracleDevNational

Enter the password         : <password>

sqlGetInfo (17)

sqlGetInfo (18)

sqlGetInfo (6)

sqlGetInfo (7)

sqlGetInfo (26)

Enter SQL statements (Press ENTER to QUIT)

Enter SQL statements (Press ENTER to QUIT)

SQL> select count(*) from dbo.table_name;

COUNT(*)

----------

      9 

8)      Creating the initialization file and configuring it:-

Create an initialization file in $ORACLE_HOME/hs/admin with the same name that you used in the odbc.ini file (name of the Data source) like initnatodbc.ora. Configure the initialization file by adding following lines.

                HS_FDS_CONNECT_INFO = natodbc

                HS_FDS_TRACE_LEVEL = 4

                HS_FDS_TRACE_FILE_NAME=/var/tmp/opt/Progress/DataDire ct/Connect64_for_ODBC_61/dg04trace.log

                HS_FDS_SHAREABLE_NAME=/var/tmp/opt/Progress/DataDire ct/Connect64_for_ODBC_61/lib/odbc.so

                set ODBCINI=/var/tmp/opt/Progress/DataDirect/Connect64_for_

                ODBC_61/odbc.ini

9)      Creating a Database link:

After configuring all the configuration files. Now we need to create a Database link to access the Target Database from Oracle.

Ex:  create database link <database link name>

         Connect to <username> Identified by <password>

         using ‘tnsnames.ora’;

where username: created in step 6.

          Tnsnames.ora:- created in step 5 (ii).

10)      Testing the Connectivity from the Oracle to SQL Server with a   sample query:

In order to test you’re the connectivity from Oracle to SQL Server. Try to execute a simple query (select a table from the SQL Server database) and see 

Note:  If you are getting any errors try to check whether you missed out any steps in the process.

Hope this Helps....

how to quickly change the redolog size which are in multiple locations using a script.

use below script to quickly resize the redolog size. you can also automate this script easily by adding some more steps. but when doing you need to be careful.
before using below script make sure you have correct redo entries in the highlighted red color.

step 1:-
before starting the process make sure you do these 2 steps.

alter system switch logfile;
alter system checkpoint global;

step 2:-

SELECT a.group#, a.member,b.status, b.bytes/1024/1024   Size_in_MB FROM v$logfile a,v$log b WHERE a.group# = b.group#;
set heading off
select '##### STEP_1: Check point issued before starting the process' from dual;
set heading on
alter system switch logfile;
alter system checkpoint global;

set heading off
select '######STEP_2:  Redologs groups info with Inactive status' from dual;
set heading on
SELECT a.group#, a.member,b.status, b.bytes/1024/1024   Size_in_MB FROM v$logfile a,v$log b WHERE a.group# = b.group# and b.status = 'INACTIVE';

set heading off
select '######:  Redologs groups info with Active status' from dual;
SELECT a.group#, a.member,b.status, b.bytes/1024/1024   Size_in_MB FROM v$logfile a,v$log b WHERE a.group# = b.group# and b.status !='INACTIVE';

select '###### STEP_3:  Now dropping the redolog groups which are INACTIVE' from dual;
--set heading on
select 'alter database drop logfile group '||GROUP#||';' from v$log where status='INACTIVE';

set heading off

select '#### STEP_4: Generating scripts for New Redolog Resize' from dual;

set heading on

set serveroutput ON

@redo_auto_script.sql

set heading off

select '#### STEP_5: Confirming active redolog at this moment and will change later' from dual;

set heading on

SELECT a.group#, a.member,b.status, b.bytes/1024/1024   Size_in_MB FROM v$logfile a,v$log b WHERE a.group# = b.group# and b.status in ('ACTIVE','CURRENT');

Hope this helps and saves your time.......