Cheat Sheet for Oracle DBA

alter database: Alter a Data File :

ALTER DATABASE DATAFILE 4 OFFLINE;

ALTER DATABASE DATAFILE '/u01/oracle/datafile/users01.dbf' OFFLINE;

ALTER DATABASE DATAFILE '/u01/oracle/datafile/users01.dbf' RESIZE 100m;

ALTER DATABASE DATAFILE '/u01/oracle/datafile/users01.dbf'

AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;

ALTER DATABASE DATAFILE 4 END BACKUP;

alter database: Alter a Tempfile :

ALTER DATABASE TEMPFILE 4 RESIZE 100M;

ALTER DATABASE TEMPFILE 4 AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;

ALTER DATABASE TEMPFILE 4 DROP INCLUDING DATAFILES;

ALTER DATABASE TEMPFILE 4 OFFLINE;

alter database: ARCHIVELOG Mode Commands:

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE NOARCHIVELOG;

ALTER DATABASE FORCE LOGGING;

ALTER DATABASE CLEAR LOGFILE '/u01/oracle/logfiles/redo01.rdo';

ALTER DATABASE CLEAR UNARCHIVED LOGFILE '/u01/oracle/logfiles/redo01.rdo';

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE);

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

alter database: Control File Operations :

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/u01/oracle/logfile_backup/backup_logfile.trc' REUSE RESETLOGS;

ALTER DATABASE BACKUP CONTROLFILE TO '/u01/oracle/logfile_backup/backup_logfile.ctl';

alter database: Create a Data File

ALTER DATABASE CREATE DATAFILE '/u01/oracle/datafile/users01.dbf' AS '/u01/oracle/datafile/users01.dbf';

ALTER DATABASE CREATE DATAFILE 4 AS '/u01/oracle/datafile/users01.dbf';

ALTER DATABASE CREATE DATAFILE '/u01/oracle/datafile/users01.dbf' AS NEW;

alter database: Logfile Commands

ALTER DATABASE ADD LOGFILE GROUP 2
('/u01/oracle/logfiles/redo02a.rdo', '/u01/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;

ALTER DATABASE ADD LOGFILE MEMBER '/u01/oracle/logfiles/redo02c.rdo' to GROUP 2;

ALTER DATABASE ADD LOGFILE thread 3 GROUP 2
('/u01/oracle/logfiles/redo02a.rdo', '/u01/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/logfiles/redo02b.rdo';

alter database: Mount and Open the Database :

ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

alter database: Move or Rename a Database File or Online Redo Log

NOTE:
The database must be mounted to rename or move online redo logs.
The database must be mounted or the data files taken offline to move database data files.

ALTER DATABASE RENAME FILE '/u01/datafile/oldfile.dbf' TO '/u01/datafile/newfile.dbf';

alter database: Open the Database Read-Only

ALTER DATABASE OPEN READ ONLY;

alter database: Open the Database with resetlogs :

ALTER DATABASE OPEN RESETLOGS;