RMAN Cloning - only 4 steps


Database name: cts
Clone database name: clone

1. connect to cts database and take RMAN full backup(database must be open).

. oraenv
cts
rman
rman>connect target /

rman>backup database plus archivelog;

exit

2. Create directory structure for clone database.

2.1 cd /u01/app/oracle/admin
   
mkdir clone
cd clone

mkdir adump bdump cdump udump pfile


2.2.cd /u01/app/oracle/oradata

mkdir clone

2.3. pfile creation steps

 cd /u01/app/oracle/admin

ls -lrt

cd cts/pfile

ls -lrt

Note: if init.ora file name is different just rename the file(MV oldname init.ora)

cp init.ora /u01/app/oracle/admin/clone/pfile

cd /u01/app/oracle/admin/clone/pfile

ls -lrt

vi init.ora

press "i" to edit

change database name,audit/background/core/user dump locations, controlfile locations and dispatchers name

add below parameters:
=====================

DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cts/','/u01/app/oracle/oradata/clone/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cts/','/u01/app/oracle/oradata/clone/'


esc+shift+:+wq  --> to save
or esc+shft+:%s/cts/clone


2.4. password file creation steps

cd /u01/app/oracle/product/10.2.0/db_1/dbs

pwd

orapwd file='/u01/app/oracle/product/10.2.0/db_1/dbs/orapwclone' password=sys entries=5

ls -lrt

Note: Make sure that CTS database also have password file.


2.5. edit the oratab entry

 vi /etc/oratab

Press "i" to edit

clone:/u01/app/oracle/product/10.2.0/db_1:N

esc+shift+:+wq  --> to save


2.6. . oraenv  --> Set the oracle environment

? clone

  (or)

export ORACLE_SID=clone
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

echo $ORACLE_SID   --> just to check the database name
echo $ORACLE_HOME  --> just to check the oracle home path

sqlplus "/as sysdba"

2.7.SQL> startup nomount pfile='/u01/app/oracle/admin/clone/pfile/init.ora';

SQL>create spfile from pfile='/u01/app/oracle/admin/clone/pfile/init.ora';

SQL>shut immediate

SQL>startup nomount

3. start listener and database entires

to check listnere status

ps -ef|grep tns

lsnrctl status LISTENER

if it is down start the listener

lsnrctl start 

to check TNS entries

tnsping cts
tnsping clone

if not pingable

cd /u01/app/oracle/product/10.2.0/db_1/network/admin/

ls -lrt
vi tnsnames.ora

put the entry for database cts and clone


4. create clone database.

. oraenv
clone

rman
rman>connet target sys/sys@cts
rman>connect auxiliary /
rman>duplicate target database to clone;

*********** end ***********

RMAN (Recovery Manager) by Raffeek

 ===============================
            ORACLE DATABASE BACKUPS
        ===============================
        *********** RAFFEEK ***********
        ===============================
     

1. logfileical backup (exp/imp --> 9i , expdp/impdp --> 10g)
2. Physical backup

2.A) User Managed backup( Cold and Hot backup)
2.B) Server Manager backup( RMAN )

2.B) RMAN - Recovery manager (RMAN) is a command line utility used to perform backup and recovery. RMAN provide greater ease of

management and administration of the backup and recovery operations.


* V$RMAN_OUTPUT - This is an in-memory view of the messages reported by RMAN holding a maximum of 32767 rows. Since this information

is not recorded in the controlfile it is lost on instance restart.
* V$RMAN_STATUS - This view displays progress and status information for in-progress and complete RMAN jobs. The information for the

in-progress jobs is memory only, while the complete job information comes from the controlfile.
* V$BACKUP_FILES - This view display information about RMAN image copies, backupsets and archived logs, similar to the information

listed by the RMAN commands LIST BACKUP and LIST COPY. This view relies on the DBMS_RCVMAN.SETDATABASE procedure being run to set the

database.


Note: Database must be open.

RMAN will take online Archive log as one Backupset(BS)

Then it will take actuall datafiles

Then it will take controlfile and spfile

Then it will take actual archive log files


* Set oracle environment

. oraenv
<cts>

rman

RMAN>connect target /

RMAN> show all   --> it will display the default parameter files

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default 
--> only(one) last backup will be valid since redundance is 1 remaining backup will be obsolete.

CONFIGURE BACKUP OPTIMIZATION OFF; # default
--> if its ON, datafile will not be backedup if there is no changes in datafile blocks.

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default 
--> Bakup will go to disk( we can mention TAPE also)

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default 
--> If its ON, Controlfile will be backedup even if we take one datafile as well.

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default  -->controlfile backup will go to disk.

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
-->we can increase the parallelsim but we shold have more CPUs in our server.

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
-->only one copy of datafiles backup will happen if we take database backup .

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
-->only one copy of archive files backup will happen if we take archivefiles.

CONFIGURE MAXSETSIZE TO UNLIMITED; # default 
--> we can define the size of backup piece.

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default 
--> We can delete the archivefiles using delete input or sysdate-1 or none

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_citi.f'; # default




> How to take full backup?


RMAN>BACKUP DATABASE PLUS ARCHIVELOG; 

*it will take four backup sets (current archivelog file, Datafiles, controlfile and spfile, actual archivelog files) each backup set

contains one or more backup  pieces.

> How to take incremental backup?

RMAN>BACKUP INCREMENTAL LEVEL 1 DATABASE;


> How to check the backup details?

RMAN>list backup;

RMAN>list backup summary;

>How to valiate backup?

RMAN>validate backupset 2;
    (or)
RMAN>validate backupset 2,3,4;


> How to check backup of datafiles?

RMAN>list backup of database;

> How to check backup of archivelog?

RMAN> list backup of archivelog all;

>How to check backup of controlfiles?

RMAN> list backup of controlfile;


>What is mean by obsolete and expiry?

* Obsolete -> Invalid backup
* Expairy -> Backup not found in os level(physically deleted)

RMAN>report obsolete;
RMAN>list expired backup;

>Types of RMAN backup?

* L0 bakup or Full backup
* L1 bckup or Incremental bckup ( it will take only changed block after last backup)

>Can we give the database commands in RMAN prompt?

sample commands:

RMAN>startup
RMAN>startup mount
RMAN>shutdown immediate
RMAN>alter database open;

>What is cross check?

 If any arhivelog file is physially deleted we can't take backup of archivelogs, so we can give the below command then we can take the

backup.

RMAN>crosscheck archivelog all;

>Full database backup command?

Example for DISK BACKUP:
=======================

. oraenv
<DB NAME>

rman target /
configure default device type to disk;
configure controlfile autobackup on;
configure channel device type disk format '<Disk_Location>/df_%t_%s_%p';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '<Disk_Location>/snapcf_oid.f';
run
{
backup current controlfile;
backup database plus archivelog;
}


Example for TAPE BACKUP:
========================
Before taking backup to tape we need to configure Tivoli client(TSM) or some other media library softwares to connect with TAPE.

. oraenv
<DB NAME>
rman
RMAN>connect target;

run {
      allocate channel t1 type 'sbt_tape'
      parms 'ENV=(TDPO_OPTFILE=/u001/app/TDP/dbname/tdpo.opt)'
      format '%d_inc0_%U.rbck';
      backup incremental level 0 database tag OPEN_INC0_DAY;
      release channel t1;
}

sql 'alter system archive log current';
change archivelog all crosscheck;

run {
      allocate channel a1 type 'sbt_tape'
      parms 'ENV=(TDPO_OPTFILE=/u001/app/TDP/dbname/tdpo.opt)'
      format '%d_arch_%U.rbck';
      backup archivelog all not backed up 3 times tag ARCHIVE_Backup ;
      delete archivelog until time = 'sysdate-7';
      release channel a1;
    }

run {
      allocate channel c1 type 'sbt_tape'
      parms 'ENV=(TDPO_OPTFILE=/u001/app/TDP/dbname/tdpo.opt)'
      format '%d_controlfile_%U.rbck';
      backup  current controlfile tag CONTROLFILE_DAY;
      release channel c1;
    }

exit

Oracle Datapump ( expdp ) / Import ( impdp ) With Full DB and Schema Refresh Steps

 ORACLE DATABASE BACKUPS:

      Backup and recovery is one of the most important aspects of a DBA's job. If you lose your company's data, you could very well lose your job. Hardware and software can always be replaced, but your data may be irreplaceable!

1. Logical backup (exp/imp --> 9i , expdp/impdp --> 10g)
2. Physical backup

2.A) User Managed backup( Cold and Hot backup)
2.B) Server Manager backup( RMAN )

1. Logical backup (Datapump expdp/impdpdp)
We can take expdport(expdp) in four levels.

1A. Full database Level expdp/impdp
1B. Schema(User) Level expdp/impdp
1C. Table Level expdp/impdp
1D. Tablespace Level expdp/impdp

Note: Do the expdport in Source(ctsp) database and impdport in target(ctsd) database.

      Database must be in open and create the directory in both source and target database.

Sourec Database:

SQL> create directory expdp_dir1 as '/u01/app/oracle/';
SQL> grant read,write on directory expdp_dir1 to system;

SQL> select *from dba_directories;

Target Database:

SQL> create directory expdp_dir1 as '/u01/app/oracle/';

SQL> grant read,write on directory expdp_dir1 to system;

SQL> select *from dba_directories;


1A. Full database Level expdp/impdp
=====================================

expdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull.log full=y

impdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull_impdp.log full=y


1B. Schema(User) Level expdp/impdp
====================================
expdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser.log schemas=raja

impdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser_impdp.log remap_schema=raja:raja


Note: RAJA is a user in the database

1C. Table Level expdp/impdp
=============================
expdp system/manager directory=expdp_dir1 dumpfile=ctstab.dmp logfile=ctstab.log tables=raja.emp


impdp system/manager directory=expdp_dir1 dumpfile=ctstab.dmp logfile=ctstab_impdp.log remap_schema=raja:raja tables=emp


DB Refresh steps:
=================

1. take export(expdp) of source database(ctsp).

expdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull.log full=y


2. move the dumpfile to target database(ctsd) using scp.. if both the database running in same server this steps is not required.

scp ctsfull.dmp oracle@<Target server IP>:/u02/app/oracle


3. Drop the application users and dont drop database default users like sys,system,dbsnmb,xdb.

If you give below command it will show username,created date. Mostly database default users will be created in same day.

select username,created from dba_users;

drop user raja cascade;


4. Befor doing import(impdp) check the used space of source database and freespace in the target database. tablespaces names should

same between sourec and target database then Target database each tablespace size should be more than or equal to source database tablespace.

Source:

select tablespace_name,sum(bytes/1024/1024) from dba_segments group by tablespace_name;

Target:

select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;

5. Start the impdport in taget database.


impdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull_impdp.log full=y


6. once competed compare the object counts between source and target databases.

select object_type,count(*) from dba_objects where status='VALID' group by object_type;

=============================== end ===========================



Schema Refersh steps:
=====================

1. take expdport of a schema in source database(ctsp).

expdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser.log schemas=raja

Note: Dont give semicolon(;) at the end of the above command.

2. move the dumpfile to target database(ctsd) using scp.if both the database running in same server this steps is not required.

scp ctsuser.dmp oracle@<Target server IP>:/u02/app/oracle


3. create the new user in target database(if already existed drop and recreate)

select username from dba_users;

drop user raja cascade;

create user raja identified by raja;


4. Befor doing import(impdp) check the used space of a schema(user) in source database and freespace in the target database then

target database tablespaces should be more than or equal to source database tablespaces.

Source:

select tablespace_name,sum(bytes/1024/1024) from dba_segments where owner='RAJA' group by tablespace_name;

Target:

select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;


5. Start the import(impdp) in taget database.


impdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser_impdp.log remap_schema=raja:raja


6. once completed compare the object counts between source and target databases.

select object_type,count(*) from dba_objects where owner='RAJA' and status='VALID' group by object_type;

Oracle Export ( exp ) / Import ( imp ) With Full DB and Schema Refresh Steps

    
ORACLE DATABASE BACKUPS:

Backup and recovery is one of the most important aspects of a DBA's job. If you lose your company's data, you could very well lose your job. Hardware and software can always be replaced, but your data may be irreplaceable!


Types of Backup:

1. Logical backup (exp/imp --> 9i , expdp/impdp --> 10g)
2. Physical backup

2.A) User Managed backup( Cold and Hot backup)
2.B) Server Manager backup( RMAN - Recovery Manager )


1. Logical backup (exp/imp)
====================

We can take export(exp) in four levels.

1A. Full database Level export/import
1B. Schema(User) Level export/import
1C. Table Level export/import
1D. Tablespace Level export/import


Note: Do the export in Source(ctsp) database and import in target(ctsd) database.

      Database must be in open.


1A. Full database Level export/import
=====================================

exp system/manager file='/u01/app/oracle/ctsfull.dmp' log='/u01/app/oracle/ctsfull.log' full=y

imp system/manager file='/u01/app/oracle/ctsfull.dmp' log='/u01/app/oracle/ctsfull_imp.log' full=y



1B. Schema(User) Level export/import
====================================
exp system/manager file='/u01/app/oracle/ctsuser.dmp' log='/u01/app/oracle/ctsuser.log' owner=raja

imp system/manager file='/u01/app/oracle/ctsuser.dmp' log='/u01/app/oracle/ctsuser_imp.log' fromuser=raja touser=raja

Note: RAJA is a user in the database


1C. Table Level export/import
=============================


exp system/manager file='/u01/app/oracle/ctstab.dmp' log='/u01/app/oracle/ctstab.log' tables=raja.emp


imp system/manager file='/u01/app/oracle/ctstab.dmp' log='/u01/app/oracle/ctstab_imp.log' fromuser=raja touser=raja tables=emp


DB Refresh steps:
=================

1. take export of source database(ctsp).

exp system/manager file='/u01/app/oracle/ctsfull.dmp' log='/u01/app/oracle/ctsfull.log' full=y


2. move the dumpfile to target database(ctsd) using scp.. if both the database running in same server this steps is not required.

scp /u01/app/oracle/ctsfull.dmp oracle@<Target server IP>:/u02/app/oracle


3. Drop the application users and dont drop database default users like sys,system,dbsnmb,xdb.

If you give below command it will show username,created date. Mostly database default users will be created in same day.

select username,created from dba_users;

drop user raja cascade;


4. Befor doing import check the used space of source database and freespace in the target database. tablespaces names should same between sourec and target database then Target database each tablespace size should be more than or equal to source database tablespace.

Source:

select tablespace_name,sum(bytes/1024/1024) from dba_segments group by tablespace_name;

Target:

select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;

5. Start the import in taget database.


imp system/manager file='/u01/app/oracle/ctsfull.dmp' log='/u01/app/oracle/ctsfull_imp.log' full=y


6. once competed compare the object counts between source and target databases.

select object_type,count(*) from dba_objects where status='VALID' group by object_type;

=============================== end ===========================



Schema Refersh steps:
=====================

1. take export of a schema in source database(ctsp).

exp system/manager file='/u01/app/oracle/ctsuser.dmp' log='/u01/app/oracle/ctsuser.log' owner=raja

Note: Dont give semicolon(;) at the end of the above command.

2. move the dumpfile to target database(ctsd) using scp.if both the database running in same server this steps is not required.

scp /u01/app/oracle/ctsuser.dmp oracle@<Target server IP>:/u02/app/oracle


3. create the new user in target database(if already existed drop and recreate)

select username from dba_users;

drop user raja cascade;

create user raja identified by raja;


4. befor doing import check the used space of a schema(user) in source database and freespace in the target database then target

database tablespaces should be more than or equal to source database tablespaces.

Source:

select tablespace_name,sum(bytes/1024/1024) from dba_segments where owner='RAJA' group by tablespace_name;

Target:

select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;


5. Start the import in taget database.


imp system/manager file='/u01/app/oracle/ctsuser.dmp' log='/u01/app/oracle/ctsuser_imp.log' fromuser=raja touser=raja


6. once competed compare the object counts between source and target databases.

select object_type,count(*) from dba_objects where owner='RAJA' and status='VALID' group by object_type;

Managing Redo Log Files

 Managing REDO Log Files:
    
     Redo log files record all changes made to data and provide a recovery mechanism from a system or media failure.

• Redo log files are organized into groups.
• An Oracle database requires at least two groups.
• Each redo log within a group is called a member.

How Redo Logs Work
==================

• Redo logs are used in a cyclic fashion.
• When a redo log file is full, LGWR will move to the next log group.
– This is called a log switch
– Checkpoint operation also occurs
– Information is written to the control file


How to add Redo log group:
=====================

SQL> select *from v$log;

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 '/u01/app/oracle/oradata/cts/log3a.rdo' SIZE 4M;


How to add a Redo log member into the redo log group:
=========================================

SQL>select *from v$logfile;

ALTER DATABASE ADD LOGFILE MEMBER
'/u01/app/oracle/oradata/cts/log1b.rdo' to group 1,   
'/u01/app/oracle/oradata/cts/log2b.rdo' to group 2,
'/u01/app/oracle/oradata/cts/log3b.rdo' to group 3;

How to drop a redo log group:
=======================

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;


How to drop a redo log group member:
============================

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/cts/log1b.rdo';


How to switche logfile and checkpoint force:

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> ALTER SYSTEM CHECKPOINT;

       
Note :
=====

* We can't drop the CURRENT and ACTIVE redo log group as well as redo log member.
* We can't Resize the redolog group. If you want to increase the size of the redolog group then add new redolog group with new size and delete old redolog group.
* We can have different size for all redolog groups but have same size for all redolog groups.

Oracle Controlfile Multiplexing

CONTROLFILE MULTIPLXEING
      
 A control file is a small binary file that is part of an Oracle database. The control file is used to keep track of the database's status and physical structure.

We can do controlfile multiplexing in 2 ways

1. Using pfile
2. Using Spfile

1. Using pfile:
===============

1. Shut down the database.

   SQL> shut immediate

2. go to the pfile location and edit the pfile.

   cd /u01/app/oracle/admin/cts/pfile

   vi init.ora

   press "i" to eit
  

control_files='/u01/app/oracle/oradata/cts/controlfile01.ctl','/u01

/app/oracle/oradata/cts/controlfile02.ctl','/u01/app/oracle/oradata

/cts/controlfile03.ctl','/u01/app/oracle/oradata/cts/controlfile04.ctl'

Press ESC+SHIFT + : + wq  --> to save

3. copy the controlfile01.ctl and change the as controlfile04.ctl

  cd /u01/app/oracle/oradata/cts/

  cp controlfile01.ctl controlfile04.ctl

4. start the database using PFILE and check the controfiles

    SQL> startup nomount

pfile='/u01/app/oracle/admin/cts/pfile/init.ora'

    SQL> select *from v$controlfile;

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

2. Using Spfile:
============

1. check the controlfiles and modify the spfile

    SQL> select *from v$controfile

    SQL> alter system set

control_files='/u01/app/oracle/oradata/cts/controlfile01.ctl','/u01

/app/oracle/oradata/cts/controlfile02.ctl','/u01/app/oracle/oradata

/cts/controlfile03.ctl','/u01/app/oracle/oradata/cts/controlfile04.

ctl','/u01/app/oracle/oradata/cts/controlfile05.ctl' scope=spfile;

2. Shut down the database.

   SQL> shut immediate

3. copy the controlfile01.ctl and change as controlfile05.ctl

  cd /u01/app/oracle/oradata/cts/

  cp controlfile01.ctl controlfile05.ctl

4. start the database and check the conrofiles

    SQL> startup

    SQL> select *from v$controlfile;

Tablespaces and Datafiles Management

 TABLESPACES and DATAFILES MANAGEMENT:
 
   Tablespaces are the bridge between certain physical and logical components of the Oracle database. Tablespaces are where you store Oracle database objects such as tables, indexes and rollback segments.  You can think of a tablespace like a shared disk drive in Windows. You can store files on your shared drive, move files around and remove files. The same is true with tablespaces. A tablespace is made up of one or more database datafiles.     

Creating tablespace:
=====================
create tablespace APPSBI datafile '/u01/app/oracle/oradata/cts/appsbi01.dbf' size 200m;

Adding Datafile:
================
alter tablespace APPSBI add datafile '/u01/app/oracle/oradata/cts/appsbi02.dbf' size 200m;

Resizing Datafile:
==================
alter database datafile '/u01/app/oracle/oradata/cts/appsbi02.dbf' resize 300m;

To create UNDO tablepsace
=========================
create undo tablespace APP_UNDO datafile '/u01/app/oracle/oradata/cts/appundo01.dbf' size 200m;

To add UNDO datafile:
=====================
alter tablespace APP_UNDO add datafile '/u01/app/oracle/oradata/cts/appundo02.dbf' size 100m;

To Resize UNDO datafile:
========================
alter database datafile '/u01/app/oracle/oradata/cts/appundo02.dbf' resize 150m;

To Create TEMPORARY TABLESPACE:
===============================
create temporary tablespace app_temp tempfile '/u01/app/oracle/oradata/cts/apptemp01.dbf' size 100m;

select file_name,bytes/1024/1024 from dba_temp_files where tablespace_name='APP_TEMP';

To ADD Tempfile:
================
alter tablespace app_temp add tempfile '/u01/app/oracle/oradata/cts/apptemp02.dbf' size 200m;

To Resize Tempfile:
===================

alter database tempfile '/u01/app/oracle/oradata/cts/apptemp01.dbf' resize 150m;


To Check Free space of all the tablespaces:
================================

 select TABLESPACE_NAME,sum(bytes/1024/1024) from DBA_FREE_SPACE group by tablespace_name;

To check allocated Space of all the tablespaces:
====================================

 select TABLESPACE_NAME,sum(bytes/1024/1024) from DBA_DATA_FILES group by tablespace_name;

To Check used space of all the tablespaces:
================================
 select TABLESPACE_NAME,sum(bytes/1024/1024) from DBA_SEGMENTS group by tablespace_name;

To check all the datafiles with allocated size in particular tablespace:
==================================================
select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='APPSBI';

Tablespace related operations:
=============================
offline/online
datafile add/resize
read/write mode
create/drop tablespace
logging/nologging

Related views:
=============
dba_tablespaces
dba_data_files
dba_temp_files
dba_temp_free_space
v$tempfile
v$datafile

Note:

1. We can create a table with particular tablespace spacification

create table test(eno number) tablespace appsbi;

2. We can allocate the datafile to  particular table

alter table test allocate extent (size 10k datafile '/u01/app/oracle/oradata/algates/appsbi02.dbf');

ORACLE DATABASE MANNUAL CREATION STEPS - For Beginners! Easy 10 Steps!
================================================================
Database Name: cts                         ||
OS: LINUX/AIX                             ||         RAFFEEK :)
Oracle S/W : 10g                             ||
================================================================
1. cd /u01/app/oracle/admin

ls -lrt
mkdir cts
cd cts

mkdir adump bdump cdump udump pfile

2.cd /u01/app/oracle/oradata

mkdir cts

3. pfile creation steps

 cd /u01/app/oracle/admin

ls -lrt

cd <EXISTING DATABASE>/pfile

ls -lrt

cp init.ora /u01/app/oracle/admin/cts/pfile

cd /u01/app/oracle/admin/cts/pfile

ls -lrt

vi init.ora

press "i" to edit

change database name,audit/background/core/user dump locations, controlfile locations and dispatchers name

esc+shift+:+wq  --> to save


4. password file creation steps

cd /u01/app/oracle/product/10.2.0/db_1/dbs

pwd

orapwd file='/u01/app/oracle/product/10.2.0/db_1/dbs/orapwcts.ora' password=sys entries=5

ls -lrt

5. edit the oratab entry

 vi /etc/oratab

Press "i" to edit

cts:/u01/app/oracle/product/10.2.0/db_1:N

esc+shift+:+wq  --> to save


6. . oraenv  --> Set the oracle environment

? cts

  (or)

export ORACLE_SID=cts
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

echo $ORACLE_SID   --> just to check the database name
echo $ORACLE_HOME  --> just to check the oracle home path

sqlplus "/as sysdba"

7.SQL> startup nomount pfile='/u01/app/oracle/admin/cts/pfile/init.ora';


8. SQL> create database cts
datafile '/u01/app/oracle/oradata/cts/system01.dbf' size 250m
sysaux datafile '/u01/app/oracle/oradata/cts/sysaux01.dbf' size 300m
default temporary tablespace temp tempfile '/u01/app/oracle/oradata/cts/tmp01.dbf' size 40m
undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/cts/undo01.dbf' size 40m
logfile
 group 1 '/u01/app/oracle/oradata/cts/log1.dbf' size 40m,
 group 2 '/u01/app/oracle/oradata/cts/log2.dbf' size 40m
/

9. spfile creation

SQL> create spfile from pfile='/u01/app/oracle/admin/cts/pfile/init.ora';

SQL> select status from v$instance;


10.SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catproc.sql

SQL>conn system/manager

SQL> @?/sqlplus/admin/pupbld.sql

exit

ps -ef|grep pmon

Oracle Patching

Applying Patches to Oracle Database:

   Oracle continues to recommend that, even though other mitigation measures may be available, Critical Patch Updates be applied as soon as possible in order for organizations to retain their security in depth posture. 

Pre-check:

1. Check oracle database full backup.
2. take oracle home backup and inventory backup.
cd $ORACLE_HOME
tar -cvf /u01/oracle/home10204_bkp.tar .

3.chek vaild and invaild object counts.
select owner,object_name,object_type from dba_objects where status='INVALID'

Apply to 10204 home:
================

Shut down DBs and listener on this home
Check for zombie processes: ps -ef | grep ora

if any process is there kill that process.

export PATH=$PATH:/u01/app/oracle/product/10.2.0/db_1/OPatch
which opatch (verify 10.2.0.4)
echo $ORACLE_HOME (verify 10.2.0.4)

Apply Jul2011CPU:
cd /orasw/dba/patches/12419249

opatch napply -skip_subset -skip_duplicate

Hit enter when it asks for e-mail address

Enter "Y" I am ok being uninformed

Then "Y" for All

Once successfully completed. start the database.

Apply to Database:
===============

SQL>Startup

Now run CATBUNDLE for each DB:
#cd $ORACLE_HOME/rdbms/admin
startup
@?/rdbms/admin/catbundle.sql cpu apply
@?/rdbms/admin/utlrp.sql

Post-check:

select *from  registry$history;
check the vaild and invaild object  counts.

start the listener.
Done!

opatch lsinventory --> os level we can check what are the patchs are applied.

How to rollback:
================
opatch rollback -id <patchid>