Difference between revisions of "Oracle"

From Bashlinux
Jump to: navigation, search
Line 1: Line 1:
 
__NOTOC__
 
__NOTOC__
  +
== 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 <tt>sqlplus</tt> prompt do:
  +
SQL> ALTER USER user_name ACCOUNT UNLOCK;
  +
  +
=== How to list locked accounts ===
  +
On the <tt>sqlplus</tt> prompt execute:
  +
SQL> SELECT username, account_status FROM dba_users;
  +
  +
=== How to remove an account's lock timeout ===
  +
On the <tt>sqlplus</tt> 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 ===
 
=== Howto backup an online oracle database ===
 
1. Login as oracle user and setup archive log mode
 
1. Login as oracle user and setup archive log mode

Revision as of 02:31, 16 June 2015

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'