Oracle

Accounts

How to reset a user's password

SQL> ALTER USER user_name IDENTIFIED BY new_password REPLACE old_password

How to unlock an account

• Login as system and unlock the account

# sqlplus system/*******

• Then on the sqlplus prompt do:

SQL> ALTER USER user_name ACCOUNT UNLOCK;

How to list locked accounts

On the sqlplus prompt execute:

SQL> SELECT username, account_status FROM dba_users;

How to remove an account's lock timeout

On the sqlplus prompt execute:

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED;
SQL> NOAUDIT ALL;
SQL> DELETE FROM SYS.AUD$;

Backup

Howto backup an online oracle database

1. Login as oracle user and setup archive log mode

# sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 25 01:28:00 2008

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

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1258488 bytes
Variable Size              92277768 bytes
Database Buffers          192937984 bytes
Redo Buffers                2932736 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>

• Run the initial backup

$ORACLE_HOME/config/scripts/backup.sh 
Doing online backup of the database.
Backup of the database succeeded.
Log file is at /usr/lib/oracle/xe/oxe_backup_current.log.
Press ENTER key to exit

• Remove the user notification line at the end of the script and add it to a cron file `/etc/cron.d/oracle`

0 1 * * * oracle $ORACLE_HOME/config/scripts/backup.sh

Howto do a mysqldump-like in oracle

Here is called export and is done with the following self-explained command:

su -l oracle -c 'exp userid=user/password@host file=/tmp/expdat.dmp'