oracle cheat sheet

notes for installing Oracle Database Express Edition 11g Release 2
Remember - oracle still thinks that the swap space needs to be double the size of the RAM available....

# rpm -i oracle-xe-11.2.0-1.0.x86_64.rpm

# /etc/init.d/oracle-xe configuration

finish the installation by starting the Web-GUI

http://<hostname>:8080/apex
http://<hostname>:8080/apex/apex_admin

environment

set the proper environment to find all the tools needed

oracle$ vi .bash_profile
# .bash_profile
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_HOME
export ORACLE_SID=XE
export PATH

APEX

if the default admin account wont work - reset the password to admin. during the inital logon later you will be force to change it.

oracle$ cd $ORACLE_HOME/apex
oracle$ sqlplus /nolog
SQL> connect / as sysdba
SQL> @apxxepwd admin 

backup and restore

Offline backup (Cold backup)
A cold OS backup is simple: the database has been cleanly shut down (not crashed, not shutdown abort) so that:
- all datafiles are consistent (same SCN) and no redo is needed in case of restore
- the datafiles are closed: they will not be updated during the copy operation
Thus, it can be restored entirely and the database can be opened without the need to recover.

Online backup (Hot backup) - only possbible in ARCHIVELOG mode
An hot backup does the copy while the database is running. That means that the copy is inconsistent and will need redo applied to be usable. Recovery is the process of applying redo log information in order to roll-forward file modifications as they were done in the original files.
begin/end backup - FAQ

listener controls

$ lsnrctl status
$ lsnrctl [start|stop]

SQL Plus

$ sqlplus /nolog
SQL> connect / as sysdba
SQL> SHOW user;
SQL> SELECT * FROM v$version;

archivelog mode - archredo - dba-oracle.com

Complete shutdown/startup

SQL> shutdown immediate
if there are still some processes running # ps ax | grep -i ora
SQL> shutdown abort

SQL> startup
or
SQL> startup nomount
SQL> alter database mount
SQL> alter database open
or
SQL> startup mount
SQL> alter database open

Suspend/Resume
The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files. The suspended state lets you back up a database without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state.
The suspend/resume feature is useful in systems that allow you to mirror a disk or file and then split the mirror, providing an alternative backup and restore solution.
Caution:
Do not use the ALTER SYSTEM SUSPEND statement as a substitute for placing a tablespace in hot backup mode. Precede any database suspend operation by an ALTER TABLESPACE BEGIN BACKUP statement.

SQL> ALTER SYSTEM SUSPEND;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS : SUSPENDED
SQL> ALTER SYSTEM RESUME;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS : ACTIVE

Begin/End-Backup

SQL> ALTER DATABASE BEGIN BACKUP 

if you get

ERROR at line 1;
ORA-01123: cannot start online backup; media recovery not enabled

enable the archive log first

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

SQL> SELECT log_mode FROM v$database;
SQL> SHOW PARAMETER log_archive_start;
or
SQL> archive log list;

When in NOARCHIVELOG mode the database runs normally, but there is no capacity to perform any type of point in time recovery operations or online backups.

SQL> select file#,status from v$backup;
SQL> ALTER DATABASE END BACKUP;
if the DB crashed during backup mode - mount it, manually end backup and open it
SQL> ALTER DATABASE END BACKUP;
SQL> ALTER DATABASE OPEN;

misc

SQL> SELECT name from v$database;
SQL> SELECT name FROM v$datafile;
SQL> SELECT member FROM v$logfile;
SQL> SELECT name FROM v$controlfile;
manual log switch
SQL> ALTER system switch logfile;
System altered.
SQL> EXIT