Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 19 March 2012

ALTER DATABASE OPTIONS in Oracle 9i/10g

Alter Database effect:
Whenever the database is altered, the control file should be backed up.
SQ>Alter database datafile 'DF' end backup
This command can be used to end the begin hot backup
SQ>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.
SQ>Alter database archivelog
SQ>alter database archivelog manual
SQ>alter database noarchivelog
Alter database archivelog is simply an instruction to oracle not to overwrite an online redo log that has not yet been archived. If the database is in archive mode then It will make a copy of full redo logfile before being overwritten.
SQ>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:
SQ>Alter database backup controlfile to '/some/arbitrary/path';
SQ>Alter database backup controlfile to '/some/arbitrary/path' reuse;
Backing it up in a human readable format:
SQL>Alter database backup controlfile to trace;
SQL>Alter database backup controlfile to trace as '/some/arbitrary/path';
SQL>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 a connect / 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.
SQL>Alter database flashback on
SQL>Alter database flashback off
This command is turn the flashback on. It will work only when the database is in mount state.
SQL>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.
A force logging might take a while to complete because it waits for all unlogged direct I/O operations to finish.
SQL>Alter database open
Opening a closed database
SQL>Alter Database Mount
To mount a database to a previously started, but not opened instance.
SQL>Alter database open read only
Opening a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes. While opening a database in read-only mode guarantees that datafile and redo log files are not written to, it does not restrict database recovery or operations that change the state of the database without generating redo. For example, you can take datafiles offline or bring them online since these operations do not effect data content.
SQL>Alter Database Open Read Write;
You can also open a database in read-write mode as follows. However, read-write is the default mode.
SQL>Alter database open resetlogs
Online redo logs are re-created . The log sequence is reset to 1. If the database is 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.
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.

SQL>Alter database SET STANDBY DATABASE TO MAXIMIZE protection;
SQL>Alter database SET STANDBY DATABASE TO MAXIMIZE availablity;
SQL>Alter database SET STANDBY DATABASE TO MAXIMIZE performance;
SQL>Alter database REGISTER [or replace] [physical | logical] logfile [ redo_log_file_spec [, ...]]
SQL>Alter database MOUNT STANDBY database
To mount the Standby database
SQL>Alter database CREATE STANDBY CONTROLFILE
SQL>Alter database create standby controlfile as '/some/path/to/a/file';
To create control file for standby database.
SQL>Alter database START LOGICAL STANDBY APPLY [ new primary dblink | initial [scn_value]]
SQL>Alter database [STOP | ABORT] logical standby apply
SQL> Alter database rename file 'Old Location and file name' to 'New Location File name';
This command is used to move or rename the datafile name and location.

0 comments:

Post a Comment