Thursday, July 21, 2011

Some Alter database commands for beginners

Alter database commands for beginners

Whenever the database is altered, the control file should be backed up.
alter database datafile
This is the way how the characteristics of datafiles can be changed.
alter database datafile 'DF' end backup
This command can be used for a hot backup
Note:There is no alter database datafile 'DF' begin backup command.
alter database datafile offline drop
This command is not meant to drop a datafile.
It is meant to offline the datafile with the intention of dropping the tablespace.
alter database archivelog
alter database archivelog
alter database archivelog manual
alter database noarchivelog
Alter database archivelog is simply an instruction to oracle not to overwrite an online redo log that has not yet beenarchived. Either you archive those manually or have arch do it.
See alter system archive log start and log_archive_start.
alter database backup controlfile to ['filename' | trace]
This command comes in two versions. One backs up the control file in a binary format while the other backs it up in a human readable form. It is required if the database is running in archive log mode and a structural change was made to the database.
Backing it up in a binary format:
alter database backup controlfile to '/some/arbitrary/path';
alter database backup controlfile to '/some/arbitrary/path' reuse;
Backing it up in a human readable format:
alter database backup controlfile to trace;
alter database backup controlfile to trace as '/some/arbitrary/path';
alter database backup controlfile to trace as '/some/arbitrary/path' reuse;
If the human readable form is chosen, the file can be made usable if the comments at the beginning are removed and replaced with aconnect / as sysdba. If the init.ora file is not at its default location, it has to be appended with a pfile=.... in the line containing a startup.
alter database flashback on | off
alter database flashback on;
alter database flashback off;
alter database [no] force logging
If a database is in force logging mode, all changes, except those in temporary tablespaces, will be logged, independently from any nologging specification.
It is also possible to put arbitrary tablespaces into force logging mode: alter tablespace force logging.
force logging might take a while to complete because it waits for all unlogged direct I/O operations to finish.
alter database open
.. yet to be finished ..
alter database open resetlogs
Online redo logs are re-created . The log sequence is reset to 1.
If the databaseis running in archive log mode, the archived redo logs should then be deleted. Otherwise, chances are, that Oracle will eventually try to create an archived redo log whose filename already exists.
alter database open read only
Standby database
The following alter database commands are used for a standby database environment.
alter database ACTIVATE [phisical|locigal] standby database [skip [standby logfile]]
Changes a database to an active database. This is a preprationlal step for the database to become a primary database. The database must be mounted.
physical and logical specify what kind of database is going to be activated.
alter database SET STANDBY DATABASE TO MAXIMIZE [protection| availablity | performance]
alter database SET STANDBY DATABASE TO MAXIMIZE protection;
alter database SET STANDBY DATABASE TO MAXIMIZE availablity;
alter database SET STANDBY DATABASE TO MAXIMIZE performance;
alter database REGISTER [or replace] [physical | logical] logfile [ redo_log_file_spec [, ...]]
alter database MOUNT STANDBY database
alter database CREATE STANDBY CONTROLFILE
alter database create standby controlfile as '/some/path/to/a/file';
See creating physical standby databases: create control file
commit switchover clause
Yet to be do
alter database START LOGICAL STANDBY APPLY [ new primary dblink | initial [scn_value]]
alter database [STOP | ABORT] logical standby apply

1 comment:

Shruti Ramalingam said...

Thanks for the great information ,was looking for this information from long.Great blog SAP Basis Training in Chennai