August 21, 2013

ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) - dependencies exist

ISSUE:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) - dependencies exist

Restore TABLE backup

Schema Status
-------------
select username from dba_users where username=upper('&username');

Table Status
------------
select count(*) from SCHEMANAME.TABLENAME;

TRUNCATE TABLE
---------------------------
spool TARGET_TABLENAME_TRUNCATE.log
set echo on term on feed on timing on

truncate table SCHEMANAME.TABLENAME;
truncate table SCHEMANAME.TABLENAME
                         *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where TABLE_NAME='&TABLE_NAME';

CONSTRAINT_NAME                C TABLE_NAME                     STATUS
------------------------------ - ------------------------------ -------
SYS_CONSTRAINT1                C TABLENAME                      ENABLED
PK_TABLENAME                   P TABLENAME                      ENABLED

alter table SCHEMANAME.TABLENAME DISABLE constraint SYS_CONSTRAINT1;

alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME;
ERROR at line 1:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) -
dependencies exist

SOLUTION:

alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME cascade;

Table altered.

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where TABLE_NAME='TABLE_NAME';


ORACLE Database Details

connect to Putty->enter the hostname->select load

Login : <userid>
password: <password>

ps -ef | grep pmon
ps -ef | grep tns
ps -ef | grep d.bin

Set the environment for the database:
------------------------------------
uname
cat /etc/oratab  OR cat /var/opt/oracle/oratab
export ORACLE_SID=<DBNAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$ORACLE_HOME/bin:$PATH

Database Details:
----------------

sqlplus "/as sysdba"

set pages 50000 lines 32767

col OPEN_MODE for a10
col HOST_NAME for a10
select NAME DB_NAME, INSTANCE_NAME, HOST_NAME, OPEN_MODE, version DB_VERSION,DATABASE_STATUS,DATABASE_ROLE, PROTECTION_LEVEL, CONTROLFILE_TYPE, LOGINS, to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;


 

Tablespace Utilization Script in Oracle

How to check oracle tablespace report 
1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.

1. Check the database details.
$ sqlplus "/as sysdba"

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;

2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"

set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/

Tablespace Utilization Script (including AUTOEXTEND) for generating report of more than 80 % used tablespaces (IN GB)
---------------------------------------------------------------------------------------------------------------------
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/

3. Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 50000 lines 32767
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;

Note:- If required, can get the DDL of a tablespace as below.

TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;

CRSCTL commands in Oracle 11g Release 2

How to shutdown CRS on all nodes and Disable CRS as ROOT user:
-------------------------------------------------------------
#crsctl stop crs
#crsctl disable crs

How to Enable CRS and restart CRS on all nodes as ROOT user:
-----------------------------------------------------------
#crsctl enable crs
#crsctl start crs

How to check VIP status is ONLINE / OFFLINE:
----------------------------------------
$crs_stat or
$crsctl stat res -t ------> 11gr2

How to Check current Version of Clusterware:
-------------------------------------------
$crsctl query crs activeversion

$crsctl query crs softwareversion [node_name]

How to Start & Stop CRS and CSS:
-------------------------------
$crsctl start crs
$crsctl stop crs

#/etc/init.d/init.crs start
#/etc/init.d/init.crs stop

#/etc/init.d/init.cssd stop
#/etc/init.d/init.cssd start

How to Enable & Disable CRS:
---------------------------
$crsctl enable crs
$crsctl disable crs

#/etc/init.d/init.crs enable
#/etc/init.d/init.crs disable

How to Check current status of CRS:
----------------------------------
$crsctl check crs

$crsctl check cluster [-node node_name]

How to Check CSS, CRS and EVMD:
------------------------------
$crsctl check cssd

$crsctl check crsd

$crsctl check evmd

How to List the Voting disks currently used by CSS:
--------------------------------------------------
$crsctl check css votedisk

$crsctl query css votedisk

How to Add and Delete any voting disk:
-------------------------------------
$crsctl add css votedisk <PATH>

$crsctl delete css votedisk <PATH>

How to start clusterware resources:
----------------------------------
$crsctl start resources

$crsctl stop resources

SRVCTL COMMANDS

How to STOP the Oracle RAC resources:
------------------------------------
#srvctl stop instance -d <database_name> -n <node_name>
#srvctl stop vip -n <node_name> -f

How to check current VIP configuration:
--------------------------------------
$srvctl config nodeapps -a

How to verify VIP status:
------------------------
$ifconfig -a

SCAN IP address in 11gR2 RAC

SCAN in RAC

Single Client Access Name (SCAN) eliminates the need to change TNSNAMES entry when nodes are added to or removed from the Cluster. RAC instances register to SCAN listeners as remote listeners. Oracle recommends assigning 3 addresses to SCAN, which will create 3 SCAN listeners, though the cluster has got dozens of nodes.. SCAN is a domain name registered to at least one and up to three IP addresses, either in DNS (Domain Name Service) or GNS (Grid Naming Service). The SCAN must resolve to at least one address on the public network. For high availability and scalability, Oracle recommends configuring the SCAN to resolve to three addresses.
http://www.freeoraclehelp.com/2011/12/scan-setup-for-oracle-11g-release211gr2.html

How to start SCAN and SCAN Listener:
-----------------------------------
#$GRID_HOME/bin/srvctl start scan
#$GRID_HOME/bin/srvctl start scan_listener

How to STOP the SCAN LISTENER and the SCAN VIP resources:
--------------------------------------------------------
#$GRID_HOME/bin/srvctl stop scan_listener
#$GRID_HOME/bin/srvctl stop scan

How to check the STATUS of the SCAN LISTENER and the SCAN VIP resources:
-----------------------------------------------------------------------
#$GRID_HOME/bin/srvctl status scan_listener
SCAN listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is not running
SCAN listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is not running
SCAN listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is not running

#$GRID_HOME/bin/srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is not running
SCAN VIP scan2 is enabled
SCAN VIP scan2 is not running
SCAN VIP scan3 is enabled
SCAN VIP scan3 is not running

How to check SCAN-VIP in the resource file:
------------------------------------------
#$GRID_HOME/bin/srvctl config scan
SCAN name:rac-scan,Network: 1/10.100.10.0/255.255.255.0/eth1
SCAN VIP name: scan1, /rac-scan.localdomain.com/10.1000.10.21
SCAN VIP name: scan1, /rac-scan.localdomain.com/10.1000.10.22
SCAN VIP name: scan1, /rac-scan.localdomain.com/10.1000.10.23

How to check SCN IP address on DNS:
----------------------------------
$nslookup <scan-name>

$nslookup rac-scan.localdomain.com
Server: dns.localdomain.com
Address: 10.100.10.70#53

Name:rac-scan.localdomain.com
Address: 10.100.10.23
Name:rac-scan.localdomain.com
Address: 10.100.10.22
Name:rac-scan.localdomain.com
Address: 10.100.10.21

BACKUP DATABASE LINK IN ORACLE

Database links backup
---------------------

All DBlinks backup:
---------------------
set pages 0
set long 90000
set trimspool on
SELECT u.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',u.DB_LINK,u.OWNER) FROM dba_db_links u;

For a specific DBlink backup:
---------------------------------
set pages 0
set long 90000
set trimspool on
SELECT DBMS_METADATA.GET_DDL('DB_LINK','&DB_LINK_NAME','&USER_NAME') FROM dba_db_links;

PROCEDURE BACKUP IN ORACLE

PROCEDURE BACKUP
----------------
set heading off
set feedback off
set trimspool on
set echo off
set flush off
set pagesize 0
set linesize 32767
set long 99999999

spool PROCEDURE_NAME.LOG
select dbms_metadata.get_ddl('PROCEDURE','&PROCEDURE_NAME','&OWNER_NAME') from dual
/
spool off;

OR

set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off

spool PROCEDURE_NAME.LOG
select text from dba_source where owner='&OWNER_NAME'and name='&PROCEDURE_NAME'
/
spool off;

August 17, 2013

TABLE_EXISTS_ACTION in Datapump

TABLE_EXISTS_ACTION during IMPORT (IMPDP)

In data pump import the parameter TABLE_EXISTS_ACTION help to do the job. The default value of this parameter is SKIP which means if table to be imported already existed in the database table will be skipped and data not to be imported and continue processing next object. However if in your import job if CONTENT=DATA_ONLY is specified, the default is APPEND, and then data will be appended into existing table.

TABLE_EXISTS_ACTION can have following values.

1)SKIP: It leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

2)APPEND: This option loads rows from the source and leaves existing rows unchanged. This is a default option is CONTENT=DATA_ONLY is specified.

3)TRUNCATE: This option deletes existing rows and then loads rows from the source.

4)REPLACE: This option drops the existing table in the database and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

Note
- If you use TRUNCATE or REPLACE, make sure that rows in the affected tables are not targets of any referential constraints.

- If you use SKIP, APPEND, or TRUNCATE then existing table dependent objects in the source, such as indexes, grants, triggers, and constraints, are ignored. In case of REPLACE, the dependent objects are dropped and again created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system.

- If you use APPEND or TRUNCATE, checks are made to ensure that rows from the source are compatible with the existing table prior to performing any action. If the existing table has active constraints and triggers, it is loaded using the external tables access method. If any row violates an active constraint, the load fails and no data is loaded. You can override this behavior by specifying DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS on the Import command line.

If you want data must be loaded but causes constraint voilations, you can disable constraints, import data, delete the rows which causes problems and then enable constraints.

- When you use APPEND, the data is always loaded into new space. So if you have any existing space available the space is not reused. So after the import operation, you may wish to compress your data after the load.

- TRUNCATE cannot be used on clustered tables or over network links.

In case of original import use ignore=y option to append data into an existing table. ignore=y causes rows to be imported into existing tables without any errors or messages being given.

How to Copy files from ASM to filesystem and filesystem to ASM

Copy files from ASM to filesystem
---------------------------------
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd

ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/EXPDP
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
ASMCMD> cp dumpfile1.dmp dumpfile1.dmp dumpfile1.dmp /oracle/backup/testdb/expdp
copying +FRA/TESTDB/EXPDP/dumpfile1.dmp -> /oracle/backup/testdb/expdp/dumpfile1.dmp
copying +FRA/TESTDB/EXPDP/dumpfile2.dmp -> /oracle/backup/testdb/expdp/dumpfile2.dmp
copying +FRA/TESTDB/EXPDP/dumpfile3.dmp -> /oracle/backup/testdb/expdp/dumpfile3.dmp
ASMCMD> exit

$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp

Copy files from filesystem to ASM
---------------------------------
$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp

$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd

ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/IMPDP
ASMCMD> ls
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile1.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile1.dmp -> +FRA/TESTDB/EXPDP/dumpfile1.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile2.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile2.dmp -> +FRA/TESTDB/EXPDP/dumpfile2.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile3.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile3.dmp -> +FRA/TESTDB/EXPDP/dumpfile3.dmp
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp

Schema Refresh using ASM Diskgroups

How to create Datapump Export Dumps within ASM diskgroups

Schema Status
-------------

select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 


EXPORT (EXPDP)

$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir EXPDP

1. Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/EXPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;

2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/expdp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;

3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir expdp
$ chmod -R 755 expdp
$ cd expdp
$pwd
/oracle/backup/testdb/expdp
$ df -h /oracle/backup/testdb/expdp -- or
$df -gt /oracle/backup/testdb/expdp

$ sqlplus "/as sysdba"

set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/expdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/expdp

Where DATAPUMP_ASM_DIR is a ASM based directory
and   DATAPUMP_LOG_DIR is on a filesystem.

Here, Export is written to ASM based dictionary,
      but log is written to another directory which is on filesystem.

We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).

$vi schema_export_in_ASM.par

user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:schema_export_in_ASM.log
schemas=schema1,schema2
exclude=statistics
parallel=6
compression=all
cluster=n  ---- 11g

:wq

$nohup expdp parfile=schema_export_in_ASM.par &

$tail -f nohup.out or

$tail -f schema_export_in_ASM.log

==================================================================================================================

scp dumpfiles from Source Server ASM to Destination Server ASM
http://select-star-from.blogspot.in/2013/08/how-to-copy-files-from-asm-to.html
==================================================================================================================

Take schema backup and Drop Schema Objects
http://select-star-from.blogspot.in/2013/05/drop-objects.html
==================================================================================================================

IMPORT (IMPDP)

Schema Status
-------------

select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 

$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir IMPDP

1. Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/IMPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;

2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/impdp';
Directory created. 
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;

3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir impdp
$ chmod -R 755 impdp
$ cd impdp
$pwd
/oracle/backup/testdb/impdp

$ sqlplus "/as sysdba"

set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/impdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/impdp

Where DATAPUMP_ASM_DIR is a ASM based directory
and   DATAPUMP_LOG_DIR is on a filesystem.

Here, Export is written to ASM based dictionary,
      but log is written to another directory which is on filesystem.

We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).

$vi import_schema_export_in_ASM.par

user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:import_schema_export_in_ASM.log
schemas=schema1,schema2
parallel=6
cluster=n  ---- 11g

If required,
REMAP_SCHEMA=source_schema1:destination_schema1,source_schema2:destination_schema2
REMAP_TABLESPACE=source_tablespace1:destination_tablespace1,source_tablespace2:destination_tablespace2

:wq

$nohup impdp parfile=import_schema_export_in_ASM.par &

$tail -f nohup.out

$tail -f import_schema_export_in_ASM.log


Schema Status
-------------

select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 

August 04, 2013

RMAN Incremental Backup Scenario in DR (Standby Database)

RMAN Incremental Backup Scenario in DR (Standby Database)


Checking Dataguard SYNC status

Standby Database (DR)
----------------
SQL>select distinct(checkpoint_time) from v$datafileheader;

CHECKPOINT_TIME
---------------
24-04-1998 18:10:20 ----------------->Should be current date and time to be in SYNC

DR database is out of Sync since Checkpoint time (as above output) leading to Archive Job failure on Production. We need to apply RMAN incremental backup as Archives are on TAPE and taking time to get restored.

August 03, 2013

Network Level Import of Schema

How to take Schema Import at Network Level

The NETWORK_LINK parameter initiates a network import. This means that the impdp client initiates the import request, typically to the local database. That server contacts the remote source database referenced by the database link in the NETWORK_LINK parameter, retrieves the data, and writes it directly back to the target database. There are no dump files involved.

1. Create a TNS entry on the local database tnsnames.ora file for the remote database
Create a TNS entry on the local database tnsnames.ora file for the remote database and create a database link using the TNS entry. The user used in the database link must have read/write permission on the remote servers directory.

SOURCE_DATABASE_LINK =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = remote-loc)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = remote-db)
   )
 )

2. Create the Database Link on the Destination database:

create database link SOURCE_DATABASE_LINK connect to SYSTEM identified by password using 'SOURCE_DATABASE_LINK';

3. Script to get database link information:

$ sqlplus "/as sysdba"

DBLINKS DETAILS
---------------
set pages 50000 lines 32767
col owner for a15
col username for a10
col host for a20
col created for a20

COL DB_LINK FORMAT A30

select owner, db_link, username, host, to_char(created,'dd-mon-yyyy hh24:mi:ss') CREATED from DBA_DB_LINKS order by owner, db_link;

OWNER      DB_LINK                        USERNAME HOST    CREATED
---------- ------------------------------ -------- ------- -------

4. PAR file.
$vi schema_network_level_import.par

user_id="/ as sysdba"
directory=DATA_PUMP_DIR
logfile=schema_network_level_import.log
schemas=schema1,schema2
NETWORK_LINK=source_database_link
FLASHBACK_TIME=SYSTIMESTAMP
exclude=statistics
parallel=6
cluster=n

:wq

$nohup impdp parfile=schema_network_level_import.par &

$tail -f nohup.out

$tail -f schema_network_level_import.log

Optional Parametrs:
------------------

ESTIMATE={BLOCKS | STATISTICS}
FLASHBACK_SCN=scn_number
FLASHBACK_TIME="TO_TIMESTAMP()"

The Import ESTIMATE parameter is valid only if the NETWORK_LINK parameter is also specified.

The FLASHBACK_SCN parameter is valid only when the NETWORK_LINK parameter is also specified. This is because the value is passed to the source system to provide an SCN-consistent data extraction.

This parameter is valid only when the NETWORK_LINK parameter is also specified. This is because the value is passed to the source system to provide a time-consistent import.

FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive.

Script to take user password backup in Oracle 11g

Script to take user password backup in Oracle 11g

$ sqlplus "/as sysdba"

set pages 50000 lines 32767

select 'alter user ' || name || ' identified by values "' || password || "';' from sys.user$
where name in ('&name');

USER DDL

SET LONG 99999999
select dbms_metadata.get_ddl('USER','&username') from dual;

August 02, 2013

How to who has changed the user password and when

How to who has changed the user password and when

$ sqlplus "/as sysdba"

SYS> create table sachin.log (msg varchar2(1000));

Table created.

SYS> create or replace procedure sachin.p (who in varchar2, what in varchar2)
  2  is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into sachin.log values (who||' modifies '||what||'''s password at '||systimestamp);
  6    commit;
  7  end;
  8  /

Procedure created.

SYS> CREATE OR REPLACE FUNCTION verify_function
  2  (username varchar2,
  3    password varchar2,
  4    old_password varchar2)
  5    RETURN boolean IS
  6  BEGIN
  7     sachin.p (user, username);
  8     RETURN(TRUE);
  9  END;
 10  /

Function created.

SYS> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function;

Profile altered.

SYS> alter user sachin identified by sachin;

User altered.

SYS> select * from sachin.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies sachin's password at 23/04/1998 18:54:34.390 +01:00

1 row selected.

SYS> connect sachin/sachin
Connected.
sachin> password
Password changed
sachin> select * from sachin.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies sachin's password at 23/04/1998 18:54:34.390 +01:00
sachin modifies sachin's password at 23/04/1998 18:55:04.093 +01:00

2 rows selected.

REFERENCE:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/autotransaction_pragma.htm

http://www.oracle-base.com/articles/misc/autonomous-transactions.php