Oracle
From Bashlinux
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'