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.

killing export/import job that is running

Ø  how to kill export/import when running on the screen.

press ctrl+C and 

Import> KILL_JOB ---and at prompt hit 'yes'

sqlplus / as sysdba

SQL> select job_name,state from dba_datapump_jobs;

if you see any table.... make sure that it is you who started...then drop it...

Ø   If the job is running in the background.... how to kill it.

> grep for import/export job at unix and kill that process..'kill -9 osid'

kill at unix will not kill the export/import job.

sqlplus / as sysdba

SQL> select job_name,state from dba_datapump_jobs;

if you see any table.... make sure you see executing/or stopped...

evoke the import/job and atatch the job that was kicked off...

impdp/expdp system/xxxx attach=sys_import_schema_01
>Import/Export> KILL_JOB -- and at prompt hit yes.

drop the table that got from above if it still exists.

Hope this helps.....

asmsnmp and sys users missing in ASM.

when you dont see asmsnmp/sys/etc  users are not available in the ASM. there is an easy way to create them back.

As ASM user,

ASMCMD> lspwusr   ---- you will not see any result.

cd $ORACLE_HOME/dbs(grid home)

create orapwd file for sys user that are missing.

then login in to sqlplus and create asmsnmp user and  give required grants that you want...

SQL> create user asmsnmp identified by  xxxxx;

User created.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

------------------------------ ----- ----- ----- ----- ----- ----- ----------

SYS                                     TRUE  TRUE  FALSE FALSE FALSE FALSE          0

ASMSNMP                        FALSE FALSE FALSE FALSE FALSE FALSE          0

now you will see the users back in the

ASMCMD> lspwusr

Username sysdba sysoper sysasm

     SYS   TRUE    TRUE  FALSE

ASMCMD> exit

Now you can also use this for monitoring the ASM using asmsnmp account with required privileges..

Hope this Helps......

Oracle 12c pre-requirements for RHEL 7.2

Requirements:-

Software Requirements

Ø  Linux software version:- this changes based on your requirement. i used.

·         Red Hat Enterprise Linux Server release 7.2 (Maipo) with kernel level

               3.10.0-327.el7.x86_64 and runlevel “N 5”.

Ø  SSH version:-

·         OpenSSH_6.6.1p1, OpenSSL 1.0.1e-fips

Ø  Note: - there is no Unbreakable RHEL (see Vikas/unix admins for any questions).

Ø  Display setting:-

·         current 2560 x 1024, maximum 2560 x 1024

Ø  Packages required for RHEL 7.2 :-

·         binutils-2.23.52.0.1-55.el7.x86_64

·         compat-libcap1-1.10-7.el7.x86_64

·         compat-libstdc++-33-3.2.3-72.el7.x86_64

·         gcc-4.8.5-4.el7.x86_64

·         gcc-c++-4.8.5-4.el7.x86_64

·         glibc-2.17-106.el7_2.1.i686

·         glibc-2.17-106.el7_2.1.x86_64

·         glibc-devel-2.17-106.el7_2.1.i686

·         glibc-devel-2.17-106.el7_2.1.x86_64

·         ksh-20120801-22.el7_1.3.x86_64

·         libaio-0.3.109-13.el7.i686

·         libaio-0.3.109-13.el7.x86_64

·         libaio-devel-0.3.109-13.el7.i686

·         libaio-devel-0.3.109-13.el7.x86_64

·         libgcc-4.8.5-4.el7.i686

·         libgcc-4.8.5-4.el7.x86_64

·         libgcc-4.8.5-4.el7.i686

·         libstdc++-4.8.5-4.el7.x86_64

·         libstdc++-devel-4.8.5-4.el7.i686

·         libstdc++-devel-4.8.5-4.el7.x86_64

·         libXi-1.7.4-2.el7.i686

·         libXi-1.7.4-2.el7.x86_64

·         libXtst-1.2.2-2.1.el7.i686

·         libXtst-1.2.2-2.1.el7.x86_64

·         make-3.82-21.el7.x86_64

·         sysstat-10.1.5-7.el7.x86_64

Ø  Kernel setting requirements:- 

Below are the minimum recommended Kernel parameters settings required for database software installation. We can go with higher values too. Modify the Kernel setting in /etc/sysctl.conf as follows.

·         semmsl                               250

·         semmns                              32000

·         semopm                              100

·         semmni                               128

·         shmall                                  40 percent of the size of physical memory in pages

              Check: -           kernel.sem                          250 32000 100 128

Ø  Note: If you are planning to  support multiple databases on this server,  then set this parameter to a value that is equal/more to the total amount of shared memory, in 4K pages, that the system can use at one time.

·         Shmmax                               Half the size of physical memory in bytes (See My Oracle Support            Note 567506.1)   ...... make sure you set this parameter based on the memory_max_target you want to use. if you are planning multiple db's on this serverthen it should be set equal/more to sum of all memory_target of all db's.

·         shmmni                                4096

·         panic_on_oops                   1

·         filemax                                 6815744

·         aio-max-nr                          1048576

Ø  Note: This value limits concurrent outstanding requests and should be set to avoid I/O subsystem failures.

·         ip_local_port_range           Minimum: 9000

                                                                                      Maximum: 65500

·         rmem_default                     262144

·         rmem_max                          4194304

·         wmem_default                   262144

·         wmem_max                        1048576

                      Note:-  make  sure /dev/shm is set if you want to use oracle’s AMM(automatic memory management). this should be almost equal/more to the memory you want to use while installing the oracle software with AMM option.

Ø    Set shell limits for oracle user

a                     Add following setting to /etc/security/limits.conf

·         oracle soft nproc                  2047

·         oracle hard nproc                 16384

·         oracle soft nofile                   1024

·         oracle hard nofile                  65536

·         oracle soft stack                    10240

·         oracle hard stack                   10240

·          

Ø  Verify the latest version of PAM is loaded, then add or edit the following line in the /etc/pam.d/login file, if it does not exist:

·         session required pam_limits.so

           

               JDK “open jdk version ‘1.8.0_65’” is installed with this release. 

               

             Ø  Make sure the firewall is set ….Make sure the FIREWALL D (OS process) to be turned off.

         Ø No ASM libraries are required as we are using ASM with Raw devices(no ASM lib concept). if you plan to use/manage disks under ASM then please install latest ASM libraries.

  note:- All reference used are from Oracle forums/oracle support....

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

CRS-5017 / ORA-00838 while starting ASM

crsctl start resource ora.asm

CRS-2672: Attempting to start 'ora.asm' on 'hostname'

CRS-5017: The resource action "ora.asm start" encountered the following error:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 4316M

. For details refer to "(:CLSN00107:)" in "$ORACLE_HOME/log/hostname/agent/ohasd/oraagent_oracle//oraagent_oracle.log".

CRS-2674: Start of 'ora.asm' on 'hostname' failed

CRS-2679: Attempting to clean 'ora.asm' on 'hostname'

CRS-2681: Clean of 'ora.asm' on 'hostname' succeeded

CRS-4000: Command Start failed, or completed with errors

solution:-

check the spfile if the parameter are correct....or if  still having an issue then re-create the spfile from pfile in +ASM and try again....

archive log setup/to put database in archive log mode (12c and 11.2)

follow below steps to put database in archive log mode.

1) create pfile from spfile
2) edit the pfile '
comment db_recovery* parameter if you dont want to use..
add below line
*.log_archive_dest_1='LOCATION=+XXX'
3) startup mount with the edited pfile file.
4) alter database archivelog;
5) alter database open.
6) create spfile from the pfile and bounce the database.




to disable:-

1) create a pfile from spfile.
2) shutdown database.
3) startup mount
4) alter database noarchivelog;
5) alter database open.

Hope this helps.....

Insight on shmax/shmall and /dev/shm settings for oracle on Linux

1) /dev/shm---- this is used by oracle when we want to use AMM(Automatic memory management).... this needs to be set to a value equal to the memory_target of all the databases on the system.
this is not used if we are not using AMM for Oracle..

df -h

2) shmall(/etc/sysctl.conf)  -----his should be set in pages. this value should be equal to the sum of all the Databases on that machine/system(all sga's/preferrably memory_target that we want to use/set) in pages.... to get this value.... first find the page-size value...

getconf PAGE_SIZE....


3) shmax(/etc/sysctl.conf)  ---- is set here...

while starting a database if you see the below errors.... check the parameter setting on the OS..

ORA-27102: out of memory
no space left on device...

SHMAX should be equal to or more than the SGA's/memory of all the databases on that system. mostly, we need the SHMAX should be more than memory_target for a database ..


Hope this helps....

Expdp fails with ORA-31693 ORA-02354 ORA-01555

one of our systems getting  ORA-31693 ORA-02354 ORA-01555 while  running expdp job...
it is taking very long and failing for some tables...

ORA-31693: Table data object "OWNER"."TABLE_NAME" 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 17 with name "_SYSSMU17_1038933293$" too small

we did not see this error before. but once we upgraded to 11.2.0.4.6... we are seeing these errors...


solution:-

issue was because we are using FLASHBACK_TIME in our daily expdp shell.....
check from oracle (Using FLASHBACK_TIME Option With DataPump Import (IMPDP) Fails With Error ORA-1555 (Doc ID 467123.1) applies to Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.4 [Release 9.2 to 11.2]

corrected the syntax for flashback_time in the script and it works...

FLASHBACK_TIME=TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')



solution 2:- 
try to increase the stream pool size 64m and try to run the export again see if it is behaving the same....


Hope this helps....

About foreign key constraints(find parent and child tables for specific table).

below is the sql script to find the foreign key relation to the reference parent table...

use below script to findout parent and child tables...


_________________________________________________________________


accept tablename prompt 'Enter the table name:'
accept tableowner prompt 'Enter the table owner name:'

SET LINESIZE 255
set verify off
col GIVEN_TABLE for a30
col PARENT_TABLE for a30
col CHILD_TABLE for a30
col CHILD_TABLE_OWNER for a20
col GIVEN_TABLE_OWNER for a20
col PARENT_TABLE_OWNER for a20
col PARENT_TAB_CONST for a30
col CHILD_TAB_FK_CONST for a30
col G_TAB_columns for a30
col GIVEN_FK_CONST_NAME for a30
col G_TAB_FK_COLUMNS for a30
col GIVEN_TAB_CONST for a30

Prompt Below are the Child tables for &tablename table.
prompt -----------------------------------------------


SELECT p.table_name GIVEN_TABLE, p.owner GIVEN_TABLE_OWNER, p.constraint_name GIVEN_TAB_CONST,
LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_columns
,c.owner CHILD_TABLE_OWNER,c.table_name CHILD_TABLE,c.constraint_name CHILD_TAB_FK_CONST
FROM dba_constraints p, dba_constraints c,dba_cons_columns t
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
and p.table_name=t.table_name
and p.constraint_name=t.constraint_name
AND p.table_name = UPPER('&tablename')
and p.owner=('&tableowner')
group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;

Prompt Below are the Parent tables for &tablename table.
prompt ------------------------------------------------


SELECT c.table_name GIVEN_TABLE,c.owner GIVEN_TABLE_OWNER, c.constraint_name GIVEN_FK_CONST_NAME,
LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_FK_columns,p.owner PARENT_TABLE_OWNER,p.table_name PARENT_TABLE
,p.constraint_name PARENT_TAB_CONST
FROM dba_constraints p, dba_constraints c ,dba_cons_columns t
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
and c.table_name=t.table_name
and c.constraint_name=t.constraint_name
AND c.table_name = UPPER('&tablename')
and c.owner='&tableowner'
group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;


_________________________________________________________________________



Foreign key constraints(on child table) can only be enabled when the primary constraint(unique) is enabled on the parent table so that the data in the parent table can be protected, which means if you want to delete some rows or add any rows(if it is duplicate row). if we want to enable the disabled foreign key then there should not be any orpan rows in the child table... so rows had to be deleted to make it work...

Hope this helps....

High Swap space usage at OS level due to Oraagent ( ORA-27300)/ORA-27301/ORA-27302)

we will be seeing below error when the Oraagent is re-spawning.

ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301:OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

Recently we were getting this error in the alert log. when did a little research i found oracle recommending couple of work arounds to fix this problem..
1) to monitor the Oraagent usage continously and kill it when it is using more swap space as it  re-spawn the process again..
2)crsctl modify resource ora.asm -attr START_DEPENDENCIES="hard(ora.cssd) weak(ora.LISTENER_DEV.lsnr)"

Before trying given oracle work arounds i did something different which fixed my issue too...
1) At first i made sure the listener is not dynamic registered. need to be OFF it if it is ON. even if it is ON leave it now and do below steps and validate after last step again.
2) stop the listener that got created and quickly unregister the listener from crs.
           crs_unregister ora.listener.lsnr
3) Re- add the listener using srvctl command with appropriate listener name and Port.
           srvctl add listener -l LISTENER -p "TCP:1521"
4) shutdown db and asm and remove asm from crs.
           srvctl remove asm –f
5)  add asm again with appropriate values..
                     srvctl add asm -l LISTENER -p $ORACLE_HOMEdbs/spfile+ASM.ora
6)  bring everything up one by one and monitor oraagent swap usage.

This fixed my above oracle error from coming....
Hope this helps.....

Regards,