Tablespace Backup Using RMAN in Oracle

TASK: To Take Backup of the USERS TABLESPACE using RMAN

[oracle@server ~]$ export ORACLE_SID=AR

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

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 4 10:37:22 2011

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1220796 bytes

Variable Size 167776068 bytes

Database Buffers 427819008 bytes

Redo Buffers 7163904 bytes

Database mounted.

Database opened.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@server ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jan 4 10:38:07 2011

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

connected to target database: AR (DBID=1270245282)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

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

Note: Use the SHOW command to display the CONFIGURE commands used to set the current RMAN configuration for one or more databases. RMAN default configurations are suffixed with #default.

RMAN> BACKUP TABLESPACE USERS;

Starting backup at 04-JAN-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

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

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

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

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/04/2011 10:42:33

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

Note: Here We try to take Backup of USERS TABLESPACE even though the datafile backupset has taken it is a incomplete Backup because the Database is in NOARCHIVELOG mode. For that change the database mode to ARCHIVELOG mode.

RMAN> quit

Recovery Manager complete.

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

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 4 11:03:38 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> startup

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> SP2-0223: No lines in SQL buffer.

Note : Now the SQL Buffer cleared automatically because of instance shutdown that why the SP2-0223: No lines in SQL buffer came.

For Eg. : SQL> connect / as sysdba

SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE

  2  FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%';

SQL>LIST

  1  SELECT FIRST_NAME, LAST_NAME, HIRE_DATE

  2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%'

SQL>RUN

( Output of the Query )

SQL> CLEAR BUFFER

buffer cleared

SQL> LIST

SP2-0223: No lines in SQL buffer.

Note: Buffer Cleared

SQL> startup mount

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1220796 bytes

Variable Size 171970372 bytes

Database Buffers 423624704 bytes

Redo Buffers 7163904 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

Note : Now the Database changed into ARCHIVELOG mode.

SQL> alter database open;

Database altered.

Note: Press Ctrl + D to Disconnect from SQL

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@server ~]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jan 4 11:05:02 2011

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

connected to target database: AR (DBID=1270245282)

RMAN> backup tablespace USERS;

Starting backup at 04-JAN-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=145 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00004 name=/u01/app/oracle/oradata/AR/USERS.dbf

input datafile fno=00005 name=/u01/app/oracle/oradata/AR/Users1.d

input datafile fno=00006 name=/u01/app/oracle/oradata/AR/Users2.dbf

input datafile fno=00007 name=/u01/app/oracle/oradata/AR/Users3.dbf

channel ORA_DISK_1: starting piece 1 at 04-JAN-11

channel ORA_DISK_1: finished piece 1 at 04-JAN-11

piece handle=/u01/app/oracle/flash_recovery_area/AR/backupset/2011_01_04/o1_mf_nnndf_TAG20110104T110522_6l5ddtjp_.bkp tag=TAG20110104T110522 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 04-JAN-11

Note : Backup of USERS TABLESPACE taken , that was stored in the default location /u01/app/oracle/flash_recovery_area/

RMAN> backup current controlfile to destination '/u01/app/oracle/FRA';

Starting backup at 04-JAN-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

channel ORA_DISK_1: starting piece 1 at 04-JAN-11

channel ORA_DISK_1: finished piece 1 at 04-JAN-11

piece handle=/u01/app/oracle/FRA/AR/backupset/2011_01_04/o1_mf_ncnnf_TAG20110104T115441_6l5h99q1_.bkp tag=TAG20110104T115441 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 04-JAN-11

Note: Backup of the Controlfile taken in the folder name FRA given by the user.

RMAN> backup tablespace USERS to destination '/u01/app/oracle/FRA';

Starting backup at 04-JAN-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00004 name=/u01/app/oracle/oradata/AR/USERS.dbf

input datafile fno=00005 name=/u01/app/oracle/oradata/AR/Users1.d

input datafile fno=00006 name=/u01/app/oracle/oradata/AR/Users2.dbf

input datafile fno=00007 name=/u01/app/oracle/oradata/AR/Users3.dbf

channel ORA_DISK_1: starting piece 1 at 04-JAN-11

channel ORA_DISK_1: finished piece 1 at 04-JAN-11

piece handle=/u01/app/oracle/FRA/AR/backupset/2011_01_04/o1_mf_nnndf_TAG20110104T115524_6l5hbn3s_.bkp tag=TAG20110104T115524 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 04-JAN-11

Note : Backup of USERS TABLESPACE Taken in the Folder name FRA manually given by the user .

To Create Tablespace & Datafile in Oracle

TASK: To Create A Tablespace ( Etc. USERS) and assign size of the Tablespace ( 20M ) then Add 3 Datafiles into the USERS Tablespace and the Total Size of USERS Tablespace should be 80M and Create a VIEW.

[ oracle@localhost ]~ export ORACLE_SID=AR

[ oracle@localhost ]~ sqlplus ‘/as sysdba’

SQL> startup

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1220796 bytes

Variable Size 167776068 bytes

Database Buffers 427819008 bytes

Redo Buffers 7163904 bytes

Database mounted.

Database opened.

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC

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

0 SYSTEM YES NO YES

1 UNDOTBS1 YES NO YES

2 SYSAUX YES NO YES

3 TEMP NO NO YES

SQL> create tablespace USERS datafile '/u01/app/oracle/oradata/AR/USERS.dbf' size 20m;

Tablespace created.

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC

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

0 SYSTEM YES NO YES

1 UNDOTBS1 YES NO YES

2 SYSAUX YES NO YES

3 TEMP NO NO YES

4 USERS YES NO YES

SQL> desc v$tablespace;

Name Null? Type

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

TS# NUMBER

NAME VARCHAR2(30)

INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)

BIGFILE VARCHAR2(3)

FLASHBACK_ON VARCHAR2(3)

ENCRYPT_IN_BACKUP VARCHAR2(3)

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC

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

0 SYSTEM YES NO YES

1 UNDOTBS1 YES NO YES

2 SYSAUX YES NO YES

3 TEMP NO NO YES

4 USERS YES NO YES

SQL> select a.TABLESPACE_NAME,

a.BYTES bytes_used,

b.BYTES bytes_free,

b.largest,

round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used

from

(

select TABLESPACE_NAME,

sum(BYTES) BYTES

from dba_data_files

group by TABLESPACE_NAME

)

a,

(

select TABLESPACE_NAME,

sum(BYTES) BYTES ,

max(BYTES) largest

from dba_free_space

group by TABLESPACE_NAME

)

b

where a.TABLESPACE_NAME=b.TABLESPACE_NAME

order by ((a.BYTES-b.BYTES)/a.BYTES) desc ;

TABLESPACE_NAME BYTES_USED BYTES_FREE LARGEST PERCENT_USED

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

SYSTEM 209715200 35463168 35463168 83.09

SYSAUX 104857600 56885248 56885248 45.75

USERS 20971520 20905984 20905984 .31

SQL> select OWNER,

SEGMENT_NAME,

SEGMENT_TYPE,

TABLESPACE_NAME,

BYTES

from dba_segments

where TABLESPACE_NAME = 'SYSTEM'

and OWNER not in ('SYS','SYSTEM')

order by OWNER, SEGMENT_NAME ;

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE TABLESPACE_NAME BYTES

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

OUTLN

OL$

TABLE SYSTEM 16384

OUTLN

OL$HINTS

TABLE SYSTEM 16384

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE TABLESPACE_NAME BYTES

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

OUTLN

OL$HNT_NUM

INDEX SYSTEM 16384

OUTLN

OL$NAME

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE TABLESPACE_NAME BYTES

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

INDEX SYSTEM 16384

OUTLN

OL$NODES

TABLE SYSTEM 16384

OUTLN

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE TABLESPACE_NAME BYTES

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

OL$SIGNATURE

INDEX SYSTEM 16384

OUTLN

SYS_IL0000000453C00021$$

LOBINDEX SYSTEM 16384

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE TABLESPACE_NAME BYTES

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

OUTLN

SYS_LOB0000000453C00021$$

LOBSEGMENT SYSTEM 24576

8 rows selected.

SQL> select TABLESPACE_NAME,

sum(BYTES) Total_free_space,

max(BYTES) largest_free_extent

from dba_free_space

group by TABLESPACE_NAME;

TABLESPACE_NAME TOTAL_FREE_SPACE LARGEST_FREE_EXTENT

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

SYSAUX 56885248 56885248

USERS 20905984 20905984

SYSTEM 35422208 35422208

SQL> select * from dba_free_space;

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

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

SYSTEM 1 21277 35422208 4324 1

SYSAUX 3 5857 56885248 6944 3

USERS 4 9 20905984 2552 4

SQL> select TABLESPACE_NAME,

sum(BYTES) BYTES

from dba_data_files

group by TABLESPACE_NAME;

TABLESPACE_NAME BYTES

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

UNDOTBS1 20971520

SYSAUX 104857600

USERS 20971520

SYSTEM 209715200

SQL> create view dbdatafiles as select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name;

View created.

SQL> select * from dbdatafiles;

TABLESPACE_NAME BYTES

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

UNDOTBS1 20971520

SYSAUX 104857600

USERS 20971520

SYSTEM 209715200

SQL> alter tablespace USERS add datafile '/u01/app/oracle/oradata/AR/Users1.dbf ' size 20m;

Tablespace altered.

SQL> select * from dbdatafiles;

TABLESPACE_NAME BYTES

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

UNDOTBS1 20971520

SYSAUX 104857600

USERS 41943040

SYSTEM 209715200

Note : After Adding a Datafile Users1.dbf the Tablespace USERS Size Increased.

SQL> alter tablespace USERS add datafile '/u01/app/oracle/oradata/AR/Users2.dbf ' size 20m;

Tablespace altered.

SQL> alter tablespace USERS add datafile '/u01/app/oracle/oradata/AR/Users3.dbf ' size 20m;

Tablespace altered.

SQL> select * from dbdatafiles;

TABLESPACE_NAME BYTES

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

UNDOTBS1 20971520

SYSAUX 104857600

USERS 83886080

SYSTEM 209715200

Note: Here the USERS Tablespace 83886080 bytes ie. 80M Increased.

To Open Account Expired & Locked in Oracle

SQL> select username,account_status from dba_users;

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
SYSMAN OPEN
RAJESH OPEN
ALWIN OPEN
OUTLN EXPIRED & LOCKED
MDSYS EXPIRED & LOCKED
ORDSYS EXPIRED & LOCKED
CTXSYS EXPIRED & LOCKED

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
ANONYMOUS EXPIRED & LOCKED
EXFSYS EXPIRED & LOCKED
DMSYS EXPIRED & LOCKED
WMSYS EXPIRED & LOCKED
XDB EXPIRED & LOCKED
ORDPLUGINS EXPIRED & LOCKED
SI_INFORMTN_SCHEMA EXPIRED & LOCKED
OLAPSYS EXPIRED & LOCKED
MDDATA EXPIRED & LOCKED
DIP EXPIRED & LOCKED
SCOTT EXPIRED & LOCKED

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
TSMSYS EXPIRED & LOCKED

23 rows selected.

SQL> alter user mdsys account unlock;

User altered.

SQL> conn mdsys/mdsys
ERROR:
ORA-28001: the password has expired


Changing password for mdsys
New password:
Retype new password:
SP2-0650: New passwords do not match
Password unchanged
Warning: You are no longer connected to ORACLE.
SQL> conn mdsys/RAJESH
ERROR:
ORA-01017: invalid username/password; logon denied

Note: Here I Typed mismatched Password

SQL> conn /as sysdba;
Connected.
SQL> select username,account_status from dba_users;

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
SYSMAN OPEN
RAJESH OPEN
ALWIN OPEN
MDSYS EXPIRED
OUTLN EXPIRED & LOCKED
ORDSYS EXPIRED & LOCKED
CTXSYS EXPIRED & LOCKED

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
ANONYMOUS EXPIRED & LOCKED
EXFSYS EXPIRED & LOCKED
DMSYS EXPIRED & LOCKED
WMSYS EXPIRED & LOCKED
XDB EXPIRED & LOCKED
ORDPLUGINS EXPIRED & LOCKED
SI_INFORMTN_SCHEMA EXPIRED & LOCKED
OLAPSYS EXPIRED & LOCKED
MDDATA EXPIRED & LOCKED
DIP EXPIRED & LOCKED
SCOTT EXPIRED & LOCKED

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
TSMSYS EXPIRED & LOCKED

23 rows selected.

SQL> conn mdsys/RAJESH
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn mdsys/mdsys
ERROR:
ORA-28001: the password has expired


Changing password for mdsys
New password:
Retype new password:
Password changed
Connected.
SQL> show user;
USER is "MDSYS"
SQL> conn / as sysdba;
Connected.
SQL> select username,account_status from dba_users;

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
MDSYS OPEN
DBSNMP OPEN
SYSMAN OPEN
RAJESH OPEN
ALWIN OPEN
OUTLN EXPIRED & LOCKED
ORDSYS EXPIRED & LOCKED
CTXSYS EXPIRED & LOCKED

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
ANONYMOUS EXPIRED & LOCKED
EXFSYS EXPIRED & LOCKED
DMSYS EXPIRED & LOCKED
WMSYS EXPIRED & LOCKED
XDB EXPIRED & LOCKED
ORDPLUGINS EXPIRED & LOCKED
SI_INFORMTN_SCHEMA EXPIRED & LOCKED
OLAPSYS EXPIRED & LOCKED
MDDATA EXPIRED & LOCKED
DIP EXPIRED & LOCKED
SCOTT EXPIRED & LOCKED

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
TSMSYS EXPIRED & LOCKED

23 rows selected.