Learn Database Concepts

Make sure you always take Database backup before any upgrades and patches.It is always good idea to do this. In the below example I am showing how to take a backup of full database to some location and how to restore it back from backup in-case of any failures. backup database before upgrade: rman "target / nocatalog" RUN { ALLOCATE CHANNEL C1 TYPE DISK; BACKUP DATABASE FORMAT '/u02/backup/backup_%U' TAG "before_upgrade"; BACKUP CURRENT CONTROLFILE FORMAT '/u02/backup/controlfilebkp.ctl'; } Restoring database from backup in-case of upgrade fail: RMAN> restore controlfile from '/u02/backup/controlfilebkp.ctl'; Starting restore at 18-JUL-12 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output filename=/u01/app/oracle/oradata/test/control01.ctl output filename=/u01/app/oracle/oradata/test/control02.ctl output filename=/u01/app/oracle/oradata/test/control03.ctl ...

oracle@Linux01:[/u01/app/oracle/datafiles/orcl] $ sqlplus /"As sysdba" SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 21 10:09:56 2012 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE  2 ; * ERROR at line 3: ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE; ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE * ERROR at line 1: ORA-01113: file 13 needs media recovery ORA-01110: data file 13: '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' SQL> recover datafile '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf'; Media recovery c...

How to restore the old data using flashback query My intention is , I want to get back past data of database after erroneously updated and committed. We know that committed data can never be flashed back. But with 10g new flashback feature we can get back past data even they are committed.  Before proceed ensure that, •The UNDO_RETENTION initialization parameter is set to a value so that you can back your data far in the past that you might want to query. •UNDO_MANAGEMENT is set to AUTO. •In your UNDO TABLESPACE you have enough space. With an example I will demonstrate the whole procedure. 1)I have created a table named test_flash_table with column name and salary. SQL> create table test_flash_table(name varchar2(10), salary number); Table created. SQL> insert into test_flash_table values('ABCD',10); 1 row created. SQL> commit; Commit complete. The table contains one row. 2)I erroneously updated column salary of Arju and commited data. SQL> update test_flash_table s...

If you want to backup from SCN until the latest one generated use below as below: oracle@Linux01:[/u01/app/oracle] $ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 11:49:42 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. connected to target database: ORCL (DBID=33581878474) RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 15 format '/u01/app/oracle/backup/ORCL_ARCHBKP_%U' tag 'ARCH_BKP'; Starting backup at 19-JAN-13 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=44 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=15 RECID=15 STAMP=901018555 input archived log thread=1 sequence=16 RECID=16 STAMP=901025590 input archived log thread=1 sequence=17 RECID=17 STAMP=901032775 input archived log thread=1 sequence=18...