alter database
·
backup
controlfile as a text file
alter database backup controlfile to trace as '';
·
resize
a data file: increase size or decrease size
ALTER DATABASE DATAFILE ''
RESIZE 1200M;
·
take
a datafile out of AUTOEXTEND Mode
alter database datafile
'' AUTOEXTEND off;
·
turn
on the autoextend feature on a datafile
alter database datafile ''
autoextend on next 100m maxsize 2000m;
·
enable
block change tracking
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING
FILE '';
·
add
logfile group
ALTER DATABASE ADD LOGFILE GROUP 1 ('')
SIZE 200M;
ALTER DATABASE ADD LOGFILE GROUP 2 ('',
'') SIZE 200M;
ALTER DATABASE ADD LOGFILE MEMBER ''
TO GROUP 1;
·
change
GLOBAL_NAME
ALTER DATABASE RENAME GLOBAL_NAME TO mydb;
·
create
a new database incarnation of the database
alter database open resetlogs;
·
change
archivelog mode
alter database archivelog
alter database archivelog manual
alter database noarchivelog
·
READ ONLY / READ WRITE: The
following statement opens the database in read-only mode:
startup mount;
ALTER DATABASE OPEN READ ONLY;
·
The following statement opens the
database in read/write mode and clears the online redo logs:
startup mount;
ALTER DATABASE OPEN READ WRITE RESETLOGS;
·
Using Parallel Recovery Processes:
The following statement performs tablespace recovery using parallel recovery
processes:
ALTER DATABASE RECOVER TABLESPACE tbs_03 PARALLEL;
·
Adding Redo Log File Groups:
Examples The following statement adds a redo log file group with two
members and identifies it with a GROUP parameter value of 3:
ALTER DATABASE
ADD LOGFILE GROUP 3
('diska:log3.log', 'diskb:log3.log') SIZE 50K;
·
The following statement adds a redo
log file group containing two members to thread 5 (in a Real Application
Clusters environment) and assigns it a GROUP parameter value of 4:
ALTER DATABASE
ADD LOGFILE THREAD 5 GROUP 4
('diska:log4.log', 'diskb:log4:log');
·
Adding Redo Log File Group Members: The
following statement adds a member to the redo log file group added in the
previous example:
ALTER DATABASE ADD LOGFILE MEMBER 'diskc:log3.log' TO
GROUP 3;
·
Dropping Log File Members: The
following statement drops one redo log file member added in the previous
example:
ALTER DATABASE DROP LOGFILE MEMBER 'diskb:log3.log';
·
The following statement drops all
members of the redo log file group 3:
ALTER DATABASE DROP LOGFILE GROUP 3;
·
Renaming a Log File Member: The
following statement renames a redo log file member:
ALTER DATABASE RENAME FILE 'diskc:log3.log' TO 'diskb:log3.log';
·
Setting the Default Type of
Tablespaces: The following statement specifies that subsequently created
tablespaces be created as bigfile tablespaces by default:
ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;
·
Changing the Default Temporary
Tablespace: The following statement makes the tbs_5 tablespace the default
temporary tablespace of the database. This statement either establishes a
default temporary tablespace if none was specified at create time, or replaces
an existing default temporary tablespace with tbs_05:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_05;
·
A group of tablespaces can be
defined as the default temporary tablespace by using a tablespace group. The
following statement makes the tablespaces in the tablespace group tbs_group_01 the
default temporary tablespaces of the database:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE
tbs_grp_01;
·
Creating a New Data File: The
following statement creates a new data file tbs_f04.dbf based on the file tbs_f03.dbf.
Before creating the new data file, you must take the existing data file (or the
tablespace in which it resides) offline.
ALTER DATABASE CREATE DATAFILE 'tbs_f03.dbf' AS
'tbs_f04.dbf';
·
Manipulating Temp Files: The
following takes offline the temp file temp02.dbf and then renames the temp
file:
ALTER DATABASE TEMPFILE 'temp02.dbf' OFFLINE;
ALTER DATABASE RENAME FILE 'temp02.dbf' TO
'temp03.dbf';
·
Changing the Global Database Name: The
following statement changes the global name of the database and includes both
the database name and domain:
ALTER DATABASE RENAME GLOBAL_NAME TO
demo.world.example.com;
·
Enabling and Disabling Block Change
Tracking: The following statement enables block change tracking and causes
Oracle Database to create a block change tracking file named tracking_file and
overwrite the file if it already exists:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE 'tracking_file' REUSE;
·
The following statement disables
block change tracking and deletes the existing block change tracking file:
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
·
Resizing a Data File: The following
statement attempts to change the size of data file diskb:tbs_f5.dbf:
ALTER DATABASE DATAFILE 'diskb:tbs_f5.dbf' RESIZE 10
M;
ALTER DATABASE CLEAR LOGFILE 'diskc:log3.log';
·
Database Recovery: The following
statement performs complete recovery of the entire database, letting Oracle
Database generate the name of the next archived redo log file needed:
ALTER DATABASE RECOVER AUTOMATIC DATABASE;
·
The following statement explicitly
names a redo log file for Oracle Database to apply:
ALTER DATABASE RECOVER LOGFILE 'diskc:log3.log';
·
The following statement performs
time-based recovery of the database:
ALTER DATABASE
RECOVER AUTOMATIC UNTIL TIME '2001-10-27:14:00:00';
·
Making a Consistent
Database Backup: Assume that the database is open and you want to make a
consistent backup of the whole database.
This example shuts down the database consistently, mounts the database, makes a
consistent whole database backup, and then opens the database.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
BACKUP DATABASE PLUS ARCHIVELOG;
# Now that the backup is complete, open the
database.
ALTER DATABASE OPEN;
·
Mounting the
Database After Restoring the Control File: This example restores the control
file, mounts it, and performs recovery. Finally,
the example resets the online redo log.
STARTUP FORCE NOMOUNT;
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
# You must run the RECOVER command after restoring a
control file even if no
# datafiles require recovery.
RECOVER DEVICE TYPE DISK DATABASE;
ALTER DATABASE OPEN RESETLOGS;
