Below are the steps for cloning ORACLE_HOME from one server to another server. 1. Check the oracle_home inventory [ SOURCE DB] $ORACLE_HOME/OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.1.0.1.10 Copyright (c) 2017, Oracle Corporation. All rights reserved. Oracle Home : /oracle/app/oracle/product/12.1.0.2_CRMTST/dbhome_1 Central Inventory : /oracle/app/oraInventory from : /oracle/app/oracle/product/12.1.0.2_CRMTST/dbhome_1/oraInst.loc OPatch version : 12.1.0.1.10 OUI […]

                    Cloning from a RAC database to standalone database, can be done either using ACTIVE CLONE or by taking  a RMAN backup from RAC database and restoring in standalone database. SEE ALSO – RMAN ACTIVE CLONING Here in this article,we will show db cloning by taking […]

1. Import using NETWORK_LINK 2. QUERY CLAUSE in EXPDP 3. SQLFILE parameter in impdp 4. TABLE_EXISTS_ACTION option in IMPDP 5. EXCLUDE/INCLUDE option 6. DATA_OPTION=SKIP_CONSTRAINT_ERRORS 7.LOGTIME=ALL Parameter – Oracle 12c new feature 8. Views_as_tables Parameter In Datapump Of Oracle 12c 9. TRANSFORM=DISABLE_ARCHIVE_LOGGING – Oracle 12c new feature 10. ENCRYPTION in export Import 11. CONTENT parameter in […]

Sometimes we may require to drop the existing tablespace undo and create a fresh one if the size of undo has increased a lot and we are unable to reclaim it. Below are the steps: 1. Check the existing UNDO details: SQL> show parameter undo NAME TYPE VALUE ———————————— ———– —————————— temp_undo_enabled boolean FALSE undo_management string […]

                Using flashback method, we can restore an accidentally dropped users in oracle. Basically, we will flashback the database to past, when the user was available, Then take an export dump of the schema, and restore the database to same current state. Once db is up, we can import […]

   In some cases the datapump utility gets corrupted .  To overcome it , we need to reload the expdp and impdp datapump utility in oracle.   Applicable from 11g onwards.  STEPS: 1.  start the database in upgrade mode: SQL> startup upgrade ORACLE instance started. Total System Global Area 1.4529E+10 bytes Fixed Size 7649368 bytes Variable […]

           There are multiple methods for enabling tracing for sessions in oracle. 1. Enabling tracing for all session of a user.              For this we need to create a trigger. CREATE OR REPLACE TRIGGER USER_TRACING_SESSION AFTER LOGON ON DATABASE BEGIN IF USER = ‘SIEBEL’THEN execute immediate […]

                       Follow below steps for disabling auto tasks in oracle database. SQL> select client_name,status from dba_autotask_client; CLIENT_NAME STATUS —————————————————————- ——– auto space advisor ENABLED auto optimizer stats collection ENABLED sql tuning advisor ENABLED set lines 180 pages 1000 col client_name for a40 col attributes for […]

            Oracle 11gR2 introduced DBMS_AUDIT_MGMT for managing audit trails.  The growth of AUD$ can impact the performance of the database. So purging it regularly is the best practice followed by DBA’s and DBMS_AUDIT_MGMT makes it easier. Follow below steps for puring aud$ table. 1. Make sure AUD$ table is not […]

        Interval Partitioning has been introduced in oracle 11g. With this method, we can automate the creation of range partition .While creating the partitioned table, we just need to define one partition. New partitions will be created automatically based on interval criteria when the data is inserted to the table. We don’t […]