ORACLE DATABASE ADMINISTRATOR

Tuesday, September 25, 2012

Hot Backup clone without using RMAN

I wish to create details steps " how to clone database without using RMAN in hotbackup mode"

SQL> conn tom/tom
Connected.
SQL> select * from tab;

A                              TABLE
B                              TABLE
SQL> select * from b;

         8
         8
         8
         8
         8
SQL>

1. Make sure the database is in archive log mode
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oradata/jetway/jatway/arch
Oldest online log sequence     40
Next log sequence to archive   42
Current log sequence           42

2. Make a note of the current archive log change number
Because the restored files will require recovery, some archive logs will be needed.
This applies even if you are not intending to put the cloned database into archive log mode.
Work out which will be the first required log by running the following query on the source database.
Make a note of the change number that is returned:

SQL> select max(first_change#) Latest_chng from v$archived_log;

LATEST_CHNG
-----------
    1134000

3. View the current location of the data files

SQL> Select tablespace_name, file_name from dba_data_files order by 1;
SAMTBS1                        /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SYSAUX                         /u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSTEM                         /u01/app/oracle/oradata/jetway/jatway/system01.dbf
UNDOTBS1                       /u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
USERS                          /u01/app/oracle/oradata/jetway/jatway/users01.db

4. Check whether the data files are in backup mode or not

SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

NAME                                FILE# STATUS
------------------------------ ---------- ------------------
SYSTEM                                  1 NOT ACTIVE
SYSAUX                                  2 NOT ACTIVE
UNDOTBS1                                3 NOT ACTIVE
USERS                                   4 NOT ACTIVE
SAMTBS1                                 5 NOT ACTIVE

The above result shows that the data files are not in backup mode

5. Generate a script to bring the data file in backup mode using the following command:

SQL> !pwd
/home/oracle

[oracle@sfo ~]$ cd /home/oracle/
[oracle@sfo ~]$ vi enable_hotbackup_mode.sql

set lines 999 pages 999
set verify off
set feedback off
set heading off

spool begin_backup.sql

select 'alter tablespace ' || tablespace_name || ' begin backup;' tsb_backup_mode
from    dba_tablespaces
where   contents != 'TEMPORARY'
order by tablespace_name
/
spool off

SQL> @enable_hotbackup_mode.sql

alter tablespace SAMTBS1 begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace SYSTEM begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace USERS begin backup;

(or) -- if you do not want a particular tablespace in backup mode,
the entire database can be brought to backup mode using the following command

SQL>alter database begin backup;

SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 ACTIVE
SYSAUX                                  2 ACTIVE
UNDOTBS1                                3 ACTIVE
USERS                                   4 ACTIVE
SAMTBS1                                 5 ACTIVE

The above result shows all the tablespaces with backup mode enabled

6. Check the database using server parameter file

SQL> show parameter spfile;

spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilejatway.ora

7. The below steps helps to bring individual tablespaces out of backup mode

SQL> alter tablespace SAMTBS1 end backup;

a)

SQL> alter tablespace SAMTBS1 end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 ACTIVE
SYSAUX                                  2 ACTIVE
UNDOTBS1                                3 ACTIVE
USERS                                   4 ACTIVE
SAMTBS1                                 5 NOT ACTIVE

b)

SQL> alter tablespace SYSTEM end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 NOT ACTIVE
SYSAUX                                  2 ACTIVE
UNDOTBS1                                3 ACTIVE
USERS                                   4 ACTIVE
SAMTBS1                                 5 NOT ACTIVE

c)

SQL> alter tablespace SYSAUX end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 NOT ACTIVE
SYSAUX                                  2 NOT ACTIVE
UNDOTBS1                                3 ACTIVE
USERS                                   4 ACTIVE
SAMTBS1                                 5 NOT ACTIVE

d)

SQL> alter tablespace UNDOTBS1 end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 NOT ACTIVE
SYSAUX                                  2 NOT ACTIVE
UNDOTBS1                                3 NOT ACTIVE
USERS                                   4 ACTIVE
SAMTBS1                                 5 NOT ACTIVE

e)

SQL> alter tablespace USERS end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;

SYSTEM                                  1 NOT ACTIVE
SYSAUX                                  2 NOT ACTIVE
UNDOTBS1                                3 NOT ACTIVE
USERS                                   4 NOT ACTIVE
SAMTBS1                                 5 NOT ACTIVE

8. The following query can also be used to find whether the tablespaces is in backup mode or not

SQL> Select * from v$backup;

         1 NOT ACTIVE            1179330 25-SEP-12
         2 NOT ACTIVE            1179330 25-SEP-12
         3 NOT ACTIVE            1179330 25-SEP-12
         4 NOT ACTIVE            1179330 25-SEP-12
         5 NOT ACTIVE            1179330 25-SEP-12

9. Copy the following tablespaces to the clone location (except the temp tablespace)
I am moving all the files to the "backup" folder

SQL> Select tablespace_name, file_name from dba_data_files order by 1;
SAMTBS1                        /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SYSAUX                         /u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSTEM                         /u01/app/oracle/oradata/jetway/jatway/system01.dbf
UNDOTBS1                       /u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
USERS                          /u01/app/oracle/oradata/jetway/jatway/users01.db

[oracle@sfo backup]$ pwd
/u01/app/oracle/oradata/jetway/jatway/backup
[oracle@sfo backup]$ ls
initclohot.ora samtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf

10. Create the TAR file of the copied tablesapces. To avoid future errors I am creating another
copy of the tablespace backup folder and moving across the clone location

[oracle@sfo jatway]$ cp -r backup backup01
[oracle@sfo jatway]$ ls
arch backup backup01 backupinitclohot.ora control01.ctl redo01.log redo02.log redo03.log redo04.log redo05.log redo06.log redo07.log samtbs01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@sfo jatway]$ tar -cvzf backup.tar.gz backup01
backup01/
backup01/system01.dbf
backup01/control01.ora
backup01/sysaux01.dbf
backup01/initclohot.ora
backup01/samtbs01.dbf
backup01/undotbs01.dbf
backup01/users01.dbf
backup01/1_41_793069279.dbf
[oracle@sfo jatway]$

[oracle@sfo jatway]$ ls
arch    backup01              backup.tar.gz redo01.log redo03.log redo05.log redo07.log    sysaux01.dbf temp01.dbf     users01.dbf
backup backupinitclohot.ora control01.ctl redo02.log redo04.log redo06.log samtbs01.dbf system01.dbf undotbs01.dbf
[oracle@sfo jatway]$ scp backup.tar.gz oracle@192.168.168.1:/u01/app/oracle/oradata

oracle@192.168.168.1's password:

backup.tar.gz                   100% 245MB 17.5MB/s   00:14
                                                                                                                                                                           100% 245MB 17.5MB/s   00:14

11. move the password file for the clone database across network

[oracle@sfo jatway]$

[oracle@sfo jatway]$ cd $ORACLE_HOME/dbs
[oracle@sfo dbs]$ ls orapw*
orapwdeccan orapwdelta orapwjatway
[oracle@sfo dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@sfo dbs]$ scp orapwjatway oracle@192.168.168.1:/u01/app/oracle/product/11.2.0/db_1/dbs
oracle@192.168.168.1's password:
orapwjatway                                 100% 1536     1.5KB/s   00:00                                                                                                                                                                             100% 1536     1.5KB/s   00:00
[oracle@sfo dbs]$

[oracle@sfo backup]$

12. Create a backup control file for the clone location and move across the clone location.
SQL> show parameter control

control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/jetway
                                                 /jatway/control01.ctl, /u01/ap
                                                 p/oracle/flash_recovery_area/j
                                                 atway/control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL>Alter database backup controlfile to trace as '/u01/app/oracle/oradata/jetway/jatway/backup/control01.ora';

[oracle@sfo backup]$ pwd
/u01/app/oracle/oradata/jetway/jatway/backup
[oracle@sfo backup]$ ls
control01.ora initclohot.ora samtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
[oracle@sfo backup]$

oracle@sfo dbs]$ scp control01.ora oracle@192.168.168.1:/u01/app/oracle/oradata/backup/control01.ora
oracle@192.168.168.1's password:
control01.ora                                 100% 1536     1.5KB/s   00:00

13. Copy archive logs for clone database
It is only necessary to copy archive logs created during the time the source database was in backup mode.
Begin by archiving the current redo: floow the step 2 to get archive log current.

SQL>alter system archive log current;

select name from v$archived_log where first_change# >= &change_no
order by name
/

Enter value for change_no: 1134000

/u01/app/oracle/oradata/jetway/jatway/arch/1_41_793069279.dbf

14. In the clone database location (name of the clone database is "clohot") and UNTAR the zip file

[oracle@nyc oradata]$cd /u01/app/oracle/oradata
[oracle@nyc oradata]$ ls
backup.tar.gz
[oracle@nyc oradata]$ ls
backup.tar.gz

[oracle@nyc oradata]$ tar -xvzf backup.tar.gz
backup01/
backup01/system01.dbf
backup01/control01.ora
backup01/sysaux01.dbf
backup01/initclohot.ora
backup01/samtbs01.dbf
backup01/undotbs01.dbf
backup01/users01.dbf
backup01/1_41_793069279.dbf

[oracle@nyc oradata]$

[oracle@nyc oradata]$ mv backup01 clohot/
[oracle@nyc oradata]$ ls -lrt
total 250916
drwxr-xr-x 4 oracle oinstall      4096 Sep 2 23:29 delta
drwxr-xr-x 3 oracle oinstall      4096 Sep 9 05:48 deccan
-rw-r--r-- 1 oracle oinstall 256665891 Sep 25 05:11 backup.tar.gz
drwxr-xr-x 4 oracle oinstall      4096 Sep 25 09:35 clohot

[oracle@nyc oradata]$ cd clohot/
[oracle@nyc clohot]$ ls -lrt
total 8
drwxr-xr-x 2 oracle oinstall 4096 Sep 25 05:06 backup01
drwxr-xr-x 2 oracle oinstall 4096 Sep 25 09:33 arch
[oracle@nyc clohot]$

15. Prepare the init parameter file for clone database("clohot")

Actul init parameter file
*.audit_file_dest='/u01/app/oracle/admin/jatway/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/jetway/jatway/control01.ctl','/u01/app/oracle/flash_recovery_area/jatway/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.db_name='jatway'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='jatway'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jatwayXDB)'
*.fal_client='delta'
*.fal_server='deccan'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(jatway,deccan)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jatway'
*.LOG_ARCHIVE_DEST_2='SERVICE=deccan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=deccan'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.log_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
~
(note: My source database has dataguard so i am just database cone with archivelog and flashback enabled)

create the required dicectory as mentined in the parameter file
[oracle@nyc backup01]$ mkdir -p /u01/app/oracle/admin/clohot/adump
[oracle@nyc backup01]$ mkdir -p /u01/app/oracle/oradata/clohot

16. Modify the parameter as per the clone database base directory setup and add entry in /etc/oratab

[oracle@nyc backup01]$ cat initclohot.ora
*.audit_file_dest='/u01/app/oracle/admin/clohot/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/clohot/control01.ctl'
*.db_block_size=8192
*.db_domain=''
#*.db_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.db_name='clohot'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
#*.db_unique_name='jatway'
*.diagnostic_dest='/u01/app/oracle'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=jatwayXDB)'
#*.fal_client='delta'
#*.fal_server='deccan'
#*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(jatway,deccan)'
#*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jatway'
#*.LOG_ARCHIVE_DEST_2='SERVICE=deccan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=deccan'
#*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
#*.log_archive_dest_state_2='ENABLE'
#*.log_archive_format='%t_%s_%r.dbf'
#*.LOG_ARCHIVE_MAX_PROCESSES=30
#*.log_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
#*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
~
(Note: change the db_domain, db_unique_name,)

[oracle@nyc clohot]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1

17. Add entry in /etc/oratab

[oracle@nyc dbs]$ vi /etc/oratab
clohot:/u01/app/oracle/product/11.2.0/db_1:N

[oracle@nyc flash_recovery_area]$ . oraenv
ORACLE_SID = [oracle] ? clohot
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@nyc flash_recovery_area]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 25 10:27:57 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size             738200544 bytes
Database Buffers          486539264 bytes
Redo Buffers                9007104 bytes
SQL>

18. Create the control file script.

actual control file look like this---

[oracle@nyc clohot]$ cat control01.ora
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="jatway"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE'
-- LOG_ARCHIVE_CONFIG='DG_CONFIG=("deccan")'
-- LOG_ARCHIVE_MAX_PROCESSES=30
-- STANDBY_FILE_MANAGEMENT=auto
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=delta
-- FAL_SERVER=deccan
--
-- LOG_ARCHIVE_DEST_2='SERVICE=deccan'
-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_2='LGWR NOAFFIRM NOEXPEDITE NOVERIFY ASYNC=61440'
-- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_2='DB_UNIQUE_NAME=deccan'
-- LOG_ARCHIVE_DEST_2='VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
-- LOG_ARCHIVE_DEST_STATE_2=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_1='DB_UNIQUE_NAME=jatway'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JATWAY" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/jetway/jatway/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/jetway/jatway/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/jetway/jatway/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 4 '/u01/app/oracle/oradata/jetway/jatway/redo04.log' SIZE 50M BLOCKSIZE 512,
--   GROUP 5 '/u01/app/oracle/oradata/jetway/jatway/redo05.log' SIZE 50M BLOCKSIZE 512,
--   GROUP 6 '/u01/app/oracle/oradata/jetway/jatway/redo06.log' SIZE 50M BLOCKSIZE 512,
--   GROUP 7 '/u01/app/oracle/oradata/jetway/jatway/redo07.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/jetway/jatway/system01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/users01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/u01/app/oracle/oradata/jetway/jatway/backup/%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_694916203.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_793069279.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/jetway/jatway/temp01.dbf'
     SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo04.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo05.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo06.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo07.log'
SIZE 50M BLOCKSIZE 512 REUSE;
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JATWAY" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/jetway/jatway/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/jetway/jatway/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/jetway/jatway/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE

DATAFILE
'/u01/app/oracle/oradata/jetway/jatway/system01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/users01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/u01/app/oracle/oradata/jetway/jatway/backup/%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_694916203.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_793069279.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

-- Create log files for threads other than thread one.
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/jetway/jatway/temp01.dbf'
     SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo04.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo05.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo06.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo07.log'
SIZE 50M BLOCKSIZE 512 REUSE;

19. using following points you can create the control file script
The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

    Remove any lines that start with '#', '--',

    Remove any blank lines in the 'CREATE CONTROLFILE' section and 'RECOVER DATABASE USING BACKUP CONTROLFILE' and 'ALTER DATABASE OPEN RESETLOGS;'

    Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file.
          Make sure that you hang onto the command, it will be used later.

   The word 'REUSE' needs to be changed to 'SET' and 'NORESETLOGS' changed to 'RESETLOGS'

    The database name needs setting to the new database name ("CLOHOT") (if it is being changed).

    Decide whether the database will be put into archivelog mode or not. IF YES USE "ARCHIVELOG", IF NO USE "NOARCHIVELOG"

    If the file paths are being changed, alter the file to reflect the changes.

20. changes are made as per the requirement

[oracle@nyc clohot]$ cat control01.ora
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CLOHOT" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/clohot/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/clohot/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/clohot/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/clohot/system01.dbf',
'/u01/app/oracle/oradata/clohot/sysaux01.dbf',
'/u01/app/oracle/oradata/clohot/undotbs01.dbf',
'/u01/app/oracle/oradata/clohot/users01.dbf',
'/u01/app/oracle/oradata/clohot/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> @/u01/app/oracle/oradata/clohot/contro1_create.sql
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size             738200544 bytes
Database Buffers          486539264 bytes
Redo Buffers                9007104 bytes

Control file created

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

21. now apply the archivelog to perfornm the recovery.

SQL> recover database using backup controlfile until cancel;
(note: The above show the suggested archive log so you need to give full path of the arhivelog)

ORA-00279: change 1179330 generated at 09/25/2012 04:35:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_42_%u_.arc
ORA-00280: change 1179330 for thread 1 is in sequence #42

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto

SQL> RECOVER DATABASE using backup controlfile until cancel;
ORA-00279: change 1179330 generated at 09/25/2012 04:35:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf
ORA-00280: change 1179330 for thread 1 is in sequence #42

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf
ORA-00279: change 1196618 generated at 09/25/2012 09:00:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_43_%u_.arc
ORA-00280: change 1196618 for thread 1 is in sequence #43
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf'
no longer needed for this recovery
(note: archivelog "1_42*" applied now it required "1_43*"

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> RECOVER DATABASE using backup controlfile until cancel;
ORA-00279: change 1196618 generated at 09/25/2012 09:00:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_43_%u_.arc
ORA-00280: change 1196618 for thread 1 is in sequence #43

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

22. Now open the database with resetlog.
(if archivelog not applied preperly it thow throw the error when open the database
Re: ORA-01195: online backup of file 1 needs more recovery to be consistent )

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

23. create spfile from pfile and shutdown and startup the database toapply the change.

SQL> create spfile from pfile;

File created.

24. create the temporary table for the clone database

CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/u01/app/oracle/oradata/clohot/temp_01.tmp' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

SQL> select NAME, TS# from v$TEMPFILE ;

NAME                                                         TS#
--------------------------------------------------------------------------------

/u01/app/oracle/oradata/clohot/temp_01.tmp                    7

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

Database altered.

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME                BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP01                            4194304   16777216

SQL>shutdown immdiate

25. change the database ID (DBID) if we clone tha database using rman it will change the DBID automatically.
due to the manual clone we need to change the DBID using "nid utility"

SQL> startup mount
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2212896 bytes
Variable Size             738200544 bytes
Database Buffers          486539264 bytes
Redo Buffers                9007104 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@nyc flash_recovery_area]$ nid target=/

DBNEWID: Release 11.2.0.1.0 - Production on Tue Sep 25 12:16:05 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to database CLOHOT (DBID=3196377116)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/clohot/control01.ctl

Change database ID of database CLOHOT? (Y/[N]) => y (note: here we have to give to give "Y" to change the DBID)

Proceeding with operation
Changing database ID from 3196377116 to 2915369990
    Control File /u01/app/oracle/oradata/clohot/control01.ctl - modified
    Datafile /u01/app/oracle/oradata/clohot/system01.db - dbid changed
    Datafile /u01/app/oracle/oradata/clohot/sysaux01.db - dbid changed
    Datafile /u01/app/oracle/oradata/clohot/undotbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/clohot/users01.db - dbid changed
    Datafile /u01/app/oracle/oradata/clohot/samtbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/clohot/temp_01.tm - dbid changed
    Control File /u01/app/oracle/oradata/clohot/control01.ctl - dbid changed
    Instance shut down

Database ID for database CLOHOT changed to 2915369990.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

26. Finally mount and open the database with resetlog option

SQL>startup mount
SQL>alter database open resetlogs
Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfileclohot.ora

SQL> select max(first_change#) Latest_chng from v$archived_log;

LATEST_CHNG
-----------
(not showing any value)

SQL> conn tom/tom
Connected.

SQL> select * from tab;
A                              TABLE
B                              TABLE

SQL> select * from b;

         8
         8
         8
         8
         8
SQL>

27. Configure the listener and tns as per the requirement

Saturday, September 22, 2012

Flash Back Concepts

Files stored in Flash Recovery Area(FRA)

Redo log file and control file are permant file resides in FRA remainig file are deleted by FRA
become obsolete or already backed up to media

(1)Flashback logs:
if Flashback enabled option enabled in the database. every changes in blocks belongs to the datafile
will be recorded as a image copy in flash recovery area named flashback log.

(2)Control file:
The multiplexed copy of current control file will e stored in FRA

(3)Control file of Autobackups:
In RMAN settin "CONFIGURE CONTROLFILE AUTOBACKUP ON" then it copies
(RMAN automatically backs up the control file and the current server parameter file)
(a) when a successful backup must be recorded in the RMAN repository
(b) when a structural change to the database affects the contents of the control file
(c) Whenever you (add /drop) ,changing the status in(online/offline/read only), (rename/resize,/increase /decrease) the size of datafileor tablespace
(d) When adding a new online redo log or adding a new redo thread.

(4)Online Redo log files:
multiplexed copy of redo log file are allow to store in FRA

(5)Archived Redo log files
FRA allow store your archived redo log file but any space issue in FRA Rman automatically will delete the files

Flash Recovey feature of 11g

SQL> desc v$flashback_database_log;
Name                                      Null?    Type
----------------------------------------- -------- --------
OLDEST_FLASHBACK_SCN          NUMBER - (minimum SCN to which you can flash back the database)
OLDEST_FLASHBACK_TIME                  DATE   - (earliest time to which you can flash back the database)
RETENTION_TARGET                        NUMBER - (how long flashback logs are retained, in minutes)
FLASHBACK_SIZE                              NUMBER - (size of flashback logs as of now)
ESTIMATED_FLASHBACK_SIZE                  NUMBER - ( The estimated size of the total flashback     logs retained to satisfy the retention target at the end of this time interval, shown in the column END_TIME)

***To calculate (using logs available in flash recovery area) how far to rewind the database to the previous states.

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
             1040830 21-SEP-12             1440       15941632
                       0
The value of the column OLDEST_FLASHBACK_SCN is 1040830, which indicates you can
flash back to the SCN up to that number only, not before that.

***(OR)

SQL> select to_char(oldest_flashback_time,'mm/dd/yy hh24:mi:ss') from v$flashback_database_log;

TO_CHAR(OLDEST_FL
-----------------
09/21/12 16:57:23

***To Calculate the total Amount of Logs Generated in Flashback Area at several timestamps
SQL> select * from v$flashback_database_stat order by begin_time;

BEGIN_TIM END_TIME FLASHBACK_DATA    DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
21-SEP-12 21-SEP-12        2998272    2785280    1826816                        0

***(OR)-- To get the exact BEGIN TIME AND END TIME of the log generation

SQL> alter session set nls_date_format = 'mm/dd/yy hh24:mi:ss';
Session altered.

SQL> select * from v$flashback_database_stat order by begin_time;

BEGIN_TIME        END_TIME          FLASHBACK_DATA    DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- -------------- ---------- ---------- ------------------------
09/21/12 16:57:23 09/21/12 17:20:49        2998272    2785280    1876992                        0

***To get the individual Size of Logs Generated in Flashback Area at every period

SQL> select end_time, estimated_flashback_size from v$flashback_database_stat order by 1;

END_TIME          ESTIMATED_FLASHBACK_SIZE
----------------- -----------------------0
09/21/12 17:22:04                154224128
09/25/12 20:37:57                137567258
10/25/12 22:28:20                194224128


***To Calculate the total Space used by Logs in the Flash Recovery Area

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ----------------- ---------------- -------------- ------------------------
             1040830 09/21/12 16:57:23             1440       15941632                169648128

***Flashing Back a Database from "RMAN Prompt" (or) "SQL prompt"

***Presteps for FRA using "RMAN Prompt" (or) "SQL prompt"
1.To calculate (using logs available in flash recovery area) how far to rewind the database to the previous states.
SQL> select * from v$flashback_database_log;
2. rman target=/
3. RMAN> shutdown immediate (or) SQL> shutdown immediate
4. RMAN> startup mount      (or) SQL> startup mount

***Poststeps of FRA using "RMAN Prompt" (or) "SQL prompt"
1. RMAN> alter database open read only (or) SQL> alter database open read only
NOTE: to verify desired information available or againg follow the presteps of FRA THEN FOLLOW STEP 2
2.RMAN> alter database open resetlogs; (or) SQL> alter database open resetlogs;
Note: always use open the database with resetlogs to create new incarnation of scn number

***Flashing Back a Database using specific point-in-time, specified by date and time
1.presteps of FRA
2.RMAN (or) SQL> flashback database to timestamp to_date('5/18/2007 00:00:00','mm/dd/yyyy hh24:mi:ss');
(OR)
RMAN> flashback database to time 'sysdate-2/60/24';
3.poststep of FRA

***Flashing Back a Database using specific SCN number
SQL> select current_scn from v$database;

1.prestep of FRA
2.RMAN (or) SQL> flashback database to scn 4587962;
3.poststep of FRA

***Flashing Back a Database using last Resetlogs operation
SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
1548632

1.prestep of FRA
2.RMAN> flashback database to before resetlogs;
3.poststep of FRA

***Flashing Back a Database using named Restore-Point
1.prestep of FRA
RMAN (or) SQL> flashback database to restore point restore_point1;
3.poststep of FRA

Note: restore point can be (create, create with guarantee, list, drop)
SQL> create restore point rp1;
SQL> create restore point rp2 guarantee flashback database;-- create guarantee RS-point
SQL> select * from v$restore_point order by 2,1; --- list the RS-point
SQL> drop restore point rp2;

Recovering a object from Recycle bin

SQL> show recyclebin
SQL> select * from user_recyclebin;
SQL> alter session set recyclebin = on; ---The recycle bin is enabled by default.
SQL> alter session set recyclebin = off;
SQL> alter system set recyclebin = off; ---To disable for the entire database (not recommended):
SQL> alter system set recyclebin = on;

SQL> purge recyclebin;-- remove all dropped objects from the recyclebin (system wide):

***Recovering table related objects( constraint, index, trigger, )
***Display the Constraints of the table:

SQL> select constraint_type, constraint_name from user_constraints where table_name = 'SAMPLE';
C CONSTRAINT_NAME
- -----------------------------
P BIN$sdlkolirkfjHDFsdfskjks==$0 ---- P(PRIMARY)
C BIN$584ASRDJGThTINjOISVM3Q==$0 ---- C(CHECK )

SQL> alter table accounts rename constraint "BIN$sdlkolirkfjHDFsdfskjks==$0" to pk_SAMPLE;
Table altered.

SQL> alter table accounts rename constraint "BIN$584ASRDJGThTINjOISVM3Q" to ck_SAMPLE_01;
Table altered.

***Display the Index of the table:

SQL> select index_name from user_indexes where table_name = 'SAMPLE';
INDEX_NAME
-----------------------------
BIN$sdlkolirkfjHDFsdfskjks==$0
BIN$584ASRDJGThTINjOISVM3Q==$0

SQL> alter index "BIN$sdlkolirkfjHDFsdfskjks==$0" rename to IN_SAMPLE_01;
Index altered.

SQL> alter index "BIN$584ASRDJGThTINjOISVM3Q==$0" rename to IN_SAMPLE_02;
Index altered.

***To check the index restored properly

Finally, make sure the indexes are in place and have correct names:
SQL> select index_name from user_indexes where table_name = 'SAMPLE';

INDEX_NAME
-----------------------------
IN_SAMPLE_01
IN_SAMPLE_02

***Display the trigger of the table:

SQL> select trigger_name from user_triggers;
TRIGGER_NAME
-----------------------------
"BIN$sdlkolirkfjHDFsdfskjks==$0"

***Rename triggers to their original names:

SQL> alter trigger "BIN$sdlkolirkfjHDFsdfskjks==$0" rename to tr_sample_01;
Trigger altered.

*Check the triggers now to make sure they are named as they were originally:
SQL> select trigger_name from user_triggers;
TRIGGER_NAME
-----------------------------
TR_SAMPLE_01

Friday, September 21, 2012

Recover Dropped Table (No Flashback enable )

No Flashback required to be enable in the database just set the parameter value RECYCLEBIN='TRUE' then we can recover dropped table from the recyclebin
flashback table <dropped_table> to before drop )

SQL> conn /as sysdba
Connected.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
SAMTBS1                        ONLINE

6 rows selected.

SQL> conn tom/tom
Connected.

SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
TABLE1                         TABLE
TABLE2                         TABLE
TABLE3                         TABLE

SQL> select * from table2;

        ID
----------
       987

SQL> insert into table2 VALUES (675);

1 row created.

SQL> insert into table2 VALUES (698);

1 row created.

SQL> insert into table2 VALUES (695);

1 row created.

SQL> select * from table2;

        ID
----------
       987
       675
       698
       695

SQL> commit;
Commit complete.

SQL> conn /as sysdba
Connected.

SQL> drop table tom.table2;
Table dropped.

SQL> commit;
Commit complete.

SQL> select * from tom.table2;
select * from tom.table2
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn /as sysdba
Connected.

SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;
no rows selected

SQL> conn tom/tom
Connected.

SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$yj4kFALrck/gQKjAAqgojA==$0 TABLE2

SQL> flashback table table2 to before drop;
Flashback complete.

SQL> select * from table2;

        ID
----------
       987
       675
       698
       695

SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin;

no rows selected

RMAN Commands (DISK, TAPE)

RMAN> list backupset;
RMAN> list backupset of database;
RMAN> list backupset of database completed before '14-JAN-11';
RMAN> list backupset of archivelog all;
RMAN> list backupset of tablespace users;
RMAN> list backupset of datafile 1;

RMAN> list backup summary;
RMAN> list backup;
RMAN> list backup of controlfile;
RMAN> list backup of tablespace SYSTEM;
RMAN> list backup by file;
RMAN> list backup of archivelog all ;

RMAN> list copy of database;
RMAN> list copy of database archivelog all;

RMAN> allocate channel for maintenance type disk;
RMAN> configure channel device type disk clear ;

RMAN> report need backup days=2 database;
RMAN> report need backup days=10 tablespace TEMP;
RMAN> report need backup days=4 datafile '/u01/app/oracle/backup/sample/TEMP01.DBF';
RMAN> report schema;
RMAN> report obsolete;

RMAN> crosscheck backup;
RMAN> crosscheck backupset;
RMAN> crosscheck copy;
RMAN> crosscheck archivelog all;
RMAN> crosscheck controlfilecopy '/u01/app/oracle/admin/BACKUP/demo/demo_controlfile_bak_03-15-11_19:59:11' ;

RMAN> delete noprompt expired backup ;
RMAN> delete noprompt obsolete;
RMAN> delete archivelog all;
RMAN> delete expired archivelog all;
RMAN> delete archivelog all completed before 'sysdate -1';
RMAN> delete noprompt archivelog until time 'sysdate - 1';
RMAN> delete archivelog all backed up 1 times to device type disk completed before 'sysdate-1';

***configure controlfile using RMAN
$ rman target / nocatalog
RMAN>configure controlfile autobackup on;
RMAN>configure retention policy to recovery window of 12 days;

***Init.ora or (spfile) Parameters shows Backup media and retention policy of controlfile
backup_tape_io_slaves=TRUE
control_file_record_keep_time=12

***Init.ora parameters to speed up backup

sort_area_size = 20971520
sort_area_retained_size = 20971520

***Allocating Channels For disk Backup

RMAN>allocate channel c1 type disk format '/u01/app/oracle/backup/sample/%U';

***Allocating Channels for Tape Backup

RMAN> allocate channel ch1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';

***Backup database+archivelog+controlfile

$ rman target / nocatalog
RMAN>backup as compressed backupset
full
maxsetsize = 32G
filesperset = 1024
diskratio=0
tag 'Full'

****Backup database + current controlfile

PLUS ARCHIVELOG skip inaccessible format '/u01/app/oracle/backup/sample/ar_%d_%t_%c_%s_%p';

***Backup database+archive log

$ rman target / nocatalog
#Backup to disk
RMAN> backup device type disk format '/u01/app/oracle/backup/sample/%U' database
plus archivelog skip inaccessible delete input;

#Backup to Tape
RMAN> backup as compressed backupset
full
filesperset = 1024
diskratio = 0
tag = 'Full'
(database format 'SBT_TAPE/RMAN_%d.%t.full.%c.%s.%p')
plus archivelog
skip inaccessible
delete input
format 'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p';

***Backup database+controlfile

RMAN>backup as compressed backupset
full
maxsetsize = 32G
filesperset = 1024
diskratio = 0
tag = 'Full'
(database include current controlfile) keep until time 'SYSDATE+365' nologs;

***Backup Controlfile for standby database

RMAN> backup device type disk format '/u01/app/oracle/backup/sample/%U' current controlfile for standby;

***Backup Controlfile

Option # 1
RMAN>copy current controlfile to '/u01/app/oracle/backup/sample/backup/demo_controlfile.bak';

Option # 2
RMAN>backup current controlfile format '/u01/app/oracle/backup/sample/%d_controlfile_bak_%T_s%s';

*****Note : Control file is automatically backed up when
RMAN>configure controlfile autobackup on;

***Backup the archive log
#Backup to disk
RMAN>backup as compressed backupset
maxsetsize =5G
diskratio = 0
skip inaccessible
tag 'Archive Logs'
(archivelog all NOT BACKED UP 1 TIMES format '/u01/app/oracle/backup/sample/ar_%d_%t_%c_%s_%p');

#Backup To Tape
RMAN> backup as compressed backupset
maxsetsize = 5G
diskratio = 0
skip inaccessible
tag = 'Archive Logs'
(archivelog all delete input format'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p');

***Command to maintain the backup by RMAN

delete force noprompt obsolete recovery window of 12 days;
crosscheck backup;
delete force noprompt expired backup;

***Report the backupsets
list backup;

Tablespace Point-in-Time Recovery (TSPITR) 11gR2 using RMAN

11g Release 2 has new features, which enables to recover from a dropped tablespace using TSPITR. In the earlier version of oracle we could not recover a dropped tablespace using TSPITR (tablespace point in time recovery)

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS

/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1

/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX

/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS

/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1

/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX

/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM

/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SAMTBS1

SQL> create user tom identified by tom default tablespace samtbs1;
User created.

SQL> grant connect,resource to tom;
Grant succeeded.

SQL> conn tom/tom
Connected.

SQL> create table table1 ( id number);
Table created.

SQL> insert into table1 values (123);
1 row created.

SQL> create table table2 ( id number);
Table created.

SQL> insert into table2 values (987);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from tab;
TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
TABLE1                         TABLE
TABLE2                         TABLE

SQL> conn / as sysdba
Connected.

SQL> alter system switch logfile;
System altered.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1022086

[oracle@sfo ~]$ . oraenv
ORACLE_SID = [jetway] ? jatway
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[oracle@sfo ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 21 12:42:42 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: JATWAY (DBID=3196377116)

RMAN>

RMAN> backup as compressed backupset database format '/u01/app/oracle/backup/backupjatway/%d_%s_%I_%T' plus archivelog format '/u01/app/oracle/backup/backupjatway/%d_%s_%I_%T';

Starting backup at 21-SEP-12
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=10 STAMP=793527386
input archived log thread=1 sequence=12 RECID=11 STAMP=793527425
input archived log thread=1 sequence=13 RECID=12 STAMP=793527610
input archived log thread=1 sequence=14 RECID=13 STAMP=793530126
input archived log thread=1 sequence=15 RECID=14 STAMP=793530172
input archived log thread=1 sequence=16 RECID=15 STAMP=793530212
input archived log thread=1 sequence=17 RECID=16 STAMP=793531476
input archived log thread=1 sequence=18 RECID=17 STAMP=793532833
input archived log thread=1 sequence=19 RECID=18 STAMP=793532851
input archived log thread=1 sequence=20 RECID=19 STAMP=793533374
input archived log thread=1 sequence=21 RECID=20 STAMP=793533390
input archived log thread=1 sequence=22 RECID=21 STAMP=793533982
input archived log thread=1 sequence=23 RECID=22 STAMP=793545410
input archived log thread=1 sequence=24 RECID=23 STAMP=793545414
input archived log thread=1 sequence=25 RECID=24 STAMP=793545621
input archived log thread=1 sequence=26 RECID=25 STAMP=794570848
input archived log thread=1 sequence=27 RECID=26 STAMP=794570853
input archived log thread=1 sequence=28 RECID=27 STAMP=794580052
input archived log thread=1 sequence=29 RECID=28 STAMP=794580314
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_29_3196377116_20120921 tag=TAG20120921T124515 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-SEP-12

Starting backup at 21-SEP-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/jetway/jatway/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/jetway/jatway/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_30_3196377116_20120921 tag=TAG20120921T124519 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_31_3196377116_20120921 tag=TAG20120921T124519 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-SEP-12

Starting backup at 21-SEP-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=29 STAMP=794580386
channel ORA_DISK_1: starting piece 1 at 21-SEP-12
channel ORA_DISK_1: finished piece 1 at 21-SEP-12
piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_32_3196377116_20120921 tag=TAG20120921T124627 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-SEP-12

RMAN> exit

Recovery Manager complete.
[oracle@sfo ~]$

[oracle@sfo ~]$ . oraenv
ORACLE_SID = [jetway] ? jatway
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[oracle@sfo ~]$sqlplus "/as sysdba"

SQL> conn tom/tom
Connected.

SQL> create table table3 ( id number);
table created

SQL> select * from table3;
        ID
----------
       654

SQL> commit;
Commit complete.

SQL> alter system switch logfile;
System altered.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1022829

SQL> drop tablespace samtbs1 including contents and datafiles;
Tablespace dropped.

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS
/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1
/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX
/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM

[oracle@sfo ~]$ . oraenv
ORACLE_SID = [jatway] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@sfo ~]$ rman target=/

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 21 13:03:47 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: JATWAY (DBID=3196377116)

RMAN> recover tablespace samtbs1 until scn 1022829 auxiliary destination '/u01/app/oracle/backup/backupjatway';

Starting recover at 21-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='csxD'

initialization parameters used for automatic instance:
db_name=JATWAY
db_unique_name=csxD_tspitr_JATWAY
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/u01/app/oracle/backup/backupjatway
log_archive_dest_1='location=/u01/app/oracle/backup/backupjatway'
#No auxiliary parameter file used

starting up automatic instance JATWAY
Oracle instance started

Total System Global Area     292278272 bytes

Fixed Size                     2212736 bytes
Variable Size                100666496 bytes
Database Buffers             184549376 bytes
Redo Buffers                   4849664 bytes
Automatic instance created

List of tablespaces that have been dropped from the target database:
Tablespace samtbs1

contents of Memory Script:
{
# set requested point in time
set until scn 1022829;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 21-SEP-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/backupjatway/JATWAY_31_3196377116_20120921
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_31_3196377116_20120921 tag=TAG20120921T124519
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/backup/backupjatway/JATWAY/controlfile/o1_mf_85sl2cxp_.ctl
Finished restore at 21-SEP-12

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until scn 1022829;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 5 to
"/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 5;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 21-SEP-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/backupjatway/JATWAY_30_3196377116_20120921
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/backupjatway/JATWAY_30_3196377116_20120921 tag=TAG20120921T124519
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 21-SEP-12

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=794581539 file name=/u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_system_85sl2l55_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=794581539 file name=/u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_undotbs1_85sl2l5l_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=794581539 file name=/u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_sysaux_85sl2l5g_.dbf

contents of Memory Script:
{
# set requested point in time
set until scn 1022829;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 5 online";
# recover and open resetlogs
recover clone database tablespace "SAMTBS1", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 5 online

Starting recover at 21-SEP-12
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/oradata/jetway/jatway/arch/1_30_793069279.dbf
archived log for thread 1 with sequence 31 is already on disk as file /u01/app/oracle/oradata/jetway/jatway/arch/1_31_793069279.dbf
archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/oradata/jetway/jatway/arch/1_32_793069279.dbf
archived log file name=/u01/app/oracle/oradata/jetway/jatway/arch/1_30_793069279.dbf thread=1 sequence=30
archived log file name=/u01/app/oracle/oradata/jetway/jatway/arch/1_31_793069279.dbf thread=1 sequence=31
archived log file name=/u01/app/oracle/oradata/jetway/jatway/arch/1_32_793069279.dbf thread=1 sequence=32
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-SEP-12

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace SAMTBS1 read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/backup/backupjatway''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/backup/backupjatway''";
}
executing Memory Script

sql statement: alter tablespace SAMTBS1 read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/backup/backupjatway''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/backup/backupjatway''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_csxD":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_csxD" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_csxD is:
   EXPDP>   /u01/app/oracle/backup/backupjatway/tspitr_csxD_55279.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace SAMTBS1:
   EXPDP>   /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_csxD" successfully completed at 13:06:40
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_csxD" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_csxD":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_csxD" successfully completed at 13:06:57
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace SAMTBS1 read write';
sql 'alter tablespace SAMTBS1 offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace SAMTBS1 read write

sql statement: alter tablespace SAMTBS1 offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_temp_85sl4ssp_.tmp deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/onlinelog/o1_mf_3_85sl4q6w_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/onlinelog/o1_mf_2_85sl4pbv_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/onlinelog/o1_mf_1_85sl4oct_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_sysaux_85sl2l5g_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_undotbs1_85sl2l5l_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/datafile/o1_mf_system_85sl2l55_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupjatway/JATWAY/controlfile/o1_mf_85sl2cxp_.ctl deleted
Finished recover at 21-SEP-12

RMAN>exit

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/jetway/jatway/users01.dbf
USERS
/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
UNDOTBS1
/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSAUX
/u01/app/oracle/oradata/jetway/jatway/system01.dbf
SYSTEM
/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SAMTBS1

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                          ONLINE
SYSAUX                        ONLINE
UNDOTBS1                     ONLINE
TEMP                               ONLINE
USERS                             ONLINE
SAMTBS1                        OFFLINE

SQL> alter tablespace samtbs1 online;
Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                          ONLINE
SYSAUX                        ONLINE
UNDOTBS1                     ONLINE
TEMP                               ONLINE
USERS                             ONLINE
SAMTBS1                        ONLINE

SQL> conn tom/tom
Connected.

SQL> select * from tab;
TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
TABLE1                         TABLE
TABLE2                         TABLE
TABLE3                         TABLE

Sunday, September 2, 2012

Silent upgrade database from 11.2.0.2.0 to 11.2.0.3.0

Silent upgrade of database from 11.2.0.2 to 11.2.0.3. We required to adjust things for Oracle restart.

1. Start the silent install using database upgrade assistance:

export ORACLE_HOME=/oracle/home/11.2.0.3.0
export PATH=$ORACLE_HOME/bin:$PATH
ota5013#NGL451T1>>/oracle>dbua -silent -sid NGL451T1

Log files for the upgrade operation are located at: /oracle/home/cfgtoollogs/dbua/NGL451T/upgrade1
Performing Pre Upgrade
1% complete
.
.
.
92% complete
Generating Summary
Database upgrade has been completed successfully, and the database is ready to use.
100% complete

Now for Oracle restart to work properly for this database do the following

ota5013#NGL451T1>>/oracle>srvctl config database -d NGL451T

Database unique name: NGL451T
Database name: NGL451T1
Oracle home: /oracle/home/11.2.0.2.0
Oracle user: grid
Spfile: +DATA/spfileNGL451T.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:

2. Now modify the database for oracle restart

ota5013#NGL451T1>>/oracle> srvctl modify database -d NGL451T -o /oracle/home/11.2.0.3.0

ota5013#NGL451T1>>/oracle> srvctl config database -d NGL451T

Database unique name: NGL451T
Database name: NGL451T1
Oracle home: /oracle/home/11.2.0.3.0
Oracle user: grid
Spfile: +DATA/spfileNGL451T.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:

change in /etc/oratab the oracle home
NGL451T1:/oracle/home/11.2.0.3.0:N

ota5013#NGL451T1>>/oracle>srvctl upgrade database -d NGL451T -o /oracle/home/11.2.0.3.0

This to prevent the oratab to be overwritten with the old oracle home of this database.

Now your done database is upgraded and oracle restart still works for this database.