Upgrade database from 11.2.0.3 to 11.2.0.4
------------------------------------------------------

Step#1:
Take database backup running with 11.2.0.3 version( Preferably you can take zip of scheduled backups to save time )

Step#2:

download 11.2.0.4 from below link:

Install Oracle Database version 11.2.0.4 in New home path /u01/app/oracle/product/11.2.0.4/dbhome_1

https://support.oracle.com/epmos/faces/PatchDetail?_adf.ctrl-state=944tiroeg_29&patch_name=13390677&releaseId=80112030&patchId=13390677&languageId=0&platformId=226&_afrLoop=113795957239081



Install Oracle Database version 11.2.0.4 in New home path /u01/app/oracle/product/11.2.0.4/dbhome_1


Step#3:
*********

Before upgrade run the preupgrade tool to check the pre-requisites before proceeding upgrade which is available in new home.
Fix as per output result.
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112i.sql

SQL> col version for a10
SQL> col comp_name for a40
SQL> col comp_id for a20
SQL> set lines 1000
SQL> select comp_id,COMP_NAME,version, status from dba_registry;

COMP_ID              COMP_NAME                                VERSION    STATUS
-------------------- ---------------------------------------- ---------- ---------------------------------
CATALOG              Oracle Database Catalog Views            11.2.0.3.0 VALID
CATPROC              Oracle Database Packages and Types       11.2.0.3.0 VALID

SQL> PURGE DBA_RECYCLEBIN ;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL>
SQL> set feedback on
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE';

SQL> SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE' ;


SQL> col name for a20
SQL> col TIME for a30
SQL> col RESTORE_POINT_TIME for a30
SQL> select * from v$restore_point;
SQL>  select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUARANTEE STORAGE_SIZE TIME                           RESTORE_POINT_TIME             PRESERVED NAME
---------- --------------------- --------- ------------ ------------------------------ ------------------------------ --------- --------------------
 709791434                     2 YES          314572800 01-NOV-15 05.50.43.000000000 A                                YES       B4_DB_UPGRADE

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        14

Step#4:
********
Shutdown database and setting new environment variable (11.2.0.4) home


SQL> select name from v$database;

NAME
---------------------------
HCDMP

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !echo $ORACLE_SID
HCDMP

SQL> echo $ORACLE_HOME
SQL> !echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.3/dbhome_1

cp /u01/app/oracle/product/11.2.0.3/dbhome_1/initorcl.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/initorcl.ora

[oracle@s616183se2vl25 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
[oracle@s616183se2vl25 ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@s616183se2vl25 ~]$ export ORACLE_SID=orcl

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2259840 bytes
Variable Size             683672704 bytes
Database Buffers          348127232 bytes
Redo Buffers                9826304 bytes
Database mounted.
Database opened.


SQL> @?/rdbms/admin/catupgrd


Step#5:
*******
Startup the upgraded database and follow post upgrade task.

[oracle@s616183se2vl25 ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 1 06:15:58 2015

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2259840 bytes
Variable Size             683672704 bytes
Database Buffers          348127232 bytes
Redo Buffers                9826304 bytes
Database mounted.
Database opened.
SQL>  @?/rdbms/admin/utlu112s

Step#6:
*******
For migrating the baseline from pre 11g database, run the below one if required.

SQL>  @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catuppst.sql


Step#7:
*******
Recompile objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Step#8:
*******

For object invalidation in sys schema before upgrade the run below one.
Also run catbundle

SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql

SQL> @?/rdbms/admin/catbundle.sql psu apply

Step#9:
*******
Verify the details

SQL> set lines 1000
SQL> col action_name for a30
SQL> col ACTION_TIME for a30
SQL> col version for a30
SQL>  col NAMESPACE for a10
SQL> col comments for a30
SQL> col BUNDLE_SERIES for a30
SQL> col action for a20
SQL> select * from registry$history;

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


SQL> set serverout on
SQL> select object_name from dba_objects where status != 'VALID';

no rows selected

Resolving Archive gap Standby Database Using Incremental RMAN(using SCN)

Resolving Archive gap Standby Database Using Incremental RMAN(using SCN)

If you find huge gap between standby and primary, and your primary site does not have archives, your backup script deleted the archives.

In this scenario we can use SCN based incremental backup to sync the standby.

Standby site

Cancel the managed recovery operation on the standby site

SQL> alter database recover managed standby database cancel;

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

     507189

SQL> shutdown immediate

Primary site

Switch to the primary site and change the log_archive_dest_2 initialization parameter and take an incremental backup of the database using the scn value that has been taken from the standby database:

Sql>alter system set LOG_ARCHIVE_DEST_STATE_2=defer;

System altered

RMAN> backup incremental from scn 507189 database format='/u02/rman_backup/incremental/incr_backup_%U';

Create the standby controlfile and switch the current redo log file:

SQL>alter database create standby controlfile as '/u01/rman_backup/standby_control.ctl';

SQL> alter system switch logfile;

System altered.

Copy the standby controlfile and incremental backup file to the standby side, start up the standby database in nomount modeand change the parameter file to make the instance use the standby controlfile:

Standby site

Assume we have copied to /u01/rman_backup/ Location

SQL> alter system set control_files='/u01/rman_backup/standby_control.ctl' scope=spfile;

System altered.

Alternate to above step, we can replace the existing controlfiles with the standby_control.ctl by renaming it to corresponding name.

SQL> shutdown immediate

SQL> startup nomount

Mount the standby database and catalog the incremental backup to its repository:

SQL> alter database mount standby database;

RMAN> catalog backuppiece'/u01/rman_backup/incr_backup_1mlfj8pq_1_1';

Now, recover the database using incremental backup:

RMAN> recover database;

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: reading from backup piece /u02/rman_backup/

incr_backup_1mlfj8pq_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/u02/rman_backup/incr_backup_1mlfj8pq_1_1

tag=TAG20100606T224202

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery

archive log thread 1 sequence 58 is already on disk as file

/u01/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/

2010_06_07/o1_mf_1_58_60s4sjbc_.arc

archive log

filename=/u01/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/

rchivelog/2010_06_07/o1_mf_1_58_60s4sjbc_.arc

thread=1 sequence=58

unable to find archive log

archive log thread=1 sequence=59

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 06/07/2010 02:38:36

RMAN-06054: media recovery requesting unknown log: thread 1 seq 59 lowscn 508306

RMAN> exit

Now switch to the standby database and run the recover standby database command.  RMAN will look for the next archived redo log file and apply it automatically:

SQL> recover standby database;

Then query the current scn value on the standby database

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

     511301

Now start redo apply process on standby

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

virtual disk sharing with multiple VM images

Ex:


disk.locking = "FALSE"
diskLib.dataCacheMaxSize = "0"
scsi1.present = "TRUE"
scsi1.virtualDev = "lsilogic"
scsi1.sharedbus = "none"
scsi1:0.present = "TRUE"
scsi1:0.fileName = " D:\Virtual Machines\Shared Disk\SHARED-DISK.vmdk "
scsi1:0.mode = "independent-persistent"
scsi1:0.shared = "TRUE"
scsi0:1.sharing = "multi-writer"
scsi1:0.redo = ""


For RAC follow this link

conver DR into realtime apply

Run in standby database side:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

set lines 200

col MEMBER for a40

set pages 100

select TYPE,MEMBER from v$logfile;

##if there is no standby redologs create by  using follown sample command:

alter database add standby logfile ('/u02/oradata/FSDR/standby_redo4a.log','/u10/oradata/FSDR/standby_redo4b.log') size 300M;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect from session;

Checking status:

set lines 200

select instance_name,host_name from gv$instance;

select process, client_process, sequence#, status from gv$managed_standby;

col DEST_NAME for a10

col DEST_NAME for a20

select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

col name for a13

col value for a13

col unit for a30

set lines 132

select name, value, unit, time_computed from gv$dataguard_stats where name in ('transport lag', 'apply lag');

Database rename

[oracle@Tstdbserver ~]$ ps -ef|grep -i tns

root        37     2  0 Mar16 ?        00:00:00 [netns]

oracle   15132  7994  0 07:28 pts/1    00:00:00 grep -i tns

oracle   17474     1  0 Mar29 ?        00:03:12 /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit

[oracle@Tstdbserver ~]$ ps -ef|grep -i pmon

oracle    8227     1  0 07:15 ?        00:00:00 ora_pmon_ora11g

oracle   15134  7994  0 07:28 pts/1    00:00:00 grep -i pmon

[oracle@Tstdbserver ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 5 07:29:02 2016

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 backup controlfile to trace as '/tmp/ORACL11g';

Database altered.

SQL> select name from v$database;

NAME

---------

ORA11G

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ora11g/system01.dbf

/u01/app/oracle/oradata/ora11g/sysaux01.dbf

/u01/app/oracle/oradata/ora11g/undotbs01.dbf

/u01/app/oracle/oradata/ora11g/users01.dbf

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ora11g/control01.ctl

/u01/app/oracle/fast_recovery_area/ora11g/control02.ctl

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ora11g/redo03.log

/u01/app/oracle/oradata/ora11g/redo02.log

/u01/app/oracle/oradata/ora11g/redo01.log

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@Tstdbserver ~]$ cd $ORACLE_HOME/dbs

[oracle@Tstdbserver dbs]$ ls -lrt

total 32

-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

-rw-r----- 1 oracle oinstall   24 Apr 26 07:43 lkSRC

-rw-r----- 1 oracle oinstall 1536 Apr 26 07:43 orapwSRC

-rw-r----- 1 oracle oinstall 3584 Jul  3 14:05 spfileSRC.ora_BKP

-rw-r--r-- 1 oracle oinstall 1024 Jul  5 07:12 initora11g.ora

-rw-rw---- 1 oracle oinstall 1544 Jul  5 07:13 hc_SRC.dat

-rw-r----- 1 oracle oinstall   24 Jul  5 07:17 lkORA11G

-rw-rw---- 1 oracle oinstall 1544 Jul  5 07:21 hc_ora11g.dat

[oracle@Tstdbserver dbs]$ vi initora11g.ora

[oracle@Tstdbserver dbs]$ cat initora11g.ora

ORCL11g.__db_cache_size=2902458368

ORCL11g.__java_pool_size=16777216

ORCL11g.__large_pool_size=33554432

ORCL11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

ORCL11g.__pga_aggregate_target=2399141888

ORCL11g.__sga_target=4294967296

ORCL11g.__shared_io_pool_size=0

ORCL11g.__shared_pool_size=1308622848

ORCL11g.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/ORCL11g/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/ORCL11g/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL11g/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ORCL11g'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL11gXDB)'

*.enable_goldengate_replication=TRUE

*.open_cursors=300

*.pga_aggregate_target=2391801856

*.processes=150

*.recyclebin='OFF'

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=4293918720

*.undo_tablespace='UNDOTBS1'

[oracle@Tstdbserver dbs]$ mkdir -p /u01/app/oracle/admin/ORCL11g/adump

[oracle@Tstdbserver dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCL11g

[oracle@Tstdbserver dbs]$ ls -lrt

total 32

-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

-rw-r----- 1 oracle oinstall   24 Apr 26 07:43 lkSRC

-rw-r----- 1 oracle oinstall 1536 Apr 26 07:43 orapwSRC

-rw-r----- 1 oracle oinstall 3584 Jul  3 14:05 spfileSRC.ora_BKP

-rw-rw---- 1 oracle oinstall 1544 Jul  5 07:13 hc_SRC.dat

-rw-r----- 1 oracle oinstall   24 Jul  5 07:17 lkORA11G

-rw-rw---- 1 oracle oinstall 1544 Jul  5 07:21 hc_ora11g.dat

-rw-r--r-- 1 oracle oinstall 1038 Jul  5 07:32 initora11g.ora

[oracle@Tstdbserver dbs]$ cp initora11g.ora initORCL11g.ora

[oracle@Tstdbserver dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 5 07:34:40 2016

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> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@Tstdbserver dbs]$ cd /tmp

[oracle@Tstdbserver tmp]$ ls -lrt

total 20

-rw-r--r-- 1 oracle oinstall  660 Jul  5 07:16 ora11g.sql

drwxr-xr-x 2 oracle oinstall 4096 Jul  5 07:24 OraInstall2016-07-05_07-24-23-AM

drwxr-xr-x 2 oracle oinstall 4096 Jul  5 07:24 hsperfdata_oracle

-rw-r--r-- 1 oracle oinstall 5235 Jul  5 07:29 ORACL11g

[oracle@Tstdbserver tmp]$ vi ORACL11g

[oracle@Tstdbserver tmp]$ cat ORACL11g

CREATE CONTROLFILE SET DATABASE "ORCL11G" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/ORCL11g/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/ORCL11g/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/ORCL11g/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

  '/u01/app/oracle/oradata/ORCL11g/system01.dbf',

  '/u01/app/oracle/oradata/ORCL11g/sysaux01.dbf',

  '/u01/app/oracle/oradata/ORCL11g/undotbs01.dbf',

  '/u01/app/oracle/oradata/ORCL11g/users01.dbf'

CHARACTER SET WE8MSWIN1252

;

[oracle@Tstdbserver tmp]$ vi /etc/oratab

[oracle@Tstdbserver tmp]$ . oraenv

ORACLE_SID = [ora11g] ? ORCL11g

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@Tstdbserver tmp]$ cd /u01/app/oracle/oradata/

[oracle@Tstdbserver oradata]$ ls -lrt

total 8

drwxr-x--- 2 oracle oinstall 4096 Mar 25 09:19 SRC

drwxr-xr-x 2 oracle oinstall 4096 Jul  5 07:20 ora11g

[oracle@Tstdbserver oradata]$ mv ora11g ORCL11g

[oracle@Tstdbserver oradata]$ ls -lrt

total 8

drwxr-x--- 2 oracle oinstall 4096 Mar 25 09:19 SRC

drwxr-xr-x 2 oracle oinstall 4096 Jul  5 07:20 ORCL11g

[oracle@Tstdbserver oradata]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 5 07:41:56 2016

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> @/tmp/ORACL11g

Control file created.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;

Database altered.

SQL> select name from v$database;

NAME

---------

ORCL11G

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ORCL11g/system01.dbf

/u01/app/oracle/oradata/ORCL11g/sysaux01.dbf

/u01/app/oracle/oradata/ORCL11g/undotbs01.dbf

/u01/app/oracle/oradata/ORCL11g/users01.dbf

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ORCL11g/control01.ctl

/u01/app/oracle/fast_recovery_area/ORCL11g/control02.ctl

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ORCL11g/redo03.log

/u01/app/oracle/oradata/ORCL11g/redo02.log

/u01/app/oracle/oradata/ORCL11g/redo01.log

SQL> Database rename session completed!!!

SP2-0734: unknown command beginning "Database r..." - rest of line ignored.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@Tstdbserver oradata]$ ps -ef|grep -i pmon

oracle   15184     1  0 07:39 ?        00:00:00 ora_pmon_ORCL11g

oracle   15275  7994  0 07:44 pts/1    00:00:00 grep -i pmon

tuning advisor queries

select client_name,status from dba_autotask_task;

select client_name,status from dba_autotask_client;

select client_name, operation_name, status from dba_autotask_operation;

tablespaces

Tablespaces:

A database is divided into one or more logical storage units called tablespaces. Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data, Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures.

 Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.

Add More Space for a Database

The size of a tablespace is the total size of the all datafiles that corresponds to the tablespace. The size of a database is the collective size of the tablespaces that constitute the database.

You can enlarge a database in three ways:

·       Add a datafile to a tablespace

·       Add a new tablespace

·       Increase the size of a datafile

When you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace.

Type of Tablespaces:

SYSTEM Tablespace: Every Oracle database contains a tablespace named SYSTEM, which Oracle Database creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

SYSAUX tablespace: The SYSAUX tablespace is an auxiliary tablespace to system tablespace The largest portion of the SYSAUX tablespace is occupied by the Automatic Workload Repository (AWR).

UNDO tablespace: Undo tablespaces are special tablespaces used for storing undo information. When the first DML operation is run within a transaction, the transaction is bound assigned to an undo segment in the current undo tablespace.

An undo tablespace cannot be dropped if it is being used by any instance.

USER tablespace: it is default tablespace for users.

TEMP tablespace: Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation.

More tablespaces:

A very small database may need only the SYSTEM tablespace; however, Oracle recommends that you create at least one additional tablespace to store user data separate from data dictionary information. This gives you more flexibility in various database administration operations and reduces contention among dictionary objects and schema objects for the same datafiles.

A database administrator can perform the following actions:

·       Create new tablespaces

·       Add datafiles to tablespaces

·       Set and alter default segment storage settings for segments created in a tablespace

·       Make a tablespace read only or read/write

·       Make a tablespace temporary or permanent

·       Rename tablespaces

·       Drop tablespaces

·       Transport tablespaces across databases and platforms

VIEWS related to tablespaces and datafiles

è dba_tablespaces, user_tablespaces

è dba_data_files, dba_temp_files

è v$tablespace

è dba_segments, user_segments

è  dba_free_space, user_free_space

Creating tablespace:

SQL> create tablespace test1 datafile '/u01/app/oracle/oradata/test001.dbf' size 50M;

Creating tablespace with auto extended on.

Sql>CREATE TABLESPACE ts_something DATAFILE '/u01/app/oracle/ts_sth.dbf' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M;

To check the tablespace created.

SQL> set lines 300

SQL> col FILE_NAME for a50

SQL> set pages 100

SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;

To check the max size of datafiles;

SQL>  select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024 from dba_data_files;

Adding datafiles to tablespaces:

SQL> alter tablespace test1 add datafile '/u01/app/oracle/oradata/test002.dbf' size 50M;

Resizing datafiles

SQL> alter database datafile '/u01/app/oracle/oradata/test001.dbf' resize 100M;

To alter maxsize

SQL> alter database datafile '/u01/app/oracle/oradata/test003.dbf' autoextend on maxsize 6G;

Creating undo tablespace:

Sql>CREATE UNDO TABLESPACE undots1DATAFILE '/u01/app/oracle/oradata/undotbs_1a.dbf'SIZE 10M AUTOEXTEND ON;

Creating Temporary tablespace:

Sql>CREATE TEMPORARY TABLESPACE temp_mtr TEMPFILE '/dbf1/mtr_temp01.dbf' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M;

è Note, a temporary tablespace has tempfiles, not datafiles.

Droping datafiles:

Sql>alter tablespace<ts name> drop datafile ‘<filename>’;

Droping tablespaces;

Sql>DROP TABLESPACE <TSname>;

Including datafiles in OS level

Sql>DROP TABLESPACE <TSname> INCLUDING CONTENTS AND DATAFILES;

Move or Rename a datafile do the following.

->   offline datafile.

->   Rename the physical file on the OS.

->   Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.

->  recover datafile.
-> put the datafile online.

Sql>alter datafile '/u01/app/oracle/oradata/orcl/users01.dbf' offline;

Sql>exit

$mv  /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/users02.dbf

$sqlplus / as sysdba

Sql>Alter database rename file ‘u01/app/oracle/oradata/orcl/users01.dbf’ to              ‘/u01/app/oracle/oradata/users02.dbf’

Sql>recover datafile '/u01/app/oracle/oradata/orcl/users01.dbf';

Sql>alter datafile '/u01/app/oracle/oradata/orcl/users01.dbf' online;

Extent management:

 Whenever a segment will be full and needs extra space then oracle automatically allocates space in the forms of extents(collection of continuous blocks) .How this extent will be allocated and What will be size of this newly allocated extent is decided by extent management according to storage parameters. You can define storage parameters (initial,next,minextents,maxextents,pctincrease...) in case of dictionary managed tablespace(DMTS) but not for locally managed tablespaces(LMTS). In LMTS we have options of AUTOALLOCATE  and UNIFORM.

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

Dictionary Managed Tablespaces (DMT):

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.

SQL> CREATE TABLESPACE ts1 DATAFILE '/oradata/ts1_01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Locally Managed Tablespaces (LMT):

Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files.

To know extent management of tablespaces; from 10g onwards by default it will create LMT.

NOTE: we cannot create dictionary tablespace if system tablespace is locally managed tablespace.

What Is Undo?

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

·       Roll back transactions when a ROLLBACK statement is issued

·       Recover the database

·       Provide read consistency

·       Analyze data as of an earlier point in time by using Oracle Flashback Query

·       Recover from logical corruptions using Oracle Flashback features

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space. In this management mode, you create an undo tablespace, and the server automatically manages undo segments and space among the various active sessions.

You set the UNDO_MANAGEMENT initialization parameter to AUTO to enable automatic undo management.

UNDO tablespace management:

We assign an undo tablespace to an instance in one of two ways:

·       At instance startup. You can specify the undo tablespace in the initialization file or let the system choose an available undo tablespace.

·       While the instance is running. Use ALTER SYSTEM SET UNDO_TABLESPACE to replace the active undo tablespace with another undo tablespace.

Undo retention:

     After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

To set the undo retention period:

Set UNDO_RETENTION in the initialization parameter file (value in seconds).

UNDO_RETENTION = 1800

Change UNDO_RETENTION at any time using the ALTER SYSTEM statement:

ALTER SYSTEM SET UNDO_RETENTION = 2400;

Retention Guarantee

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.

Sql> ALTER TABLESPACE <undo tbs> retention guarantee.

Sql> ALTER TABLESPACE <undo tbs> retention noguarantee.

You can determine the current retention period by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT view. This view contains one row for each 10-minute statistics collection interval over the last 4 days. (Beyond 4 days, the data is available in the DBA_HIST_UNDOSTAT view.)TUNED_UNDORETENTION is given in seconds.

Sql>select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention from v$undostat order by end_time;

BEGIN_TIME      END_TIME        TUNED_UNDORETENTION

--------------- --------------- -------------------

04-FEB-05 00:01 04-FEB-05 00:11               12100

      ...                                         

07-FEB-05 23:21 07-FEB-05 23:31               86700

07-FEB-05 23:31 07-FEB-05 23:41               86700

07-FEB-05 23:41 07-FEB-05 23:51               86700

07-FEB-05 23:51 07-FEB-05 23:52               86700

You can decide how many seconds based on the TUNED_UNDORETENTION.