Friday, July 18, 2008
oracle backup concept
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10743/backrec.htm
Thursday, July 17, 2008
tablespace hot backup consistency
Ooooppps, something I forgot to mention.
After you performed your online backup, did you make sure you archived the
current online redo log and copied it across as well ?
You'll need all the redo logs generated during the backup to ensure that the
database can resync itself. And that'll hence include the current online
redo as well.
After you performed your online backup, did you make sure you archived the
current online redo log and copied it across as well ?
You'll need all the redo logs generated during the backup to ensure that the
database can resync itself. And that'll hence include the current online
redo as well.
redo undo and data consistency
the following text was copied from http://www.freelists.org/archives/oracle-l/12-2005/msg00537.html
=========================
When modify a table with normal insert/update/delete, then
1) redo for corresponding UNDO block change for this update is placed in redolog buffer
2) corresponding UNDO block is changed
3) redo for DATA block change for this update is placed in redolog buffer
4) DATA block itself is changed
Note that during buffer cache block changes we record the RBA (redo byte address in redo stream) of last change in buffer headers (can be seen from X$BH). RBA is an address in redo stream, it can uniquely identify a redo record/change vector in redologs.
As long as we don't commit (with sync option) here, no redo has to be written onto disk.
1) If instance crashes now, we don't have neither the redo nor datablock changes persisting, thus no inconsistencies
2) If instance crashes after LGWR has written the redo to disk, but DBWR hasn't written the datablocks, then during instance recovery we:
a) read old versions of datablocks from disk
b) apply redo to the old versions - note that Oracle guarantees every change vector between old version on disk and current version until commit to be in online redologs - e.g. it doesn't allow any redolog to be overwritten before all corresponding datablocks are checkpointed to disk.
c) this above is called cache recover - we make sure that all datafiles are physically consistent. After this point if we still have inconsistent/fractured blocks, we have encountered a hardware fault or software bug
d) database can be opened now, if any server processes see uncommitted datablocks, they can roll these back on their own - leaving the blocks they don't need for SMON (this is called fast start rollback if memory serves correctly)
e) SMON goes through undo segments to see whether there are uncommitted transactions (the committed transactions have a commit record at their end in redologs) and rolls those back (or hands those over to parallel slaves to roll back depending on transaction sizes)
3) It is not possible to have a situation where DBWR writes modified buffers back to disk before their redo is written. This is called write-ahead logging protocol and is mentioned in documentation. It is achieved by marking RBA of latest change to buffer header (as mentioned above) and DBWR checks whether redo up to max(RBA) in write block batch is already on disk (from memory structure underlying X$KCCCP).
If it's not, DBWR will signal LGWR and won't write those buffers to disk until LGWR acknowledges that redo is on disk.
So, as long as the foundations on what Oracle relies (OS, hardware) works properly and we don't hit any bugs (and unless I'm wrong) Oracle ALWAYS guarantees the blocks to be consistent after cache recovery in case of instance failure. It doesn't matter whether only half of DBWR's block write succeeded, or 99% or 1%, Oracle's recovery mechanisms are conceptually reliable.
All of above was a simplification, especially from 10g where we can have some redo log buffers in shared pool (private strands), some undo data cached in shared pool (in-memory undo), out-of order physical redo (multiple redolog strands and redo allocation latches), but again, I'm sure Oracle has based their recovery mechanisms on solid theoretical foundations here - no vulnerabilities on race conditions nor dependencies on which order you execute your stuff.
Tanel.
=========================
When modify a table with normal insert/update/delete, then
1) redo for corresponding UNDO block change for this update is placed in redolog buffer
2) corresponding UNDO block is changed
3) redo for DATA block change for this update is placed in redolog buffer
4) DATA block itself is changed
Note that during buffer cache block changes we record the RBA (redo byte address in redo stream) of last change in buffer headers (can be seen from X$BH). RBA is an address in redo stream, it can uniquely identify a redo record/change vector in redologs.
As long as we don't commit (with sync option) here, no redo has to be written onto disk.
1) If instance crashes now, we don't have neither the redo nor datablock changes persisting, thus no inconsistencies
2) If instance crashes after LGWR has written the redo to disk, but DBWR hasn't written the datablocks, then during instance recovery we:
a) read old versions of datablocks from disk
b) apply redo to the old versions - note that Oracle guarantees every change vector between old version on disk and current version until commit to be in online redologs - e.g. it doesn't allow any redolog to be overwritten before all corresponding datablocks are checkpointed to disk.
c) this above is called cache recover - we make sure that all datafiles are physically consistent. After this point if we still have inconsistent/fractured blocks, we have encountered a hardware fault or software bug
d) database can be opened now, if any server processes see uncommitted datablocks, they can roll these back on their own - leaving the blocks they don't need for SMON (this is called fast start rollback if memory serves correctly)
e) SMON goes through undo segments to see whether there are uncommitted transactions (the committed transactions have a commit record at their end in redologs) and rolls those back (or hands those over to parallel slaves to roll back depending on transaction sizes)
3) It is not possible to have a situation where DBWR writes modified buffers back to disk before their redo is written. This is called write-ahead logging protocol and is mentioned in documentation. It is achieved by marking RBA of latest change to buffer header (as mentioned above) and DBWR checks whether redo up to max(RBA) in write block batch is already on disk (from memory structure underlying X$KCCCP).
If it's not, DBWR will signal LGWR and won't write those buffers to disk until LGWR acknowledges that redo is on disk.
So, as long as the foundations on what Oracle relies (OS, hardware) works properly and we don't hit any bugs (and unless I'm wrong) Oracle ALWAYS guarantees the blocks to be consistent after cache recovery in case of instance failure. It doesn't matter whether only half of DBWR's block write succeeded, or 99% or 1%, Oracle's recovery mechanisms are conceptually reliable.
All of above was a simplification, especially from 10g where we can have some redo log buffers in shared pool (private strands), some undo data cached in shared pool (in-memory undo), out-of order physical redo (multiple redolog strands and redo allocation latches), but again, I'm sure Oracle has based their recovery mechanisms on solid theoretical foundations here - no vulnerabilities on race conditions nor dependencies on which order you execute your stuff.
Tanel.
Sunday, July 13, 2008
get oracle DDL
select DBMS_METADATA.GET_DDL('TABLE','USERS','ILPS') from dual;
select DBMS_METADATA.GET_DDL('TABLESPACE','SYSTEM') from dual;
select DBMS_METADATA.GET_DDL('TABLESPACE','SYSTEM') from dual;
oracle automatic startup and shutdown
http://oracle-base.com/articles/linux/AutomatingDatabaseStartupAndShutdownOnLinux.php
install Oracle 11g on RHEL4 and RHEL5
http://oracle-base.com/articles/11g/OracleDB11gR1InstallationOnEnterpriseLinux4and5.php
oracle temporary tablespace
A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in tempfiles.
create oracle database
CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Step 10: Create Additional Tablespaces
To make the database functional, you need to create additional tablespaces for your application data. The following sample script creates some additional tablespaces:
CREATE TABLESPACE apps_tbs LOGGING
DATAFILE '/u01/app/oracle/oradata/mynewdb/apps01.dbf'
SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace (optional)
CREATE TABLESPACE indx_tbs LOGGING
DATAFILE '/u01/app/oracle/oradata/mynewdb/indx01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
For information about creating tablespaces, see Chapter 12, "Managing Tablespaces".
Step 11: Run Scripts to Build Data Dictionary Views
Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
EXIT
The at-sign (@) is shorthand for the command that runs a SQL*Plus script. The question mark (?) is a SQL*Plus variable indicating the Oracle home directory. The following table contains descriptions of the scripts:
Script Description
CATALOG.SQL Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL Runs all scripts required for or used with PL/SQL.
PUPBLD.SQL Required for SQL*Plus. Enables SQL*Plus to disable commands by user.
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Step 10: Create Additional Tablespaces
To make the database functional, you need to create additional tablespaces for your application data. The following sample script creates some additional tablespaces:
CREATE TABLESPACE apps_tbs LOGGING
DATAFILE '/u01/app/oracle/oradata/mynewdb/apps01.dbf'
SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace (optional)
CREATE TABLESPACE indx_tbs LOGGING
DATAFILE '/u01/app/oracle/oradata/mynewdb/indx01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
For information about creating tablespaces, see Chapter 12, "Managing Tablespaces".
Step 11: Run Scripts to Build Data Dictionary Views
Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
EXIT
The at-sign (@) is shorthand for the command that runs a SQL*Plus script. The question mark (?) is a SQL*Plus variable indicating the Oracle home directory. The following table contains descriptions of the scripts:
Script Description
CATALOG.SQL Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL Runs all scripts required for or used with PL/SQL.
PUPBLD.SQL Required for SQL*Plus. Enables SQL*Plus to disable commands by user.
Oracle tempfile creation if the current one is full
CREATE
TEMPORARY TABLESPACE "TEMP1" TEMPFILE
'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TEMP02.DBF' SIZE 200M
REUSE AUTOEXTEND
ON NEXT 640K MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1024K;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP1";
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
TEMPORARY TABLESPACE "TEMP1" TEMPFILE
'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TEMP02.DBF' SIZE 200M
REUSE AUTOEXTEND
ON NEXT 640K MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1024K;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP1";
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
oracle backup and recovery
copy from http://www.oracle-dba-online.com/backup_and_recovery.htm
===================
Opening or Bringing the database in Archivelog mode.
To open the database in Archive log mode. Follow these steps:
STEP 1: Shutdown the database if it is running.
STEP 2: Take a full offline backup.
STEP 3: Set the following parameters in parameter file.
LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc
LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
If you want you can specify second destination also
LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc1”
Step 3: Start and mount the database.
SQL> STARTUP MOUNT
STEP 4: Give the following command
SQL> ALTER DATABASE ARCHIVELOG;
STEP 5: Then type the following to confirm.
SQL> ARCHIVE LOG LIST;
STEP 6: Now open the database
SQL>alter database open;
Step 7: It is recommended that you take a full backup after you brought the database in archive log mode.
To again bring back the database in NOARCHIVELOG mode. Follow these steps:
STEP 1: Shutdown the database if it is running.
STEP 2: Comment the following parameters in parameter file by putting " # " .
# LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
# LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc2”
# LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc
STEP 3: Startup and mount the database.
SQL> STARTUP MOUNT;
STEP 4: Give the following Commands
SQL> ALTER DATABASE NOARCHIVELOG;
STEP 5: Shutdown the database and take full offline backup.
TAKING OFFLINE BACKUPS. ( UNIX )
Shutdown the database if it is running. Then start SQL Plus and connect as SYSDBA.
$sqlplus
SQL> connect / as sysdba
SQL> Shutdown immediate
SQL> Exit
After Shutting down the database. Copy all the datafiles, logfiles, controlfiles, parameter file and password file to your backup destination.
TIP:
To identify the datafiles, Logfiles query the data dictionary tables V$DATAFILE and V$LOGFILE before shutting down.
Lets suppose all the files are in "/u01/ica" directory. Then the following command copies all the files to the backup destination /u02/backup.
$cd /u01/ica
$cp * /u02/backup/
Be sure to remember the destination of each file. This will be useful when restoring from this backup. You can create text file and put the destinations of each file for future use. Now you can open the database.
TAKING ONLINE (HOT) BACKUPS.(UNIX)
To take online backups the database should be running in Archivelog mode. To check whether the database is running in Archivelog mode or Noarchivelog mode. Start sqlplus and then connect as SYSDBA.
After connecting give the command "archive log list" this will show you the status of archiving.
$sqlplus
Enter User:/ as sysdba
SQL> ARCHIVE LOG LIST
If the database is running in archive log mode then you can take online backups.
Let us suppose we want to take online backup of "USERS" tablespace. You can query the V$DATAFILE view to find out the name of datafiles associated with this tablespace. Lets suppose the file is
"/u01/ica/usr1.dbf ".
Give the following series of commands to take online backup of USERS tablespace.
$sqlplus
Enter User:/ as sysdba
SQL> alter tablespace users begin backup;
SQL> host cp /u01/ica/usr1.dbf /u02/backup
SQL> alter tablespace users end backup;
SQL> exit;
RECOVERING THE DATABASE IF IT IS RUNNING IN NOARCHIVELOG MODE.
Option 1: When you don’t have a backup.
If you have lost one datafile and if you don't have any backup and if the datafile does not contain important objects then, you can drop the damaged datafile and open the database. You will loose all information contained in the damaged datafile.
The following are the steps to drop a damaged datafile and open the database.
(UNIX)
STEP 1: First take full backup of database for safety.
STEP 2: Start the sqlplus and give the following commands.
$sqlplus
Enter User:/ as sysdba
SQL> STARTUP MOUNT
SQL> ALTER DATABASE DATAFILE '/u01/ica/usr1.dbf ' offline drop;
SQL>alter database open;
Option 2:When you have the Backup.
If the database is running in Noarchivelog mode and if you have a full backup. Then there are two options for you.
i . Either you can drop the damaged datafile, if it does not contain important information which you can
afford to loose.
ii . Or you can restore from full backup. You will loose all the changes made to the database since last full
backup.
To drop the damaged datafile follow the steps shown previously.
To restore from full database backup. Do the following.
STEP 1: Take a full backup of current database.
STEP 2: Restore from full database backup i.e. copy all the files from backup to their original locations.
(UNIX)
Suppose the backup is in "/u2/oracle/backup" directory. Then do the following.
$cp /u02/backup/* /u01/ica
This will copy all the files from backup directory to original destination. Also remember to copy the control files to all the mirrored locations.
RECOVERING FROM LOST OF CONTROL FILE.
If you have lost the control file and if it is mirrored. Then simply copy the control file from mirrored location to the damaged location and open the database
If you have lost all the mirrored control files and all the datafiles and logfiles are intact. Then you can recreate a control file.
If you have already taken the backup of control file creation statement by giving this command. " ALTER DATABASE BACKUP CONTROLFILE TO TRACE; " and if you have not added any tablespace since then, just create the controlfile by executing the statement
Buf If you have added any new tablespace after generating create controlfile statement. Then you have to alter the script and include the filename and size of the file in script file.
If your script file containing the control file creation statement is "CR.SQL"
Then just do the following.
STEP 1: Start sqlplus
STEP 2: connect / as sysdba
STEP 3: Start and do not mount a database like this.
SQL> STARTUP NOMOUNT
STEP 4: Run the "CR.SQL" script file.
STEP 5: Mount and Open the database.
SQL>alter database mount;
SQL>alter database open;
If you do not have a backup of Control file creation statement. Then you have to manually give the CREATE CONTROL FILE statement. You have to write the file names and sizes of all the datafiles. You will lose any datafiles which you do not include.
Refer to "Managing Control File" topic for the CREATE CONTROL FILE statement.
Recovering Database when the database is running in ARCHIVELOG Mode.
Recovering from the lost of Damaged Datafile.
If you have lost one datafile. Then follow the steps shown below.
STEP 1. Shutdown the Database if it is running.
STEP 2. Restore the datafile from most recent backup.
STEP 3. Then Start sqlplus and connect as SYSDBA.
$sqlplus
Enter User:/ as sysdba
SQL>Startup mount;
SQL>Set autorecovery on;
SQL>alter database recover;
If all archive log files are available then recovery should go on smoothly. After you get the "Media Recovery Completely" statement. Go on to next step.
STEP 4. Now open the database
SQL>alter database open;
Recovering from the Lost Archived Files:
If you have lost the archived files. Then Immediately shutdown the database and take a full offline backup.
Time Based Recovery (INCOMPLETE RECOVERY).
Suppose a user has a dropped a crucial table accidentally and you have to recover the dropped table.
You have taken a full backup of the database on Monday 13-Aug-2007 and the table was created on Tuesday 14-Aug-2007 and thousands of rows were inserted into it. Some user accidently drop the table on Thursday 16-Aug-2007 and nobody notice this until Saturday.
Now to recover the table follow these steps.
STEP 1. Shutdown the database and take a full offline backup.
STEP 2. Restore all the datafiles, logfiles and control file from the full offline backup which was taken on Monday.
STEP 3. Start SQLPLUS and start and mount the database.
STEP 4. Then give the following command to recover database until specified time.
SQL> recover database until time '2007:08:16:13:55:00'
using backup controlfile;
STEP 5. Open the database and reset the logs. Because you have performed a Incomplete Recovery, like this
SQL> alter database open resetlogs;
STEP 6. After database is open. Export the table to a dump file using Export Utility.
STEP 7. Restore from the full database backup which you have taken on Saturday.
STEP 8. Open the database and Import the table.
===================
Opening or Bringing the database in Archivelog mode.
To open the database in Archive log mode. Follow these steps:
STEP 1: Shutdown the database if it is running.
STEP 2: Take a full offline backup.
STEP 3: Set the following parameters in parameter file.
LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc
LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
If you want you can specify second destination also
LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc1”
Step 3: Start and mount the database.
SQL> STARTUP MOUNT
STEP 4: Give the following command
SQL> ALTER DATABASE ARCHIVELOG;
STEP 5: Then type the following to confirm.
SQL> ARCHIVE LOG LIST;
STEP 6: Now open the database
SQL>alter database open;
Step 7: It is recommended that you take a full backup after you brought the database in archive log mode.
To again bring back the database in NOARCHIVELOG mode. Follow these steps:
STEP 1: Shutdown the database if it is running.
STEP 2: Comment the following parameters in parameter file by putting " # " .
# LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
# LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc2”
# LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc
STEP 3: Startup and mount the database.
SQL> STARTUP MOUNT;
STEP 4: Give the following Commands
SQL> ALTER DATABASE NOARCHIVELOG;
STEP 5: Shutdown the database and take full offline backup.
TAKING OFFLINE BACKUPS. ( UNIX )
Shutdown the database if it is running. Then start SQL Plus and connect as SYSDBA.
$sqlplus
SQL> connect / as sysdba
SQL> Shutdown immediate
SQL> Exit
After Shutting down the database. Copy all the datafiles, logfiles, controlfiles, parameter file and password file to your backup destination.
TIP:
To identify the datafiles, Logfiles query the data dictionary tables V$DATAFILE and V$LOGFILE before shutting down.
Lets suppose all the files are in "/u01/ica" directory. Then the following command copies all the files to the backup destination /u02/backup.
$cd /u01/ica
$cp * /u02/backup/
Be sure to remember the destination of each file. This will be useful when restoring from this backup. You can create text file and put the destinations of each file for future use. Now you can open the database.
TAKING ONLINE (HOT) BACKUPS.(UNIX)
To take online backups the database should be running in Archivelog mode. To check whether the database is running in Archivelog mode or Noarchivelog mode. Start sqlplus and then connect as SYSDBA.
After connecting give the command "archive log list" this will show you the status of archiving.
$sqlplus
Enter User:/ as sysdba
SQL> ARCHIVE LOG LIST
If the database is running in archive log mode then you can take online backups.
Let us suppose we want to take online backup of "USERS" tablespace. You can query the V$DATAFILE view to find out the name of datafiles associated with this tablespace. Lets suppose the file is
"/u01/ica/usr1.dbf ".
Give the following series of commands to take online backup of USERS tablespace.
$sqlplus
Enter User:/ as sysdba
SQL> alter tablespace users begin backup;
SQL> host cp /u01/ica/usr1.dbf /u02/backup
SQL> alter tablespace users end backup;
SQL> exit;
RECOVERING THE DATABASE IF IT IS RUNNING IN NOARCHIVELOG MODE.
Option 1: When you don’t have a backup.
If you have lost one datafile and if you don't have any backup and if the datafile does not contain important objects then, you can drop the damaged datafile and open the database. You will loose all information contained in the damaged datafile.
The following are the steps to drop a damaged datafile and open the database.
(UNIX)
STEP 1: First take full backup of database for safety.
STEP 2: Start the sqlplus and give the following commands.
$sqlplus
Enter User:/ as sysdba
SQL> STARTUP MOUNT
SQL> ALTER DATABASE DATAFILE '/u01/ica/usr1.dbf ' offline drop;
SQL>alter database open;
Option 2:When you have the Backup.
If the database is running in Noarchivelog mode and if you have a full backup. Then there are two options for you.
i . Either you can drop the damaged datafile, if it does not contain important information which you can
afford to loose.
ii . Or you can restore from full backup. You will loose all the changes made to the database since last full
backup.
To drop the damaged datafile follow the steps shown previously.
To restore from full database backup. Do the following.
STEP 1: Take a full backup of current database.
STEP 2: Restore from full database backup i.e. copy all the files from backup to their original locations.
(UNIX)
Suppose the backup is in "/u2/oracle/backup" directory. Then do the following.
$cp /u02/backup/* /u01/ica
This will copy all the files from backup directory to original destination. Also remember to copy the control files to all the mirrored locations.
RECOVERING FROM LOST OF CONTROL FILE.
If you have lost the control file and if it is mirrored. Then simply copy the control file from mirrored location to the damaged location and open the database
If you have lost all the mirrored control files and all the datafiles and logfiles are intact. Then you can recreate a control file.
If you have already taken the backup of control file creation statement by giving this command. " ALTER DATABASE BACKUP CONTROLFILE TO TRACE; " and if you have not added any tablespace since then, just create the controlfile by executing the statement
Buf If you have added any new tablespace after generating create controlfile statement. Then you have to alter the script and include the filename and size of the file in script file.
If your script file containing the control file creation statement is "CR.SQL"
Then just do the following.
STEP 1: Start sqlplus
STEP 2: connect / as sysdba
STEP 3: Start and do not mount a database like this.
SQL> STARTUP NOMOUNT
STEP 4: Run the "CR.SQL" script file.
STEP 5: Mount and Open the database.
SQL>alter database mount;
SQL>alter database open;
If you do not have a backup of Control file creation statement. Then you have to manually give the CREATE CONTROL FILE statement. You have to write the file names and sizes of all the datafiles. You will lose any datafiles which you do not include.
Refer to "Managing Control File" topic for the CREATE CONTROL FILE statement.
Recovering Database when the database is running in ARCHIVELOG Mode.
Recovering from the lost of Damaged Datafile.
If you have lost one datafile. Then follow the steps shown below.
STEP 1. Shutdown the Database if it is running.
STEP 2. Restore the datafile from most recent backup.
STEP 3. Then Start sqlplus and connect as SYSDBA.
$sqlplus
Enter User:/ as sysdba
SQL>Startup mount;
SQL>Set autorecovery on;
SQL>alter database recover;
If all archive log files are available then recovery should go on smoothly. After you get the "Media Recovery Completely" statement. Go on to next step.
STEP 4. Now open the database
SQL>alter database open;
Recovering from the Lost Archived Files:
If you have lost the archived files. Then Immediately shutdown the database and take a full offline backup.
Time Based Recovery (INCOMPLETE RECOVERY).
Suppose a user has a dropped a crucial table accidentally and you have to recover the dropped table.
You have taken a full backup of the database on Monday 13-Aug-2007 and the table was created on Tuesday 14-Aug-2007 and thousands of rows were inserted into it. Some user accidently drop the table on Thursday 16-Aug-2007 and nobody notice this until Saturday.
Now to recover the table follow these steps.
STEP 1. Shutdown the database and take a full offline backup.
STEP 2. Restore all the datafiles, logfiles and control file from the full offline backup which was taken on Monday.
STEP 3. Start SQLPLUS and start and mount the database.
STEP 4. Then give the following command to recover database until specified time.
SQL> recover database until time '2007:08:16:13:55:00'
using backup controlfile;
STEP 5. Open the database and reset the logs. Because you have performed a Incomplete Recovery, like this
SQL> alter database open resetlogs;
STEP 6. After database is open. Export the table to a dump file using Export Utility.
STEP 7. Restore from the full database backup which you have taken on Saturday.
STEP 8. Open the database and Import the table.
Friday, July 4, 2008
how to change sysman and dbsnmp password for Oracle 11g under Linux
- emctl stop dbconsole
- emctl status dbconsole and emctl status agent to make sure it's stopped
- ORACLE_SID=orcl
- sqlplus / as sysdba
- alter user sysman identified by sysman (change new password to 'sysman')
- conn sysman/sysman
- alter user sysman account unlock (if above command shows 'locked')
- cd $ORACLE_HOME/hostname_sid/sysman/config
- vi emoms.properties
- change orcle.sysman.eml.mntr.emdRepPwd=newplaintextpassword
- change orcle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE (from true to false)
- emctl start dbconsole
- now the plain text password will be encrypted, and false will become true
for dbsnmp password change, same thing except for the below.
-
modify the file targets.xml under sysman/emd folder
Just like before, change encryptedpassword to new plaintext password and change TRUE to FALSE.
Wednesday, July 2, 2008
manually create EM db console
#emca -config dbcontrol db -repos recreate
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/11.1.0/db_2
Local hostname ................ hpdb1.domain.com
Listener port number ................ 1521
Database SID ................ testdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
INFO: >>>>>>>>>>> The Database Control URL is https://hpdb1.domain.com:5500/em <<<<<<<<<<<
Jul 3, 2008 11:21:57 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.1.0/db_2/hpdb1.domain.com_testdb/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/11.1.0/db_2
Local hostname ................ hpdb1.domain.com
Listener port number ................ 1521
Database SID ................ testdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
INFO: >>>>>>>>>>> The Database Control URL is https://hpdb1.domain.com:5500/em <<<<<<<<<<<
Jul 3, 2008 11:21:57 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.1.0/db_2/hpdb1.domain.com_testdb/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
Subscribe to:
Posts (Atom)