FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / Oracle Database Administration

mount windows format folder on linux and oracle archive log

m.mostafa.khalaji — Tue, 13 Dec 2011 08:05:35 GMT

mount -t cifs -o directio //windows-share-folder/db_backup/rman /u01/rman -o cred=/u01/new_script/upcifs

and not archive log with oracle database 10 g

please help me ?

Relational Database Management Systems

Admin — Tue, 29 Nov 2011 21:54:56 GMT

What is/are Relational Database Management Systems?

Wikipeidia states:

A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model.

A short definition of an RDBMS is: a DBMS in which data is stored in tables and the relationships among the data are also stored in tables. The data can be accessed or reassembled in many different ways without having to change the table forms.

Upgrade Oracle 10g to Oracle 11gR2

Admin — Thu, 24 Jun 2010 18:10:10 GMT

Upgrade Steps From 10g to 11gR2

1. BACKUP BINARIES, DB,TNSNAMES,LISTENER,INIT,SPFILE,PWFILE THAT ARE BEING UPGRADED

2. COMPATIBLE PARAMETER MUST BE SET TO MINIMUM 10.0.0.0. THE RECOMMENDED IS 11.2.0 FOR 11gR2 PARAMETER FILE.

Part A.

Install the New Oracle Database Software & Apply any patches necessary.

Part B.

Run the Pre-Upgrade Information Tool (You Must Run This tool)

1. Copy the Pre-Upgrade Information Tool (utlul12i.sql) from the Oracle Database 11gR2 directory: $ORACLE_HOME/rdbms/admin to a temporary directory /tmp.

2. Set your environment to the one that is being upgraded. Assuming 10g.

3. Change directory to /tmp that you copied utlu112i.sql to in Step 1.

4. Start SQL*Plus and login as ‘/ as sysdba’

5. Spool the results to a log file:

o SQL> SPOOL upgrade_info.log

6. Run the Pre-Upgrade Information Tool:

o SQL> @utlul12i.sql

o SQL> SPOOL OFF

Check the output of the Pre-Upgrade Information Tool in upgrade_info.log.

The following is an example of the output generated by the Pre-Upgrade Information Tool:

Oracle Database 11.2 Pre-Upgrade Information Tool 10-14-2008 23:25:25 . ******************************************************************** Database: ****************************************************************** --> name: ORCL --> version: 10.2.0.2.0 --> compatible: 10.2.0.2 --> blocksize: 8192 Run the Pre-Upgrade Information Tool Upgrading to the New Release 3-9 --> platform: Linux IA (32-bit) --> timezone file: V2 . ****************************************************************** Tablespaces: [make adjustments in the current environment] ******************************************************************** WARNING: --> SYSTEM tablespace is not large enough for the upgrade. .... currently allocated size: 560 MB .... minimum required size: 910 MB .... increase current size by: 350 MB .... tablespace is NOT AUTOEXTEND ENABLED.
		--> UNDOTBS1 tablespace is adequate for the upgrade. .... minimum required size: 457 MB .... AUTOEXTEND additional space required: 352 MB --> SYSAUX tablespace is adequate for the upgrade. .... minimum required size: 617 MB .... AUTOEXTEND additional space required: 287 MB --> TEMP tablespace is adequate for the upgrade. .... minimum required size: 61 MB .... AUTOEXTEND additional space required: 41 MB --> EXAMPLE tablespace is adequate for the upgrade. .... minimum required size: 69 MB . ******************************************************************** Update Parameters: [Update Oracle Database 11.2 init.ora or spfile] ****************************************************************** WARNING: --> "sga_target" needs to be increased to at least 388 MB . ****************************************************************** Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile] ****************************************************************** WARNING: --> "plsql_compiler_flags" old value was "INTERPRETED"; new name is "plsql_code_type" new value is "INTERPRETED" . ****************************************************************** Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile] ****************************************************************** --> "max_enabled_roles" --> "remote_os_authent" --> "background_dump_dest" replaced by "diagnostic_dest" --> "user_dump_dest" replaced by "diagnostic_dest" . ****************************************************************** Components: [The following database components will be upgraded or installed] ****************************************************************** --> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> Messaging Gateway [upgrade] VALID --> OLAP Analytic Workspace [upgrade] VALID --> OLAP Catalog [upgrade] VALID --> Oracle Label Security [upgrade] VALID --> EM Repository [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle interMedia [upgrade] VALID --> Spatial [upgrade] VALID Run the Pre-Upgrade Information Tool 3-10 Oracle Database Upgrade Guide --> Data Mining [upgrade] VALID --> Expression Filter [upgrade] VALID --> Rule Manager [upgrade] VALID --> Oracle Application Express [upgrade] --> Oracle OLAP API [upgrade] VALID . ****************************************************************** Miscellaneous Warnings ******************************************************************** WARNING: --> Database is using a timezone file older than version 11. .... After the release migration, it is recommended that DBMS_DST package .... be used to upgrade the 10.2.0.2.0 database timezone version .... to the latest version which comes with the new release. WARNING: --> Database contains stale optimizer statistics.
	.... Refer to the 11g Upgrade Guide for instructions to update .... statistics prior to upgrading the database. .... Component Schemas with stale statistics: .... SYS .... WMSYS .... CTXSYS WARNING: --> Database contains INVALID objects prior to upgrade. .... The list of invalid SYS/SYSTEM objects was written to .... registry$sys_inv_objs. .... The list of non-SYS/SYSTEM objects was written to .... registry$nonsys_inv_objs. .... Use utluiobj.sql after the upgrade to identify any new invalid .... objects due to the upgrade. .... USER PUBLIC has 7 INVALID objects. .... USER FLOWS_010600 has 1 INVALID objects. .... USER SYS has 1 INVALID objects. WARNING: --> Database contains schemas with objects dependent on network packages. .... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs. .... USER WKSYS has dependent objects. .... USER SYSMAN has dependent objects. .... USER FLOWS_010600 has dependent objects. WARNING: --> EM Database Control Repository exists in the database. .... Direct downgrade of EM Database Control is not supported. Refer to the .... 11g Upgrade Guide for instructions to save the EM data prior to upgrade. . WARNING: --> There are materialized view refreshes in progress. .... Ensure all materialized view refreshes are complete prior to upgrade. WARNING: --> There are files which need media recovery. .... Ensure no files need media recovery prior to upgrade. WARNING: --> There are files in backup mode. .... Ensure no files are in backup mode prior to upgrade. WARNING:--> There are outstanding unresolved distributed transactions. .... Resolve outstanding distributed transactions prior to upgrade. WARNING:--> A standby database exists. .... Sync standby database prior to upgrade. WARNING: --> log_archive_format must be updated. .... As of 10.1, log_archive_format requires a %r format qualifier .... be present in its format string. Your current setting is: .... log_archive_format='%t_%s.dbf'. .... Archive Logging is currently OFF, but failure to add the %r to the .... format string will still prevent the upgraded database from starting up. WARNING:--> recycle bin in use. .... Your recycle bin is turned on and it contains .... 3 object(s). It is REQUIRED .... that the recycle bin is empty prior to upgrading .... your database. Run the Pre-Upgrade Information Tool Upgrading to the New Release 3-11 .... The command: PURGE DBA_RECYCLEBIN 1. .... must be executed immediately prior to executing your upgrade.

Check the warning messages and rectify them as needed. The following points must be addressed correctly otherwise there maybe problems.

Example: CONNECT role now only has create session privilege.

If you have any db links with passwords, you may want to back up the sys.link$ table to save the information.

Oracle recommends gathering stats before the upgrade: EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

■ Deprecated CONNECT Role

■ Access Control to Network Utility Packages

■ Database Links with passwords

■ TIMESTAMP WITH TIME ZONE Data Type

■ Optimizer Statistics

■ Invalid Objects

■ Save Oracle Enterprise Manager Database Control Data

■ Complete Materialized View Refreshes

■ Ensure No Files Need Media Recovery

■ Ensure No Files Are in Backup Mode

■ Resolve Outstanding Distributed Transactions

■ Sync Standby Database with the Primary Database

■ Purging the Database Recycle Bin

Part C.

Prepare the new 11gR2 ORACLE_HOME

1. Copy configuration files (init file, tnsnames, listener, pwfile) from old Oracle Home to new.

2. Update init files with new COMPATIBLE parameter, fix any deprecated ones, and adjust the values to at least the minimum values indicated by the Pre-Upgrade Tool.

3. Update any relative path names in parameter file to fully path names.

Part D.

Upgrade the Database

1. Shutdown the database:

o SQL> SHUTDOWN IMMEDIATE;

2. Make sure the following checks:

o The oratab file points to Oracle Database 11g Release 2 Oracle Home

o The following environment variables point to the Oracle 11g Release 2 directories:

§ ORACLE_HOME

§ PATH

3. Change to the $ORACLE_HOME/rdbms/admin directory and start SQL*Plus

o sqlplus ‘/ as sysdba’

4. Start the instance by issuing following command (you may get messages that parameters are obsolete, fix those and start up the db again):

o SQL> STARTUP UPGRADE;

o SQL> SPOOL upgrade.log

5. Run the catupgrd.sql script:

o SQL> @catupgrd.sql

6. Once completed, shutdown the database and restart it.

o SQL> SHUTDOWN IMMEDIATE;

o SQL> STARTUP;

7. Run the Post-Upgrade Status Tool to provide a summary of the upgrade. (If there are any INVALID components, then check upgrade manual for fixes.)

o SQL> @utlul12s.sql

8. Run catuppst.sql to perform upgrade actions that do not require db to be in upgrade mode:

o SQL> $ORACLE_HOME/rdbms/admin/catuppst.sql

9. Run utlrp to recompile any remaining stored PL/SQL and other objects.

o SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql

10. Verify that all objects are valid:

o SQL> SELECT count(*) FROM dba_invalid_objects;

o SQL> SELECT distinct object_name FROM dba_invalid_objects;

You Can download these steps in the attachment.

Oracle RMAN Backup is Slow if AIO is enabled

Admin — Fri, 13 Aug 2010 15:25:46 GMT

When I enabled aio on the LPAR, RMAN takes drastically too long time to complete backup , but if i disable aio on the same , the backup
is completed within one hour , i don't understand this peculier behaviour , could some one make me understand why it is so , any
config to be changed to aio to work correctly.

What is oradiag_oracle directory in oracle's home directory in 11.2?

Admin — Tue, 08 Jun 2010 16:51:10 GMT

What is oradiag_oracle directory in oracle's home directory in 11.2?

This directory is created in the home directory of the oracle user, after making a local connection.

Take a look here:

oracle->TESTDB1:/u01> ls -ltr

total 16
drwxrwxr-x 3 oracle 10101 4096 Jun 8 14:05 downloads
drwxr-xr-x 5 oracle oinstall 4096 Jun 8 14:19 app

oracle->TESTDB1:/u01> sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 8 16:50:05 2010

SQL> exit;

Disconnected
oracle->TESTDB1:/u01> ls -ltr

total 16
drwxrwxr-x 3 oracle 10101 4096 Jun 8 14:05 downloads
drwxr-xr-x 5 oracle oinstall 4096 Jun 8 14:19 app
drwxr-xr-x 3 oracle oinstall 4096 Jun 8 14:36 oradiag_oracle

Thanks,

Outputting Spool correct in oracle. Spooling only results, spool output in oracle

Admin — Wed, 09 Dec 2009 13:27:36 GMT

I recently was in a process to create a script that rebuilds indexes on a weekly basis and I had a unix shell script that dynamically created the alter statements and spools it out to a file and then runs it. I was trying to figure out how to spool only the results and stop the heading and feedbacks etc.. This is what I had and it kept on putting the sql statement and spool off in the spool file:

#!/usr/bin/ksh

. ~/.profile_PRODDB

echo $ORACLE_SID
echo $ORACLE_HOME
echo " "

$ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF
set feedback off
set verify off
set pagesize 0
set term off
set termout off
set heading off
set echo off
spool rebuild_ecom_indexes.sql
select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where owner='ECOM' and TEMPORARY='N';
spool off
set timing on
set term on
spool /oracle/dba/scripts/logs/rebuild_indexes.log
@rebuild_ecom_indexes.sql
EOF

When I run this unix shell script it kept on putting the following in the rebuild_ecom_indexes.sql script:

SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where owner='ECOM' and TEMPORARY='N';
alter index ECOM.IDX$$_17CE30002 rebuild;
alter index ECOM.SYS_C007048 rebuild;
alter index ECOM.ADD_ARC_IDXSYSID rebuild;
alter index ECOM.ADD_ARC_EXPORDERS2 rebuild;
alter index ECOM.CUST_4413_5487__PRIMARYKEY rebuild;
SQL> spool off

I couldn't figure out why the item in red above kept coming into the spool file. I had set the following in the script:

set feedback off
set verify off
set pagesize 0
set term off
set termout off
set heading off
set echo off

And still no good. I started searching google and couldn't figure it out ... there were others looking for the same soultion and i found out that in the sqlplus command we need to issue a -s and it will stop the sql commands from being spooled out to the spool file.

So it should look like this:

$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF
set feedback off
set verify off
set pagesize 0
set term off
set termout off
set heading off
set echo off
spool rebuild_ecom_indexes.sql
select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where owner='ECOM' and TEMPORARY='N';
spool off
set timing on
set term on
spool /oracle/dba/scripts/logs/rebuild_indexes.log
-- @rebuild_ecom_indexes.sql
EOF

Hope this helps.

Good luck,

Thanks,

Data Masking helps in reducing data privacy violations

debrah.h48 — Wed, 09 Dec 2009 07:36:40 GMT

We've been using data masking in our business very effectively for quit some time now. Masking helps in hiding the specific fields that identify an individual, potentially exposing customers or employees to identity theft or other forms of privacy invasion.

Data Security for Oracle E-Business Suite

debrah.h48 — Fri, 27 Nov 2009 04:33:18 GMT

Recently we had a bad experience of losing live data. So with the help of Solix we started creating non-production clones of Oracle E-Business Suite for testing, development, and even training. It really helped us a lot in E-Business Suite Archiving.

Turning on Audit in Oracle database

Admin — Wed, 28 Oct 2009 16:29:31 GMT

The standard audit commands allow all system privileges to be audited along with access at the object level to any table or

view on the database for select, delete, insert or update. Audit can be run for either successful or unsuccessful attempts or both.

It can be for each individual user or for all users and it can also be done at the session level or access level. At action level a single

record is created per action and at session level one record is created for all audit actions per session.

Here are some commands to set auditing on:

To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user,

BY SESSION (the default and only value for this option), enter the following statement:

AUDIT SESSION;

You can set this option selectively for individual users also, as in the next example:

AUDIT SESSIONBY scott, lori;

Privilege audit options exactly match the corresponding system privileges. For example, the option to audit use of the

DELETE ANY TABLE privilege is DELETE ANY TABLE. To turn this option on, you use a statement similar to the following example:

AUDIT DELETE ANY TABLE    BY ACCESS    WHENEVER NOT SUCCESSFUL;

To audit all successful and unsuccessful uses of the DELETE ANY TABLE system privilege, enter the following statement:
AUDIT DELETE ANY TABLE;
 
To audit all unsuccessful SELECT, INSERT, and DELETE statements on all tables and unsuccessful uses of the 
EXECUTE PROCEDURE system privilege, by all database users, and by individual audited statement, 
issue the following statement:
 
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE      BY ACCESS      WHENEVER NOT SUCCESSFUL;
 
To audit all successful and unsuccessful DELETE statements on the scott.emp table, 
BY SESSION (the default value), enter the following statement:
 
AUDIT DELETE ON scott.emp;
 
To audit all successful SELECT, INSERT, and DELETE statements on the dept table owned by user jward, 
BY ACCESS, enter the following statement:
 
AUDIT SELECT, INSERT, DELETE     ON jward.dept     BY ACCESS     WHENEVER SUCCESSFUL;
 
To set the default object auditing options to audit all unsuccessful SELECT statements, 
BY SESSION (the default), enter the following statement:
 
AUDIT SELECT     ON DEFAULT     WHENEVER NOT SUCCESSFUL;

You can check the following to see audit settings in oracle.

set serveroutput on size 1000000

--
-- SQL to check if the audit has been enabled in the database and where the output will be
-- written to.
--

select name,value from v$parameter
where name like 'audit%'
/

--
-- Test to see what audit actions are defined. Note:- doesn't include object audit actions.
--

select * from dba_stmt_audit_opts
union
select * from dba_priv_audit_opts
/

--
-- Turn on audit for capturing logon and logoff attemempts for all users except
-- privileged attempts.
--

audit create session
/

--
-- check what audit privileges have been granted and to what users and roles.
--

select *
from dba_sys_privs
where privilege like '%AUDIT%'

--
-- SQL to test for failed logon attempts, grouped per day.
--

select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY')
from dba_audit_session
where returncode<>0
group by username,terminal,to_char(timestamp,'DD-MON-YYYY')
/

--
-- SQL to check for attempts to access the database with non existant users. This could
-- indicate someone trying to guess user names and passwords.
--

select username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')
from dba_audit_session
where returncode<>0
and not exists (select 'x'
from dba_users
where dba_users.username=dba_audit_session.username)
/

--
-- Check for access attempts to the database at unusual hours. The two times should
-- be altered or passed in and if detail is needed for multiple days then group by
-- the date as well.
--

select username,
terminal,
action_name,
returncode,
to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),
to_char(logoff_time,'DD-MON-YYYY HH24:MI:SS')
from dba_audit_session
where to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') < to_date('08:00:00','HH24:MI:SS')
or to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') > to_date('19:30:00','HH24:MI:SS')
/

--
-- Check for users sharing database logons.
--

select count(distinct(terminal)),username
from dba_audit_session
having count(distinct(terminal))>1
group by username
/

--
-- Check for multiple database accounts being used from one terminal. This could indicate
-- wrong doing as each terminal should in theory be used by one person and one account?
--

select count(distinct(username)),terminal
from dba_audit_session
having count(distinct(username))>1
group by terminal
/

--
-- Check the audit trail for any changes being made to the structure of the database schema.
--

col username for a8
col priv_used for a16
col obj_name for a22
col timestamp for a17
col returncode for 9999
select username,
priv_used,
obj_name,
to_char(timestamp,'DD-MON-YYYY HH24:MI') timestamp,
returncode
from dba_audit_trail
where priv_used is not null
and priv_used<>'CREATE SESSION'
/

Hope this helps.

RMAN Backup and Recovery Solutions Discussion

Admin — Wed, 04 Mar 2009 13:03:15 GMT

What is RMAN?

You can discuss anything related to RMAN here.

Modify AWR Default Snapshot settings.

Admin — Tue, 08 Sep 2009 15:02:20 GMT

Here is how you can modify AWR Default Snapshot settings.

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 21600, -- Minutes (= 15 Days).Current value retained if NULL.
interval => 60); -- Minutes. Current value retained if NULL.
END;
/

Modifying DEFAULT profile.

Admin — Wed, 13 May 2009 10:35:05 GMT

There is a default password verify function under $ORACLE_HOME/rdbms/admin with filename utlpwdmg.sql. This script creates a password verify function named "verify_function" and alters the default profile with the below attributes:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 5
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 6
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;

verify_function has the following attributes:
- Check if the password is same as the username
- Check for the minimum length of the password (default = 4)
- Check if the password is too simple. A dictionary of words may be maintained and a check may be made so as not to allow the words that are too simple for the password. ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd' words are not accepted as password by default)
- Check if the password contains at least one letter, one digit and one punctuation mark.
- Check if the password differs from the previous password by at least 3 letters.

Here is an example of the password verify function:

CREATE OR REPLACE FUNCTION "SYS"."VERIFY_FUNCTION"
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);

BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';

-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;

-- Check for the minimum length of the password
IF length(password) < 8 THEN
raise_application_error(-20002, 'Password length less than 8');
END IF;

-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
-- IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
-- raise_application_error(-20002, 'Password too simple');
-- END IF;

-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit and one character');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
-- GOTO findpunct;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit and one character');
END IF;
-- 3. Check for the punctuation
-- <<findpunct>>
-- ispunct:=FALSE;
-- FOR i IN 1..length(punctarray) LOOP
-- FOR j IN 1..m LOOP
-- IF substr(password,j,1) = substr(punctarray,i,1) THEN
-- ispunct:=TRUE;
-- GOTO endsearch;
-- END IF;
-- END LOOP;
-- END LOOP;
-- IF ispunct = FALSE THEN
-- raise_application_error(-20003, 'Password should contain at least one \
-- digit, one character and one punctuation');
-- END IF;

<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);

IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;

differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;

IF differ < 3 THEN
raise_application_error(-20004, 'Password should differ by at \
least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/

You can customize this script to have different password verify function attributes, profile attributes and to apply to another profile.

Command to find out what are the libraries linked to an executable in unix?

Admin — Tue, 21 Apr 2009 11:47:23 GMT

The question is:

Is there any way to find out what are the libraries linked to an executable?

Yes you can. The following command will assist you in finding the libraries associated with executibles in unix.

OS: SunOS 5.10 sun4u sparc SUNW,Sun-Fire-280R

Command: ldd

Location: /usr/bin/ldd

On HP there is a similar command called: chatr

Example:

# ldd /u01/oradba/product/10.2.0.3/bin/lsnrctl

libclntsh.so.10.1 => /u01/oradba/product/10.2.0.3/lib/libclntsh.so.10.1
libnnz10.so => /u01/oradba/product/10.2.0.3/lib/libnnz10.so
libkstat.so.1 => /lib/64/libkstat.so.1
libnsl.so.1 => /lib/64/libnsl.so.1
libsocket.so.1 => /lib/64/libsocket.so.1
libgen.so.1 => /lib/64/libgen.so.1
libdl.so.1 => /lib/64/libdl.so.1
libsched.so.1 => /usr/lib/64/libsched.so.1
libc.so.1 => /lib/64/libc.so.1
libaio.so.1 => /lib/64/libaio.so.1
libm.so.2 => /lib/64/libm.so.2
librt.so.1 => /lib/64/librt.so.1
libmp.so.2 => /lib/64/libmp.so.2
libmd5.so.1 => /lib/64/libmd5.so.1
libscf.so.1 => /lib/64/libscf.so.1
libdoor.so.1 => /lib/64/libdoor.so.1
libuutil.so.1 => /lib/64/libuutil.so.1
/platform/SUNW,Sun-Fire-280R/lib/sparcv9/libc_psr.so.1
/platform/SUNW,Sun-Fire-280R/lib/sparcv9/libmd5_psr.so.1

Example 2:

# ldd $ORACLE_HOME/bin/oracle

libskgxp10.so => /u01/oradba/product/10.2.0.3/lib/libskgxp10.so
libhasgen10.so => /u01/oradba/product/10.2.0.3/lib/libhasgen10.so
libskgxn2.so => /u01/oradba/product/10.2.0.3/lib/libskgxn2.so
libocr10.so => /u01/oradba/product/10.2.0.3/lib/libocr10.so
libocrb10.so => /u01/oradba/product/10.2.0.3/lib/libocrb10.so
libocrutl10.so => /u01/oradba/product/10.2.0.3/lib/libocrutl10.so
libjox10.so => /u01/oradba/product/10.2.0.3/lib/libjox10.so
libclsra10.so => /u01/oradba/product/10.2.0.3/lib/libclsra10.so
libdbcfg10.so => /u01/oradba/product/10.2.0.3/lib/libdbcfg10.so
libnnz10.so => /u01/oradba/product/10.2.0.3/lib/libnnz10.so
libkstat.so.1 => /lib/64/libkstat.so.1
libnsl.so.1 => /lib/64/libnsl.so.1
libsocket.so.1 => /lib/64/libsocket.so.1
libgen.so.1 => /lib/64/libgen.so.1
libdl.so.1 => /lib/64/libdl.so.1
libsched.so.1 => /usr/lib/64/libsched.so.1
libc.so.1 => /lib/64/libc.so.1
libaio.so.1 => /lib/64/libaio.so.1
librt.so.1 => /lib/64/librt.so.1
libm.so.2 => /lib/64/libm.so.2
libmp.so.2 => /lib/64/libmp.so.2
libmd5.so.1 => /lib/64/libmd5.so.1
libscf.so.1 => /lib/64/libscf.so.1
libdoor.so.1 => /lib/64/libdoor.so.1
libuutil.so.1 => /lib/64/libuutil.so.1
/platform/SUNW,Sun-Fire-280R/lib/sparcv9/libc_psr.so.1
/platform/SUNW,Sun-Fire-280R/lib/sparcv9/libmd5_psr.so.1

HP UX:

chatr oracle
oracle:
shared executable
shared library dynamic path search:
SHLIB_PATH enabled first
embedded path disabled second Not Defined
shared library list:
dynamic /all/oracle/product/8.1.7/lib/libjox8.sl
dynamic /all/oracle/product/8.1.7/lib/libobk.sl
dynamic /usr/lib/librt.2
dynamic /usr/lib/libpthread.1
dynamic /usr/lib/libnss_dns.1
dynamic /usr/lib/libdld.2
dynamic /usr/lib/libm.2
dynamic /usr/lib/libc.2
shared library binding:
deferred
global hash table disabled
plabel caching disabled
global hash array size:1103
global hash array nbuckets:3
shared vtable support disabled
static branch prediction enabled
executable from stack: D (default)
kernel assisted branch prediction enabled
lazy swap allocation disabled
text segment locking disabled
data segment locking disabled
third quadrant private data space disabled
fourth quadrant private data space disabled
data page size: D (default)
instruction page size: D (default)

Moving Datafiles between diskgroups in ASM

Admin — Fri, 13 Mar 2009 11:27:04 GMT

You can follow these steps to move datafiles between diskgroups in a database that is using ASM.

I'll show you the steps for the system related tablespaces: System, UNDO, sysaux etc..

The reason for this is because with the other tablespace you can put them offline and use rman to copy and then bring them online. With SYSTEM/SYSAUX etc.. it's difficult to bring them offline. So let's begin:

Here are the files that I would like to have moved to diskgroup called : +SHARED_DATA_DG03 . Currently they are on diskgroup +SHARED_DATA_DG01.

SQL> col file_name format a65
SQL> col member format a65
SQL> select file_name from dba_data_files;

FILE_NAME
-----------------------------------------------------------------
+SHARED_DATA_DG01/PRODDB/datafile/system.260.681144809
+SHARED_DATA_DG01/PRODDB/datafile/undotbs1.261.681144817
+SHARED_DATA_DG01/PRODDB/datafile/sysaux.262.681144821
+SHARED_DATA_DG01/PRODDB/datafile/tools.264.681144827
+SHARED_DATA_DG01/PRODDB/datafile/users.265.681144827

I've already brought the database down using shutdown immediate command. Now, I'm going to bring it back up using mount.

prdadminsrv01 [PRODDB]-> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Mar 11 16:33:49 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size 2402240 bytes
Variable Size 2843793472 bytes
Database Buffers 1.3237E+10 bytes
Redo Buffers 22708224 bytes
Database mounted.
SQL> exit

Now I'm going into rman to copy the datafiles.

prdadminsrv01 [PRODDB]-> rman

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Mar 11 16:31:03 2009

RMAN> connect target /

connected to target database: PRODDB (DBID=3179283874, not open)

RMAN>

RMAN> copy datafile '+SHARED_DATA_DG01/PRODDB/datafile/system.260.681144809' to '+SHARED_DATA_DG03';

Starting backup at 11-MAR-2009 16:32:24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=71 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+SHARED_DATA_DG01/PRODDB/datafile/system.260.681144809
output filename=+SHARED_DATA_DG03/PRODDB/datafile/system.265.681237145 tag=TAG20090311T163225 recid=1 stamp=681237155
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 11-MAR-2009 16:32:40

RMAN> copy datafile '+SHARED_DATA_DG01/PRODDB/datafile/undotbs1.261.681144817' to '+SHARED_DATA_DG03';

Starting backup at 11-MAR-2009 16:32:42
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+SHARED_DATA_DG01/PRODDB/datafile/undotbs1.261.681144817
output filename=+SHARED_DATA_DG03/PRODDB/datafile/undotbs1.266.681237163 tag=TAG20090311T163243 recid=2 stamp=681237176
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 11-MAR-2009 16:32:58

RMAN> copy datafile '+SHARED_DATA_DG01/PRODDB/datafile/sysaux.262.681144821' to '+SHARED_DATA_DG03';

Starting backup at 11-MAR-2009 16:33:02
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+SHARED_DATA_DG01/PRODDB/datafile/sysaux.262.681144821
output filename=+SHARED_DATA_DG03/PRODDB/datafile/sysaux.267.681237183 tag=TAG20090311T163302 recid=3 stamp=681237193
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 11-MAR-2009 16:33:18

RMAN> copy datafile '+SHARED_DATA_DG01/PRODDB/datafile/tools.264.681144827' to '+SHARED_DATA_DG03';

Starting backup at 11-MAR-2009 16:33:20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+SHARED_DATA_DG01/PRODDB/datafile/tools.264.681144827
output filename=+SHARED_DATA_DG03/PRODDB/datafile/tools.268.681237201 tag=TAG20090311T163320 recid=4 stamp=681237203
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 11-MAR-2009 16:33:27

RMAN> copy datafile '+SHARED_DATA_DG01/PRODDB/datafile/users.265.681144827' to '+SHARED_DATA_DG03';

Starting backup at 11-MAR-2009 16:33:30
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+SHARED_DATA_DG01/PRODDB/datafile/users.265.681144827
output filename=+SHARED_DATA_DG03/PRODDB/datafile/users.269.681237211 tag=TAG20090311T163330 recid=5 stamp=681237211
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 11-MAR-2009 16:33:33

RMAN> exit

Recovery Manager complete.

The next step is to issue the rename command to rename the files. This step you have to becareful because if you look at the rman command above, you will notice that rman gave a new name to the file. Look at the output filename= part and you will see that each datafile is given a new name. This is because I let oracle manage my files. Here is the step to rename the file.

prdadminsrv01 [PRODDB]-> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Mar 11 16:33:49 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> alter database rename file '+SHARED_DATA_DG01/PRODDB/datafile/system.260.681144809' to '+SHARED_DATA_DG03/PRODDB/datafile/system.265.681237145';

Database altered.

SQL> alter database rename file '+SHARED_DATA_DG01/PRODDB/datafile/undotbs1.261.681144817' to '+SHARED_DATA_DG03/PRODDB/datafile/undotbs1.266.681237163';

Database altered.

SQL> alter database rename file '+SHARED_DATA_DG01/PRODDB/datafile/sysaux.262.681144821' to '+SHARED_DATA_DG03/PRODDB/datafile/sysaux.267.681237183';

Database altered.

SQL> alter database rename file '+SHARED_DATA_DG01/PRODDB/datafile/tools.264.681144827' to '+SHARED_DATA_DG03/PRODDB/datafile/tools.268.681237201';

Database altered.

SQL> alter database rename file '+SHARED_DATA_DG01/PRODDB/datafile/users.265.681144827' to '+SHARED_DATA_DG03/PRODDB/datafile/users.269.681237211';

Database altered.

SQL>

That's pretty much it. You can open your database now.

SQL> alter database open;

Database altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+SHARED_DATA_DG03/PRODDB/datafile/system.265.681237145
+SHARED_DATA_DG03/PRODDB/datafile/undotbs1.266.681237163
+SHARED_DATA_DG03/PRODDB/datafile/sysaux.267.681237183
+SHARED_DATA_DG03/PRODDB/datafile/tools.268.681237201
+SHARED_DATA_DG03/PRODDB/datafile/users.269.681237211

Hope you found this useful. There are many other ways that can be done. If you have done it differently feel free to share it with others.

Thanks,

UPDATING SCHEDULER WINDOWS:

Admin — Thu, 05 Mar 2009 10:52:50 GMT

There are times when you will need to update your windows based on business requirements or simply because your process takes longer then the default weeknight and weekend scheduler window. Here are some guidelines to use for creating and updating scheduler activities.

[b]To create a window, use the CREATE_WINDOW procedure. The following statement illustrates an example of creating a window:[/b]

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW (
window_name => 'CUSTOM_WINDOW',
resource_plan => 'HI_RESOURCE1',
start_date => '15-JAN-09 01.00.00 AM',
repeat_interval => 'FREQ=DAILY',
end_date => '15-SEP-09 01.00.00 AM',
duration => interval '60' minute,
window_priority => 'HIGH',
comments => 'This is my custom window.');
END;
/

There are default windows already created in the system. If you would like to make changes to the attributes to the default windows, here are the steps:
Default window names are: WEEKNIGHT_WINDOW and WEEKEND_WINDOW:

[b]This will change the WEEKNIGHT_WINDOW to run M-F starting at 5pm (17hours). It will also run for 600 Minutes or 10 hours.[/b]

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'repeat_interval', 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=17;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'duration', interval '600' minute);

[b]This will change the WEEKEND_WINDOW to run from Friday - Sunday (5pm to 3AM) (10 Hours)[/b]

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW', 'repeat_interval', 'freq=daily;byday=FRI, SAT, SUN;byhour=17;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW', 'duration', interval '600' minute);

[b]You can see the updated schedule by querying this view:[/b]

select * from dba_scheduler_windows
where window_name in ('WEEKNIGHT_WINDOW','WEEKEND_WINDOW');

BMC SQL Backtrack Discussions

Admin — Wed, 04 Mar 2009 12:56:46 GMT

RMAN vs. BMC SQL Backtrack

Is is really necessary to purchase SQL Backtrack? Why would anyone pay for it if you can do same thing via RMAN?

Well for starters, SQL Backtrack does a nice job in logical extraction. Say you want to bring back a table from a backup, you can do that with SQL Backtrack but not with RMAN. I don't know how they do it behind the scenes but that is awesome feature.

At the same time, RMAN had the ability to find out block corruption and allows you to do incrementals as well.

Both have their positives and negatives. We'll see them in the points coming in newer posts.

Reverse Engineering Oracle Database Schema

Admin — Mon, 23 Feb 2009 15:01:42 GMT

Have you ever wanted to reverse engineer a database schema but didn't have a GUI tool such as TOAD or DBArtisian?
We all know how tricky this was and very time consuming prior to version 9i.

Here are some helpful tips in getting you the results with some of the newer features that allow you to do this.

Hope it helps and do leave some feedback and your experiences regarding this.

Thanks,

Use can use GET_DDL and granted_ddl to retrieve the dll of objects in the database.
Here is some examples:

dbms_metadata.get_ddl

The general syntax of GET_DDL is
GET_DDL(object_type, name, schema, version, model, transform);

select DBMS_METADATA.GET_DDL('TABLE',table_name) from user_tables;

select DBMS_METADATA.GET_DDL('INDEX','DEPT_IDX','USER_NAME') from dual;

select DBMS_METADATA.GET_DDL('USER','USER_NAME') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','USER_NAME') from dual;

Remember when running these commands you will need to adjust your pagesize and linesize:

set linesize 110
set pagesize 100
set long 1000
set echo off
set heading off

You can adjust these based on how much data it's retrieved from the statement.

[b]Example:[/b]

SQL> set long 10000
SQL> select DBMS_METADATA.GET_DDL('DB_LINK','CNV_LNK','PUBLIC') from dual;

DBMS_METADATA.GET_DDL('DB_LINK','CNV_LNK','PUBLIC')
--------------------------------------------------------------------------------

CREATE PUBLIC DATABASE LINK "CNV_LNK"
CONNECT TO "CNV_RO" IDENTIFIED BY VALUES '043C7G221E5F5C736E3D91704EAFG419HJ1411100639LLF12X'
USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = NJCVRNYKSPDB01)(PORT =1521))(CONNECT_DATA = (SID = PROD)))'

Querying DBA_SOURCE

Admin — Thu, 19 Feb 2009 16:30:28 GMT

I didn't have TOAD or any GUI utility in my laptop and I needed to view the source of a procedure. Now we all know how difficult it can be do view the source in DBA_SOURCE view. The output can be horrible and you'll never know what you're looking at if you don't format it properly.

Here is a quick outline to help you get a better output from DBA_SOURCE view.

SQL>
SQL>
SQL>
SQL> set feedback on
SQL> set heading off
SQL> set termout off
SQL> set linesize 1000
SQL> set trimspool on
SQL> set verify off
SQL>
SQL> select decode( type||'-'||to_char(line,'fm99999'),
2 'PACKAGE BODY-1', '/'||chr(10),
3 null) ||
4 decode(line,1,'create or replace ', '' ) ||
5 text text
6 from dba_source
7 where name = '';

You can adjust the output based on your linesize and pagesize.

Good luck.

Thanks,

Data Pump Enhancements in Oracle Database 11g Release 1

Admin — Wed, 04 Feb 2009 11:10:30 GMT

COMPRESSION

The COMPRESSION parameter allows you to decide what, if anything, you wish to compress in your export. The syntax is shown below.

COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}

The available options are:

ALL: Both metadata and data are compressed.
DATA_ONLY: Only data is compressed.
METADATA_ONLY: Only metadata is compressed. This is the default setting.
NONE: Nothing is compressed.

Here is an example of the COMPRESSION parameter being used.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log  compression=all

The COMPATIBLE initialization parameter should be set to "11.0.0" or higher to use these options, except for the METADATA_ONLY option, which is available with a COMPATIBLE setting of "10.2".

Encryption Parameters

Data pump encryption is an Enterprise Edition feature, so the parameters described below are only relevant for Enterprise Edition installations. In addition, the COMPATIBLE initialisation parameter must be set to "11.0.0" or higher to use these features.

ENCRYPTION and ENCRYPTION_PASSWORD

The use of encryption is controlled by a combination of the ENCRYPTION or ENCRYPTION_PASSWORD parameters. The syntax for the ENCRYPTION parameter is shown below.

ENCRYPTION = {ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE}

The available options are:

ALL: Both metadata and data are encrypted.
DATA_ONLY: Only data is encrypted.
ENCRYPTED_COLUMNS_ONLY: Only encrypted columns are written to the dump file in an encrypted format.
METADATA_ONLY: Only metadata is encrypted.
NONE: Nothing is encrypted.

If neither the ENCRYPTION or ENCRYPTION_PASSWORD parameters are set, it is assumed the required level of encryption is NONE. If only the ENCRYPTION_PASSWORD parameter is specified, it is assumed the required level of encryption is ALL. Here is an example of these parameters being used.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log  encryption=all encryption_password=password

ENCRYPTION_ALGORITHM

The ENCRYPTION_ALGORITHM parameter specifies the encryption algorithm to be used during the export, with the default being "AES128". The syntax is shown below.

ENCRYPTION_ALGORITHM = { AES128 | AES192 | AES256 }

The ENCRYPTION_ALGORITHM parameter must be used in conjunction with the ENCRYPTION or ENCRYPTION_PASSWORD parameters, as shown below.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log  encryption=all encryption_password=password encryption_algorithm=AES256

ENCRYPTION_MODE

The ENCRYPTION_MODE parameter specifies the type of security used during export and import operations. The syntax is shown below.

ENCRYPTION_MODE = { DUAL | PASSWORD | TRANSPARENT }

The allowable values and their default settings are explained below:

DUAL: This mode creates a dump file that can be imported using an Oracle Encryption Wallet, or the the ENCRYPTION_PASSWORD specified during the export operation. This is the default setting if the ENCRYPTION_PASSWORD parameter is set and there is an open wallet.
PASSWORD: This mode creates a dump file that can only be imported using the ENCRYPTION_PASSWORD specified during the export operation. This is the default setting if the ENCRYPTION_PASSWORD parameter is set and there isn't an open wallet.
TRANSPARENT: This mode creates an encrypted dump file using and open Oracle Encryption Wallet. If the ENCRYPTION_PASSWORD is specified while using this mode and error is produced. This is the default setting of only the ENCRYPTION parameter is set.

Wallet setup is described here.

The ENCRYPTION_MODE requires either the ENCRYPTION or ENCRYPTION_PASSWORD parameter to be specified.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log  encryption=all encryption_password=password encryption_mode=password

TRANSPORTABLE

The TRANSPORTABLE parameter is similar to the TRANSPORT_TABLESPACES parameter available previously in that it only exports and imports metadata about a table, relying on you to manually transfer the relevent tablespace datafiles. The export operation lists the tablespaces that must be transfered. The syntax is shown below.

TRANSPORTABLE = {ALWAYS | NEVER}

The value ALWAYS turns on the transportable mode, while the default value of NEVER indicates this is a regular export/import.

The following restrictions apply during exports using the TRANSPORTABLE parameter:

This parameter is only applicable during table-level exports.
The user performing the operation must have the EXP_FULL_DATABASE privilege.
Tablespaces containing the source objects must be read-only.
The COMPATIBLE initialization parameter must be set to 11.0.0 or higher.
The default tablespace of the user performing the export must not be the same as any of the tablespaces being transported.

Some extra restictions apply during import operations:

The NETWORK_LINK parameter must be specified during the import operation. This parameter is set to a valid database link to the source schema.
The schema performing the import must have both EXP_FULL_DATABASE and IMP_FULL_DATABASE privileges.
The TRANSPORT_DATAFILES parameter is used to identify the datafiles holding the table data.

Examples of the export and import operations are shown below.

expdp system tables=TEST1.TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log  transportable=ALWAYSimpdp system tables=TEST1.TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log  transportable=ALWAYS network_link=DB11G transport_datafiles='/u01/oradata/DB11G/test01.dbf'

PARTITION_OPTIONS

The PARTITION_OPTIONS parameter determines how partitions will be handled during export and import operations. The syntax is shown below.

PARTITION_OPTIONS={none | departition | merge}

The allowable values are:

NONE: The partitions are created exactly as they were on the system the export was taken from.
DEPARTITION: Each partition and sub-partition is created as a separate table, named using a combination of the table and (sub-)partition name.
MERGE: Combines all partitions into a single table.

The NONE and MERGE options are not possible if the export was done using the TRANSPORTABLE parameter with a partition or subpartition filter. If there are any grants on objects being departitioned, an error message is generated and the objects are not loaded.

expdp test/test directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log tables=test.tab1  partition_options=merge

REUSE_DUMPFILES

The REUSE_DUMPFILES parameter can be used to prevent errors being issued if the export attempts to write to a dump file that already exists.

REUSE_DUMPFILES={Y | N}

When set to "Y", any existing dumpfiles will be overwritten. When the default values of "N" is used, an error is issued if the dump file already exists.

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log  reuse_dumpfiles=y

REMAP_TABLE

This parameter allows a table to be renamed during the import operations performed using the TRANSPORTABLE method. It can also be used to alter the base table name used during PARTITION_OPTIONS imports. The syntax is shown below.

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename

An example is shown below.

impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log  remap_table=TEST.TAB1:TAB2

Existing tables are not renamed, only tables created by the import.

DATA_OPTIONS

SKIP_CONSTRAINT_ERRORS

During import operations using the external table acces method, setting the DATA_OPTIONS parameter to SKIP_CONSTRAINT_ERRORS allows load operations to continue through non-deferred constraint violations, with any violations logged for future reference. Without this, the default action would be to roll back the whole operation. The syntax is shown below.

DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

An example is shown below.

impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log  data_options=SKIP_CONSTRAINT_ERRORS

This parameter has no impact on deferred constraints, which still cause the operation to be rolled back once a violation is detected. If the object being loaded has existing unique indexes or constraints, the APPEND hint will not be used, which may adversely affect performance.

XML_CLOBS

During an export, if XMLTYPE columns are currently stored as CLOBs, they will automatically be exported as uncompressed CLOBs. If on the other hand they are currently stored as any combination of object-relational, binary or CLOB formats, they will be exported in compressed format by default. Setting the DATA_OPTIONS parameter to XML_CLOBS specifies that all XMLTYPE columns should be exported as uncompressed CLOBs, regardless of the default action. The syntax is shown below.

DATA_OPTIONS=XML_CLOBS

An example is shown below.

expdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log  version=11.1 data_options=XML_CLOBS

Both the export and import must use the same XML schema and the job version must be set to 11.0.0 or higher.

REMAP_DATA

During export and import operations, the REMAP_DATA parameter allows you to associate a remap packaged function that will accept the column value as a parameter and return a modified version of the data. The syntax is shown below.

REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function

This can be used to mask sensitive data during export and import operations by replacing the original data with random alternatives. The mapping is done on a column-by-column basis, as shown below.

expdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log  remap_data:tab1.col1:remap_pkg.remap_col1  remap_data:tab1.col2:remap_pkg.remap_col2

The remapping function must return the same datatype as the source column and it must not perform a commit or rollback.

RMAN Configuration Step By Step

Admin — Wed, 04 Feb 2009 10:36:37 GMT

From a command prompt type in the following (depending on your OS/Shell):

set ORACLE_SID=your oracle SID

rman target /

my sid=orcl

You should see something similar to this:

Next thing, is your database running in ARCHIVE LOG MODE?
You can check this by typing archive log list from the sql command prompt.

sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Feb 2 20:39:08 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 218
Current log sequence 220
SQL>

This will affect how you back up your database.

The above database is not running archive and I can only therefore carry out an OFFLINE backup. In rman you can issue this:

RMAN> run {
shutdown immediate;
startup mount;
backup database;
alter database open;
}

If your database is running in archive log mode you can issue this:

run {
backup database include current controlfile;
backup (archivelog all delete input);
}

The above command will backup the database and all existing archive log files and then it will delete those archive logs.

Then type this command to list the backups:

list backup;

This is the basic rman backups.