how to put database in an archive log mode
-------------------------------------------
log_archive_dest=/u01/app/arch
log_archive_format=vaibhav_%r_%t_%s.arc
shutdown normal
startup mount
alter database archivelog;
alter database open;
How to check if the database is archiving correctly
----------------------------------------------------
archive log list;
select log_mode from v$database;
ls -l /u01/app/arch
How to manually do a log switch
-------------------------------
alter system switch logfile;
How to manually perform a checkpoint
-------------------------------------
alter system checkpoint;
How to find out which redo log group is current
-----------------------------------------------
select group#, status from v$log;
How to take a hot backup
-------------------------
export ORACLE_SID=vaibhav
sqlplus / <
alter tablespace sysaux begin backup;
host cp /u01/app/oracle/oradata/data/sysaux01.dbf /backup/sysaux01.dbf
alter tablespace sysaux end backup;
alter tablespace system begin backup;
host cp /u01/app/oracle/oradata/data/system01.dbf /backup/system01.dbf
alter tablespace system end backup;
alter tablespace undotbs begin backup;
host cp /u01/app/oracle/oradata/data/undotbs01.dbf /backup/undotbs01.dbf
host cp /u01/app/oracle/oradata/data/undotbs02.dbf /backup/undotbs02.dbf
alter tablespace undotbs end backup;
alter database backup controlfile to '/backup/c_backup.ctl';
alter database backup controlfile to trace;
alter system archive log current;
host cp /u01/app/oracle/oradata/arch/* /backup/
EOF
cp initvaibhav.ora /backup
cp spvaibhav.ora /backup
cp listener.ora /backup
cp tnsnames.ora /backup
cp password_file /backup
cp altervaibhav /backup
What to do when one datafile of a running database is corrupted
-----------------------------------------------------------------
1. sql> alter database datafile '/u01/app/oradata/user01.dbf' offline;
2. restore the datafile from backup
$ cp /backup/user01.dbf /u01/app/oradata/user01.dbf
3. sql> recover datafile '/u01/app/oradata/user01.dbf';
The above command will ask for archive log files. You just press
ENTER if archive log files are in the log_archive_dest diretory.
4. sql> alter database datafile '/u01/app/oradata/user01.dbf' online;
What to do when system or undo datafile of a running database is corrupted
--------------------------------------------------------------------------
1. sql> shutdown abort;
2. restore the system or undo datafile from backup
$ cp /backup/system01.dbf /u01/app/oradata/system01.dbf
3. sql> startup mount;
4. sql> recover datafile '/u01/app/oradata/system01.dbf';
The above command will ask for archive log files. You just press
ENTER if archive log files are in the log_archive_dest diretory.
5. sql> alter database open;
What to do when one control file out of two is corrupted
---------------------------------------------------------
1. sql> shutdown abort
2. $ cp /u01/app/oradata/c1.ctl /u01/app/oradata/c2.ctl
3. sql> startup <== instance recovery will happen at this time
What to do when a temp file is lost
------------------------------------
1. sql> create temporary tablesapce temp_new tempfile
'/u01/app/oradata/tenp_new01.dbf' size 100M;
2. sql> alter database default temporary tablespace temp_new;
3. sql> drop temporary tablespace temp;
What to do when one member of a current redo group is lost
-------------------------------------------------------------
1. Make the current redo group a non-current group by doing
a manual log switch
SQL> alter system switch logfile;
2. Copy the good member over the bad/lost member
$ cp /u01/app/oradata/redo01_a.log /u01/app/oradata/redo01_b.log
Note: The database will keep on running without any issues. The alert
log file will show the error message that lgwr is unable to
write to the lost member of the group.
What to do when one of the archived log file is lost
-------------------------------------------------------------------------
When an archived log file is lost, you will not have any problem for the
running database. In this situation take a fresh backup immediately to
avoid inability to recover from any crash.
The problem will come if you have lost an archive log file and a data file
at the same time. In this situation you can perform only incomplete recovery
till the last SCN in the archived log file created prior to the lost one.
What to do when both control files are lost (incomplete recovery)
-----------------------------------------------------------------
Note: There is no data loss even if we call this an incomplete
recovery
1. sql> shutdown abort
2. Restore controlfile from its backup
$ cp /backup/c_backup.ctl /u01/app/oradata/c1.ctl
$ cp /backup/c_backup.ctl /u01/app/oradata/c2.ctl
4. sql> startup mount;
3. restore all data files
$ cp /backup/system01.dbf /u01/app/oradata/system01.dbf
$ cp /backup/sysaux01.dbf /u01/app/oradata/sysaux01.dbf
$ cp /backup/undo01.dbf /u01/app/oradata/undo01.dbf
4. All archive log file generated after the last backup must
be present in the log_archive_dest directory
sql> recover database using backup controlfile;
- The above command will ask for archive log files. You just press
ENTER if archive log files are in the log_archive_dest diretory.
- When the last archived log file is asked, enter the name of the
redo file.
- To find the name of the current redo file query v$log and v$logfile
SQL> select member
from v$log l,
v$logfile lf
where l.group# = lf.group#
and l.status = 'CURRENT';
5. sql> alter database open resetlog;
The above command will start a new incarnation of the database.
i.e. the log sequence number will start from 1.
This command will clear any contents of all online redo groups. If
the redo group is not present it will be created.
Even though the Log Sequence Number is reset to 1, the SCN does not
get reset to 1
6. You should take a new backup to have the ability to recover
from any further crashes.
What to do when all copies of current redo are lost (incomplete recovery)
-------------------------------------------------------------------------
Note : There would be a data loss
1. sql> shutdown abort
4. sql> startup mount;
3. restore all data files
$ cp /backup/system01.dbf /u01/app/oradata/system01.dbf
$ cp /backup/sysaux01.dbf /u01/app/oradata/sysaux01.dbf
$ cp /backup/undo01.dbf /u01/app/oradata/undo01.dbf
4. All archive log file generated after the last backup must
be present in the log_archive_dest directory
sql> restore database until scn 99999 ;
- The above command will ask for archive log files. You just press
ENTER if archive log files are in the log_archive_dest diretory.
Note: Use v$log to find 99999
SQL> select first_change# -1 from v$log where status = 'CURRENT';
5. sql> alter database open resetlog;
The above command will start a new incarnation of the database.
i.e. the log sequence number will start from 1.
This command will clear any contents of all online redo groups. If
the redo group is not present it will be created.
Even though the Log Sequence Number is reset to 1, the SCN does not
get reset to 1
6. You should take a new backup to have the ability to recover
from any further crashes. You can delete all older backups and
archive log files.
Related views
---------------
select * from v$log;
select * from v$logfile;
select * from v$datafile;
select * from v$controlfile;
select * from v$archived_log;
select file#, checkpoint_change# from v$datafile_header;
select current_scn from v$database;
Command RMAN Commands
----------------------
export ORACLE_SID=abcd
rman target /
report schema
backup database
backup database plus archivelog all;
backup database format '/tmp/%d_%s_%p.bak'
backup archivelog all
backup current controlfile
list backup
delete backupset 6
delete force backupset
