Difference between revisions of "Oracle"
From Bashlinux
(2 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
+ | == Accounts == |
||
− | __NOTOC__ |
||
+ | === How to reset a user's password === |
||
− | = Oracle = |
||
+ | SQL> ALTER USER user_name IDENTIFIED BY new_password REPLACE old_password |
||
⚫ | |||
+ | |||
+ | === How to unlock an account === |
||
+ | * Login as system and unlock the account |
||
⚫ | |||
+ | * 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 == |
||
⚫ | |||
1. Login as oracle user and setup archive log mode |
1. Login as oracle user and setup archive log mode |
||
+ | # sqlplus /nolog |
||
− | <pre><nowiki> |
||
⚫ | |||
− | |||
SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 25 01:28:00 2008 |
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. |
Copyright (c) 1982, 2005, Oracle. All rights reserved. |
||
⚫ | |||
− | |||
SQL> connect / as sysdba |
SQL> connect / as sysdba |
||
Connected. |
Connected. |
||
Line 19: | Line 36: | ||
SQL> startup mount |
SQL> startup mount |
||
ORACLE instance started. |
ORACLE instance started. |
||
⚫ | |||
− | |||
Total System Global Area 289406976 bytes |
Total System Global Area 289406976 bytes |
||
Fixed Size 1258488 bytes |
Fixed Size 1258488 bytes |
||
Line 27: | Line 44: | ||
Database mounted. |
Database mounted. |
||
SQL> alter database archivelog; |
SQL> alter database archivelog; |
||
+ | |||
− | |||
Database altered. |
Database altered. |
||
+ | |||
− | |||
SQL> alter database open; |
SQL> alter database open; |
||
+ | |||
− | |||
Database altered. |
Database altered. |
||
+ | |||
− | |||
SQL> |
SQL> |
||
− | </nowiki></pre> |
||
⚫ | |||
− | |||
⚫ | |||
⚫ | |||
− | <pre><nowiki> |
||
$ORACLE_HOME/config/scripts/backup.sh |
$ORACLE_HOME/config/scripts/backup.sh |
||
Doing online backup of the database. |
Doing online backup of the database. |
||
Line 46: | Line 59: | ||
Log file is at /usr/lib/oracle/xe/oxe_backup_current.log. |
Log file is at /usr/lib/oracle/xe/oxe_backup_current.log. |
||
Press ENTER key to exit |
Press ENTER key to exit |
||
− | </nowiki></pre> |
||
⚫ | |||
− | |||
⚫ | |||
⚫ | |||
− | <pre><nowiki> |
||
0 1 * * * oracle $ORACLE_HOME/config/scripts/backup.sh |
0 1 * * * oracle $ORACLE_HOME/config/scripts/backup.sh |
||
− | </nowiki></pre> |
||
⚫ | |||
− | |||
⚫ | |||
Here is called export and is done with the following self-explained command: |
Here is called export and is done with the following self-explained command: |
||
⚫ | |||
− | <pre><nowiki> |
||
su -l oracle -c 'exp userid=user/password@host file=/tmp/expdat.dmp' |
su -l oracle -c 'exp userid=user/password@host file=/tmp/expdat.dmp' |
||
− | </nowiki></pre> |
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'