1echo $ORACLE_SID # the Oracle instance used before a SQL connection.
2echo $ORACLE_HOME # the Oracle home directory.
Load the instance environment:
1. oraenv
/etc/oratab:
1+ASM:/u01/oracle/base/product/12.2.0/grid:N
2ORCL:/u01/oracle/base/product/12.2.0/dbhome_1:N
All running instances:
1ps -ef | grep pmon
2oracle 2201 1 0 12:02 ? 00:00:00 ora_pmon_ORCL
3oracle 30513 1 0 Feb12 ? 00:01:11 asm_pmon_+ASM
1ps -ef | grep ora_pmon | grep -v grep | awk '{print $NF}' | cut -d"_" -f3
Check whether an Oracle Clusterware layer is present:
1ps -ef | grep d.bin
2# /u01/oracle/base/product/12.2.0/grid/bin/[ohasd|oraagent|evmd|ocssd].bin ...
1srvctl config database
Listeners and processes:
1ps -edf | grep lsn # the Oracle listeners on the host.
2ps -edf | grep ora # the Oracle processes.
The central inventory (inventory.xml):
1<HOME_LIST>
2 <HOME NAME="OraGI19Home1" LOC="/u01/oracle/base/product/19.0.0/grid" TYPE="O" IDX="1" CRS="true"/>
3 <HOME NAME="OraDB12Home1" LOC="/u01/oracle/base/product/12.2.0/dbhome_1" TYPE="O" IDX="2"/>
4</HOME_LIST>
1SHUTDOWN [parameter];
NORMAL (or plain shutdown;) โ normal shutdown: new connections refused, Oracle waits for all current connections to finish.TRANSACTIONAL โ no new connections; SQL statements in progress run to completion and no new ones are accepted.IMMEDIATE โ users are disconnected; current operations are rolled back.ABORT โ the instance terminates without closing files; an instance recovery is usually required at the next startup.1SHUTDOWN IMMEDIATE;
The startup goes through these states:
1OFF โโโบ nomount (SPFILE) โโโบ mount (CTRLFILE) โโโบ open
2 STARTED MOUNTED OPEN
3 (SGA + PGA) (control file read, (DB operational)
4 files checked, R/O)
Step by step:
1STARTUP NOMOUNT;
2ALTER DATABASE MOUNT;
3ALTER DATABASE OPEN;
4
5ALTER DATABASE CLOSE;
6ALTER DATABASE DISMOUNT;
Start with a chosen SPFILE:
1STARTUP SPFILE='/path/to/spfile.ora';
Check the state (its startup level):
1SELECT status FROM v$instance; -- STARTED / MOUNTED / OPEN
2SELECT status, database_status, logins FROM v$instance;
1STARTUP [NOMOUNT | MOUNT | OPEN] [EXCLUSIVE] [PFILE=...] [FORCE] [RESTRICT] [RECOVER];
NOMOUNT โ creates the SGA and starts the background processes, but gives no access to the database.MOUNT โ mounts the database for DBA activities, but gives no access.OPEN โ users can access the database.EXCLUSIVE โ only the current instance can access the database.PFILE โ use the given init file.FORCE โ aborts the current instance before a normal startup.RESTRICT โ only users with the RESTRICTED SESSION privilege.RECOVER โ starts media recovery at startup.On Windows, Oracle runs as a service; change the startup script strt[SID].cmd in %ORACLE_HOME%\DATABASE.
1SHOW PARAMETERS pga;
2SHOW PARAMETERS sga;
1SELECT pool, ROUND(bytes/1024/1024,0) free_mb FROM v$sgastat WHERE name LIKE '%free memory%';
2
3SELECT SUM(bytes/1024/1024) free_mb FROM v$sgastat WHERE name LIKE '%free memory%';
4
5SELECT * FROM v$sga_target_advice ORDER BY sga_size;
Resize:
1ALTER SYSTEM SET sga_max_size = '2352M' SCOPE=SPFILE;
2ALTER SYSTEM SET sga_target = '2352M' SCOPE=SPFILE;
3ALTER SYSTEM SET pga_aggregate_limit = '2G' SCOPE=SPFILE;
4ALTER SYSTEM SET pga_aggregate_target = '581M' SCOPE=SPFILE;
1CREATE PFILE='/u01/backup/pfile.txt' FROM SPFILE;
2SHUTDOWN IMMEDIATE;
3STARTUP NOMOUNT;
4SHOW PARAMETER sga;
5ALTER DATABASE MOUNT;
6ALTER DATABASE OPEN;
1SHOW PARAMETER background_dump_dest; -- directory containing the alert.log.
1ls $ORACLE_BASE/diag/rdbms/*/*/trace/alert*.log
2tail -500f $ORACLE_BASE/diag/rdbms/<SID>/<UNIQ_NAME>/trace/alert_<SID>_1.log
1$ORACLE_HOME/bin/adrci
2
3adrci> show home
4adrci> set home diag/rdbms/orcl/ORCL_1
5adrci> show alert -tail 50
6adrci> show problem
7adrci> show incident -mode basic
8adrci> show incident -mode detail -p "incident_id=61553"
9adrci> ips create package problem 1 correlate all # zip to send to Oracle Support.
Purge old logs:
1adrci> purge -age 48 -type trace # 48 hours
2adrci> purge -age 2160 -type alert # 2160 hours = 90 days
3# purge -age 2160 -type incident|cdump|stage|sweep|hm
1adrci> select SHORTP_POLICY, LONGP_POLICY from ADR_CONTROL;
Script to purge every home:
1#!/bin/bash
2# Purge every ADR home (uses adrci)
3for f in $( adrci exec="show homes" | grep -v "ADR Homes:" ); do
4 echo "Purging ${f}"
5 adrci exec="set home $f; purge -age 0;"
6done
As the Oracle user:
1cd $ORACLE_HOME/bin
2./relink all
3view /u01/oracle/base/product/19c/install/relinkActions*.log
1sqlplus / as sysdba
2shutdown immediate;
(in a RAC ONE NODE environment, prefer srvctl stop/start -db <db_name>.)
1startup upgrade
1alter system set max_string_size=EXTENDED scope=both;
utl32k.sql:1cd $ORACLE_HOME/rdbms/admin/
2sqlplus / as sysdba
3@utl32k.sql
1shutdown immediate;
1startup
utlrp.sql, connected AS SYSDBA:1cd $ORACLE_HOME/rdbms/admin/
2sqlplus / as sysdba
3@utlrp.sql
1SELECT * FROM gv$version;
1ALTER TABLESPACE undotbs1 RETENTION GUARANTEE; -- guarantee the undo retention.
2ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE; -- remove the guarantee.
3
4SHOW PARAMETER undo_retention; -- the undo retention period in seconds (e.g. 900 s).
5
6ALTER SYSTEM SET undo_retention = 1200; -- change the retention value.
If the alert log shows ORA-06512 at DBMS_STATS_* (an invalidated statistics package):
1grep ORA-06512 $ORACLE_BASE/diag/rdbms/*/*/trace/alert_*.log
1EXEC dbms_stats.init_package();