Rman System datafile recovery scenerio

System datafile recovery:

Lets create a scenario where we will drop the system datafile, and we will recover using the rman restore and recover process.Since system datafile is key for database functioning.

Before we start. Lets verify that database is functioning without any problems.

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 20:47:23 2014

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


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

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14


Lets drop system datafile now.

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  users01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  undotbs01.dbf

[oracle@linux1 orcl]$ rm system*

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  users01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf
redo01.log     standby_redo02.log  temp01.dbf
redo02.log     standby_redo03.log  undotbs01.dbf

After dropping system datafile. lets query some data from database. We will now see that database is complaining that it can't access system01.dbf

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 21:18:06 2014

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


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

SQL> select username from dba_users;
select username from dba_users
                     *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Now lets start the recover process.Since this is system datafile it is mandatory that you restart the server in mount state. Even if you try to start the
database using startup, it will just mount the database and fail asking for system.dbf file.

SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort;
ORACLE instance shut down.


Now login into rman session and restore the system datafile.

[oracle@linux1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 7 21:19:20 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1378116623, not open)


RMAN> run {
2> restore datafile 1;
3> recover datafile 1;
4> }

Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/datafiles/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 07-JUL-14

Starting recover at 07-JUL-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 65 is already on disk as file /u01/app/oracle/oraarch/orcl_1_65_850335889.arc
archived log for thread 1 with sequence 66 is already on disk as file /u01/app/oracle/oraarch/orcl_1_66_850335889.arc
archived log for thread 1 with sequence 67 is already on disk as file /u01/app/oracle/oraarch/orcl_1_67_850335889.arc
archived log file name=/u01/app/oracle/oraarch/orcl_1_65_850335889.arc thread=1 sequence=65
media recovery complete, elapsed time: 00:00:05
Finished recover at 07-JUL-14

Note: In case you don't know the datafile number to recover you can use below query.
Most of the time system datafile is the #1. You can run this query  when DB is in mount state.

SQL> select file#,Name, status from v$datafile where name='SYSTEM';

     FILE# Name STATUS
---------- ----------------------------------- -------
         1 /u01/app/oracle/datafiles/orcl/system01.dbf SYSTEM
   


verify that system datafile has been restored and recovered.

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  undotbs01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf        users01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  temp01.dbf


Now open the database and query some random data.

SQL> alter database open;

Database altered.

SQL> select count(username) from dba_users;

COUNT(USERNAME)
---------------
             36

RMAN FULL database recovery scenerio

Full database recovery :

We will create a scenerio where we will drop all the datafiles and do a full database recovery.

• Database name : ORCL
• Assuming that you have already taken the full database backup (if not please take the full backup before your perform this test/task).
• Database is running in archive-log mode, if not to have a consistent backup you need to shutdown the database open it in Mount mode and take a full backup.

-----------      If you haven't taken backup do so (please see below)   ------

[oracle@linux1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 7 20:20:55 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1378116623)

RMAN>  backup database tag"full_backup";

Starting backup at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/datafiles/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/datafiles/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/datafiles/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/datafiles/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/datafiles/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-JUL-14
channel ORA_DISK_1: finished piece 1 at 07-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:51
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-JUL-14
channel ORA_DISK_1: finished piece 1 at 07-JUL-14
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_ncsnf_FULL_BACKUP_9vpkxymd_.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUL-14

----  Oracle DB restore process -------

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 20:18:05 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2214456 bytes
Variable Size             369100232 bytes
Database Buffers           92274688 bytes
Redo Buffers                8241152 bytes

lets check name of database:

SQL> select name from v$database;

NAME
---------
ORCL

Check if the database is in archivelog mode;

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oraarch/
Oldest online log sequence     62
Next log sequence to archive   64
Current log sequence           64


list out all the data-files:

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  undotbs01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf        users01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  temp01.dbf

Now lets drop all the dbf files:

[oracle@linux1 orcl]$ rm *.dbf

lets verify that all files have been dropped :

[oracle@linux1 orcl]$ ls -al
total 368564
drwxr-x--- 2 oracle oinstall     4096 Jul  7 20:29 .
drwxr-xr-x 3 oracle oinstall     4096 Jun 15 20:22 ..
-rw-r----- 1 oracle oinstall  9912320 Jul  7 20:29 control01.ctl
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:25 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:29 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:20 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul  7 20:23 standby_redo04.log

Most times you will see that server is still open, even tough you see that database has crashed and you can't access datafile.
we will now do a full database recovery. since all the datafiles have been lost.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2214456 bytes
Variable Size             369100232 bytes
Database Buffers           92274688 bytes
Redo Buffers                8241152 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'



RMAN> run {
2> restore database;
3> recover database;
4> }

Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/datafiles/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/datafiles/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/datafiles/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/datafiles/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/datafiles/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:16
Finished restore at 07-JUL-14

Starting recover at 07-JUL-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 07-JUL-14

Since the full database recovery has been completed successfully. we need to open the database now

SQL> alter database open;

Database altered.

verify that you can access the database without any errors.

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14


14 rows selected.

How To Shrink Table AUD$

The SYSTEM tablespace is created with manual segment allocation and as such it is not possible to run the SHRINK command for the objects that are located within. However, if the AUD$ table is moved to another tablespace (locally managed with automatic segment space management) then it can be shrunk. It is recommended to use shrink on the AUD$ only during a downtime window, since part of the shrink operation will use incompatible locks as referenced in note 242090.1, also because of some issues fixed with shrink in patchset 11.2.0.2 it is not recommended to use shrink in an earlier version than 11.2.0.2.


Note : These steps have been tested on DB that is older to 10G

To move the AUD$ table one can use the DBMS_AUDIT_MGMT package, as documented in:

Note 731908.1- "New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information"


We need to move the AUD$ table to a locally managed tablespace.
To move the table to a locally managed tablespace with ASSM and then shrink it do the following:

1)

conn / as sysdba

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'USERS');
END;
/

This is manual process of doing it (for pre 11g DB)
2)
 

alter table sys.aud$ enable row movement;
alter table sys.aud$ shrink space cascade;

Comment: if Oracle Label Security (OLS) option is installed and enabled the AUD$ table resides in the SYSTEM schema.


3) If needed the table can be moved back to the SYSTEM tablespace:

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'SYSTEM');
END;
/

Audit Trail Management and Cleanup (including DBMS_AUDIT_MGMT package) is a supported feature for all RDBMS  from 11.2. (Standard Edition and Enterprise Edition)

Drop database including Rman backups in oracle

Example -  Deleting a Database
 
In this example, you want to delete a test database called test1 that is registered in the recovery catalog. You start the RMAN client, connect to database test1 as TARGET, and connect to the recovery catalog. You then run the following commands to delete the target database files, as well as all backups, copies, and archived logs associated with the database:

RMAN> CONNECT TARGET SYS@test1

target database Password: password
connected to target database: TEST1 (DBID=3952556153453)

RMAN> STARTUP FORCE MOUNT

RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';

RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;

ORA-29516: Aurora assertion failure: Assertion failure

ORA-29516: Aurora assertion failure: Assertion failure

From Oracle 11g release 1 (11.1), Oracle introduces just-in-time(JIT) compiler for Oracle JVM environment, which results in faster execution because invalidation, recompilation, and storage of code is done dynamically.

JIT is controlled by parameter java_jit_enabled, and if it is set to TRUE then the Java methods are automatically compiled to native code by the JIT compiler and made available for use by all sessions.

But you can get the error Ora-29516 Aurora assertion failure on Linux x64bit platform, and the workaround to overcome that error is turning off the JIT compiler:

alter system set java_jit_enabled=false;

Oracle user profile setup guide

A profile is a named set of the following password and resource limits:

a.. Password aging and expiration
b.. Password history
c.. Password complexity verification
d.. Account locking
e.. CPU time
f.. Input/output (I/O) operations
g.. Idle time
h.. Connect time
i.. Memory space (private SQL area for Shared Server only)
j.. Concurrent sessions


After a profile has been created, the database administrator can assign it
to each user. If resource limits are enabled, the Oracle server limits the
database usage and resources to the defined profile of the user.

The Oracle server automatically creates a DEFAULT profile when the database
is created.

The users who have not been explicitly assigned a specific profile conform
to all the limits of the DEFAULT profile. All limits of the DEFAULT profile
are initially unlimited. However, the database administrator can change the
values so that limits are applied to all users by default.


Profile usage:
***********
a.. Restrict users from performing some operations that require heavy use
of resources.
b.. Ensure that users log off the database when they have left their
session idle for some time.
c.. Enable group resource limits for similar users.
d.. Easily assign resource limits to users.
e.. Manage resource usage in large, complex multiuser database systems.
f.. Control the use of passwords


Profile characteristics:
*******************
Profile assignments do not affect current sessions.

Profiles can be assigned only to users and not to roles or other profiles.

If you do not assign a profile when creating a user, the user is
automatically assigned the DEFAULT profile.


Password Management
-------------------------------

For greater control over database security, Oracle password management is
controlled by database administrators with profiles.

The available password management features:

Account locking: Enables automatic locking of an account when a user fails
to log in to the system in the specified number of attempts

Password aging and expiration: Enables the password to have a lifetime,
after which it expires and must be changed

Password history: Checks the new password to ensure that the password is not
reused for a specified amount of time or a specified number of password
changes

Password complexity verification: Performs a complexity check on the
password to verify that it is complex enough to provide protection against
intruders who might try to break into the system by guessing the password


Enabling Password Management
--------------------------------------

Create the profile to limit password settings, and assign the profile to the
user by using the CREATE USER or ALTER USER command.

Password limit settings in profiles are always enforced.

When password management is enabled, the user account can be locked or
unlocked by using the CREATE USER or ALTER USER command.

To enable password management, run the utlpwdmg.sql script as the user SYS.


Password Account Locking
---------------------------------

The Oracle server automatically locks an account after the
FAILED_LOGIN_ATTEMPTS value is reached. The account is either automatically
unlocked after a specified time, defined by the PASSWORD_LOCK_TIME
parameter, or it must be unlocked by the database administrator using the
ALTER USER command.

The database account can be explicitly locked with the ALTER USER command.
When this happens, the account is not automatically unlocked.


Password Expiration and Aging
--------------------------------------

The PASSWORD_LIFE_TIME parameter sets the maximum lifetime after which the
password must be changed.

The database administrator can specify a grace period PASSWORD_GRACE_TIME,
which begins after the first attempt to log in to the database after
password expiration. A warning message is generated every time the user
tries to log in until the grace period is over. The user is expected to
change the password within the grace period.

If the password is not changed, the account is locked.

The user's account status is changed to EXPIRED by explicitly setting the
password to be expired.


Password History
----------------------

Password history checks ensure that a user cannot reuse a password for a
specified time interval. These checks can be implemented by using one of the
following:


PASSWORD_REUSE_TIME: To specify that a user cannot reuse a password for a
given number of days

PASSWORD_REUSE_MAX: To force a user to define a password that is not
identical to earlier passwords

When one parameter is set to a value other than DEFAULT or UNLIMITED, the
other parameter must be set to UNLIMITED.


Password Verification
---------------------------

Before assigning a new password to a user, a PL/SQL function can be invoked
to verify the validity of the password.

The Oracle server provides a default verification routine or the database
administrator can write a PL/SQL function.


User-Provided Password Function
----------------------------------------

When a new password verification function is added, the database
administrator must consider the following restrictions:

a.. The procedure must use the specification indicated below.
b.. The procedure returns the value TRUE for success and FALSE for
failure.
c.. If the password function raises an exception, then an error is
returned and the ALTER USER or CREATE USER command is terminated.
d.. The password function is owned by SYS.
e.. If the password function becomes invalid, then an error message is
returned and the ALTER USER or CREATE USER command is terminated.


Template for the Password Function
----------------------------------------

function_name(

userid_parameter IN VARCHAR2(30),

password_parameter IN VARCHAR2(30),

old_password_parameter IN VARCHAR2(30))

RETURN BOOLEAN


Password Verification Function
---------------------------------------

The Oracle server provides a complexity verification function, in the form
of a default PL/SQL function called VERIFY_FUNCTION of the utlpwdmg.sql
script, which must be run in the SYS schema.

During the execution of the utlpwdmg.sql script, the Oracle server creates
VERIFY_FUNCTION and changes the DEFAULT profile with the following ALTER
PROFILE command:


SQL> ALTER PROFILE DEFAULT LIMIT

2 PASSWORD_LIFE_TIME 60

3 PASSWORD_GRACE_TIME 10

4 PASSWORD_REUSE_TIME 1800

5 PASSWORD_REUSE_MAX UNLIMITED

6 FAILED_LOGIN_ATTEMPTS 3

7 PASSWORD_LOCK_TIME 1/1440

8 PASSWORD_VERIFY_FUNCTION verify_function;


The default routine provides the following restrictions:


a.. Minimum length is four characters.
b.. Password should not be equal to username.
c.. Password should have at least one alphabetic, one numeric, and one
special character.
d.. Password should differ from the previous password by at least three
letters.


This is the default routine provided by Oracle in the utlpwdmg.sql file:

CREATE OR REPLACE FUNCTION 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) < 4 THEN

raise_application_error(-20002, 'Password length less than 4');

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, one character and one punctuation');

END IF;

-- 2. Check for the character

<>

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;

END IF;

END LOOP;

END LOOP;

IF ischar = FALSE THEN

raise_application_error(-20003, 'Password should contain at least one
\ digit, one character and one punctuation');

END IF;

-- 3. Check for the punctuation

<>

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;


<>

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

/



Creating a Profile
-------------------------------

Use the following CREATE PROFILE command to administer passwords:

CREATE PROFILE profile LIMIT

[FAILED_LOGIN_ATTEMPTS max_value]

[PASSWORD_LIFE_TIME max_value]

[ {PASSWORD_REUSE_TIME

|PASSWORD_REUSE_MAX} max_value]

[PASSWORD_LOCK_TIME max_value]

[PASSWORD_GRACE_TIME max_value]

[PASSWORD_VERIFY_FUNCTION

{function|NULL|DEFAULT} ]


where:


PROFILE: Is the name of the profile to be created


FAILED_LOGIN_ATTEMPTS: Specifies the number of failed attempts to log in to
the user account before the account locked


PASSWORD_LIFE_TIME: Limits the number of days the same password can be used
for authentication. Password expires if not changed within this period, and
further connections are rejected


PASSWORD_REUSE_TIME: Specifies the number of days before a password can be
reused. If you set PASSWORD_REUSE_TIME to an integer value, then you must
set PASSWORD_REUSE_MAX to UNLIMITED


PASSWORD_REUSE_MAX: Specifies the number of password changes required before
the current password can be reused. If you set PASSWORD_REUSE_MAX to an
integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED


PASSWORD_LOCK_TIME: Specifies the number of days an account will be locked
after the specified number of consecutive failed login attempts


PASSWORD_GRACE_TIME: Specifies the number of days after the grace period
begins during which a warning is issued and login is allowed. Password
expires if not changed during grace period


PASSWORD_VERIFY_FUNCTION: Enables a PL/SQL password complexity verification
function to be passed as an argument to the CREATE PROFILE statement


SQL> CREATE PROFILE grace_5 LIMIT

2 FAILED_LOGIN_ATTEMPTS 3

3 PASSWORD_LOCK_TIME UNLIMITED

4 PASSWORD_LIFE_TIME 30

5 PASSWORD_REUSE_TIME 30

6 PASSWORD_VERIFY_FUNCTION verify_function

7 PASSWORD_GRACE_TIME 5;

Profile created.


SQL> create user spongebob identified by squarepants

2 default tablespace userdata01

3 quota 5m on userdata01

4 profile grace_5;

create user spongebob identified by squarepants

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20003: Password should contain at least one digit, one character and one
punctuation


SQL> ed

Wrote file afiedt.buf

1 create user spongebob identified by square_pants2

2 default tablespace userdata01

3 quota 5m on userdata01

4* profile grace_5


SQL> /

User created.

SQL> connect spongebob/square_pants2

ERROR:

ORA-01045: user SPONGEBOB lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect system/manager

Connected.

SQL> grant create session to spongebob;

Grant succeeded.

SQL> grant resource to spongebob;

Grant succeeded.

SQL> connect spongebob/square_pants2

Connected.

SQL> connect spongebob/square_pants3

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect spongebob/square_pants4

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> connect spongebob/square_pants5

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> connect spongebob/square_pants2

ERROR:

ORA-28000: the account is locked

SQL> connect system/manager

Connected.

SQL> alter user spongebob account unlock;

User altered.

SQL> connect spongebob/square_pants2

Connected.


Altering a Profile
----------------------------

Use the ALTER PROFILE command to change the password limits assigned to a
profile:


ALTER PROFILE profile LIMIT

[FAILED_LOGIN_ATTEMPTS max_value]

[PASSWORD_LIFE_TIME max_value]

[ {PASSWORD_REUSE_TIME

|PASSWORD_REUSE_MAX} max_value]

[PASSWORD_LOCK_TIME max_value]

[PASSWORD_GRACE_TIME max_value]

[PASSWORD_VERIFY_FUNCTION

{function|NULL|DEFAULT} ]


If you want to set the password parameters to less than a day:

1 hour: PASSWORD_LOCK_TIME = 1/24

10 minutes: PASSWORD_LOCK_TIME = 10/1400

5 minutes: PASSWORD_LOCK_TIME = 5/1440


ALTER PROFILE default LIMIT

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LIFE_TIME 60

PASSWORD_GRACE_TIME 10;


Dropping a Profile: Password Setting
-----------------------------------------------

Drop a profile using the DROP PROFILE command:


DROP PROFILE profile [CASCADE]

where:

profile: Is the name of the profile to be dropped


CASCADE: Revokes the profile from users to whom it is assigned (The Oracle
server automatically assigns the DEFAULT profile to such users. Specify this
option to drop a profile that is currently assigned to users.)


Guidelines:

The DEFAULT profile cannot be dropped.

When a profile is dropped, this change applies only to sessions that are
created subsequently and not to the current sessions.


DROP PROFILE developer_prof;

DROP PROFILE developer_prof CASCADE;

Exclude some tables from schema export

 exporting schema excluding some tables:

To exclude table from EXPDP Backup. Let's take a case, you have 100 tables in your schema and you want to export only 99 of them except two which are huge in size and already available at destination. Here, expdp exclude=table:"in\('EMP'\,'DEPT'\)" parameter is best for DBA . Make sure you provide table name in upper case, since values given are case sensitive.

 

expdp scott/***** directory=dpump schemas=scott dumpfile=scottdmp  logfile=scott.log exclude=table:"in\('emp'\,'emp_no'\)"

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 78.68 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

How to insert an ambersand '&' into database

When ever you try to insert an ambersand '&' into database, database tries to intract and get the values values from screen. in that case we can follow below steps

create table test (name varchar2(35));
insert into test values ('hello&world');

I tried the escape character '\' but the system asks for a value of the substitution variable.

Solution:

set define off
create table test (name varchar2(35));
insert into test values ('hello&world');


press 'F5' to execute the above commands.

(OR) 

insert into table1 values('&'||'hello world');

1 row created.

select * from table1;

COL1
--------------------------
&hello world