Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Alteration of Databases

Oracle Database Tips by Donald Burleson

Even the best-designed database eventually has to be changed. New log group member files may need to be added, data files may need to be renamed or moved, archive logging status may need to be changed, and so on. These tasks are all accomplished through the use of the ALTER DATABASE command. Let's look at a simplified command format and some of the options.

ALTER DATABASE name
     [NO]MOUNT [STANDBY|CLONE] DATABASE
     CONVERT
     OPEN [READ[ ONLY]|WRITE][RESETLOGS|NORESETLOGS]
     ACTIVATE STANDBY DATABASE
     ARCHIVELOG|NOARCHIVELOG
     Recover_clause
     ADD LOGFILE [THREAD n] [GROUP n] file_spec
     ADD LOGFILE MEMBER file_spec [REUSE] TO GROUP n|group_spec
     DROP LOGFILE [GROUP n|group spec] file_spec
     DROP LOGFILE MEMBER file_spec
     CLEAR [UNARCHIVED] LOGFILE [GROUP n|group_spec] file_spec UNRECOVERABLE DATAFILE
     RENAME file_spec TO file_spec
     CREATE STANDBY CONTROLFILE AS file_name REUSE
     BACKUP CONTROLFILE TO file_name REUSE|TO TRACE RESETLOGS|NORESETLOGS
     RENAME GLOBAL NAME TO database[.domain]
     RESET COMPATIBILITY
     ENABLE [PUBLIC] THREAD n
     DISABLE THREAD n
     CREATE DATAFILE file_spec AS file_name
     DATAFILE file_name
               [ONLINE|OFFLINE[ DROP]]
               RESIZE n [K|M]
               AUTOEXTEND CLAUSE
               END BACKUP
      CHARACTER SET
      NATIONAL CHARACTER SET
      Set time zone clauses
      DEFAULT TEMPORARY TABLESPACE tablespace

where:

DATABASE name. Assigned to a maximum of eight characters. If it is not specified, the value in the INIT.ORA file will be used.

File_spec. A file specification in the format of ?filename? SIZE integer K or M REUSE, with filename an OS-specific full path name; K or M specifies integer as kilobytes or megabytes; and REUSE specifies to reuse the current file if it exists. If SIZE isn't specified, 500K will be used. REUSE is optional.

File_name. A full path filename.

MOUNT.  Database is available for some Remote DBA functions, but not normal functions. Either exclusive, which is default, or PARALLEL.

STANDBY DATABASE.  With version 7.3 and greater, the command operates against a hot-standby database (see Chapter 15, Backup and Recovery Procedures for Oracle).

CLONE.  With 8i a clone database is used in the recovery of a tablespace to a point in time.

OPEN.  Database is mounted and opened for general use, either with RESET LOGS (default) or NORESET LOGS (see Chapter15). @@@Au: Subsequent xrefs to chapter, give chapter number only; not necessary to repeat title.@@@

ACTIVATE STANDBY DATABASE.  See Chapter 15.

ADD LOGFILE THREAD.  Adds a thread or redo to a PARALLEL instance.

ADD LOGFILE MEMBER.  Adds a logfile member to an existing group.

CLEAR.  Reinitializes an online redo log and, optionally, does not archive the redo log. CLEAR LOGFILE is similar to adding and dropping a redo log except that the command may be issued even if there are only two logs for the thread, and it also may be issued for the current redo log of a closed thread.

CLEAR LOGFILE.  Cannot be used to clear a log needed for media recovery. If it is necessary to clear a log containing redo after the database checkpoint, then incomplete media recovery will be necessary. The current redo log of an open thread can never be cleared. The current log of a closed thread can be cleared by switching logs in the closed thread.    

If the CLEAR LOGFILE command is interrupted by a system or instance failure, then the database may hang. If so, the command must be reissued once the database is restarted. If the failure occurred because of I/O errors accessing one member of a log group, then that member can be dropped and other members added.

UNARCHIVED.  Must be specified if you want to reuse a redo log that was not archived. Note that specifying UNARCHIVED will make backups unusable if the redo log is needed for recovery.

UNRECOVERABLE DATAFILE.  Must be specified if the tablespace has a data file offline, and the unarchived log must be cleared to bring the tablespace online. If so, then the data file and entire tablespace must be dropped once the CLEAR LOGFILE command completes.

DROP LOGFILE.  Drops an existing log group.

DROP LOGFILE MEMBER.  Drops an existing log member.

RENAME.  Renames the specified database file.

ARCHIVELOG/NOARCHIVELOG.  Turns archive logging on or off.

RECOVER.  Puts database into recovery mode. The form of recovery is specified in the recovery clause. (See Chapter 15)

BACKUP CONTROLFILE.  This can be used in two ways: first, to make a recoverable backup copy of the control file ('tO 'filename'?) and, second, to make a script to rebuild the control file (? TO TRACE?).

CREATE DATAFILE.  Creates a new data file in place of an old one. You can use this option to re-create a data file that was lost with no backup. The ?filename? must identify a file that was once a part of the database. The filespec specifies the name and size of the new data file. If you omit the AS clause, ORACLE creates the new file with the same name and size as the file specified by 'filename'.

CREATE STANDBY CONTROLFILE.  Creates a control file for use with the standby database.

DATAFILE.  Allows you to perform manipulations against the data files in the instance such as resizing, turning autoextend on or off, and setting backup status.

ENABLE and DISABLE threads.  Allows the enabling and disabling of redo log threads (only used for parallel databases).

RESET COMPATIBILITY.  Marks the database to be reset to an earlier version of Oracle7 when the database is next restarted. This will render archived redo logs unusable for recovery.

Tip: The RESET COMPATIBILITY option will not work unless you have successfully disabled Oracle9i features that affect backward compatibility.

RENAME GLOBAL_NAME TO.  Changes the global name of the database. A rename will automatically flush the shared pool. It doesn't change data concerning your global name in remote instances, connect strings, or db links.

Some examples of the use of ALTER DATABASE are:

* To mount a database PARALLEL:

      ALTER DATABASE dbname MOUNT PARALLEL

* To drop a logfile member:

ALTER DATABASE

            DROP LOGFILE '/oracle1/ORTEST1/redo/ORTEST1_redo31.log'

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational