Oracle 11g Admin - 1 Getting Started with Database Administration

- Connecting to the Database with SQL*Plus

• Set Operating System Environment Variables

[oracle@localhost ~]$ vim .bash_profile
# Oracle Settings
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1;
export ORACLE_SID=ORCL;
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export PATH=$ORACLE_HOME/bin:$PATH;

• Start SQL*Plus (set PATH to ORACLE_HOME/bin)

Syntax

{username | /}[@connect_identifier] [edition={edition_name | DATABASE_DEFAULT}]

Examples:

$ sqlplus /nolog
connect sys as sysdba
connect / as sysdba
connect salesadmin@"dbhost.example.com/sales.example.com"
connect salesadmin@"dbhost.example.com/sales.example.com:dedicated"
connect salesadmin@"dbhost.example.com:1522/sales.example.com"
connect salesadmin@"192.0.2.5/sales.example.com"
connect salesadmin@"[2001:0DB8:0:0::200C:417A]/sales.example.com"
connect salesadmin@"dbhost.example.com//orcl"
connect salesadmin@sales1
connect /@sales1
connect /@sales1 as sysdba
connect salesadmin@sales1 edition=rev21 # edition in which the new database session starts

- Oracle Database Release Number

- Checking Your Current Release Number

$ sqlplus / as sysdba
SQL> startup
ORACLE instance started.
Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             348129976 bytes
Database Buffers          100663296 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.

SQL> show user;
USER is "SYS"
SQL> select instance_name,status,database_status,version,archiver from v$instance;
INSTANCE_NAME    STATUS               DATABASE_STATUS   VERSION              ARCHIVE
---------------- -------------------- ----------------- -------------------- -------
orcl             OPEN                 ACTIVE            11.2.0.2.0           STARTED

SQL> COL PRODUCT FORMAT A40
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT                                  VERSION         STATUS
---------------------------------------- --------------- ---------------
NLSRTL                                                     11.2.0.2.0      Production
Oracle Database 11g Enterprise Edition     11.2.0.2.0      Production
PL/SQL                                                       11.2.0.2.0      Production
TNS for Linux:                                            11.2.0.2.0      Production

- Oracle Password file (orapwd utility)
### main reason of password file to be able to connect to database even it's shutdown for maintenance operations
### without password file when attempting to grant SYSDBA or SYSOPER privileges will result in the following error:
SQL> grant sysdba to scott;
ORA-01994: GRANT failed: cannot add users to public password file

- Required Password File Name and Location on UNIX, Linux, and Windows

Platform	Required Name	Required Location)
UNIX and Linux	orapwORACLE_SID	ORACLE_HOME/dbs
Windows	PWDORACLE_SID.ora	ORACLE_HOME\database

Syntax

$ orapwd file=password_file_name [password=the_password] [entries=n] [force=Y|N] [ignorecase=Y|N] [nosysdba=Y|N]

• FILE, Name to assign to the password file, supply complete path
• PASSWORD, password privileged users should enter while connecting as SYSDBA or SYSOPER or SYSASM.
• FORCE, If Y, permits overwriting an existing password file.
• IGNORECASE, If Y, passwords are treated as case-insensitive 
• NOSYSDBA, For Oracle Data Vault installations.

- Sharing and Disabling the Password File (REMOTE_LOGIN_PASSWORDFILE)

• NONE: as if the password file does not exist
• EXCLUSIVE: 
• used with only one instance of one database
• EXCLUSIVE password file enables you to add, modify, and delete users
• SHARED: 
• used by multiple databases running on the same server
• multiple instances of RAC
• SHARED password file cannot be modified 
• cannot change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED

Example:

$ orapwd file=orapwSID password=sys_password force=y nosysdba=y
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret
$ orapwd file=orapwprod entries=30 force=y
C:\orapwd file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora password=2012 entries=20
C:\orapwd file=D:\oracle11g\product\11.1.0\db_1\database\pwdsfs.ora password=id entries=6 force=y
$ orapwd file=orapwPRODB3 password=abc123 entries=10 ignorecase=n
$ orapwd file=orapwprodb password=oracle1 ignorecase=y

### Create & Delete password file, add user SYSTEM to SYSDBA members

### list password file location
$ cd $ORACLE_HOME/dbs/
$ ls -la
-rw-r-----  1 oracle oracle 1536 Sep 24 01:15 orapworcl

### delete current password file by moving it
$ mv orapworcl orapworcl.old

### create new password file and list it
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle

$ ls -la
-rw-r-----  1 oracle oracle 1536 Sep 24 03:02 orapworcl
-rw-r-----  1 oracle oracle 1536 Sep 24 01:15 orapworcl.old

### list password file members
$ sqlplus / as sysdba

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

###  add user SYSTEM to  SYSDBA privileges
SQL> grant sysdba to system;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
SYSTEM                         TRUE  FALSE FALSE

### delete password by set parameter REMOTE_LOGIN_PASSWORDFILE to none
SQL> show parameter password;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=none SCOPE=SPFILE;

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive SCOPE=SPFILE;

System altered.

- Oracle Database Utilities 11g Release 2

http://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm