Tag Archives: data

11g, Oracle, Oracle 10g, oracle 9i

Determining actual size of data stored in an Oracle RDBMS.

Leave a comment

When using any RDBMS there exist a distinct difference between the actual size of the data and the physical size of the database. In this article we will demonstrate how to get the actual size of the data stored in an Oracle RDBMS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 01 17:17:02 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Select the sum of all bytes allocated in the view DBA_SEGMENTS, returns the actual space being consumed on disk.

SQL> select sum(bytes)/1024/1024 from dba_segments;

SUM(BYTES)/1024/1024
——————–
316865.805

4. This completes the determining the actual size of data in an Oracle database.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, RMAN

Oracle RMAN recovery of files on a lost mount point

Leave a comment

Oracle’s Recovery Manger (RMAN), originally released in Oracle 8, is a proprietary utility used for the backup and recovery of Oracle databases. Given a good Oracle RMAN backup, A DBA can recover from any disaster which may occur to their database, to include: loss of controlfiles, loss of datafiles, corruption of datafiles, loss of the entire database and even provides the ability to clone a database. In this article we give detailed instructions on the recovery of a lost mount point in Oracle 9i, however these instructions will work in versions 10g and 11g.

1. Logon to the Oracle server as the Oracle software owner and set the ORACLE_SID.

[oracle@testdb02 ORCL]$ su – oracle
Password:
[oracle@testdb02 ~]$ export ORACLE_SID=ORCL
[oracle@testdb02 ~]$

2. Logon to SQL*PLUS and obtain the location of all datafiles from the table dba_data_files. Note: There exist two data files on /u05 that can be used for this test.

[oracle@testdb02 ~]$ SQL*PLUS ‘/ as sysdba’
SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:03:06 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL>

SQL> select tablespace_name, file_name from dba_data_files order by tablespace_name;
TABLESPACE_NA FILE_NAME
————- ————————————————–
SYSTEM /u01/oradata/ORCL/system01.dbf
TOOLS /u05/oradata/ORCL/tools01.dbf
UNDOTBS1 /u04/oradata/ORCL/undo01.dbf
USERS /u05/oradata/ORCL/users01.dbf

SQL>

3. Exit SQL*PLUS and navigate to /u05/oradata/ORCL, create temp directory and move all files located on the mount point /u05 to the new directory. This will simulate the loss of all Oracle related files on /u05. In a real situation, you would have your SA recreate the /u05 mount point.

[oracle@testdb02 ~]$ cd /u05/oradata/ORCL
[oracle@testdb02 ORCL]$ ls
sqlnet.log temp02.dbf tools01.dbf users01.dbf
[oracle@testdb02 ORCL]$ mkdir temp
[oracle@testdb02 ORCL]$ mv tools01.dbf temp/
[oracle@testdb02 ORCL]$ mv users01.dbf temp/
[oracle@testdb02 ORCL]$ ls
sqlnet.log temp temp02.dbf
[oracle@testdb02 ORCL]$

4. Log back into SQL*PLUS and attempt to shutdown the database. You will receive an indicating that the files on mount point /u05 cannot be located.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:27:47 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> shutdown
ORA-01116: error in opening database file 3
ORA-01110: data file 3: ‘/u05/oradata/ORCL/users01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>

5. Exit SQL*PLUS and force a shutdown of the database by killing the smon process.

[oracle@testdb02 ORCL]$ ps -ef|grep smon
oracle 12677 1 0 21:04 ? 00:00:00 ora_smon_ORCL
oracle 14115 13009 0 21:35 pts/4 00:00:00 grep smon
[oracle@testdb02 ORCL]$ kill -9 12677
[oracle@testdb02 ORCL]$ ps -ef|grep ORCL
oracle 12023 11973 0 20:51 pts/6 00:00:00 vim ORCL_RMAN.sh
oracle 14137 13009 0 21:36 pts/4 00:00:00 grep ORCL
[oracle@testdb02 ORCL]$

6. Logon to SQL*PLUS and perform open mount operation, so the recovery operation can be performed.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:37:20 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 144670496 bytes
Fixed Size 741152 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 1323008 bytes
Database mounted.
SQL>

7. Exit SQL*PLUS and enter the RMAN utility connecting to the target database and recovery catalog.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ RMAN

Recovery Manager: Release 9.2.0.8.0 – 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> connect target sys/h1g0alz

connected to target database: ORCL (DBID=1373796530)

RMAN> connect catalog RMAN/RMAN@RMANDB

connected to recovery catalog database

RMAN>

8. Issue the restore database command, which will restore all database components.

RMAN> restore database;

Starting restore at 12-JUN-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/ORCL/system01.dbf
restoring datafile 00002 to /u04/oradata/ORCL/undo01.dbf
restoring datafile 00003 to /u05/oradata/ORCL/users01.dbf
restoring datafile 00004 to /u05/oradata/ORCL/tools01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220129_1_26_1_0qjiooh1.dbf tag=BACKUP_FULL_ORCL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-JUN-08

RMAN>

9. Issue the recover database command, which will apply all archive redo logs since the last backup set.

RMAN> recover database;

Starting recover at 12-JUN-08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 28 is already on disk as file /u01/oradata/ORCL/archive/ORCL_ARCH28.arc
archive log thread 1 sequence 29 is already on disk as file /u06/oradata/ORCL/archive/ORCL_ARCH29.arc
archive log filename=/u01/oradata/ORCL/archive/ORCL_ARCH28.arc thread=1 sequence=28
media recovery complete
Finished recover at 12-JUN-08

RMAN>

10. Exit out of RMAN, enter into SQL*PLUS and open the database. Once the database has successfully opened, you know that all database files are in a consistent state.

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:43:14 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database open;

Database altered.

SQL>

This completes recovery of all files on a single mount point or directory.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, RMAN

RMAN recovery of lost or corrupted tablespace datafile

Leave a comment

Oracle’s Recovery Manger (RMAN), originally released in Oracle 8, is a proprietary utility used for the backup and recovery of Oracle databases. Given a good Oracle RMAN backup, A DBA can recover from any disaster which may occur to their database, to include: loss of controlfiles, loss of datafiles, corruption of datafiles, loss of the entire database and even provides the ability to clone a database. In this article we give detailed instructions on the recovery of a lost or corrupted tablespace datafile in Oracle 9i, however these instructions will work in versions 10g and 11g.

1. Logon to the oracle server as the oracle software owner.

2. Set the ORACLE_SID and logon to sqlplus as sysdba.

[oracle@testdb02 scripts]$ su – oracle
Password:
[oracle@testdb02 ~]$ export ORACLE_SID=ORCL
[oracle@testdb02 ~]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:03:06 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL>

3. Query the dba_data_files table for tablespaces and datafiles. We are looking for a datafile which can be moved out of the location specified in the controlfile, resulting in tablespace corruption

SQL> select tablespace_name, file_name from dba_data_files order by tablespace_name;
TABLESPACE_NA FILE_NAME
————- ————————————————–
SYSTEM /u01/oradata/ORCL/system01.dbf
TOOLS /u05/oradata/ORCL/tools01.dbf
UNDOTBS1 /u04/oradata/ORCL/undo01.dbf
USERS /u05/oradata/ORCL/users01.dbf

SQL>

4. Shutdown the database with immediate.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

5. Exit SQL*PLUS and move to directory /u05/oradata/ORCL. Create a temp directory and move the datafile users01.dbf into the new temp directory.

[oracle@testdb02 ~]$ cd /u05/oradata/ORCL
[oracle@testdb02 ORCL]$ ls
temp02.dbf tools01.dbf users01.dbf
[oracle@testdb02 ORCL]$
[oracle@testdb02 ORCL]$ mkdir temp
[oracle@testdb02 ORCL]$
[oracle@testdb02 ORCL]$ mv users01.dbf temp/
[oracle@testdb02 ORCL]$ ls
temp temp02.dbf tools01.dbf
[oracle@testdb02 ORCL]$

6. Log back into sqlplus and startup your Oracle instance. Note: the error received referring to datafile users01.dbf, there exists a reference to this datafile in the controlfile, however Oracle could not locate the file. This results in the tablespace USERS being in an unusable inconsistent state.

[oracle@testdb02 ORCL]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:48:14 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 144670496 bytes
Fixed Size 741152 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 1323008 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 – see DBWR trace file
ORA-01110: data file 3: ‘/u05/oradata/ORCL/users01.dbf’

SQL>

7. To resolve the inconsistence, we must startup the RMAN utility and connect to both the target database and recovery catalog.

[oracle@testdb02 ORCL]$ rman

Recovery Manager: Release 9.2.0.8.0 – 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/h1g0alz

connected to target database: ORCL (DBID=1373796530)

RMAN> connect catalog rman/rman@RMANDB

connected to recovery catalog database

RMAN>

8. From within the RMAN utility, issue the command to ‘restore tablespace ’. In this example the tablespace was USERS. The restore command replaces the tablespace’s datafiles in your database with the copy in your backup set.

RMAN> restore tablespace users;

Starting restore at 12-JUN-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=15 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u05/oradata/ORCL/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220129_1_26_1_0qjiooh1.dbf tag=BACKUP_FULL_ORCL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-JUN-08

RMAN>

9. From within the RMAN utility, issue the command ‘recover tablespace ’. The recover command applies all archive redo logs which have occurred since the last backup set.

RMAN> recover tablespace users;

Starting recover at 12-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK

starting media recovery

archive log thread 1 sequence 28 is already on disk as file /u01/oradata/ORCL/archive/ORCL_ARCH28.arc
archive log thread 1 sequence 29 is already on disk as file /u06/oradata/ORCL/archive/ORCL_ARCH29.arc
archive log filename=/u01/oradata/ORCL/archive/ORCL_ARCH28.arc thread=1 sequence=28
media recovery complete
Finished recover at 12-JUN-08

RMAN>

10. Exit the RMAN utility and enter SQL*PLUS, alter the database to an open status. You know that the tablespace USERS is now in a consistent state with the controlfiles when the database opens successfully

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:03:11 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database open;

Database altered.

SQL>

This completes successful recovery of a tablespace.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com