Difference between revisions of "Oracle"
From Bashlinux
(One intermediate revision by the same user not shown) | |||
Line 1: | Line 1: | ||
+ | == Accounts == |
||
− | __NOTOC__ |
||
+ | === 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 |
Latest revision as of 02:32, 16 June 2015
Contents
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'