unrecoverable error ORA-29701 raised in ASM I/O path;

ERROR: unrecoverable error ORA-29701 raised in ASM I/O path; terminating process 9830432 

Thu Jul 28 17:02:07 2016 ERROR: unrecoverable error ORA-29701 raised in ASM I/O path; terminating process 

This error was recently noticed when one of our unix admin accidentally cleaned the files in /tmp folder on AIX server.. he had deleted/removed the .oracle  folder and its files(hidden files).
This should not be done when oracle is up and running....

Soln:-
when you see this error .. make sure you shutdown oracle and asm/agent as you can.... And ask the Unix admin to bounce the server/reboot....once it is up.. you can try to start the oracle resources.. it should start with no issues...


Hope this Helps....

Unix Variable to sqlplus and sqplus to UNIX different scenarios

1) passing variable to sqlplus
Variable_IN="'ABC_VAR_1','ABC_VAR_2'"

2) how to store sqlplus result to a variable back to unix variable.

v_count1=$(sqlplus -L -S sys/$passd@$PDB as sysdba <<EOF   ---(for 12c)
set feed off heading off echo off verify off termout off
select count(*) from dba_tab_statistics where stale_stats = 'YES';
exit
EOF
)

3) how to pass your own variables to use in the sqlplus...

Variable_IN="'ABC_VAR_1','ABC_VAR_2'"

declare
type_p_abc_dba integer;
begin
  select count(*) into type_p_abc_dba from dba_types where type_name='STORE_VAL_abc_DBA';
  if (type_p_abc_dba = 0) then
    execute immediate 'create type store_val_abc_dba  as table of varchar2(100);';
  end if;
end;
/

declare

    type pa_tablename is table of all_tables.table_name%type

        index by binary_integer;

    va_tablename pa_tablename;

cursor c_f is (select column_value from table(store_val_abc_dba($Variable_IN)));

BEGIN

FOR ind IN  c_f

LOOP

begin

     .............

     .............

end;

end loop;

end;

Hope this helps.......

script to find stale objects and run the stats

You can use below script to automate running stats when stale objects are found in the database...
here we are ignoring all the objects were their stats are locked.

In order to get updated  stale objects... it is better you flush the monitoring info... as this updates the changes occurred recently....and tells whether any objects got staled or not. we are doing this manually because oracle has it own way/time of updating... if you have hard hitting tables then they might be stale but the monitoring info will not capturing it...  by doing so we can have better stats for  the tables needed.

you can easily automate the below code with you environment set.

exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

Script:-

CHANNELS=`cat /proc/cpuinfo | grep processor | wc -l`


===================
sqlplus  -S sys /change@ABCD_SID as sysdba <<EOF >> $LOG
set linesize 200
set serveroutput on size 1000000
set feedback on echo off verify off

Prompt Running stats for Stale Table objects

declare
sql_stmt varchar2(256);
va_tab dba_tab_statistics.table_name%type;

cursor c_f is (select owner,table_name,global_stats,stale_stats from dba_tab_statistics
where stale_stats = 'YES' and table_name not like 'BIN$%' and STATTYPE_LOCKED is null);

begin
    for i in c_f
    loop
        begin
           dbms_stats.gather_table_stats(''||i.owner||'',''||i.table_name||'',
                estimate_percent => 100,
                degree => $CHANNELS,
                method_opt => 'for all columns size auto', cascade => true);
                dbms_output.put_line(sysdate || ': stats done for ' || ''||i.owner||'' ||'.' || ''||i.table_name||'');
            exception
            when others then
            continue;
            dbms_output.put_line(sysdate || ': failed to gather stats for ' || ''||i.owner||'' ||'.' || ''||i.table_name||'' || chr(10) || sqlerrm);
         end;
    end loop;
           dbms_output.put_line(sysdate || ': #######################' || ': Stats done for Above Found Stale objects ' || ': #######################');
END;
/

Prompt Running stats for Stale index objects

declare
sql_stmt varchar2(256);
va_tab dba_ind_statistics.table_name%type;

cursor c_f is (select owner,index_name,global_stats,stale_stats from dba_ind_statistics
where stale_stats = 'YES' and table_name not like 'BIN$%' and STATTYPE_LOCKED is null);

begin
    for i in c_f
    loop
        begin
           dbms_stats.gather_index_stats(''||i.owner||'',''||i.index_name||'',
                estimate_percent => 100,
                degree => $CHANNELS,
                no_invalidate => false);
                dbms_output.put_line(sysdate || ': stats done for ' || ''||i.owner||'' ||'.' || ''||i.index_name||'');
            exception
            when others then
            continue;
            dbms_output.put_line(sysdate || ': failed to gather stats for ' || ''||i.owner||'' ||'.' || ''||i.index_name||'' || chr(10) || sqlerrm);
         end;
    end loop;
           dbms_output.put_line(sysdate || ': #######################' || ': Stats done for Above Found Stale objects ' || ': #######################');
END;
/


EOF
echo "Done" >> $LOG

Hope this Helps......

how to mount the raw disks or nfs mounts that are listed in fstab

we can use this simple method to find what disks or mounts are missing before we bring the database up....

To understand this better.... initially the admins will put an entry in fstab for the disks and mounts that need to be mounted... but sometimes we dont see all disks come up.... it figure it out and make them available you can use this small script....

list=`awk '{print $2}' /etc/fstab |grep -i "/" | grep -v '^/etc/fstab' | grep -v '^/director' | grep -v '/abcdef_data' | egrep '/.'`         ----(you can exclude some more using grep -V)
for i in $list
do
grep -iw $i /proc/mounts > /dev/null
status=$?
if [ $status -eq 0 ]; then
echo " $i is mounted"
else
echo "$i is not mounted"                      ---------you can mount the missing one by adding (mount $i)                                                                      ... if you want it to mount directly)
fi
done

how to attach files to email in UNIX in shell scripts or command line

Use below command to send the files as attachment in Email. doing this also helps you to zip the files and email. also this reduces the email size too.
as we all know we send logfiles

Linux:-

echo body_text_you_want | mailx -a file_name.zip -s "Subject_line_you  want to specify" abcef@domain.com

for AIX:-

to attach a file

(echo "Subject:Test email\nTo:adbcef@domain.com\nFrom: adbcef@domain.com\n\nAttached log file";uuencode file_name file_name) |  /usr/sbin/sendmail abced@domain.com

to attach multiple files....

(echo "Subject:Test email\nTo:adbcef@domain.com\nFrom: adbcef@domain.com\n\nAttached log file";(uuencode file_name file_name; uuencode file_name_2 file_name_2)) |  /usr/sbin/sendmail abced@domain.com

Hope this helps.

create user without C## in the 12c container database

In oracle to create a normal user/profiles in oracle 12c container database. we need to set the session to below so that we can create a normal user...
alter session set "_ORACLE_SCRIPT"=true; 

then try to create a user. this time it will work...


Hope this helps....

generating large number of *.aud file under Grid_Home().. (12c and 11g)

Recently, what we observed was.. the audit files under grid_home were generating too many.

GRID_HOME/rdbms/audit....

By default in 11g the audit_sys_operations is "FALSE" but as you upgrade/go to 12C then this is "TRUE"

Having said that, this will be logging all the audit activity when ever something log's in... so we need to set this to "FALSE"

>   alter system set audit_sys_operations=FALSE" scope=spfile.  --- this need a bounce..

once this is done.... this will slow down the audit info writing to the files.... but it will still generate the files... 

 as per oracle..this is a BUG....

Large Number of Audit Files are getting generated in 12c Oracle Restart (Doc ID 2049448.1)

so we need to apply... this doesn't require any bounce of crs....

1     crsctl modify resource ora.asm -attr  START_DEPENDENCIES="hard(ora.cssd)" –unsupported    (for 12c)

1      crsctl modify resource ora.asm -attr  START_DEPENDENCIES="hard(ora.cssd)"  (for 11g)...

       monitor the directory for the change....

      

       Hope this Helps.....

   

   

    

Renaming or moving the controlfiles in oracle

1) sqlplus in to the database and get the location of the current control files...
     SQL> show parameter control_files;
2) make the desired directory structure where you want to move the controlfiles.
3) then in sqlplus, set the new location for the control files. AND Create a Pfile from existing SPFILE before making any CHANGES.
   SQL> ALTER SYSTEM SET control_files='+Apt/CONTROLFILE/control01.ctl','+TEMP/CONTROLFILE/control02.ctl' SCOPE=SPFILE;

4) shutdown the database;
5) start the database in nomount;
6) In another window, start rman ... rman target /
7) restore the control files to the above mentioned controlfiles from step 3 with a  current control file from old location from step 1.
  rman target /
restore controlfile to '+Apt/CONTROLFILE/control01.ctl'' from 'Apt/TESTDB/controlabc123';
restore controlfile to '+TEMP/CONTROLFILE/control02.ctl' fromn 'TEMP/TESTDB/controlabc123';

8) open the database in Mount.
9) open database and validate the changes.



Hope this Helps .....

Renaming or moving tempfile in oracle(ASM/Non-ASM)

1) sqplus to the database and make sure the Tempfiles that you want to rename or change the location.
   SQL> select name from v$tempfile;
2) create a pfile before making the changes.
3) shutdown the database.
4) make the directory structure where you want to move the temp files..
5) start the database in Mount.
    SQL> startup mount
6) alter database rename file '+APT/TESTDB/TEMPFILE/tempabc' to '+TEMP/TESTDB/TEMPFILE/temp01.dbf'

for filesysystem also you can follow the same procedure. just give the location that you want to.

7) open the database and make sure the temp file is created at the desired location.



In 12C:-

Below are the steps to move the Temp files… for container / PDB.

set linesize 200

col file_name for a60

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

1)

Create a tempfile of a decent size.

alter tablespace TEMP add tempfile '+TEMP/TESTDBC/TEMPFILE/temptest.dbf' size 2G;

2)

take the tempfile offline which you want to move.

alter database tempfile '+DATIND/TESTDBC/TEMPFILE/temp.441.943798245' offline;

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

3)

Copy the tempfile in the ‘+ASM’ to the desired location. Note create the folders in TEMP directory.

cp '+DATIND/TESTDBC/TEMPFILE/TEMP.441.943798245' '+TEMP/TESTDBC/TEMPFILE/temp01.dbf'

4)

Rename the tempfile you already moved.

alter database rename file '+DATIND/TESTDBC/TEMPFILE/temp.441.943798245' to '+TEMP/TESTDBC/TEMPFILE/temp01.dbf';

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

5)

Make the tempfile online again.

alter database tempfile '+TEMP/TESTDBC/TEMPFILE/temp01.dbf' online;

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

Note:- do these steps for other tempfiles in the temp(do 2/3/4/5 steps).

Once done drop the created tempfile in step 1.

alter database tempfile '+TEMP/TESTDBC/TEMPFILE/temptest.dbf' offline;

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

alter tablespace TEMP drop tempfile '+TEMP/TESTDBC/TEMPFILE/temptest.dbf';

SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

Hope this helps......

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

How to compare schemas in different databases using Toad

Bring up toad and drill down the tab as shown below.

Select the source and target database for the schemas you want to compare.

Select the options as show in below with the schema name filter (like ‘ABC %’)

Select the options you want to compare between the schemas.

Select the output location/path with the below options…

Then you are now ready to execute (the green button). Once you run it will take few minutes depending on the number of objects to compare.

Hope this Helps...................

oracle 12c pre-requirements for Aix 7.1 service pack 3 or above

Requirements:-

Software Requirements:-

Ø  AIX software version:- Oracle Database 12c Release 1 (12.1.0.2), with AIX 7.1 technology level 3 or later is required with 64-bit kernel and run level 2 minimum

Ø  Ensure display cards provide at least 1024 x 768 display resolution, so that Oracle Universal Installer displays correctly while performing a system console-based installation.

Ø  Ensure that OpenSSH is installed on your servers. OpenSSH is the required SSH software.

Ø   Display setting:- current 2560 x 1024, maximum 2560 x 1024

Ø  Packages required for AIX7:-

         The following packages (or later versions) must be installed.

·         bos.adt.base

·         bos.adt.lib

·         bos.adt.libm

·         bos.perf.libperfstat

·         bos.perf.perfstat

·         bos.perf.proctools

·         xlC.aix61.rte:11.1.0.4 or later

·         xlC.rte.11.1.0.4 or later

·         rsct.basic.rte

·         rsct.compat.clients.rte

 Ø  AIX APAR needed.

·         IV16737, IV21116, IV21235,

·         IV28925, IV34869, IV35057,

·         IV39136, IV37940 , IV41415, IV45072,

·         IV45073

·         Note: APAR number may vary according to TL / SP level, please contact IBM for exact APAR number for your OS TL/SP version.

          Checks: - please validate below APARS if they are included in the present level that is installed. If not install these too.

·         IV34869, IV37790, IV41380, IV19836, IV33857

Ø  Kernel setting requirements:- 

·         Note:- “Not required“ Set the port range high enough to avoid reserved ports for any applications you may intend to use.If the lower value of the range you have is greater than 9000, and the range is large enough for your Anticipated workload, then you can ignore OUI warnings regarding the ephemeral port range.

·         tcp_ephemeral_low = 32768

·         tcp_ephemeral_high = 65535

·         udp_ephemeral_low = 32768

·         udp_ephemeral_high = 65535

Ø    Set shell limits for oracle user

·         Soft File Descriptors at least                 1024 KB

·         Hard File Descriptors at least               65536 KB

·         Soft FILE size                                            -1(Unlimited)

·         Soft CPU time                                          -1(Unlimited)

·         Soft DATA segment                                -1(Unlimited)

·         Soft STACK size                                        -1(Unlimited)

·         Soft Real Memory size                           -1(Unlimited)

·         Processes (per user)                               -1(Unlimited) Note: This limit isavailable only in AIX 6.1 or later.

·         maxuprocs                                                16384

·         ncargs                                                        128

Ø  check if ulimit parameters are set to unlimited. If not change them to unlimited.