Oracle core stuff. Startup, Shutdown, Archiving, built in users.

Index
- alert.log location
- core dump location and incident folder.
- V$DIAG_INFO
- Change password
- Unlock Account
- How to create admin_user 
- Difference between SYS, SYSDBA, SYSOPER, SYSTEM
- How to change Archive mode
- sqlplus / vs sqlplus /nolog

- What to do if Oracle is stuck because of Audit file destination is full

- SPFILE

- BIN$XXX Tables 

=========================================================
alert.log location
It is set by parameter user_dump_dest

On Oracle 11 the default alert log is at:
$ORACLE_HOME/diag/rdbms/<SID>/<SID>/trace/alert_<SID>.log

On Oracle 9 the default alert log is at:
$ORACLE_HOME/admin/<SID>/bdump/alert_<SID>.log

core dump location
The default location is at:
$ORACLE_HOME/diag/rdbms/<SID>/<SID>/cdump/

It is set by parameter core_dump_dest
Whenever a core dump occurs, a file is generated under $ORACLE_HOME/diag/rdbms/<SID>/<SID>/incident/incident_<PID>

V$DIAG_INFO table.
To see all other logs, query V$DIAG_INFO table.

SELECT * FROM V$DIAG_INFO

INST_ID NAME                 VALUE
------- -------------------- ----------------------------------------------------------        1 Diag Enabled         TRUE
      1 ADR Base             d:\software\oracle
      1 ADR Home           d:\software\oracle\diag\rdbms\igt\igt
      1 Diag Trace           d:\software\oracle\diag\rdbms\igt\igt\trace
      1 Diag Alert           d:\software\oracle\diag\rdbms\igt\igt\alert
      1 Diag Incident        d:\software\oracle\diag\rdbms\igt\igt\incident
      1 Diag Cdump           d:\software\oracle\diag\rdbms\igt\igt\cdump
      1 Health Monitor       d:\software\oracle\diag\rdbms\igt\igt\hm
      1 Default Trace File   d:\software\oracle\diag\rdbms\igt\igt\trace\igt_ora_9324.trc
      1 Active Problem Count 5
      1 Active Incident Count 343

Difference between sid, serial#, audsid

sid: Session identifier, used to join to other columns 

serial#: Counter, which is incremented each time a SID is reused by another session.
                 (When a session ends and another session starts and uses the same SID) 

audsid: Auditing session ID uniquely identifies a session over the life of a database. 
                 It is also useful when finding the parallel query slaves, 
                 since during the PQ execution they have the same AUDSID.

Difference between SYS, SYSDBA, SYSOPER, SYSTEM
SYS and SYSTEM are users, i.e. real schemas.

SYS is the owner of Oracle Internal Objects, known as Data Dictionary
SYSTEM is owner of additional tables and views that display administrative information

SYSDBA and SYSOPER are roles.

SYSDBA is like a root account in UNIX.
Internally, if you connect as SYSDBA, your default schema is SYS.

SYSOPER is used for start/stop the Database.
If you connect as SYSOPER, your default schema is "public" and will only be able to do things granted to public AND start/stop the database.

In general, it is a bad idea to login as SYS or SYSTEM.

Default passwords:
For SYS: CHANGE_ON_INSTALL
For SYSTEM: MANAGER

Example connection with SYSDBA and as SYSOPER
For SYSDBA:
In Linux: Connect as user who is member of dba group.
In Windows: Connect as user who is a member of ORA_DBA group 

For SYSOPER:
In Linux: Connect as user who is member of oper group.
In Windows: Connect as user who is a member of ORA_OPER group 

$ sqlplus / as sysdba
or
$ sqlplus '/ as sysdba'
or
$ sqlplus /@orainst2 as sysdba

SQL> show user
USER is "SYS"

SQL> conn / as sysoper
Connected.
SQL> show user
USER is "PUBLIC"

SQL> conn scott/tiger as sysdba
Connected.
SQL> show user
USER is "SYS"

SQL> conn test/test as sysoper
Connected.
SQL> show user
USER is "PUBLIC"


Summary of Oracle Created Default Database Users.

Startup and Shutdown Database modes
psoug Reference for Stop/Start Database.

Shutdown

Best way To Shutdown Oracle Immediately, per Connor McDonald (Ask Tom) is:
ALTER SYSTEM CHECKPOINT;
SHUTDOWN ABORT;

Other ways

SHUTDOWN NORMAL;
SHUTDOWN TRANSACTIONAL;
SHUTDOWN IMMEDIATE;
SHUTDOWN ABORT;

ALTER DATABASE CLOSE NORMAL
There are three modes for the database:

ALTER DATABASE CLOSE NORMAL

Startup Database modes
There are three modes for the database:

ALTER DATABASE MOUNT
Database is mounted but not open.

ALTER DATABASE OPEN
ALTER DATABASE OPEN - This is the default.
ALTER DATABASE OPEN READ ONLY - will not generated redo logs.

ALTER DATABASE OPEN RESETLOGS
ALTER DATABASE OPEN RESETLOGS - This is needed in-case of a recovery from backup.

How to change Archive mode:
Before running these steps: setup the Linux environment variables:
export ORACLE_BASE=/software/oracle
export ORACLE_HOME=/software/oracle/111
export PATH=$PATH:$ORACLE_HOME
export ORACLE_SID=orainst

Linux>sqlplus / as sysdba

SHUTDOWN ABORT;

STARTUP RESTRICT;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

SQL> ALTER DATABASE NOARCHIVELOG; / ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

How to check on current archive mode:
SELECT LOG_MODE FROM V$DATABASE;

=================
RESETLOGS 
=================
ALTER DATABASE OPEN RESETLOGS;
This command:
- Archives the current online redo logs (or up to the last redo record before redo corruption if corruption is found)
- Clears the contents of the online redo logs
- Resets the online redo logs to log sequence 1.


sqlplus / vs sqlplus /nolog
sqlplus / 
login with OS user Authentication

sqlplus /nolog 
login without connecting to any schema.


What to do if Oracle is stuck because of Audit file destination is full?
cd $ORACLE_HOME/oracle/admin/$SID/adump

1.  Make some space on the destination folder:
  mv *.aud some/other/path/

2. Add some space in that mount point.

3. Change the audit destination to another directory

show parameter audit_file_dest;

SPFILE

Startup from bad spfile pfile

This might needed in case spfile got corrupt data, that prevents Oracle from starting up.

Step A - Create pfile from spfile<SID>.ora.

      CREATE pfile='myinit.ora' FROM spfile='spfileORCL.ora';

Step B - Edit pfile.

Step C - Startup Oracle from pfile.

      STARTUP pfile='myinit.ora';

Step D - Save OK settings to spfile

      CREATE spfile='spfileORCL.ora' FROM pfile='myinit.ora';

Step E - It is OK now to start Oracle as usual.
      STARTUP;

Default location of spfile

On Windows: %ORACLE_HOME%\database\
On Linux:  $ORACLE_HOME/dbs/

How to find location of spfile:

show parameter spfile ;

Default name of spfile is spfile<SID>.ora:

/software/oracle/101/dbs/spfileSID.ora

Under same path, there is also init<SID>.ora

- BIN$XXX Tables 
When doing DROP table, BIN$xxx table is being created.
The  solution is to PURGE TABLE after doing DROP TABLE
DROP TABLE MY_TABLE
PURGE TABLE MY_TABLE
The reason fr this is that the RECYCLEBIN option is enabled.