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 for this is that the RECYCLEBIN option is enabled.
To see the objects in recycle bin:
SELECT * FROM DBA_RECYCLEBIN;
SELECT DISTINCT 'PURGE TABLE '||owner||'.'||original_name||';' , droptime
FROM DBA_RECYCLEBIN
order by droptime DESC;
No comments:
Post a Comment