ORACLE RMAN(RECOVERY MANAGER) COMPONENTS AND CONFIGURATIONS

ORACLE RMAN(RECOVERY MANAGER) COMPONENTS AND CONFIGURATIONS:  

RMAN:

             Rman or Oracle Recovery Manager introduced in oracle 8i,oracle proprietary software client or utility similar to sqlplus used to perform backups,restores,recoveries and other Database operations,Rman takes backup only usedblocks in entire database blocklevel backup,it performs block level backup parallelism,Rman is the utility to take backups and restore oracle recommends,Rman is faster it takes block level backups,Rman is faster because we can initiative parallesism,validate your database using Rman detect block corruption,Rman will repair the database block corruption for you validating backup,incremental backup,recovery catalog.and it is a platform independent tool.

            Rman stores backup metadata inforamtion in the database ,use control file to hold backup metadata information catalog, also using remote repsitory catalog schema database it is a online backup tool its a special feature does block backup with incremental concept,metadata is the control file data stores the information into different database on server,Multi destination backups like tapes,Archive log mode is must to use Rman. Rman utility comes with oracle binaries,no special installation orlicence required for using Rman at command prompt just type rman,it defaults connects to database environmental variables defined, Rman utility can be used only when Database is atleast mount stage,Rman is used while the database is UP and running and have a very little performance impact is backup is running .

COMPONENTS OF RMAN

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

RMAN PROMPT

TARGET DATABASE

RECOVERY CATALOG

AUXILAR DATABASE-->clone DB connection target clone

MEDIA MANAGEMENT LAYER-->RMAN and third party tools (net backup)

RMAN CHANNELS---> back up is speed 

FULL BACKUP-----> ENTIRE DATABASE BACKUP (RMAN FULL BACKUP IS EQUAL TO HOT BACKUP  CANNOT APPLY INCREMENTAL BACKUP ON FULL BACKUP)

              

INCREMENTAL BACKUP----> LEVEL 0--->FULL DB BACKUP--> Full Db backup takes bakup of the used blocks( we can restore Db level0)

  |----> LEVEL 1--->BACKUP CHANGES ONLY FROM LAST LEVEL 0 BACKUP--->Takes Backup of only the changed blocks from last backup,Rman will know which block will be changed block header SCN number backup only changed block  taken by referrring data block header for updated SCN (we can recover DB applying archives and open database)

RECOVERY IN TWO PARTS----->RESTORE--->LEVEL 0

                                              |--->RECOVERY--> LEVEL 1

CONFIGURATIONS OF RMAN

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

To connect using rman

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

$rman target/

To Display the configuration of RMAN.

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

rman>show all;

Configuring Device Type - Tape / Disk

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

For Tape: Tivoli manager (IBM)

rman>Configure default device type to sbt;

For Disk : Default

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

rman>Configure default device type to disk;

Configuring Channels with Parallelism Option - EE

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

Multiple backupsets can run with parallel option.

Depends on number of cores, can increase the performance of the backup using multiple channels.

$mkdir -p /orabackup/prod/rman/stream1

$mkdir -p /orabackup/prod/rman/stream2

rman>Configure device type disk parallelism 2;

rman>CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/orabackup/prod/rman/stream1/backup%U';

rman>CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/orabackup/prod/rman/stream2/backup%U';

Maximum Piece Size

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

Limit the backup piece size 

rman>CONFIGURE CHANNEL  DEVICE TYPE DISK MAXPIECESIZE 1000m;

Optimization

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

oracle skips the files that are already backedup by enabling optimization.

rman>configure backup optimization on;

Control file autobackup

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

Control file holds data and control information of database

 as well metadata of backup information.

On every backup event, the control has to get backed up. Enable autobackup.

With control file - includes spfile also

rman>configure controlfile autobackup on;

rman>configure controlfile autobackup off;

rman>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabackup/prod/rman/%F';

Eg:

Output :

--------

Starting Control File and SPFILE Autobackup at 23-JUL-20

piece handle=/orabackup/prod/rman/c-369100151-20171110-00 comment=NONE

Finished Control File and SPFILE Autobackup at 23-JUL-20

Retention Policy - default 1 day

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

is defined , how to long to hold the backups ..

Once retention value reached, the backup files will expire.

rman>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Snapshot controlfile

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

rman>CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/orabackup/prod/rman/snapcf_prod.f';

To update the recovery catalog to get sync for consistent , will have a snapshot controlfile.

Backup sets / Backup Pieces

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

RMAN can also store its backups in an RMAN-specific format called a backup set.

A backup set is a collection of files called backup pieces, each of which may contain the backup of one or several database files.

retention

optimization

control on , path/loc

snapshot

parallelism

channels 

maxpiecesize

disk/tape

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

How to have a backup using rman?

rman target/

rman>backup database plus archivelog;

Only datafiles

rman>backup database;

only archivelog files

rman>backup archivelog all;

Image backup - same as dbf format

rman>backup as copy database;

list of backup files

rman>list backup;

rman>list backup summary;

Error : scenario:

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

ORA-19625: error identifying file /archives/prod/1_986936449_51.arc

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

in case : 

archive log missing , cannot continue backup.

for that , use crosscheck command . will validate the existing archives and mark has expired for

those missing archives in control file. so next backup will skip those missing archives found expired.

rman>crosscheck archivelog all;

rman>backup database plus archivelog;

For backups 

rman>crosscheck backup;

Error : Scenario:-

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

ORA-19502: write error on file "/orabackup/prod/rman/stream2/backup0kth1k51_1_1", block number 51456 (block size=8192)

ORA-27072: File I/O error

Verify the physical file system space on /orabackup

$df -h

if 100% 

remove old files using

Step1 : rman>report obsolete;

will list all files that are expired/obsolete;

Step2 : rman>delete obsolete;

will delete those listed obsolete files.

If those files not listed and not part of the same database.

then delete using rm -rf * from /orbackup/prod/rman/stream1

1. cd /orabackup/prod/rman/stream1

2. ls -ltrh

3. $rm -rf *

Compressed backup

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

rman>BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Validate the db

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Skip those archives missing,and free up space on archive location while 

backup.

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

RMAN>backup archivelog all delete input skip inaccessible;

/archives - 100%

Take a archive log backup,and resume space by deleteing those backedup archives.

move -/archives - /orabackup (1-10 -old ls -ltr)

backup - delete - space resumed

/orabackup-/archives (1-10) - free already

crosscheck

backup - delete

Connect - RMAN

Backup and Recovery

Configuration

retention policy

controlfile auto

path - controlfile

parallelism - faster

channels

optimization - to skip

snapshot -

maxpiece size

Backup sets - specific format

Backup pieces - files

backup database plus archivelog

crosscheck - validate

delete obsolete

report obsolete

Incremental - Level0 - level 1 | Cummulative

Difference - delete obsolete | delete expired

compressed

Backup Strategy - Explain ?

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

Incremental backup - Block Level - only changes.

Two types of incremental backup.

    Level 0 - Full incremental backup

    Level 1

        Differential incremental backup.

        Cummulative Backup

Full Incremental - Level 0 Backup -is the full complete

 base backup taken normally on peak off hours.

RMAN>BACKUP INCREMENTAL LEVEL 0 DATABASE;

Level - 1

Differential Backup

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

Sunday – full backup Including all archivelogs – this is a base backup.

Up on this – all changes made will be backedup on every day since last

 incremental backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Cummulative – Including all Previous Changes from

base backup (Level 0) on Sunday.

----------

RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

So we can use next incremental backup for recovery , if previous level 1 backups are lost.

Its the best backup. If no redundancy.

But still, having multiple reduncies in terms tapes/disks. Will

recommend differential.

Crosscheck

---------

we have archives ,... while backup if archives are missing ? can we continue

 the backup?

What happens ?

 The backup fails.

How to continue ?...

ORA-19625: error identifying file /archives/prod/1_998901437_22.arc

RMAN> crosscheck archivelog all;

Crosscheck command validates archives physically exists and updates control

 file repository the file status.

So while rman backup , will verify the catalog and skip those files are

expired(marked) which are physically not exists.

rman>backup archivelog all;

But recommended to continue with full backup in this situation.

every 2/4 hrs will have archive log backup.

Difference between delete obsolete and delete expired?

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

Obsolete? : out of retention period. physically files exists and files are

 out retention.

Expired : physically not exists and marked has expired in catalog.

rman>report obsolete;

rman>delete obsolete;

rman>delete expired backup;

will delete records from catalog those expird.

Note:

Level 0 - Full Incremental Backup - Complete

1TB

Level 1

Differential -- Only changes from last incremental

Mon - 1GB -

Tues - 0.5G

Wed - 0.25G -

Only those changes - the difference

Cummulative - best - including previous changes on top of base backup

Mon - 1G

Tues - 1G+0.5G - lost

Wed = 1G+0.5+0.25G - can retain from wed backup of Tues

which includes previous changes as it is cummulative

Space and time

Real time , we use only differential if redundancy managed with additional tape and storage backup.

If tape backup of tuesday with differential and lost disk backup

Can we retain from tape

/orabackup - rman - tues -

once done

will have tape backup

Note:

------

rman>backup archivelog all delete input skip inaccessible;

difference delete obsolete and expired

based on retention, the backup files get expired but physically exists

those need to be deleted

as backup policy , retention 7 days if incremental

after 7 days - expired

need to clean up space to resume

use obsolete

Recoveries

----------

Recover - Lost datafile

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

    users will not be able to write in users datafile (scott/hr-eg)

Make sure - we have full DB backup - last night including - archives.

as we lost - keep offline datafile.

@09 last night

rman>backup database plus archivelog;

@next day at 03PM - lost datafile

$rm users01.dbf

SQL> alter database datafile '/oradata/prod/users01.dbf' offline;

rman target/

RMAN>restore datafile '/oradata/prod/users01.dbf';

rman>recover datafile '/oradata/prod/users01.dbf';

will recover changes from last night 09 till today 03pm from

archives.

SQL> alter database datafile '/oradata/prod/users01.dbf' online;

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

inconsistent Recovery - If no archives.

    Timebased

        using SCN number(v$database)

As you would expect, RMAN allows incomplete recovery to a specified time,

 SCN or sequence number:

$rman target/

$rman>run {

 shutdown abort;

 startup mount;

 set until sequence 21;

 restore database;

 recover database; #media recovery from archiveslogs

 alter database open resetlogs;

}

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

Whole database recovery

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

If the controlfiles and online redo logs are still present a whole database

 recovery can be achieved by running the following script:

rman target/

rman>

run { shutdown abort

# use abort if this fails

 startup mount;

 restore database;

 recover database;

alter database open;

}

Note: Using run{} block ,we can execute multiple commands in rman.

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

Difference between delete obsolete and delete expired

Delete Obsolete - will delete, where the files physical exists and out of

 retention period.

rman>delete obsolete;

rman>delete obsolete noprompt;

Delete expired - will delete those files are marked as expired and physically

 not exists.

rman>delete expired backup;

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

Compressed backup

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

rman>BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Validate the db

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Skip those archives missing,and free up space on archive location while

backup.

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

RMAN>backup archivelog all delete input skip inaccessible;

/archives - 100%--->/orabackup

space resume by delete

if missed,skipping

move -/archives - /orabackup (1-10 -old ls -ltr)

backup - delete - space resumed

/orabackup-/archives (1-10) - free already

crosscheck

backup - delete

13.arc bkp arclog

14.arc

15.arc

16.arc till - next fail. due to missing archives

18.arc skip(17) - inaccessible will continue also delete

those backedup(13,14,15,16,18).

21.arc skip(19,20) - inaccessible

backup completed and resumed space

22.arc

23.arc

missing 17/19

NOTE: info on Rman may be differs in  your environment like production,testing,development or u r host machine.

THANK YOU VIEWING MY BLOG FOR MORE UPDATES VISIT MY BLOG REGULARLY

MANAGING TABLESPACE AND DATAFILES IN ORACLE

MANAGING TABLESPACE AND DATAFILES IN ORACLE

Introduction: 

Tablespace :In this blog i am going to explain MANAGING TABLESPACE AND DATAFILES IN ORACLE,Tablespace is one or more logical storage units in oracle database  which collectively store all the database data and each tablespace has one or more datafiles, datafiles are physical structure tablespace is not visbile in the filesytem of the machine which datbase resides,The tablespace builds the bridge between the oracle database and the filesystem in which the tables or index's data stored ,The data in oracle database are stored in tablespaces, An oracledatabase can be logically grouped into smaller logical areas of space known as tablespaces,each tablespace consits of one or more operating system files,which are called datafiles

1)SYSTEM TABLESPACE: SYSTEM tablespace is used to store the sytem related data which includes tables,indexes,sequences,and other objects,Contains the data dictionary,including stored program units Contains the SYSTEM undo segment,should not contain user data,SYSTEM tablespace always online when database is open ,oracle database have a SYSTEM tablespace when database created SYSTEM is the first tablespace


2)SYSAUX TABLESPACE : SYSAUX TABLESPACE stores many databse components,always online when database is open,these SYSAUX table created when oracle database installed,if u SYSAUX table gets filled 100% none of the users will not be able to login 


3)UNDO TABLESPACE :  UNDO TABLESPACE is a kind of permanent tablespace used by oracele to manage undo data if your running your database in automatic undo management mode this undo data or undo records are generally used to roll back transations  Recover from logical corruptions using flashback feature 


4)USERS TABLESPACE: USERS TABLESPACE is used to store the user objects and data in permanently every database have a tablespace for permanent user data is assigned to users otherwise the objects will be stored SYSTEM tablespace  


5)TEMP TABLESPACE:  TEMP TABLESPACE is stores the temporary data  that only exists during the database session,oracle uses temporary tablesapce to improve the concurrency of multiple sort operations, temp tablesapce shared by multiple users


DATABASE STORAGE HIERARCHY  HOW DATA IS STORED IN LOGICAL AND PHYSICAL
--------------------------------------------------------------------------------------------------------------------------

  DATABASE
     |
     |
     v
  TABLESPACE  ---------> DATAFILE-----> PHYSICAL------> SAN(STORAGE AREA NETWORK)
     |                                    | 
     |                                    |-----------> NAS (NETWORK ATTACHED STORAGE)
     v
  SEGMENT---------->LOGICAL
     |
     |
     v
  EXTENT----------->LOGICAL
     |
     |
     v
 ORACLE DATA BLOCK------------> LOGICAL OS BLOCK



TABLESPACE------>LOGICAL STORAGE UNIT
SEGMENTS-------->SPACE ALLOCATED FOR A TABLES INFORM OF EXTENTS
EXTENTS -------->CONTIGUOS ORACLE BLOCK 



Dictionary Tables

dba_tablespaces
dba_data_files
dba_segments
dba_extents
dba_free_space


List the tablespaces

sql>desc dba_tablespaces

sql>select tablespace_name from dba_tablespaces;

sql>select tablespace_name,extent_management,allocation_type,segment_space_management from dba_tablespaces;

sql>desc dba_tablespaces

Finding tablespace for sys owned.

Sys stores objects in system tablespace.

SQL> select username,default_tablespace from dba_users where username like 'SYS';


Listing datafiles of a tablespace:

sql>select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'CHAITBS';

Creating Tablespace:

sql>create tablespace CHAITBS datafile '/oradata/prod/chaitu01.dbf' size 100m;

Adding datafile:

sql>alter tablespace CHAITBS add datafile '/oradata/prod/chaitu02.dbf' size 100m autoextend on maxsize unlimited;

Note: 32G is the max file size on unix level

Finding size of a datafile:

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_data_files where tablespace_name like 'CHAITBS';


Resize Datafile:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' resize 200m;

Enable Autoextend on/off:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize 8192m;

Unlimited:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize unlimited;

In oracle, the maxsize of the datafile on unix level is max 32G.


Dropping tablespace :

sql>drop tablespace CHAITBS including contents and datafiles;


Create tablespace
add datafile
resize
autoextend on
drop tablespaces
finding free space
find datafile location/size/max/autoextnd
list of tablespaces /managed type/segment space management

Dictionary tables/views

    dba_tablespaces
    dba_data_files
    dba_free_space
    dba_segments
    dba_extents


Find the usage

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_data_files where tablespace_name like 'SYSTEM';


Finding free space.of a particular /specific tablespace

sql>select tablespace_name,sum(bytes/1024/1024) from dba_free_space where tablespace_name like 'SYSTEM' group by tablespace_name;

For all tablespaces - using group by

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


--------------------90%(7GB)-------limi(8G)----------------------max(32G)
New --------------------------------------------------------------max(32G)
existing limit ------------(8G)

Assume - Limit - 8GB
        threshold reached 90% (7GB)
            but Max size 32GB
Now action:
        Add new datafile with unlimited
        then limit existing datafile to 8GB


sql>alter tablespace CHAITBS add datafile '/oradata/prod/chaitu02.dbf' size 100m autoextend on maxsize unlimited;

Now limit existing

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize 8192m;

Temp Tablespace (non system Tablespace)

    purpose : for sorting,analyze,index rebuild,group by

    default - temp(one)

dba_temp_files
v$temp_space_header

we can create many
sql>create temporary tablespace chaitempfile '/oradata/prod/chaitutemp01.dbf' size 100m autoextend on maxsize unlimited;

Adding temp file:

sql>alter tablespace cctemp add tempfile '/oradata/prod/cctemp02.dbf' size 100m autoextend on maxsize unlimited;

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_temp_files where tablespace_name like 'CCTEMP';

Finding free usage:

sql>desc v$temp_space_header

SQL> select tablespace_name,sum(bytes_used/1024/1024),sum(bytes_free/1024/1024) from v$temp_space_header group by tablespace_name;

Error:

unable to extend an extent in temp segment of temporary tablespace
find the usage and add tempfile

Datafile monitoring,check freespace,add , limit,autoextend.

V$sort_usage - inactive - kill - support - approval


Dictionary Tables :

dba_tablespaces
dba_data_files
dba_free_space
dba_temp_files
v$temp_space_header


User management


Create user Chaitanya identified by chaitu123 default tablespace chaitbs temporary tablespace cctemp;

application users ---> application - -->chaitanya- - ->db

schema- - ->collection of objects ---> acces to db users


file limit - 8G
--------------------7G---->8G(Max)-----------setto(32G-YES)
  90% reached - alert received action

add datafile New - -32G
limit existing to 8G - autoextend on maxsize 8G.


resize - if disable autoextend on ,then limit by resize - physically allocate
maxsize- only allocate on growth


Managing Tablespaces and data files locally managed tablespace


sql>create  tablespace tbs1 datafile '/oradata/prod/data01.dbf'
    size=50m
    extent management local autoallocate;

Alternative to Autoallocate is uniform

sql>create tablespace tbs2 datafile' '/oradata/prod/data01.dbf'
   size=50m
  extent management local uniform size 256k;

Dictionary managed tablespace


sql>create tablespace tbs1 datafile '/oradata/prod/data01.dbf'
    size 50m
   extent management dictionary;


Big File Tablespace


sql>create bigfile tablespace tbsbf1 datafile '/oradata/prod/databf01.dbf'
    size 50g;

Coalesce statement

sql>alter tablespace tbs1 coalesce;


Viewing information about tablespace and datafiles

sql>select * from dba_tablespaces;
sql>select * from v$tablespace;


To view information about datafiles


sql>select * from dba_data_files;
sql>select * from v$datafiles;

To view information about tempfiles


sql>select * from dba_temp_files;
sql>select * from v$tempfile;


To view information about freespace in tempfiles

sql>select * from v$temp_space_header;


To view information about free space in datafiles

sql>select * from dba_free_space;


To view the value and type of the blocksize

sql>show parameter db_block_size;

Note : Info on MANAGING TABLESPACE AND DATAFILES IN ORACLE it may be differ in your environment like production,testing,development,etc




THANKS FOR VIEWING MY BLOG FOR MORE UPDATES SUBSCRIBE ME AND FOLLOW ME 
   

ORACLE DATAPUMP

ORACLE DATAPUMP
-----------------------------

DataPump Introduction
----------------------
 DataPump: DataPump is built in utility in oracle  to take logical backups its a technology high speed of data movement data and metadata from one database to another database 
 Datapump is available on oracle 10 g release 1 and later it is faster and more flexible alternatives to the traditional exp and imp. datapump runs with in the 
 oracle server processes and can read directly from database files and writes directly to file on the server, Data Pump is an extension to traditional EXP/IMP which
 provides more advantages like security ,speed etc,traditional export and import was mainly security problem for eg : we take take export scott schema .dmp file(dump file)
 we can easily take the scott.schema .dmp file import to any of the database machine traditional export and import does not provide any security to the .dmp file  

How Datapump Works:
-------------------
DataPump Export: oracle Datapump will create master table in the corresponding schema and data will be transferred parellely to dump file(.dmp)

DataPump Import: Oracle Datapump will happen reverse order that is dump file to master table will be created and from that original table

after finishing either export or import in datapump oracle will automatically drops the master table

Note: Whenever datapump export is done using parallel option,import also should be done with the same option,otherwise it will effect the time taking for import

sqlplus directory should be created both the exportdp and importdp

os level directory should be created both the exportdp and importdp

Datapump directory is a secure feature

You must create a directory in os level and DB level

On Target Database,you need to again create directory to use datapump

The Directory name can be different on target server

This adds one more layer of security for export/import

The Following Levels of Datapump export/import are possible
-------------------------------------------------------------

1)Database Level
2)Schema Level
3)Table Level
4)Row Level
5)Tablespace Level

   
in this  i am showing testing server (source) and production server (target)
----------------------------------------------------------------------------
first create directory at OS level

# mkdir -p /u02/dp_exp_dir

second create a directory at sql level

sql> create directory datapump as '/u02/dp_exp_dir';

grant permissions on directory

sql> grant read,write on directory datapump to scott ; (better to give sysuser)

To view directory information

sql> select * from dba_directories

production server
----------------- 


# mkdir -p /u02/dp_exp_dir

second create a directory at sql level

sql> create directory datapump as '/u02/dp_exp_dir';

grant permissions on directory

sql> grant read,write on directory datapump to scott ; (better to give sysuser)

To view directory information

sql>select * from dba_directories;

Test server
-----------

# mkdir -p /u02/dp_imp_dir

sqlplus '/as sysdba'

second create a directory at sql level

sql> create directory datapump as '/u02/dp_imp_dir';

craete or repalce directory datapump as '/u01/imp_dir';

grant permissions on directory

sql> grant read,write on directory datapump to scott ; (better to give sysuser)

to know options of datapump export

$expdp help =y

To take the database level export
-----------------------------------

$expdp directory=datapump dumpfile=fullprod.dmp logfile=fullprod.log full=y

To take the schema level export
-----------------------------------

$expdp directory=datapump dumpfile=scott_bkp.dmp logfile= scott_bkp.log schemas='scott'


To take the table  level export
-----------------------------------

$expdp directory=datapump dumpfile=emp_bkp.dmp logfile=emp_bkp.log table='SCOTT.EMP';( here scott is the owner and emp is the table)


To take the row level export
-----------------------------------

$expdp directory=datapump dumpfile=emprows_bkp.dmp logfile=emprows_bkp.log tables= 'SCOTT.EMP' query=\"where deptno=10"\


production server
-----------------

$expdp directory =datapump dumpfile=scott_bkp.dmp logfile=scott_bkp.log schemas='SCOTT'

sqlplus '/as sysdba'

$cd /u02/dp_exp_dir/

db_exp_dir] $ ls -lrt

scott_bkp.log
scott_bkp.dmp

$expdp directory= dataump dumpfile=emp_bkp.dmp logfile=emp_bkp.log tables='SCOTT.EMP'

To Import a full database
----------------------

sqlplus '/as sysdba'

$impdp directory= datapump dumpfile=fullprod.dmp logfile=imp_fullprod.log full=y

To know options of datapump import

$ impdp help =y

To Import a schema
----------------

$impdp directory =datapump dumpfile=scott_bkp.dmp logfile= imp_schema.log remap_schema='SCOTT:SCOTT'

$impdp directory= datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema ='SCOTT:ABC'


To Import a table
--------------
$impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP" remap_schema='SCOTT:SCOTT'

To Import a table to another user
-------------------------------

$impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables= 'EMP" remap_schema= 'SCOTT:system'


To Import a table to another tablespace(only in datapump)
------------------------------------------------------
$imp directory= datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='emp remap_schema='SCOTT:SCOTT' remap_tablespace='MYDATA:MYTBS'

Production server
-----------------
$db_exp_dir]$ ls -lrth

scott_bkp.dmp

$scp scott_bkp.dmp chaitanya@192.168.1.100: /u02/dp_imp_dir (production to testing server copy scott_bkp.dmp to /u02/dp_imp_dir location in testing server)  
password

Target server
-------------
$cd /uo2/dp_imp_dir
dp_imp_dir]ls -lrth
scott_bkp.dmp

$impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_scott.log remap_schema='SCOTT:IMP_TEST  (here user is imp_test scott is owner)

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

Oracle - datapumps - Logical Backup
------------------------------------

Note : For Logical backup, the DB must be up and running.

using logical backup, will move the data from one server to another server.

with utilities , expdp and impdp will export and import in to target database.

prod - expdp - .dmp - > move to target server using scp
---> .dmp - impdp ->import - target 

Prior to 10g version , we have exp and imp. From 10g oracle introduced Oracle datapumps with expdp and impdp which is 15-45 faster than normal imports.

We also use logical backup for refresh activities.

If developers want to build a module , for that they need production data to development server.
So DBA will refresh data from prod to develop server as per critirea.

There are three levels of Logical backup.

1. Full Database
2. Schema Level
3. Table Level

Export Parameters - job
------------------------
Will export in to binary dump file .dmp with expdp utility.

To get help on export parameters.
--------------------------------
$expdp help=y

Use physical location to hold dump files.
$mkdir -p /orabackup/prod/dp


Create logical alias for physical directory.
----------------------------------------
sql>create or replace directory dp_dir as '/orabackup/prod/dp';
sql>grant read,write on directory dp_dir to public;


Verify - the path /name - dba_directories
------------------------------------------
SQL> select directory_name,directory_path from dba_directories where directory_name like 'DP_DIR';

Two grants needed for a user to export and import.

datapump_exp_full_database
datapump_imp_full_database

SQL> select role from dba_roles where role like '%DATAPUMP%';

eg: If scott user to have a backup
sql>grant datapump_exp_full_database to scott;
sql>grant datapump_imp_full_database to scott;

Default system user has these roles. so can use system user to
have logical backup.
---------------------------------------------------------------
Full Database backup
--------------------
$expdp system/sys123 directory=dp_dir dumpfile=expdpfull220720.dmp logfile=expdpfull220720.log full=y compression=ALL job_name=j1

Interactive Method -
-------------------- 
can start/continue/stop/status/parallel - jobs

To stop the dp job.
--------------------

use ctrl+c
export>stop
export>....yes

Verify the job - status
-------------------------

sql>select job_name,state,operation from dba_datapump_jobs;

To continue - the dp job

1. attach
2. continue.

$ expdp system/sys123 attach=P1
export>continue


To terminate the job - kill.

use ctrl+c
export>kill
export>yes.

Schema Level Backup
--------------------
$expdp system/sys123 directory=dp_dir dumpfile=expdpscott.dmp logfile=expdpscott.log schemas=scott,hr,sh job_name=t1 compression=ALL content=metadata_only

Table Level backup
-------------------
$expdp system/sys123 directory=dp_dir dumpfile=expdpscotttab.dmp logfile=expdpscotttab.log tables=scott.emp,hr.departments job_name=r1 compression=ALL


How to diagnose the errors while logical backup? either expdp/impdp.
---------------------------------------------------------------------

$more expdpscotttab.log 
will have messages 
successfully completed
abnormally terminated
No space left on device (clean up backup space)
object doesnot exists
user doesnot exists
user already exists
objects skipped


Parameters
----------
Full
Schemas
Directory
tables
dumpfile
logfile
content
compression
job_name
parfile

Using parfile 
-------------
is a parameter file. will enclose the parameters in parfile.

Create parfile using vi

$vi expdpfull.par
directory=dp_dir
dumpfile=expdpfull.dmp
logfile=expdpfull.log
full=y
job_name=p1
estimate=blocks

Provide permissions - execution
$chmod +x expdpfull.par


$expdp system/sys123 parfile=expdpfull.par

using parameter estimate/estimate_only

  with estimate=blocks|statistics

will estimate the size of the dump file while export.
in terms of blocks level or using statistics

With estimate_only
will show the dumpfile size , but not export.


$expdp system/sys123 directory=dp_dir full=y estimate_only=yes

Finding usage of each folder in current location
------------------------------------------------
#du -sh *

Error: Severity 1
/u01 threshold limit reached 90% 
Action:

inventory
binaries
network files
alert log file / trace files --- will cause full - house keep

Error : Sev2
/oradata - 90% threshold reached?
action:
find the maxbytes with autoextend on 
limit with autoextend on
find space to clean up if any other files
Find system engineer to add more space.
find other fs if so, to add more datafiles.


Number of databases on server?
/etc/oratab

Number of instances currently running on server?
ps -ef | grep pmon
or
ps -ef| grep smon

Number of instance can start on boot?
/etc/oratab
Y/N


CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

To export only table structure and with out data
can use content parameter with metadata_only
default is all(includes data)

eg:
$expdp system/sys123 directory=dp_dir dumpfile=expdpscott220720.dmp logfile=expdpscott220720.log schemas=scott,hr  content=metadata_only


Note: 
To estimate_only , finding the size of the dumpfile.
        Doesnt need to specify the dumpfile and log file.
Will not export. Just estimates.
------------------------------------------------------------
Parallel
--------

Can write/load in to multiple dumpfiles for large database.
For faster performance.
using parallel parameter , depends on number of CPU's
eg: Number of cores : 2 
parallel=2

eg:
$expdp system/sys123 directory=dp_dir dumpfile=expdpfull220720_%U.dmp logfile=expdpfull220720.log schemas=scott,hr,sh parallel=2

%U - unique number


COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.


EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

QUERY : Specific rows/records can unload.
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

$expdp system/sys123 directory=dp_dir dumpfile=expdpscott220720.dmp logfile=expdpscott220720.log QUERY='scott.emp:"WHERE deptno > 10"' 

VERSION
--------
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.

$expdp system/sys123 directory=dp_dir dumpfile=expdpscott037.dmp logfile=expdpscott0307.log QUERY='scott.emp:"WHERE deptno > 10"' version=12.2

eg: version=12.2  or version=LATEST
can use for migrating data from source to target of different version.
from 12.1.0  - ? 12.2.0

-------------------------------------------------------
Imports
----------
till now,...using expdp utility dumped in to .dmp file.

Now from source DB will dump into .dmp and scp to remote server
using source .dmp file will import into target DB

Note: scp - server copy
eg:
$scp  filename  root@servername:/path
eg: $scp expdpscott.dmp oracle@chaitanyahost:/orabackup/dev/dp
Paswd: oracle123
using impdp will import in to target db.

prod -> expdp -> .dmp - scp
->>>> .dmp -> impdp - > target db

$impdp help=y

The rqmnt
full
table
schema
with/without
compresed
query
version

having dump file from exports
will use to import in target db.

full ---> full/schema/table/query
Schema -> schema/table/query
table -> table/query

Finding Count of objects in schema before export to validate in source and target.
schemas exists in both source and target.
Tablespace associated with schema in target db as same as source.

eg:
source target
scott scott
  users users
Note: In target db, if schema user doesnt exist, will create automatially while import.
But make sure tablespace exists in target same in source before import.


sql>select object_type,count(*) from all_objects where owner like 'CHAITANYA' group by object_type;

select object_type,count(*) from all_objects where owner like 'ABHIRAM' group by object_type;

Count of Invalids - plsql objects will become invalid
-----------------
sql>select owner,object_type,count(*) from all_objects where status like 'INVALID' group by object_type,owner;

PLSQL objects - procedures/packages/functions/triggers will go off invalid.


Execute following script - to validate and compile.
--------------------------------------------------
sql>@?/rdbms/admin/utlrp.sql

sql>select username,default_tablespace from dba_users where username like 'SCOTT';

sql>select tablespace_name from dba_tablespaces where tablespace_name like 'USERS';

----------------------------------------------------------
Scheme Level
-------------
$ expdp system/sys123 directory=dp_dir dumpfile=expdpfull220720scott.dmp logfile=expdpfull220720scott.log schemas=scott

$impdp system/sys123 directory=dp_dir dumpfile=expdpscott220720.dmp logfile=impdpscott220720.log schemas=scott

Remap Schema
------------
unload/load in to different schema.
using
remap_schema=scott:manasa

$expdp system/sys123 directory=dp_dir dumpfile=expdpscott220720.dmp logfile=expdpscott220720.log schemas=scott

$impdp system/sys123 directory=dp_dir dumpfile=expdpscott220720.dmp logfile=impdpscott220720.log schemas=scott remap_schema=scott:manasa

sql>select username from dba_users where username like 'DEV';

sql>select object_type,count(*) from all_objects where owner like 'DEV' group by object_type;


Again reimport , what will happen?

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 5 error(s) at

ORA-39151: Table "DEV"."SALGRADE" exists.a will be skipped due to table_exists_action of skip

ORA-31684: Object type USER:"DEV" already exists

--------Still , want to continue reimport - how?

Using table_exists_action parameter in impdp
--------------------------------------------
$impdp system/sys123 directory=dp_dir dumpfile=expdpfull220720scott.dmp logfile=impdpscott220720.log schemas=scott remap_schema=scott:dev table_exists_action=truncate

Note: truncate , will delete the table records and can reuse the space.


TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST, or any valid database version


TABLESPACES
Identifies a list of tablespaces to import.


TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_2020.

SCHEMAS
List of schemas to import.

REMAP_SCHEMA
Objects from one schema are loaded into another schema.


QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".


NOLOGFILE
Do not write log file [N].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file.

FULL
Import everything from source [Y].

HELP
Display help messages [N].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of import job to create.

LOGFILE
Log file name [import.log].


EXCLUDE
Exclude specific object types.

DIRECTORY
Directory object to be used for dump, log and SQL files.

DUMPFILE
List of dump files to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.


CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.



Export Import
full full/query/table/content/schema
schema schema/table/query/content
table table/query/content




Note: Here i am giving some info on datapump which may differ from your environment like production, testing and ipaddress  etc.




THANK YOU FOR VIEWING MY BLOG FOR MORE UPDATES VISIT MY BLOG REGULARLY  https://chaitanyaoracledba.blogspot.com/

USER MANAGEMENT IN ORACLE

USER  MANAGEMENT IN ORACLE

Introduction:

In this blog i am going to expalin USER MANAGEMENT IN ORACLE,User is basically used to connect a database  it may be production, pre-production,development or testing server (UAT) but users  depends on  what the role and designation it should be manager, admin or programmer,test user 

all database objects like Table,index,view,etc can be created under the user,in oracle users,and schemas are important you can consider that user is the account connect to database ,and schema is the set of objects (tables,vies,indexes).

USER MANAGEMENT IN ORACLE that belong to the account any user minimum privelige is necessary to connect a database it should be like admin priviles   

User Management  - SQL

Create user ( creating the user in database)

alter user  (alter the user in database)

drop user   (drop the user in database)

sql>create user chaitanya identified by chaitu123;

Already exists/list of user / got created.

Using dictionary view/table owned sys user.

Information from dba_users

sql>desc dba_users;

sql>select username,password,account_status from dba_users where username like 'CHAITANYA';

sql>select username,password,account_status from dba_users where username in ('CHAITANYA','SYS','SCOTT');

sql>select distinct account_status from dba_users;

Locked

Expired & Locked

Open

Account Lock/Unlock/reset

sql>alter user Chaitanya account lock;

sql>select username,account_status from dba_users where username like 'CHAITANYA';

sql>alter user chaitanya account unlock;

sql>alter user chaitanya identified by chaitu123;

Dropping user

sql>drop user chaitanya;

Creating user :

sql>create user abhiram identified by abhi123;

sys owned dictionary tables, can find the information of user details.

Note : dba_users

sql>select username,account_status from dba_users where username in ('CHAITANYA','MANASA','ABHIRAM','SAIBABU','PAVAN','VASANTH','SYS','SYSTEM');

sql>select username,password from dba_users;

Finding number of users in DB

sql>select count(*) from dba_users;

Altering user 

Account lock/unlock

sql>select username,account_status from dba_users where username like 'CHAITANYA';

sql>alter user chaitanya account lock;

sql>alter user chaitanya account unlock;

Password reset / Changing

sql>alter user Manasa identified by manu123;

Drop user - removing user from db

sql>drop user abhiram;

   

    note: including dependencies , his objects to remove.

 

      If its a schemas, then use cascade.

sql>drop user abhiram cascade;

    In case, scott user

 

    sql>drop user scott cascade;

Roles :

Roles are groupings or collection of privileges that you can use to create different levels of database access.Defining subset of privileges through a role to a user.it allows easier management of privileges in USER MANAGEMENT IN ORACLE

We can also grant role to a role and to a user.

• manager
• dba
• developer
• tester
• privilege - > user
• privilege -> role - > user
• privileges - > role - > role - > user
• 
  
• 
  
•         dba_roles
•         dba_role_privs
•         role_role_privs
•         dba_sys_privs

sql>select role from dba_roles;

ROLE

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

CONNECT

RESOURCE

DBA

SELECT_CATALOG_ROLE

EXECUTE_CATALOG_ROLE

DELETE_CATALOG_ROLE

EXP_FULL_DATABASE

IMP_FULL_DATABASE

Creating role:

sql>create role dev_role;

sql>create role test_role;

sql>select role from dba_roles where role like 'DEV_ROLE';

How to grant role to a user?

sql>grant dev_role to scott;

Connect role :

Connect : is a system defined role with create session privilege.

    Every new user needs create session privilege.That granted with role connect.

Privilege(create session) ---> user (abhiram) 

SQL>grant create session to dev_role;

Privilege(create session) -> connect(role) -> user (abhiram)

privileges - > role(connect) - > role(dev) -> user

dev(role) -> user will get including connect role 

sql>grant connect to chaitanya;

sql>connect chaitanya/chaitu123;

Resource role : built in system defined role

    resource role has create objects privileges.

    create table,create index,create trigger

SQL> select privilege from dba_sys_privs where grantee like 'RESOURCE';

SQL> select privilege from dba_sys_privs where grantee like 'CONNECT';

SQL> select privilege from dba_sys_privs where grantee like 'DBA';

SQL> select privilege from dba_sys_privs where grantee like 'DEV_ROLE';

sql> grant dba to scott;

Now scott has DBA role, have all privileges.

sql>grant resource to abhiram;

  role - role ----> user

  privilege ---> role ----> user

  privilege ---> user

  privilege ---> role ---> role ---> user

  create session ---> connect,resource ---> dev_role---> abhiram

  create user,drop user---> dev_role ---> abhiram

DBA Role :

        If user does need all sys privileges to manage database. 

provide DBA role which has all system privileges.

sql>grant dba to chaitanya;

sql>revoke dba from chaitanya;

Instead of giving connect,resource or other roles.we can create and grant with one single role.So we can grant role to role.

sql>grant connect,resource to  dev_role;

sql>grant dev_role to manasa;

So now manasa has create session,create table,trigger,index privileges via dev_role.So for next new user, directly we can grant with one single role.

sql>grant dev_role to vasanth;

What roles granted for a role?

sql>select granted_role from role_role_privs where role like 'DEV';

What roles granted for a user?

SQL> select grantee,granted_role from dba_role_privs where grantee like 'PAVAN';

Dropping a role:

sql>drop role dev_role cascade;

 Note : use cascade, to remove for dependencies

user-----> create/alter/drop

Role ----> create/drop/assign/revoke

Privileges/Permissions

Two Types of Privileges

1. System Level Privileges
2. Object Level Privileges

dba_tab_privs - Object Level Privileges---->(select,insert,update,delete)

sql>desc dba_tab_privs

dba_sys_privs - System Level Privileges---->(shutdown,backups,create user,alter user,create role,drop role,create table,alter table)

sql>desc dba_sys_privs

System Privileges -

SQL> select grantee,privilege from dba_sys_privs where grantee like 'CONNECT';

SQL> select grantee,privilege from dba_sys_privs where grantee like 'RESOURCE';

List of system Privileges

sql>select distinct privilege from dba_sys_privs; 

CREATE USER

SELECT ANY TABLE

CREATE SESSION

CREATE TABLESPACE

CREATE PROFILE

ALTER USER

DROP USER

How to grant /provide system privilege to scott ?

sql>grant create user,drop user,alter user to scott;

SQL> select privilege from dba_sys_privs where grantee like 'SCOTT';

To remove/revoke privileges

sql>revoke drop user from scott;

Privileges--->(Create session,create table..)--->Role(Connect,resource)---> role(devop)---> User(ravi)

sql>create user chaitanya identified by chaitu123;

sql>create role devop;

sql>grant connect,resource,create user,drop user to devop;

sql>grant devop to chaitanya;

sql>create user manasa identified by manu123;

Now grant role devop to manasa(developer)

sql>grant devop to manasa;

sql>revoke drop user from devop;

    but this applicable for all users under devop role.

Object / Table Privileges

sql>desc dba_tab_privs

SQL> select owner,grantee,grantor,privilege from dba_tab_privs where owner like 'SCOTT' and table_name like 'EMP';

sql>grant select,insert,update on scott.emp to hr;

SQL> select grantee,grantor,privilege from dba_tab_privs where owner like 'SCOTT' and table_name like 'EMP';

sql>revoke update  on scott.emp from hr;

sql>connect hr/hr123

sql>select * from scott.emp;

Profiles:

       Profile enforces set of password security rules and resources usage limit while creating a user if no profile is mentioned, then DEFAULT profile will be assigned to the user ,limiting resources for a user defining a profile.USER MANAGEMENT IN ORACLE

sql>desc dba_profiles

       

    system defined---> two profiles

        default

        monitoring_profile

SQL> select resource_name,limit from dba_profiles where profile like 'DEFAULT';

Altering profile:

SQL> alter profile devop limit idle_time 380;

create profile:

sql>create profile devop limit idle_time 180 failed_login_attempts 3;

Assign a profile to a user:

SQL> alter user scott profile devop;

Verify:

SQL> select username,profile from dba_users where username like 'SCOTT';

Drop profile :

sql>drop profile devop;

If user dependency , then use cascade to drop

sql>drop profile devop cascade;

• dba_users - username,paswd,profile,account_status
• dba_roles - roles
• dba_role_privs
• role_role_privs - roles
• dba_sys_privs - system level privs
• dba_tab_privs - object level privs
• dba_profiles - profiles details - default

Here i am giving some tables create it in your database and assign to user and roles in  

USER MANAGEMENT IN ORACLE

Tables

• agents
• customers
• orders

sql > CREATE TABLE  AGENTS("AGENT_CODE" CHAR(6) NOT NULL PRIMARY KEY,AGENT_NAME CHAR(40),WORKING_AREA CHAR(35),COMMISSION NUMBER(10,2),PHONE_NO CHAR(15),COUNTRY VARCHAR2(25));

sql>CREATE TABLE CUSTOMER(CUST_CODE VARCHAR2(6) NOT NULL PRIMARY KEY,CUST_NAME VARCHAR2(40) NOT NULL,CUST_CITY CHAR(35),WORKING_AREA VARCHAR2(35) NOT NULL,CUST_COUNTRY" VARCHAR2(20) NOT NULL, GRADE NUMBER,OPENING_AMT NUMBER(12,2) NOT NULL,RECEIVE_AMT NUMBER(12,2) NOT NULL,PAYMENT_AMT NUMBER(12,2) NOT NULL, OUTSTANDING_AMT NUMBER(12,2) NOT NULL,PHONE_NO" VARCHAR2(17) NOT NULL,AGENT_CODE CHAR(6) NOT NULL REFERENCES AGENTS);

sql>CREATE TABLE ORDERS(ORD_NUM NUMBER(6,0) NOT NULL PRIMARY KEY,ORD_AMOUNT NUMBER(12,2) NOT NULL,ADVANCE_AMOUNT" NUMBER(12,2) NOT NULL,ORD_DATE DATE NOT NULL,CUST_CODE VARCHAR2(6) NOT NULL REFERENCES CUSTOMER,AGENT_CODE CHAR(6) NOT NULL REFERENCES AGENTS,ORD_DESCRIPTION VARCHAR2(60) NOT NULL);

 Inserting values into agent table values

sql>INSERT INTO AGENTS VALUES ('A003', 'vasanth', 'Bangalore', '0.15', '077-25814763', 'india');

sql>INSERT INTO AGENTS VALUES ('A006', 'saibabu ', 'hyderabad', '0.13', '075-12458969', 'india');

sql>INSERT INTO AGENTS VALUES ('A009', 'pavan', 'rajahmundry', '0.12', '044-25874365', 'india');

 Inserting values into customer table values

INSERT INTO CUSTOMER VALUES ('C00012', 'tribhuvan', 'visakhapatnam', 'vizag', 'india', '2', '6000.00', '5000.00', '7000.00', '4000.00', '9898989898', 'A003');

INSERT INTO CUSTOMER VALUES ('C00021', 'vinod', 'rajahmundry', 'rjy', 'india', '2', '3000.00', '5000.00', '2000.00', '6000.00', '7878787878', 'A008');

INSERT INTO CUSTOMER VALUES ('C00027', 'kalyan', 'hyderabad', 'hyd', 'india', '3', '5000.00', '7000.00', '6000.00', '6000.00', '9400940021', 'A008');

 Inserting values into orders table values

INSERT INTO ORDERS VALUES('200102', '1000.00', '600.00', '08/01/2008', 'C00013', 'A003', 'SOD');

INSERT INTO ORDERS VALUES('200110', '3000.00', '500.00', '04/15/2008', 'C00019', 'A006', 'SOD');

INSERT INTO ORDERS VALUES('200107', '4500.00', '900.00', '08/30/2008', 'C00007', 'A009', 'SOD');

Creating user

create user abhiram identified by abhi123;

grant create session to abhiram;

create user manasa identified by manu123;

grant create session to manasa;

Creating Roles &Assigning Roles

create role cust_serv_clerk;

grant select on customer,agents,orders, to cust_serv_clerk;

grant select,insert,update on customer to cust_serv_clerk;

grant insert,update,delete on customer,agents,orders to cust_serv_mgr;

create role cust_serv_mgr;

grant cust_serv_clerk to cust_serv_mgr;

Assiginging Roles to the Particular User 

grant cust_serv_clerk to abhiram

grant cust_serv_mgr to manasa;

create user chaitanya identified by chaitu123

default tablespace users

quota 10m on users

temporary tablespace temp

quota 5m on system

profile application_user

password expire;

Note: Info on USER MANAGEMENT IN ORACLE,This is for the practical purpose how to assign a role and profile and resources to the user to a database it may be differ from your environment

like production,testing,development etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME AND SUBSCRIBE ME

 

   

 

Oracle Database Cloning Using Rman Utility

Oracle Database Cloning Using Rman Utility

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

Prerequisites 

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

1)OEL Oracle enterprise Linux server

2)Oracle installed with out database

Activity Flow

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

1)Take source backup using Rman

2)move pfile,Controlfile,backup pieces, to target server 

3)Start the Instance in Mount Stage and Restore from backup pieces

4)Open the database as source SID

5)Rename the Database

Trigger Backup on source

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

$ RMAN> backup database plus archivelog delete input;

$ RMAN> restore controlfile to '/tmp/prod_control.ctl';

Move files to Target server

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

parameter file pfile

edit pfile change SID except for DB_NAME parameter keep it source

create directories as per new pfile

$ RMAN> rman target /catalog rman-rc/rman-rc@rca

$ RMAN>backup database plus archivelog delete input;

$ RMAN>list backup of database summary;

$ RMAN>restore controlfile to '/tmp/prod_control.ctl';

$ RMAN>exit

$ cd ORACLE_HOME/dbs

$ ls -lrt

  initproddb.ora

 

i want to copy the initproddb.ora to the target server using scp

$ scp initproddb.ora oracle@192.168.0.100:$ORACLE_HOME/dbs    (it will ask password promt enter the password remote target server and enter it)

initproddb.ora   (you will prompt the 100% complete)

Target server

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

$ cd   ORACLE_HOME/dbs

$ ls -lrth

initproddb.ora

open this file in VI editor

replace with 

%s/proddb/testdb/   

save and exit the file  

(source db is proddb and target db is testdb create the directories as per new file)

(after that open the parameter file initproddb.ora in cat command we need to create directories)

$ cat initproddb.ora

$ mkdir -p /u01/app/oracle/admin/testdb/adump

$ mkdir -p /u01/app/oracle/oradata/testdb/

$ mkdir -p /u01/app/oracle/fast_recovery_area/testdb/

$ mkdir -p /u01/app/oracle/fast_recovery_area

scp/tmp/prod_control.ctl ---> target server control location  move files to target server

$ cd /tmp

 $ ls -lrth

source server

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

$ scp prod_control.ctl oracle@ 192.168.0.100:/u01/app/oracle/oradata/testdb/control01.ctl

$ scp prod_control.ctl oracle@ 192.168.0.100:/u01/app/oracle/oradata/testdb/control02.ctl

Target Server

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

$ ls -lrth

$ /u01/app/oracle/oradata/testdb/control01.ctl

$ /u01/app/oracle/oradata/testdb/control02.ctl

database backup pieces ---> same location as source

$ rman target /catalog rman -rc/rman_rc@rcat

conneceted target database :proddb (DBID=674237234)

connecteed to recovery catalog database

$ RMAN> list backup of database summary;  (it will show like this key ty lv device type and tag)

key --> 2949  ty--->B   LV--->A  Device type---> disk  Tag ---> TAG202012t105306  (tag is the important using tag only we can perform restore and recovery in backup)

$ RMAN> list backup TAG202012t105306;

list of backup piece name :/u01/app/oracle/fast_receovery_area/proddb/backupset/2020-07-20/TAG202012t105306.bkp

$ scp/u01/app/oracle/fast_recovery_area/proddb/backupset/2020-07-20/TAG202012t105306.bkp oracle@192.168.0.100:/u01/app/oracle/fast_recovery_area/proddb/backupset/2020-07-20/

Target server

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

$ mkdir-p /u01/app/oracle/fast_recovery_area/proddb/backupset/2020-07-20   (target server directory may not exist create this directory)

archive backup pieces ---> same location as source

$ scp/u01/app/oracle/fast_recovery_area/proddb/backupset/2020-07-20/TAG202012t105306.bkp oracle@192.168.0.100:/u01/app/oracle/fast_recovery_area/proddb/backupset/2020-07-20/

Start cloning 

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

export environment variables connect to rman

Target Server

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

$ env |grep ora

 ORACLE_SID =proddb

ORACLE_HOME= '/u01/app/oracle/product/11.2.0/db_home-1

$ rman target /

connected to target database (not started)

$ RMAN> startup mount;

get the last scn available in the archive log backup

$RMAN> list backup of archivelog all;

last archive log--> 6  next scn---.> 960034

-Rename the DB redolog files so they can be created in new location

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

sql> alter database rename file '/u01/app/oracle/oradata/proddb/redo01.log' to '/u01/app/oracle/oradata/testdb/redo01.log';

Target server

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

sql> select member from v$logfle;

member

-------

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

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

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

we have to change the datafile proddb to testdb redo03,02,01

Restore the datafiles to new location

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

run {

set newname for datafile1 to '/u01/app/oracle/oradata/testdb/system01.dbf';

set newname for datafile1 to '/u01/app/oracle/oradata/testdb/sysaux01.dbf';

set newname for datafile1 to '/u01/app/oracle/oradata/testdb/undotbs01.dbf';

set newname for datafile1 to '/u01/app/oracle/oradata/testdb/user01.dbf';

set newname for datafile1 to '/u01/app/oracle/oradata/testdb/example01.dbf';

restore datafile from TAG202012t105306;

switch datafile all;

recover database untill scn 960034;

alter database open resetlogs;

}

Renaming Database after cloning

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

sql> select name,open_mode from v$database;

name--->proddb  open_mode->read_write 

hostname  dctest.chaitanya.com

 ( we have to rename the proddb to testdb we are in the testserver)

Take control file backup to trace with resetlog options

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

sqlplus '/as sysdba'

sql> alter database backup controlfile to trace as'/tmp/ create_ctrol_file.sql';

sql> database altered

sql> shut immedaite ;

sql>exit

create pfile for new dbid

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

$cd $ORACLE_HOME/dbs

$ ls -ltr

initproddb.ora

$ mv initproddb.ora inittestdb.ora

$ vi inittestdb.ora        (one parameter db_name =proddb change to testdb   db_name=testdb save and exit vi editor)

$ export ORACLE_SID= testdb 

sqlplus '/as sysdba'

startup instance in nomount stage

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

sql>startup nomount;

exit

$ cd/tmp

ls -lrt

create_ctrolfile.sql           (copy the create_ctrolfile.sql to new note pad and paste it)

$ cat create_ctrolfile.sql

Edit the control file in trace location with new sid  

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

create control file set database  "testdb" resetlogs archivelog

remove reuse and set change norestlogs to resetlogs

it look like this when u open file 

maxlogfile

--

---

---

log file

--

--

group1 /u01---

group2 /u01---

group3 /u01---

datafile 

 

------/u01

---

---

---

---

character set WE8MSWIN1252

$cd  $ ORACLE_HOME/dbs  (remove the oldcontrol file)

$ ls -lrt

$ inittestdb.ora

cat inittsetdb.ora

$ rm-rf /u01/app/oracle/oradata/testdb/control01.ctl /u01/app/oracle/oradata/testdb/control02.ctl

Create controlfiles for new instance

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

sqlplus '/as sysdba'

sql> select instance_name ,status from v%instance;

instance_name -->testdb   

status--->started

we have created control statement create_ctrol.sql and run 

 it will display controlfile created

sql> alter database open resetlogs

sql>select name,open_mode from v$database;

name---> testdb

open_mode---> read,write

sql> select name from v$ controlfile;

sql>select member from v$ logfile; 

cloning is done

Note: it will differ in your environment it may be u r development or testing or production and also directory structure in linux  mountpoints and IP address but the process is same.  

THANK YOU FOR VIEWING FOR MORE UPDATES VISIT MY BLOG 

http://chaitanyaoracledba.blogspot.com/