Hi frnds i hope this video is help ful to u.
http://www.youtube.com/watch?v=3oGt0vzwcH4&playnext_from=TL&videos=7j-pisMSdjs
Wednesday, July 21, 2010
Monday, July 19, 2010
Ebook for Oracle DBA
I think this book will helpful for those who are learning the Oracle DBA
http://www.mediafire.com/?avynmnw1oxy75d9
http://www.mediafire.com/?avynmnw1oxy75d9
Most Common Questions
1 How do you verify and validate the Datafile which is backed up?
Answers
Answers
In rman list recoverable backup of database
Use RMAN Backup validate database.
It will performs a validation of the datafiles by checking for logical and physical block corruption.
No backupsets are created.
To verify that the datafile is particularly backed up or not.
Sql>select * from v$backup-->This will shows the status of a particular file.
sql>select * from v$recover_file-->This is show whether any file has to be recovered.
Sql>select * from v$backup-->This will shows the status of a particular file.
sql>select * from v$recover_file-->This is show whether any file has to be recovered.
use dbverify to validate the backups.thanks
The DBVERIFY Utility
An OS executable (dbv in $ORACLE_HOME/bin) used to verify datafiles
Works against datafiles live or backup But doesnot work on Backup sets...
Works for datafiles on raw devices but you must give it a range of blocks to look at.
In the output it refers to blocks as pages . Block 5 would be page 5.
RMAN performs its own validation when backing up so dbv is not necessary. It can be used on user backups (aka non-RMAN backups).
If a block is influx then DBV was forced to check it multiple times because it was in use.
2 How much time will it take a hotbackup if the batabase size is 60Gb?
Answers
It depends on the datafiles related to database at OS Level.
Depends upon number of servers and RAM.
Depends on the amount of data inside the database.
Its all upto the Kind of Server and No of server Parameters configured and the type of backup device
Depends! On the I/O at that time...
Hot backup involves many Steps including - taking the particular Tablespace in begin backup mode and then copying the file to Backup directory.
It all depends on the Server Configuration & I/O at that time. Generally the DB will be stored on EMC - where EMC connects to large number of Servers.
3. What is mean live dead lock? How to prevent it.?
Answers
When there r 2 or more process run at a time and want to same resource then if one process take resource and othere process want same resource then there will be dedlock occour.
A deadlock occurs when two or more users are waiting for data locked by each other(Not commited by user01).
To fix this recreate the segment with higher INITTRANS and/or PCTFREE values. This will allow more space in the data blocks for Oracle to allocate more transaction entries (24 bytes at a time) when required
4. What is the difference between recovery and restoring of the database?
Answers
Restoring a database is copying the physical files from a backup medium (disk or tape) to the appropriate file locations for database operation
Recovery is process of updating database files restored from backup w/changes made to the database since backup, typically using redo log files.(Restored backup files + changes = Recovery)
5. What are the different tools available for hot backups?
Answers
Hot backup can be done by either RMAN,User Managed Backups by puting tablespace in backup mode my OEM which does the same as the user managed backup.But the Backup depends upon the size of the database you are using . if the database size in TB the RMAN backup will take more than 10 hours to complete and if the database is critical you can' wait for long to go for so long in this case their are special backup techniques which are given by vendors like TIVOLI and Netbackup they provide BC Vol backup called Business content Volumn Sync which copies a snapshot of the primary data to another place and backsup the database from one SAN to another with in 15 min for 2 TB of database and is the preferable method for big companies.
6 How can I Export 9i data in 8i ? I mean when I Export from 9i and Import in 8i, I face errors. what sould i do?
Answers
Answers
Follow these steps
1) Install Oracle 8i Client
2) Export database from Oracle 8i Client PC
3) Import data into Oracle 8i Database.
1) Install Oracle 8i Client
2) Export database from Oracle 8i Client PC
3) Import data into Oracle 8i Database.
execute catexp.sql of 8 in oracle 9i then take export and then import in 8 database.
7. what is the difference between media recovery and crash recovery..?
Answers
Media recovery is a process to recover database from backup when physical disk failure occure.
Cash recovery is a automated process take care by oracle when instance failure occure.
Cash recovery is a automated process take care by oracle when instance failure occure.
8. what do you mean by MEDIA RECOVERY..?
Answers
Loss of tablespaces, datafiles, controlfile, tables it is media failure and we need to recover such a tablespaces, datafiles, controlfile or tablespace through latest backup
It is MEDIA recovery.
It is MEDIA recovery.
9. what is the disk migration?what isthe steps involved in diskmigration?
Answers
Disk migration is noting but migration of data from one OS dependent database to another Dependent database. The steps involved in this are
- first go to your target database and export all your data into flat files
-next in the destination database during the installation of the database it asks for data source instead of giving the data of the oracle provided give the path of the flat file you exported previously .
10. What are the steps involved in Database Startup ?
Answers
Start Instance, Mount database and open for user access
Step1 :: Read parameter file and verify parameters(Memory Initialization & Background Process started)
Step 2 :: Mount the database and Read the control file information but No verification against this data will be done at this stage.
Step 3:: Open database and before opening verify Control file Contents and synchronize Redo files and data files with the respect to Transactions and then open the database.
Step1 :: Read parameter file and verify parameters(Memory Initialization & Background Process started)
Step 2 :: Mount the database and Read the control file information but No verification against this data will be done at this stage.
Step 3:: Open database and before opening verify Control file Contents and synchronize Redo files and data files with the respect to Transactions and then open the database.
11. What are the steps involved in Database Shutdown?
Answers
Close the Database, Dismount the Database and Shutdown the
Instance.
Different types of Shutdown
There are four types of shutdown
Shutdown Normal - Normal is the default mode, here new user can't logon to the database, but the current user can start the new transaction and work till he wants, the database shutdown only after the last user logs off.
Shutdown Transactional - here new user can't logon to the database, and the current user can't start the new transaction, once the current transaction complets, database shutdown.
Shutdown Immediate - here new user can't logon to the database, user can't start the new transaction, the current transactions rolls back, and database shutdown.
Shutdown Abort - here new user can't logon to the database, user can't start new transaction, the current transaction is not rolled back, database shutdown's.
While shutting down oracle updates data file header and control file with the current checkpoint informanation, Except Shutdown Abort the checkpoint is same in datafile header and control file it means database is consistance and it is clean shutdown. If the checkpoint is not same at both locations then instance recovery required at next startup.
12. What is Restricted Mode of Instance Startup ?
Answers
By starting Instance in restricted mode it will not allow all users to access and only users with restriction privilege will be allowed to access.
This will be done time of make some data changes so that no users should be allowed to access data on time of changes happening
This will be done time of make some data changes so that no users should be allowed to access data on time of changes happening
To Enable Restricted Session
Alter system enable restricted session;
To Disable Restricted Session
Alter system disable restricted session;
To Start the Database in Restricted Mode
STARTUP RESTRICT
Alter system enable restricted session;
To Disable Restricted Session
Alter system disable restricted session;
To Start the Database in Restricted Mode
STARTUP RESTRICT
Answers
Exclusive Mode If the first instance that mounts adatabase does so in exclusive mode, only that Instance canmount the database. Parallel Mode If the first instance that mounts adatabase is started in parallel mode, other instances thatare started in parallel mode can also mount the database.14. What is Full Backup ?
Answers
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.
15. Can Full Backup be performed when the database is open ?
Answers
Yes we can perform the database backup when database is up and running. We can make it by using user managed backups and RMAN backups.
use we can take full backup while database is open. By using hot backup. Take backup of all tablespaces using hotback and take backup on control file using alter database backup controlfile to trace; and then take backup of pfile using: create pfile from spfile; In this way Full backup can be performed when the database is open;
16. What is Partial Backup ?
Answers
A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
17. What is On-line Redo Log?
18. What is Mirrored on-line Redo Log ?
Answers
A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.
19. What is Archived Redo Log ?
Answers
Archived Redo Log is defined as a copy of one of the filled members of an online redo log group. It is maintained when the database is in the ARCHIVELOG mode
20. What are the advantages of operating a database in ARCHIVELOG mode over operating it?
Answers
If you run in NOARCHIVELOG mode, if your database crashes, you can only recover to the point of time of your last backup.
ARCHIVELOGMODE allows you to recover to the point of the crash. Downside is it takes more diskspace and can cause the db to freeze if that disk fills up. If you have a production system with no tolerance for loss, this isn't an option.
21. What are the steps involved in Instance Recovery ?
Answers
Rolling forward to recover data that has not been recordedin data files, yet has been recorded in the on-line redolog, including the contents of rollback segments. Rolling back transactions that have been explicitly rolledback or have not been committed as indicated by the rollbacksegments regenerated in step a. Releasing any resources (locks) held by transactions inprocess at the time of the failure. Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instancefailure.
Sunday, July 18, 2010
Some of the Oracle Tips
Show Oracle Version and installed Options
The data dictionary views V$OPTION and V$VERSION can be used to get the current Oracle version and the installed options. Click here for the Script.
select banner
from sys.v$version;
select parameter
from sys.v$option
where value = 'TRUE';
select parameter
from sys.v$option
where value <> 'TRUE';
begin
dbms_output.put_line('Specific Port Information:
'||dbms_utility.port_string);
end;
/
Here is the produced Output from the Script:
Oracle Version:
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.5.0.0 - Production
TNS for 32-bit Windows: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
Installed Options:
PARAMETER
----------------------------------------------------------------
Objects
Advanced replication
Bit-mapped indexes
Connection multiplexing
Connection pooling
Database queuing
Incremental backup and recovery
Instead-of triggers
Parallel backup and recovery
Parallel execution
Parallel load
Point-in-time tablespace recovery
Fine-grained access control
N-Tier authentication/authorization
Function-based indexes
Plan Stability
Online Index Build
Coalesce Index
Managed Standby
Materialized view rewrite
Materialized view warehouse refresh
Database resource manager
Spatial
Visual Information Retrieval
Export transportable tablespaces
Transparent Application Failover
Fast-Start Fault Recovery
Sample Scan
Duplexed backups
Java
Not Installed Options:
PARAMETER
----------------------------------------------------------------
Partitioning
Parallel Server
Specific Port Information: IBMPC/WIN_NT-8.1.0
Inserting in two or more tables with SQL*Loader
If you want to load data from an ASCII or EBCDIC file into an Oracle Database -- SQL*Loader is the tool. In this article we want to show, how to distribute the rows in the file in two tables depending on a value at a specified character position in the file.
As an example, insert the row in table A if the value ra is 2 on position 7, insert the row in table B if ra is 1 (see the following figure).
You need the following SQL*Loader controlfile to accomplish this task.
OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET WE8EBCDIC500
INFILE '$BOTENFILE' "fix 86"
BADFILE '$BOTENFILE.bad'
DISCARDFILE '$BOTENFILE.dsc'
REPLACE
INTO TABLE B
WHEN ra = X'F1'
(
astrnr POSITION (1:6) INTEGER EXTERNAL,
ra POSITION (7:7) INTEGER EXTERNAL,
mc POSITION (8:8) INTEGER EXTERNAL,
astrg POSITION (9:36) CHAR,
astrgk POSITION (37:61) CHAR "UPPER(LTRIM(RTRIM(:astrgk)))",
astamm POSITION (62:71) CHAR,
aadrplz POSITION (72:77) INTEGER EXTERNAL,
astrart POSITION (78:79) INTEGER EXTERNAL,
apra POSITION (80:81) INTEGER EXTERNAL,
asprc POSITION (82:82) INTEGER EXTERNAL,
gplz POSITION (83:86) INTEGER EXTERNAL,
line SEQUENCE (1,1)
)
INTO TABLE A
WHEN ra = X'F2'
(
astrnr POSITION (1:6) INTEGER EXTERNAL,
ra POSITION (7:7) INTEGER EXTERNAL,
abzart POSITION (8:8) INTEGER EXTERNAL,
abbez POSITION (9:11) CHAR "(LTRIM(RTRIM(:abbez)))",
ahnrvn POSITION (12:15) INTEGER EXTERNAL,
ahnrva POSITION (16:17) CHAR "(LTRIM(RTRIM(:ahnrva)))",
ahnrbn POSITION (18:21) INTEGER EXTERNAL,
ahnrba POSITION (22:23) CHAR "(LTRIM(RTRIM(:ahnrba)))",
atc POSITION (24:24) CHAR,
line SEQUENCE (1,1)
)
The first line in the EBCDIC file is skipped OPTIONS (SKIP=1). The most interesting part is the WHEN clause. The attribute "ra" is the single character that has the value specified by hex-byte in the character encoding schema, such as X ' F1 ' (equivalent to 241 decimal), "X" must be uppercase. Due to National Language Support NLS requirements, hex 00 cannot be used (See Oracle 8 Utilities Guide).
How to migrate LONG fields into VARCHAR2
In Oracle7 the LONG datatype often cause difficulties, for example a table in Oracle7 can have only one LONG, LONG's couldn't replicated and more. We have seen, that in many cases simple text is inserted in these LONG fields. Due to this, we suggest to convert the LONGs into VARCHAR2 with the following simple PL/SQL script. In Oracle8 you may specify a longer VARCHAR2 (40000).
CREATE TABLE long_tab (
long_field LONG
);
ALTER TABLE long_tab ADD (char_field VARCHAR2(2000));
CREATE OR REPLACE PROCEDURE long2varchar IS
CURSOR getrowid IS
SELECT rowid, long_field
FROM long_tab;
BEGIN
FOR rec IN getrowid LOOP
UPDATE long_tab
SET char_field = rec.long_field;
END LOOP;
COMMIT;
END;
/
EXECUTE long2varchar;
Tracing other user sessions
If you want to look or trace at a certain oracle process, belonging to an Orcale User, the best way is to trace the session for this user. As a DBA, you can trace other sessions, normal users can only trace their own sessions.
1). Enable TIMED statistics
ALTER SESSION SET TIMED_STATISTICS = TRUE;
However, most Oracle systems have this INIT.ORA parameter already set to TRUE, because the performance loss is small.
2). Get SID and Serial#
SELECT s.sid,s.serial#,p.pid,p.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;
3). Stop the process
EXEC dbms_system.sql_trace_in_session(sid,serial,true);
Then use TKPROF in USER_DUMP_DEST with the generated trace file.
How to start an Oracle database with corrupted or lost dbfile ?
Help -- we cannot start the Oracle Database ... we get an error message telling us, that one datafile is lost or corrupted ... what can we do ?
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/db/test.dbf'
If the database is in ARCHIVELOG mode with a working online backup concept you are a lucky person, recover the database and everything is OK. But what can we do, if the database is in NOARCHIVELOG mode and no backup is present ?
a). If the database is still running do the following
1). Switch the damaged datafile to the RECOVER status
svrmgr> ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;
svrmgr> SELECT file#,status,bytes,name FROM v$datafile;
FILE# STATUS BYTES NAME
------- -------- --------- ------------------------------
1 SYSTEM 104857600 /u01/db/SOL3/sys/SOL3_sys1.dbf
2 RECOVER 2097152 /u01/db/test.dbf
2). Stop and Start the database to verify that the database can be started without ' test.dbf '.
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
3). DROP the tablespace to which the datafile belongs
svrmgr> DROP TABLESPACE test INCLUDING CONTENTS;
b). If the database is not running do the following
The database can only be stopped with SHUTDOWN ABORT with a damaged or lost datafile and the datafile is still in ONLINE mode. Therefore it's better to switch the datafile to the RECOVER status as shown above before stopping the database. However there is a way to switch the datafile to the RECOVER status when the database is stopped.
1). Mount the database and switch the damaged datafile to the RECOVER status
svrmgr> STARTUP MOUNT;
svrmgr> ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;
svrmgr> ALTER DATABASE OPEN;
2). DROP the tablespace to which the datafile belongs
svrmgr> DROP TABLESPACE test INCLUDING CONTENTS;
3). Stop and Start the database to verify that the database can be started without ' test.dbf '.
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
Important note
* The term ' OFFLINE DROP ' is misleading, it is not possible to drop a datafile with this command. The only purpose of this command is to startup a database with damaged or missing datafile and the databae is in NOARCHIVELOG mode.
* The command ' ALTER DATABASE DATAFILE ... OFFLINE DROP ' changes the datafile status from ONLINE to RECOVER. In this mode, the database can be started even if the datafile is not present.
* If space management (e.g. CREATE TABLE ..) occurs for this datafile, Oracle will try to allocate space in this ' dropped ' datafile and fails.
* The only way to drop a datafile is to drop the tablespace to which the datafile belongs. Unfortunately you will lose data if you don't have an actual backup.
The Mistery of Inline Views
One of the hidden features of Oracle, are the so called Inline Views (Immediate View, View on the Fly, Anonymous View). They are very useful to overcome some restrictions as:
* Users want to use Views, but have no CREATE VIEW privilege.
* A SELECT statement within CONNECT BY can select only one table.
Instead to create a view with CREATE VIEW, which needs the necessary privileges, you can specify the view directly within the FROM clause. The following example, shows how to print the number of rows from two tables in one line using an Inline View.
SELECT e.emp_count, d.dept_count
FROM (SELECT COUNT(*) emp_count FROM emp) e,
(SELECT COUNT(*) dept_count FROM dept) d;
EMP_COUNT DEPT_COUNT
--------- ----------
14 4
Another example using an Inline View gives departments' total employees and salaries as a decimal value of all the departments:
SELECT a.deptno "Department",
(a.num_emp/b.total_count)*100 "%Employees",
(a.sal_sum/b.total_sal)*100 "%Salary"
FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum
FROM emp
GROUP BY deptno) a,
(SELECT COUNT(*) total_count, SUM(sal) total_sal
FROM emp) b;
Department %Employees %Salary
---------- ---------- ---------
10 21.428571 30.146425
20 35.714286 37.4677
30 42.857143 32.385874
The next example demonstrates how to overcome the restriction, that a SELECT statement with a CONNECT BY to represent hierarchical dependencies, can specify only one table in the FROM clause. There is no possibility to enter a JOIN directly in the FROM clause. This problem can be solved with an Inline View. In the top SELECT list, all attributes must be defined, including those within the Inline View.
COLUMN employee FORMAT A12 HEADING "Chart"
COLUMN empno FORMAT 9999 HEADING "EmpNo"
COLUMN job FORMAT A10 HEADING "Job"
COLUMN dname FORMAT A10 HEADING "Dept"
COLUMN mgr FORMAT A8 HEADING "Boss"
COLUMN sal FORMAT 9999 HEADING "Salary"
COLUMN mgrno FORMAT A6 HEADING "BossNo"
SELECT employee, empno, job, d.dname, mgr, sal, mgrno
FROM dept d,
(SELECT LPAD(' ',2*(LEVEL-1)) || ename employee,
empno, job, deptno, PRIOR(ename) mgr, sal,
DECODE(LEVEL,1,NULL,PRIOR(empno)) mgrno
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH job = 'PRESIDENT') e
WHERE e.deptno = d.deptno;
Note, that the SELECT list corresponds with the Inline View (Attributtes without table alias). The departement name (d.dname) does not exist within the Inline View, it is directly selected in the table DEPT using the JOIN e.deptno = d.deptno in the WHERE clause, outside the Inline View.
Chart EmpNo Job Dept Boss Salary BossNo
------------ ----- ---------- ---------- ------ ------ ------
KING 7839 PRESIDENT ACCOUNTING 5000
JONES 7566 MANAGER RESEARCH KING 2975 7839
SCOTT 7788 ANALYST RESEARCH JONES 3000 7566
ADAMS 7876 CLERK RESEARCH SCOTT 1100 7788
FORD 7902 ANALYST RESEARCH JONE 3000 7566
SMITH 7369 CLERK RESEARCH FORD 800 7902
BLAKE 7698 MANAGER SALES KING 2850 7839
ALLEN 7499 SALESMAN SALES BLAKE 1600 7698
WARD 7521 SALESMAN SALES BLAKE 1250 7698
MARTIN 7654 SALESMAN SALES BLAKE 1250 7698
TURNER 7844 SALESMAN SALES BLAKE 1500 7698
JAMES 7900 CLERK SALES BLAKE 950 7698
CLARK 7782 MANAGER ACCOUNTING KING 2450 7839
MILLER 7934 CLERK ACCOUNTING CLARK 1300 7782
How to cache a table in the SGA ?
In order to cache a table in the SGA it has to be smaller than CACHE_SIZE_THRESHOLD as set in the init.ora file. However, the cost based analyzer doesn't take table cacheing into account when doing optimization so you may want to force the table using hints.
Check current ROLE for Database Access
When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES. You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view, for example:
SELECT role FROM session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
You may check the DB access in your application context using the following code construct.
DECLARE
HasAccess BOOLEAN := FALSE;
CURSOR cur_get_role IS
SELECT role FROM session_roles;
BEGIN
FOR role_rec IN cur_get_role LOOP
IF (UPPER(role_rec.role) IN ('ADMIN','CLERK')) THEN
HasAccess := TRUE;
END IF;
END LOOP;
IF (NOT HasAccess) THEN
RAISE_APPLICATION_ERROR
(-20020,'Sorry, you have no access to the database');
END IF;
END;
/
Eliminating duplicate records/fields from the data file
The Unix command for this is "uniq". It eliminates or reports consecutive lines that are identical. Because only adjacent lines are compared, you may have to use the sort utility before using uniq. In Example1, suppose you wanted to keep only the first entry with DEPTNO = 10. The Unix command would be:
uniq +43 example1.dat > example1.unq
The "+43" indicates that the first 43 characters in each record should be ignored for the comparison. The output file example1.unq would contain:
7782 CLARK MANAGER 7839 2572.50 0.20 10
7654 MARTIN SALESMAN 7698 1894.00 0.15 30
Counting the number of lines in the data file
Use "wc". For example:
wc -l example1.dat
3 example1.dat
which indicates that the file contains three lines.
How to remove duplicate rows from a table ?
SQL> select * from address;
NAME VORNAME BIRTH
-------- -------- ----
zahn martin 1954
zahn martin 1954
hodler kurt 1962
wyss bruno 1965
Get the Duplicates with:
SELECT name,vorname FROM address
GROUP BY name,vorname
HAVING COUNT(*) > 1;
NAME VORNAME
-------- --------
zahn martin
Delete the Duplicates with
DELETE from address A
WHERE (A.name, A.vorname, A.birth) IN
(SELECT B.name, B.vorname, B.birth FROM address B
WHERE A.name = B.name AND A.vorname = B.vorname
AND A.birth = B.birth AND A.rowid > B.rowid);
1 row deleted.
Enabling and Disabling Automatic Extension for a Datafile
You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The files increase in specified increments up to a specified maximum.
Advantage of using AUTOEXTEND ON
* Reduces the need for immediate intervention when a tablespace runs out of space.
* Ensures applications will not halt because of failures to allocate extents.
Disadvantage of using AUTOEXTEND ON
If you have no automatic mechanism to check the growth of your Oracle Datafile, ATOEXTEND ON will fillup the filesystem until 100%, then it is not easily to reclaim space. We think, that the disadvantage is greater than the advantage, due to this, check your Oracle Datafiles and reset AUTOEXTEND to OFF.
Check AUTOEXTEND on the Oracle Datafiles
To find out if a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.
SELECT file_name,autoextensible FROM dba_data_files;
FILE_NAME AUT
----------------------------------- ---
/u01/db/DIA3/sys/DIA3_sys1.dbf NO
/u01/db/DIA3/cdr/DIA3_cdr1.dbf NO
/u01/db/DIA3/cre/DIA3_cre1.dbf NO
/u01/db/DIA3/rbs/DIA3_rbs1.dbf NO
/u01/db/DIA3/usr/DIA3_users1.dbf NO
/u01/db/DIA3/tab/DIA3_tab1.dbf YES
Enabling automatic file extension
You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements:
* CREATE DATABASE
* CREATE TABLESPACE
* ALTER TABLESPACE
CREATE TABLESPACE rbs
DATAFILE '/u01/db/DIA3/rbs/DIA3_rbs1.dbf' SIZE 512064K REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
PERMANENT
ONLINE;
You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile using the SQL statement ALTER DATABASE. The following example enables automatic extension for a datafile added to the USERS tablespace:
ALTER TABLESPACE users
ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.
Disabling automatic file extension
The next example disables the automatic extension for the datafile.
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf'
AUTOEXTEND OFF;
Restrict the Number of Records
While developping database application we often have to restrict the number of records an end user can insert into a table. A simple solution is the following code fragment in the BEFORE-INSERT-FOR-EACH-ROW Trigger:
select count(*) into l_cnt from detail;
if l_cnt >= 5 then
raise_application_error(-20101,
'Maximum number of records exceeded');
end if;
To reconstruct or understand the following descriptions please download the example code ex_restr_nbrof_records.sql or consult the source code, we assume, that the maximum number of detailrecords must be limited to five.
We try to insert a 6Th record:
insert into detail values (6,'M1');
*
ERROR at line 1:
ORA-20101: Maximum number of recors reached
ORA-06512: at "SCOTT.BI_DETAIL", line 6
ORA-04088: error during execution of trigger
'SCOTT.BI_DETAIL'
So far so good. We’ll now demonstrate what happens, when we insert records from two concurrent transactions. Delete one record in order to be able to insert one record hereafter:
delete from detail where id=5;
1 row deleted.
commit;
Invoke the SQL-Plus tool and insert one record
insert into detail values (5,'M1');
1 row created.
DO NOT COMMIT !
Invoke a second session by starting a SQL-Plus again and run the statement
insert into detail values (6,'M1');
1 row created.
commit;
Commit complete.
Change to the other session and issue a commit
commit;
Commit complete.
select count(*) from detail;
COUNT(*)
----------
6
We now have 6 records. The maximum number allowed was 5 !
How to avoid this situation ?
Oracle does not support the "dirty read“ isolation level which is defined with:
"A transaction reads data written by concurrent uncommitted transaction“
A solution to solve this problem is to lock the corresponding master record:
Change the ON INSERT Trigger:
create or replace trigger bi_detail
before insert
on detail
for each row
declare
l_cnt number;
l_dummy master.id%type;
begin
-- lock the master record to avoit too many record
-- by using concurrent sessions.
select id into l_dummy from master
where id = :new.m_id for update nowait;
select count(*) into l_cnt from detail;
if l_cnt >= 5 then
raise_application_error(
-20101,'Maximum number of recors reached');
end if;
end;
Delete the 2 records to test the solution:
delete from detail where id=5;
delete from detail where id=6;
commit;
There should be 4 records now:
select count(*) from detail;
COUNT(*)
----------
4
Now we try again with the two concurrent sessions:
insert into detail values (5,'M1');
1 row created.
Change to the second session and issue:
insert into detail values (6,'M1');
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "SCOTT.BI_DETAIL", line 6
ORA-04088: error during execution of trigger
'SCOTT.BI_DETAIL'
Go back to the first session and run:
commit;
Commit complete.
Remarks
If an exact maximum number of records is specified for a table, using a lock is the only solution to avoid the above situation demonstrated. Often we want to limit the number of deteilrecords in a way, that an end user can’t claim a huge amount of disk space by inserting unbound number of records. In this case, the simple solution shown at the begin will do it.
If the table doesn’t use a foreign key constraint, you can implement an auxilliary table, insert one row and lock this row instead of the masterrecord.
The data dictionary views V$OPTION and V$VERSION can be used to get the current Oracle version and the installed options. Click here for the Script.
select banner
from sys.v$version;
select parameter
from sys.v$option
where value = 'TRUE';
select parameter
from sys.v$option
where value <> 'TRUE';
begin
dbms_output.put_line('Specific Port Information:
'||dbms_utility.port_string);
end;
/
Here is the produced Output from the Script:
Oracle Version:
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.5.0.0 - Production
TNS for 32-bit Windows: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
Installed Options:
PARAMETER
----------------------------------------------------------------
Objects
Advanced replication
Bit-mapped indexes
Connection multiplexing
Connection pooling
Database queuing
Incremental backup and recovery
Instead-of triggers
Parallel backup and recovery
Parallel execution
Parallel load
Point-in-time tablespace recovery
Fine-grained access control
N-Tier authentication/authorization
Function-based indexes
Plan Stability
Online Index Build
Coalesce Index
Managed Standby
Materialized view rewrite
Materialized view warehouse refresh
Database resource manager
Spatial
Visual Information Retrieval
Export transportable tablespaces
Transparent Application Failover
Fast-Start Fault Recovery
Sample Scan
Duplexed backups
Java
Not Installed Options:
PARAMETER
----------------------------------------------------------------
Partitioning
Parallel Server
Specific Port Information: IBMPC/WIN_NT-8.1.0
Inserting in two or more tables with SQL*Loader
If you want to load data from an ASCII or EBCDIC file into an Oracle Database -- SQL*Loader is the tool. In this article we want to show, how to distribute the rows in the file in two tables depending on a value at a specified character position in the file.
As an example, insert the row in table A if the value ra is 2 on position 7, insert the row in table B if ra is 1 (see the following figure).
You need the following SQL*Loader controlfile to accomplish this task.
OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET WE8EBCDIC500
INFILE '$BOTENFILE' "fix 86"
BADFILE '$BOTENFILE.bad'
DISCARDFILE '$BOTENFILE.dsc'
REPLACE
INTO TABLE B
WHEN ra = X'F1'
(
astrnr POSITION (1:6) INTEGER EXTERNAL,
ra POSITION (7:7) INTEGER EXTERNAL,
mc POSITION (8:8) INTEGER EXTERNAL,
astrg POSITION (9:36) CHAR,
astrgk POSITION (37:61) CHAR "UPPER(LTRIM(RTRIM(:astrgk)))",
astamm POSITION (62:71) CHAR,
aadrplz POSITION (72:77) INTEGER EXTERNAL,
astrart POSITION (78:79) INTEGER EXTERNAL,
apra POSITION (80:81) INTEGER EXTERNAL,
asprc POSITION (82:82) INTEGER EXTERNAL,
gplz POSITION (83:86) INTEGER EXTERNAL,
line SEQUENCE (1,1)
)
INTO TABLE A
WHEN ra = X'F2'
(
astrnr POSITION (1:6) INTEGER EXTERNAL,
ra POSITION (7:7) INTEGER EXTERNAL,
abzart POSITION (8:8) INTEGER EXTERNAL,
abbez POSITION (9:11) CHAR "(LTRIM(RTRIM(:abbez)))",
ahnrvn POSITION (12:15) INTEGER EXTERNAL,
ahnrva POSITION (16:17) CHAR "(LTRIM(RTRIM(:ahnrva)))",
ahnrbn POSITION (18:21) INTEGER EXTERNAL,
ahnrba POSITION (22:23) CHAR "(LTRIM(RTRIM(:ahnrba)))",
atc POSITION (24:24) CHAR,
line SEQUENCE (1,1)
)
The first line in the EBCDIC file is skipped OPTIONS (SKIP=1). The most interesting part is the WHEN clause. The attribute "ra" is the single character that has the value specified by hex-byte in the character encoding schema, such as X ' F1 ' (equivalent to 241 decimal), "X" must be uppercase. Due to National Language Support NLS requirements, hex 00 cannot be used (See Oracle 8 Utilities Guide).
How to migrate LONG fields into VARCHAR2
In Oracle7 the LONG datatype often cause difficulties, for example a table in Oracle7 can have only one LONG, LONG's couldn't replicated and more. We have seen, that in many cases simple text is inserted in these LONG fields. Due to this, we suggest to convert the LONGs into VARCHAR2 with the following simple PL/SQL script. In Oracle8 you may specify a longer VARCHAR2 (40000).
CREATE TABLE long_tab (
long_field LONG
);
ALTER TABLE long_tab ADD (char_field VARCHAR2(2000));
CREATE OR REPLACE PROCEDURE long2varchar IS
CURSOR getrowid IS
SELECT rowid, long_field
FROM long_tab;
BEGIN
FOR rec IN getrowid LOOP
UPDATE long_tab
SET char_field = rec.long_field;
END LOOP;
COMMIT;
END;
/
EXECUTE long2varchar;
Tracing other user sessions
If you want to look or trace at a certain oracle process, belonging to an Orcale User, the best way is to trace the session for this user. As a DBA, you can trace other sessions, normal users can only trace their own sessions.
1). Enable TIMED statistics
ALTER SESSION SET TIMED_STATISTICS = TRUE;
However, most Oracle systems have this INIT.ORA parameter already set to TRUE, because the performance loss is small.
2). Get SID and Serial#
SELECT s.sid,s.serial#,p.pid,p.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;
3). Stop the process
EXEC dbms_system.sql_trace_in_session(sid,serial,true);
Then use TKPROF in USER_DUMP_DEST with the generated trace file.
How to start an Oracle database with corrupted or lost dbfile ?
Help -- we cannot start the Oracle Database ... we get an error message telling us, that one datafile is lost or corrupted ... what can we do ?
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/db/test.dbf'
If the database is in ARCHIVELOG mode with a working online backup concept you are a lucky person, recover the database and everything is OK. But what can we do, if the database is in NOARCHIVELOG mode and no backup is present ?
a). If the database is still running do the following
1). Switch the damaged datafile to the RECOVER status
svrmgr> ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;
svrmgr> SELECT file#,status,bytes,name FROM v$datafile;
FILE# STATUS BYTES NAME
------- -------- --------- ------------------------------
1 SYSTEM 104857600 /u01/db/SOL3/sys/SOL3_sys1.dbf
2 RECOVER 2097152 /u01/db/test.dbf
2). Stop and Start the database to verify that the database can be started without ' test.dbf '.
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
3). DROP the tablespace to which the datafile belongs
svrmgr> DROP TABLESPACE test INCLUDING CONTENTS;
b). If the database is not running do the following
The database can only be stopped with SHUTDOWN ABORT with a damaged or lost datafile and the datafile is still in ONLINE mode. Therefore it's better to switch the datafile to the RECOVER status as shown above before stopping the database. However there is a way to switch the datafile to the RECOVER status when the database is stopped.
1). Mount the database and switch the damaged datafile to the RECOVER status
svrmgr> STARTUP MOUNT;
svrmgr> ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;
svrmgr> ALTER DATABASE OPEN;
2). DROP the tablespace to which the datafile belongs
svrmgr> DROP TABLESPACE test INCLUDING CONTENTS;
3). Stop and Start the database to verify that the database can be started without ' test.dbf '.
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
Important note
* The term ' OFFLINE DROP ' is misleading, it is not possible to drop a datafile with this command. The only purpose of this command is to startup a database with damaged or missing datafile and the databae is in NOARCHIVELOG mode.
* The command ' ALTER DATABASE DATAFILE ... OFFLINE DROP ' changes the datafile status from ONLINE to RECOVER. In this mode, the database can be started even if the datafile is not present.
* If space management (e.g. CREATE TABLE ..) occurs for this datafile, Oracle will try to allocate space in this ' dropped ' datafile and fails.
* The only way to drop a datafile is to drop the tablespace to which the datafile belongs. Unfortunately you will lose data if you don't have an actual backup.
The Mistery of Inline Views
One of the hidden features of Oracle, are the so called Inline Views (Immediate View, View on the Fly, Anonymous View). They are very useful to overcome some restrictions as:
* Users want to use Views, but have no CREATE VIEW privilege.
* A SELECT statement within CONNECT BY can select only one table.
Instead to create a view with CREATE VIEW, which needs the necessary privileges, you can specify the view directly within the FROM clause. The following example, shows how to print the number of rows from two tables in one line using an Inline View.
SELECT e.emp_count, d.dept_count
FROM (SELECT COUNT(*) emp_count FROM emp) e,
(SELECT COUNT(*) dept_count FROM dept) d;
EMP_COUNT DEPT_COUNT
--------- ----------
14 4
Another example using an Inline View gives departments' total employees and salaries as a decimal value of all the departments:
SELECT a.deptno "Department",
(a.num_emp/b.total_count)*100 "%Employees",
(a.sal_sum/b.total_sal)*100 "%Salary"
FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum
FROM emp
GROUP BY deptno) a,
(SELECT COUNT(*) total_count, SUM(sal) total_sal
FROM emp) b;
Department %Employees %Salary
---------- ---------- ---------
10 21.428571 30.146425
20 35.714286 37.4677
30 42.857143 32.385874
The next example demonstrates how to overcome the restriction, that a SELECT statement with a CONNECT BY to represent hierarchical dependencies, can specify only one table in the FROM clause. There is no possibility to enter a JOIN directly in the FROM clause. This problem can be solved with an Inline View. In the top SELECT list, all attributes must be defined, including those within the Inline View.
COLUMN employee FORMAT A12 HEADING "Chart"
COLUMN empno FORMAT 9999 HEADING "EmpNo"
COLUMN job FORMAT A10 HEADING "Job"
COLUMN dname FORMAT A10 HEADING "Dept"
COLUMN mgr FORMAT A8 HEADING "Boss"
COLUMN sal FORMAT 9999 HEADING "Salary"
COLUMN mgrno FORMAT A6 HEADING "BossNo"
SELECT employee, empno, job, d.dname, mgr, sal, mgrno
FROM dept d,
(SELECT LPAD(' ',2*(LEVEL-1)) || ename employee,
empno, job, deptno, PRIOR(ename) mgr, sal,
DECODE(LEVEL,1,NULL,PRIOR(empno)) mgrno
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH job = 'PRESIDENT') e
WHERE e.deptno = d.deptno;
Note, that the SELECT list corresponds with the Inline View (Attributtes without table alias). The departement name (d.dname) does not exist within the Inline View, it is directly selected in the table DEPT using the JOIN e.deptno = d.deptno in the WHERE clause, outside the Inline View.
Chart EmpNo Job Dept Boss Salary BossNo
------------ ----- ---------- ---------- ------ ------ ------
KING 7839 PRESIDENT ACCOUNTING 5000
JONES 7566 MANAGER RESEARCH KING 2975 7839
SCOTT 7788 ANALYST RESEARCH JONES 3000 7566
ADAMS 7876 CLERK RESEARCH SCOTT 1100 7788
FORD 7902 ANALYST RESEARCH JONE 3000 7566
SMITH 7369 CLERK RESEARCH FORD 800 7902
BLAKE 7698 MANAGER SALES KING 2850 7839
ALLEN 7499 SALESMAN SALES BLAKE 1600 7698
WARD 7521 SALESMAN SALES BLAKE 1250 7698
MARTIN 7654 SALESMAN SALES BLAKE 1250 7698
TURNER 7844 SALESMAN SALES BLAKE 1500 7698
JAMES 7900 CLERK SALES BLAKE 950 7698
CLARK 7782 MANAGER ACCOUNTING KING 2450 7839
MILLER 7934 CLERK ACCOUNTING CLARK 1300 7782
How to cache a table in the SGA ?
In order to cache a table in the SGA it has to be smaller than CACHE_SIZE_THRESHOLD as set in the init.ora file. However, the cost based analyzer doesn't take table cacheing into account when doing optimization so you may want to force the table using hints.
Check current ROLE for Database Access
When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES. You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view, for example:
SELECT role FROM session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
You may check the DB access in your application context using the following code construct.
DECLARE
HasAccess BOOLEAN := FALSE;
CURSOR cur_get_role IS
SELECT role FROM session_roles;
BEGIN
FOR role_rec IN cur_get_role LOOP
IF (UPPER(role_rec.role) IN ('ADMIN','CLERK')) THEN
HasAccess := TRUE;
END IF;
END LOOP;
IF (NOT HasAccess) THEN
RAISE_APPLICATION_ERROR
(-20020,'Sorry, you have no access to the database');
END IF;
END;
/
Eliminating duplicate records/fields from the data file
The Unix command for this is "uniq". It eliminates or reports consecutive lines that are identical. Because only adjacent lines are compared, you may have to use the sort utility before using uniq. In Example1, suppose you wanted to keep only the first entry with DEPTNO = 10. The Unix command would be:
uniq +43 example1.dat > example1.unq
The "+43" indicates that the first 43 characters in each record should be ignored for the comparison. The output file example1.unq would contain:
7782 CLARK MANAGER 7839 2572.50 0.20 10
7654 MARTIN SALESMAN 7698 1894.00 0.15 30
Counting the number of lines in the data file
Use "wc". For example:
wc -l example1.dat
3 example1.dat
which indicates that the file contains three lines.
How to remove duplicate rows from a table ?
SQL> select * from address;
NAME VORNAME BIRTH
-------- -------- ----
zahn martin 1954
zahn martin 1954
hodler kurt 1962
wyss bruno 1965
Get the Duplicates with:
SELECT name,vorname FROM address
GROUP BY name,vorname
HAVING COUNT(*) > 1;
NAME VORNAME
-------- --------
zahn martin
Delete the Duplicates with
DELETE from address A
WHERE (A.name, A.vorname, A.birth) IN
(SELECT B.name, B.vorname, B.birth FROM address B
WHERE A.name = B.name AND A.vorname = B.vorname
AND A.birth = B.birth AND A.rowid > B.rowid);
1 row deleted.
Enabling and Disabling Automatic Extension for a Datafile
You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The files increase in specified increments up to a specified maximum.
Advantage of using AUTOEXTEND ON
* Reduces the need for immediate intervention when a tablespace runs out of space.
* Ensures applications will not halt because of failures to allocate extents.
Disadvantage of using AUTOEXTEND ON
If you have no automatic mechanism to check the growth of your Oracle Datafile, ATOEXTEND ON will fillup the filesystem until 100%, then it is not easily to reclaim space. We think, that the disadvantage is greater than the advantage, due to this, check your Oracle Datafiles and reset AUTOEXTEND to OFF.
Check AUTOEXTEND on the Oracle Datafiles
To find out if a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.
SELECT file_name,autoextensible FROM dba_data_files;
FILE_NAME AUT
----------------------------------- ---
/u01/db/DIA3/sys/DIA3_sys1.dbf NO
/u01/db/DIA3/cdr/DIA3_cdr1.dbf NO
/u01/db/DIA3/cre/DIA3_cre1.dbf NO
/u01/db/DIA3/rbs/DIA3_rbs1.dbf NO
/u01/db/DIA3/usr/DIA3_users1.dbf NO
/u01/db/DIA3/tab/DIA3_tab1.dbf YES
Enabling automatic file extension
You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements:
* CREATE DATABASE
* CREATE TABLESPACE
* ALTER TABLESPACE
CREATE TABLESPACE rbs
DATAFILE '/u01/db/DIA3/rbs/DIA3_rbs1.dbf' SIZE 512064K REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
PERMANENT
ONLINE;
You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile using the SQL statement ALTER DATABASE. The following example enables automatic extension for a datafile added to the USERS tablespace:
ALTER TABLESPACE users
ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.
Disabling automatic file extension
The next example disables the automatic extension for the datafile.
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf'
AUTOEXTEND OFF;
Restrict the Number of Records
While developping database application we often have to restrict the number of records an end user can insert into a table. A simple solution is the following code fragment in the BEFORE-INSERT-FOR-EACH-ROW Trigger:
select count(*) into l_cnt from detail;
if l_cnt >= 5 then
raise_application_error(-20101,
'Maximum number of records exceeded');
end if;
To reconstruct or understand the following descriptions please download the example code ex_restr_nbrof_records.sql or consult the source code, we assume, that the maximum number of detailrecords must be limited to five.
We try to insert a 6Th record:
insert into detail values (6,'M1');
*
ERROR at line 1:
ORA-20101: Maximum number of recors reached
ORA-06512: at "SCOTT.BI_DETAIL", line 6
ORA-04088: error during execution of trigger
'SCOTT.BI_DETAIL'
So far so good. We’ll now demonstrate what happens, when we insert records from two concurrent transactions. Delete one record in order to be able to insert one record hereafter:
delete from detail where id=5;
1 row deleted.
commit;
Invoke the SQL-Plus tool and insert one record
insert into detail values (5,'M1');
1 row created.
DO NOT COMMIT !
Invoke a second session by starting a SQL-Plus again and run the statement
insert into detail values (6,'M1');
1 row created.
commit;
Commit complete.
Change to the other session and issue a commit
commit;
Commit complete.
select count(*) from detail;
COUNT(*)
----------
6
We now have 6 records. The maximum number allowed was 5 !
How to avoid this situation ?
Oracle does not support the "dirty read“ isolation level which is defined with:
"A transaction reads data written by concurrent uncommitted transaction“
A solution to solve this problem is to lock the corresponding master record:
Change the ON INSERT Trigger:
create or replace trigger bi_detail
before insert
on detail
for each row
declare
l_cnt number;
l_dummy master.id%type;
begin
-- lock the master record to avoit too many record
-- by using concurrent sessions.
select id into l_dummy from master
where id = :new.m_id for update nowait;
select count(*) into l_cnt from detail;
if l_cnt >= 5 then
raise_application_error(
-20101,'Maximum number of recors reached');
end if;
end;
Delete the 2 records to test the solution:
delete from detail where id=5;
delete from detail where id=6;
commit;
There should be 4 records now:
select count(*) from detail;
COUNT(*)
----------
4
Now we try again with the two concurrent sessions:
insert into detail values (5,'M1');
1 row created.
Change to the second session and issue:
insert into detail values (6,'M1');
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "SCOTT.BI_DETAIL", line 6
ORA-04088: error during execution of trigger
'SCOTT.BI_DETAIL'
Go back to the first session and run:
commit;
Commit complete.
Remarks
If an exact maximum number of records is specified for a table, using a lock is the only solution to avoid the above situation demonstrated. Often we want to limit the number of deteilrecords in a way, that an end user can’t claim a huge amount of disk space by inserting unbound number of records. In this case, the simple solution shown at the begin will do it.
If the table doesn’t use a foreign key constraint, you can implement an auxilliary table, insert one row and lock this row instead of the masterrecord.
Friday, July 16, 2010
Recovering a datafile which has no backup using RMAN
Step 1: Confirm Database Name and Identify Tablespace Name to be used for DR Test.
SQL> select INSTANCE_NAME, VERSION from v$instance;
INSTANCE_NAME VERSION
---------------- -----------------
opsdba 10.2.0.2.0
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP1
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/users01.dbf
/u02/ORACLE/opsdba/sysaux01.dbf
/u02/ORACLE/opsdba/undotbs01.dbf
/u02/ORACLE/opsdba/system01.dbf
/u02/ORACLE/opsdba/users05.dbf
/u02/ORACLE/opsdba/users02.dbf
/u02/ORACLE/opsdba/users03.dbf
/u02/ORACLE/opsdba/users06.dbf
/u02/ORACLE/opsdba/users07.dbf
/u02/ORACLE/opsdba/users04.dbf
10 rows selected.
Step 2: Create a new tablespace with 1 Datafile which will be used for recovery exercise.
SQL> create tablespace drtbs datafile '/u02/ORACLE/opsdba/drtbs1.dbf'
size 100M extent management local;
Tablespace created.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
DRTBS
TEMP1
6 rows selected.
SQL> select file_name from dba_data_files where tablespace_name=
'DRTBS';
FILE_NAME
--------------------------------------------------------------
/u02/ORACLE/opsdba/drtbs1.dbf
SQL> exit
Step 3: Take a full Backup of Database & Archive log.
RMAN> backup database plus archivelog;
Starting backup at 28-JAN-07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=362 stamp=612901138
input archive log thread=1 sequence=2 recid=363 stamp=612901141
input archive log thread=1 sequence=3 recid=364 stamp=612901146
input archive log thread=1 sequence=4 recid=365 stamp=612943256
input archive log thread=1 sequence=5 recid=366 stamp=612976032
input archive log thread=1 sequence=6 recid=367 stamp=612976036
input archive log thread=1 sequence=7 recid=368 stamp=613049876
input archive log thread=1 sequence=8 recid=369 stamp=613049878
input archive log thread=1 sequence=9 recid=370 stamp=613049879
input archive log thread=1 sequence=10 recid=371 stamp=613049880
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.145.1.1.
613089429 tag=TAG20070128T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=372 stamp=613049882
input archive log thread=1 sequence=2 recid=373 stamp=613049884
input archive log thread=1 sequence=3 recid=374 stamp=613049885
input archive log thread=1 sequence=4 recid=375 stamp=613049887
input archive log thread=1 sequence=5 recid=376 stamp=613049888
input archive log thread=1 sequence=6 recid=377 stamp=613049889
input archive log thread=1 sequence=7 recid=378 stamp=613049890
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.146.1.1.
613089445 tag=TAG20070128T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=379 stamp=613089428
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.147.1.1.
613089453 tag=TAG20070128T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-JAN-07
Starting backup at 28-JAN-07
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=00001 name=/u02/ORACLE/opsdba/system01.dbf
input datafile fno=00003 name=/u02/ORACLE/opsdba/sysaux01.dbf
input datafile fno=00002 name=/u02/ORACLE/opsdba/undotbs01.dbf
input datafile fno=00011 name=/u02/ORACLE/opsdba/drtbs1.dbf
input datafile fno=00004 name=/u02/ORACLE/opsdba/users01.dbf
input datafile fno=00005 name=/u02/ORACLE/opsdba/users02.dbf
input datafile fno=00006 name=/u02/ORACLE/opsdba/users03.dbf
input datafile fno=00007 name=/u02/ORACLE/opsdba/users05.dbf
input datafile fno=00010 name=/u02/ORACLE/opsdba/users04.dbf
input datafile fno=00008 name=/u02/ORACLE/opsdba/users06.dbf
input datafile fno=00009 name=/u02/ORACLE/opsdba/users07.dbf
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455 tag=TAG20070128T223735 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 28-JAN-07
Starting backup at 28-JAN-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=380 stamp=613089480
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.149.1.1.
613089480 tag=TAG20070128T223800 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-JAN-07
Starting Control File and SPFILE Autobackup at 28-JAN-07
piece handle=/opt/oracle/product10gpr2/dbs/c-1493612009-20070128-03
comment=NONE
Finished Control File and SPFILE Autobackup at 28-JAN-07
RMAN>exit
Step 4: Add a New Datafile to that Tablespace and verify that the new file is now a member of that tablespace. Also switch few log files just for confirmation.
SQL> select file_name from dba_data_files where tablespace_name=
'DRTBS';
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/drtbs1.dbf
SQL> alter tablespace drtbs add datafile '/u02/ORACLE/opsdba/drtbs2.
dbf' size 100m;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name=
'DRTBS';
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/drtbs1.dbf
/u02/ORACLE/opsdba/drtbs2.dbf
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Step 5: Create a new Table in that tablespace and perform some DML operation. Also after DML operations switch some logfile.
SQL> create table t1(col1 number(10)) tablespace DRTBS;
Table created.
SQL> insert into t1 values (&a);
Enter value for a: 1
old 1: insert into t1 values(&a)
new 1: insert into t1 values(1)
1 row created.
SQL> /
Enter value for a: 2
old 1: insert into t1 values(&a)
new 1: insert into t1 values(2)
1 row created.
SQL> /
Enter value for a: 3
old 1: insert into t1 values(&a)
new 1: insert into t1 values(3)
1 row created.
SQL> /
Enter value for a: 4
old 1: insert into t1 values(&a)
new 1: insert into t1 values(4)
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
COL1
----------
1
2
3
4
SQL> alter system switch logfile;
System altered.
Step 6: In the OS Level remove all files of that tablespace including the newly added one (whose backup does not exist).
opsdba:/opt/oracle>cd /u02/ORACLE/opsdba/
opsdba:/u02/ORACLE/opsdba>ls –lrt drtbs*.dbf
total 1441496
-rw-r----- 1 oracle dba 104865792 Jan 28 22:38 drtbs1.dbf
-rw-r----- 1 oracle dba 104865792 Jan 28 23:08 drtbs2.dbf
opsdba:/u02/ORACLE/opsdba>rm -r drtbs*.dbf
opsdba:/u02/ORACLE/opsdba>ls -lrt drtbs*.dbf
ls: drtbs*.dbf: No such file or directory
opsdba:/u02/ORACLE/opsdba>
Step 7: Try to bring the tablespace offline and we will get error message as follows.
opsdba:/u02/ORACLE/opsdba>sql
SQL> alter tablespace drtbs offline;
alter tablespace drtbs offline
*
ERROR at line 1:
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/u02/ORACLE/opsdba/drtbs1.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Step 8: Now bring the Tablespace offline with IMMEDIATE option and confirm.
SQL> alter tablespace drtbs offline immediate;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
USERS ONLINE
TEMP1 ONLINE
DRTBS OFFLINE
6 rows selected.
Step 9: Now connect to RMAN and Confirm that No backup exist for the Newly added Datafile. Then try to restore the TABLESPACE and we will see that RMAN is creating that newly added Datafile as a part of the restore process. This is a new feature in 10G.
SQL> select file_id, file_name from dba_data_files where
tablespace_name='DRTBS';
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------
----------
11
/u02/ORACLE/opsdba/drtbs1.dbf
12
/u02/ORACLE/opsdba/drtbs2.dbf
SQL> exit;
opsdba:/u02/ORACLE/opsdba>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Jan 28 23:18:
09 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: OPSDBA (DBID=1493612009)
RMAN> list backup of datafile 11;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
129 Full 669.09M DISK 00:00:15 28-JAN-07
BP Key: 129 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T223735
Piece Name: /opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455
List of Datafiles in backup set 129
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 2747296 28-JAN-07 /u02/ORACLE/opsdba/drtbs1.dbf
RMAN> list backup of datafile 12;
No output …
RMAN> restore tablespace drtbs;
Starting restore at 28-JAN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
creating datafile fno=12 name=/u02/ORACLE/opsdba/drtbs2.dbf
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /u02/ORACLE/opsdba/drtbs1.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/backup/
opsdba/OPSDBA.20070128.148.1.1.613089455
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455 tag=TAG20070128T223735
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 28-JAN-07
opsdba:/u02/ORACLE/opsdba>rman target /
Step 10: Start Recovery of that Tablespace.
opsdba:/u02/ORACLE/opsdba>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Jan 28 23:49:33 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: OPSDBA (DBID=1493612009)
RMAN> recover tablespace drtbs;
Starting recover at 28-JAN-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
starting media recovery
un Jan 28 23:22:36 2007
alter database recover logfile '/u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf'
Sun Jan 28 23:22:36 2007
Media Recovery Log /u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf
Sun Jan 28 23:22:36 2007
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo03.log
Sun Jan 28 23:22:36 2007
Recovery of Online Redo Log: Thread 1 Group 2 Seq 4 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo02.log
Sun Jan 28 23:22:36 2007
Recovery of Online Redo Log: Thread 1 Group 1 Seq 5 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo01.log
Sun Jan 28 23:22:36 2007
Media Recovery Complete (opsdba)
Completed: alter database recover logfile '/u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf'
Sun Jan 28 23:22:52 2007
media recovery complete, elapsed time: 00:00:00
Finished recover at 28-JAN-07
RMAN> exit
Recovery Manager complete.
Step 11: Bring the Tablespace online and confirm .
SQL> alter tablespace drtbs online;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
USERS ONLINE
TEMP1 ONLINE
DRTBS ONLINE
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from t1;
COL1
----------
1
2
3
4
SQL> select file_name from dba_data_files where tablespace_name=
'DRTBS';
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/drtbs1.dbf
/u02/ORACLE/opsdba/drtbs2.dbf
SQL> exit
Step 12: As a standard practice immediately after the recovery please take a FULL DATABASE BACKUP.
opsdba:/u02/ORACLE/opsdba>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Jan 28 23:25:
01 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: OPSDBA (DBID=1493612009)
RMAN> backup database plus archivelog;
Starting backup at 28-JAN-07
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
skipping archive log file /u02/ORACLE/opsdba/arch/arch_1_1_613052894. dbf; already backed up 1 time(s)
skipping archive log file /u02/ORACLE/opsdba/arch/arch_1_2_613052894. dbf; already backed up 1 time(s)
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=381 stamp=613091353
input archive log thread=1 sequence=4 recid=382 stamp=613091355
input archive log thread=1 sequence=5 recid=383 stamp=613092208
input archive log thread=1 sequence=6 recid=384 stamp=613092210
input archive log thread=1 sequence=7 recid=385 stamp=613092318
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.154.1.1.
613092318 tag=TAG20070128T232518 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-JAN-07
Starting backup at 28-JAN-07
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=00001 name=/u02/ORACLE/opsdba/system01.dbf
input datafile fno=00003 name=/u02/ORACLE/opsdba/sysaux01.dbf
input datafile fno=00002 name=/u02/ORACLE/opsdba/undotbs01.dbf
input datafile fno=00011 name=/u02/ORACLE/opsdba/drtbs1.dbf
input datafile fno=00012 name=/u02/ORACLE/opsdba/drtbs2.dbf
input datafile fno=00004 name=/u02/ORACLE/opsdba/users01.dbf
input datafile fno=00005 name=/u02/ORACLE/opsdba/users02.dbf
input datafile fno=00006 name=/u02/ORACLE/opsdba/users03.dbf
input datafile fno=00007 name=/u02/ORACLE/opsdba/users05.dbf
input datafile fno=00010 name=/u02/ORACLE/opsdba/users04.dbf
input datafile fno=00008 name=/u02/ORACLE/opsdba/users06.dbf
input datafile fno=00009 name=/u02/ORACLE/opsdba/users07.dbf
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.155.1.1.
613092320 tag=TAG20070128T232520 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 28-JAN-07
Starting backup at 28-JAN-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=8 recid=386 stamp=613092345
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.156.1.1.
613092346 tag=TAG20070128T232545 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-JAN-07
Starting Control File and SPFILE Autobackup at 28-JAN-07
piece handle=/opt/oracle/product10gpr2/dbs/c-1493612009-20070128-07
comment=NONE
Finished Control File and SPFILE Autobackup at 28-JAN-07
RMAN> list backup of datafile 11;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
129 Full 669.09M DISK 00:00:15 28-JAN-07
BP Key: 129 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T223735
Piece Name: /opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455
List of Datafiles in backup set 129
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 2747296 28-JAN-07 /u02/ORACLE/opsdba/drtbs1.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136 Full 669.73M DISK 00:00:21 28-JAN-07
BP Key: 136 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T232520
Piece Name: /opt/oracle/backup/opsdba/OPSDBA.20070128.155.1.1.
613092320
List of Datafiles in backup set 136
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 2748771 28-JAN-07 /u02/ORACLE/opsdba/drtbs1.dbf
RMAN> list backup of datafile 12;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136 Full 669.73M DISK 00:00:21 28-JAN-07
BP Key: 136 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T232520
Piece Name: /opt/oracle/backup/opsdba/OPSDBA.20070128.155.1.1.
613092320
List of Datafiles in backup set 136
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
12 Full 2748771 28-JAN-07 /u02/ORACLE/opsdba/drtbs2.dbf
RMAN>
SQL> select INSTANCE_NAME, VERSION from v$instance;
INSTANCE_NAME VERSION
---------------- -----------------
opsdba 10.2.0.2.0
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP1
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/users01.dbf
/u02/ORACLE/opsdba/sysaux01.dbf
/u02/ORACLE/opsdba/undotbs01.dbf
/u02/ORACLE/opsdba/system01.dbf
/u02/ORACLE/opsdba/users05.dbf
/u02/ORACLE/opsdba/users02.dbf
/u02/ORACLE/opsdba/users03.dbf
/u02/ORACLE/opsdba/users06.dbf
/u02/ORACLE/opsdba/users07.dbf
/u02/ORACLE/opsdba/users04.dbf
10 rows selected.
Step 2: Create a new tablespace with 1 Datafile which will be used for recovery exercise.
SQL> create tablespace drtbs datafile '/u02/ORACLE/opsdba/drtbs1.dbf'
size 100M extent management local;
Tablespace created.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
DRTBS
TEMP1
6 rows selected.
SQL> select file_name from dba_data_files where tablespace_name=
'DRTBS';
FILE_NAME
--------------------------------------------------------------
/u02/ORACLE/opsdba/drtbs1.dbf
SQL> exit
Step 3: Take a full Backup of Database & Archive log.
RMAN> backup database plus archivelog;
Starting backup at 28-JAN-07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=362 stamp=612901138
input archive log thread=1 sequence=2 recid=363 stamp=612901141
input archive log thread=1 sequence=3 recid=364 stamp=612901146
input archive log thread=1 sequence=4 recid=365 stamp=612943256
input archive log thread=1 sequence=5 recid=366 stamp=612976032
input archive log thread=1 sequence=6 recid=367 stamp=612976036
input archive log thread=1 sequence=7 recid=368 stamp=613049876
input archive log thread=1 sequence=8 recid=369 stamp=613049878
input archive log thread=1 sequence=9 recid=370 stamp=613049879
input archive log thread=1 sequence=10 recid=371 stamp=613049880
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.145.1.1.
613089429 tag=TAG20070128T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=372 stamp=613049882
input archive log thread=1 sequence=2 recid=373 stamp=613049884
input archive log thread=1 sequence=3 recid=374 stamp=613049885
input archive log thread=1 sequence=4 recid=375 stamp=613049887
input archive log thread=1 sequence=5 recid=376 stamp=613049888
input archive log thread=1 sequence=6 recid=377 stamp=613049889
input archive log thread=1 sequence=7 recid=378 stamp=613049890
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.146.1.1.
613089445 tag=TAG20070128T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=379 stamp=613089428
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.147.1.1.
613089453 tag=TAG20070128T223709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-JAN-07
Starting backup at 28-JAN-07
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=00001 name=/u02/ORACLE/opsdba/system01.dbf
input datafile fno=00003 name=/u02/ORACLE/opsdba/sysaux01.dbf
input datafile fno=00002 name=/u02/ORACLE/opsdba/undotbs01.dbf
input datafile fno=00011 name=/u02/ORACLE/opsdba/drtbs1.dbf
input datafile fno=00004 name=/u02/ORACLE/opsdba/users01.dbf
input datafile fno=00005 name=/u02/ORACLE/opsdba/users02.dbf
input datafile fno=00006 name=/u02/ORACLE/opsdba/users03.dbf
input datafile fno=00007 name=/u02/ORACLE/opsdba/users05.dbf
input datafile fno=00010 name=/u02/ORACLE/opsdba/users04.dbf
input datafile fno=00008 name=/u02/ORACLE/opsdba/users06.dbf
input datafile fno=00009 name=/u02/ORACLE/opsdba/users07.dbf
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455 tag=TAG20070128T223735 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 28-JAN-07
Starting backup at 28-JAN-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=380 stamp=613089480
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.149.1.1.
613089480 tag=TAG20070128T223800 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-JAN-07
Starting Control File and SPFILE Autobackup at 28-JAN-07
piece handle=/opt/oracle/product10gpr2/dbs/c-1493612009-20070128-03
comment=NONE
Finished Control File and SPFILE Autobackup at 28-JAN-07
RMAN>exit
Step 4: Add a New Datafile to that Tablespace and verify that the new file is now a member of that tablespace. Also switch few log files just for confirmation.
SQL> select file_name from dba_data_files where tablespace_name=
'DRTBS';
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/drtbs1.dbf
SQL> alter tablespace drtbs add datafile '/u02/ORACLE/opsdba/drtbs2.
dbf' size 100m;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name=
'DRTBS';
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/drtbs1.dbf
/u02/ORACLE/opsdba/drtbs2.dbf
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Step 5: Create a new Table in that tablespace and perform some DML operation. Also after DML operations switch some logfile.
SQL> create table t1(col1 number(10)) tablespace DRTBS;
Table created.
SQL> insert into t1 values (&a);
Enter value for a: 1
old 1: insert into t1 values(&a)
new 1: insert into t1 values(1)
1 row created.
SQL> /
Enter value for a: 2
old 1: insert into t1 values(&a)
new 1: insert into t1 values(2)
1 row created.
SQL> /
Enter value for a: 3
old 1: insert into t1 values(&a)
new 1: insert into t1 values(3)
1 row created.
SQL> /
Enter value for a: 4
old 1: insert into t1 values(&a)
new 1: insert into t1 values(4)
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
COL1
----------
1
2
3
4
SQL> alter system switch logfile;
System altered.
Step 6: In the OS Level remove all files of that tablespace including the newly added one (whose backup does not exist).
opsdba:/opt/oracle>cd /u02/ORACLE/opsdba/
opsdba:/u02/ORACLE/opsdba>ls –lrt drtbs*.dbf
total 1441496
-rw-r----- 1 oracle dba 104865792 Jan 28 22:38 drtbs1.dbf
-rw-r----- 1 oracle dba 104865792 Jan 28 23:08 drtbs2.dbf
opsdba:/u02/ORACLE/opsdba>rm -r drtbs*.dbf
opsdba:/u02/ORACLE/opsdba>ls -lrt drtbs*.dbf
ls: drtbs*.dbf: No such file or directory
opsdba:/u02/ORACLE/opsdba>
Step 7: Try to bring the tablespace offline and we will get error message as follows.
opsdba:/u02/ORACLE/opsdba>sql
SQL> alter tablespace drtbs offline;
alter tablespace drtbs offline
*
ERROR at line 1:
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/u02/ORACLE/opsdba/drtbs1.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Step 8: Now bring the Tablespace offline with IMMEDIATE option and confirm.
SQL> alter tablespace drtbs offline immediate;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
USERS ONLINE
TEMP1 ONLINE
DRTBS OFFLINE
6 rows selected.
Step 9: Now connect to RMAN and Confirm that No backup exist for the Newly added Datafile. Then try to restore the TABLESPACE and we will see that RMAN is creating that newly added Datafile as a part of the restore process. This is a new feature in 10G.
SQL> select file_id, file_name from dba_data_files where
tablespace_name='DRTBS';
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------
----------
11
/u02/ORACLE/opsdba/drtbs1.dbf
12
/u02/ORACLE/opsdba/drtbs2.dbf
SQL> exit;
opsdba:/u02/ORACLE/opsdba>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Jan 28 23:18:
09 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: OPSDBA (DBID=1493612009)
RMAN> list backup of datafile 11;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
129 Full 669.09M DISK 00:00:15 28-JAN-07
BP Key: 129 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T223735
Piece Name: /opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455
List of Datafiles in backup set 129
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 2747296 28-JAN-07 /u02/ORACLE/opsdba/drtbs1.dbf
RMAN> list backup of datafile 12;
No output …
RMAN> restore tablespace drtbs;
Starting restore at 28-JAN-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
creating datafile fno=12 name=/u02/ORACLE/opsdba/drtbs2.dbf
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /u02/ORACLE/opsdba/drtbs1.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/backup/
opsdba/OPSDBA.20070128.148.1.1.613089455
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455 tag=TAG20070128T223735
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 28-JAN-07
opsdba:/u02/ORACLE/opsdba>rman target /
Step 10: Start Recovery of that Tablespace.
opsdba:/u02/ORACLE/opsdba>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Jan 28 23:49:33 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: OPSDBA (DBID=1493612009)
RMAN> recover tablespace drtbs;
Starting recover at 28-JAN-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
starting media recovery
un Jan 28 23:22:36 2007
alter database recover logfile '/u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf'
Sun Jan 28 23:22:36 2007
Media Recovery Log /u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf
Sun Jan 28 23:22:36 2007
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo03.log
Sun Jan 28 23:22:36 2007
Recovery of Online Redo Log: Thread 1 Group 2 Seq 4 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo02.log
Sun Jan 28 23:22:36 2007
Recovery of Online Redo Log: Thread 1 Group 1 Seq 5 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo01.log
Sun Jan 28 23:22:36 2007
Media Recovery Complete (opsdba)
Completed: alter database recover logfile '/u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf'
Sun Jan 28 23:22:52 2007
media recovery complete, elapsed time: 00:00:00
Finished recover at 28-JAN-07
RMAN> exit
Recovery Manager complete.
Step 11: Bring the Tablespace online and confirm .
SQL> alter tablespace drtbs online;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
USERS ONLINE
TEMP1 ONLINE
DRTBS ONLINE
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from t1;
COL1
----------
1
2
3
4
SQL> select file_name from dba_data_files where tablespace_name=
'DRTBS';
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/drtbs1.dbf
/u02/ORACLE/opsdba/drtbs2.dbf
SQL> exit
Step 12: As a standard practice immediately after the recovery please take a FULL DATABASE BACKUP.
opsdba:/u02/ORACLE/opsdba>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Sun Jan 28 23:25:
01 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: OPSDBA (DBID=1493612009)
RMAN> backup database plus archivelog;
Starting backup at 28-JAN-07
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
skipping archive log file /u02/ORACLE/opsdba/arch/arch_1_1_613052894. dbf; already backed up 1 time(s)
skipping archive log file /u02/ORACLE/opsdba/arch/arch_1_2_613052894. dbf; already backed up 1 time(s)
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=381 stamp=613091353
input archive log thread=1 sequence=4 recid=382 stamp=613091355
input archive log thread=1 sequence=5 recid=383 stamp=613092208
input archive log thread=1 sequence=6 recid=384 stamp=613092210
input archive log thread=1 sequence=7 recid=385 stamp=613092318
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.154.1.1.
613092318 tag=TAG20070128T232518 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-JAN-07
Starting backup at 28-JAN-07
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=00001 name=/u02/ORACLE/opsdba/system01.dbf
input datafile fno=00003 name=/u02/ORACLE/opsdba/sysaux01.dbf
input datafile fno=00002 name=/u02/ORACLE/opsdba/undotbs01.dbf
input datafile fno=00011 name=/u02/ORACLE/opsdba/drtbs1.dbf
input datafile fno=00012 name=/u02/ORACLE/opsdba/drtbs2.dbf
input datafile fno=00004 name=/u02/ORACLE/opsdba/users01.dbf
input datafile fno=00005 name=/u02/ORACLE/opsdba/users02.dbf
input datafile fno=00006 name=/u02/ORACLE/opsdba/users03.dbf
input datafile fno=00007 name=/u02/ORACLE/opsdba/users05.dbf
input datafile fno=00010 name=/u02/ORACLE/opsdba/users04.dbf
input datafile fno=00008 name=/u02/ORACLE/opsdba/users06.dbf
input datafile fno=00009 name=/u02/ORACLE/opsdba/users07.dbf
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.155.1.1.
613092320 tag=TAG20070128T232520 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 28-JAN-07
Starting backup at 28-JAN-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=8 recid=386 stamp=613092345
channel ORA_DISK_1: starting piece 1 at 28-JAN-07
channel ORA_DISK_1: finished piece 1 at 28-JAN-07
piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.156.1.1.
613092346 tag=TAG20070128T232545 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-JAN-07
Starting Control File and SPFILE Autobackup at 28-JAN-07
piece handle=/opt/oracle/product10gpr2/dbs/c-1493612009-20070128-07
comment=NONE
Finished Control File and SPFILE Autobackup at 28-JAN-07
RMAN> list backup of datafile 11;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
129 Full 669.09M DISK 00:00:15 28-JAN-07
BP Key: 129 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T223735
Piece Name: /opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455
List of Datafiles in backup set 129
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 2747296 28-JAN-07 /u02/ORACLE/opsdba/drtbs1.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136 Full 669.73M DISK 00:00:21 28-JAN-07
BP Key: 136 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T232520
Piece Name: /opt/oracle/backup/opsdba/OPSDBA.20070128.155.1.1.
613092320
List of Datafiles in backup set 136
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 2748771 28-JAN-07 /u02/ORACLE/opsdba/drtbs1.dbf
RMAN> list backup of datafile 12;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136 Full 669.73M DISK 00:00:21 28-JAN-07
BP Key: 136 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T232520
Piece Name: /opt/oracle/backup/opsdba/OPSDBA.20070128.155.1.1.
613092320
List of Datafiles in backup set 136
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
12 Full 2748771 28-JAN-07 /u02/ORACLE/opsdba/drtbs2.dbf
RMAN>
Thursday, July 15, 2010
UNIX FOR DBA
How to kill all similar processes with single command (in this case opmn)
ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}
Locating Files under a particular directory
find . -print |grep -i test.sql
Using AWK in UNIX
To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)
ps -ef |grep -i oracle |awk '{ print $2 }'
Changing the standard prompt for Oracle Users
Edit the .profile for the oracle user
PS1="`hostname`*$ORACLE_SID:$PWD>"
Display top 10 CPU consumers using the ps command
/usr/ucb/ps auxgw | head -11
Show number of active Oracle dedicated connection users for a particular ORACLE_SID
ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l
Total number of semaphores held by all instances on server
ipcs -as | awk '{sum += $9} END {print sum}'
View allocated RAM memory segments
ipcs -pmb
Manually deallocate shared memeory segments
ipcrm -m ''
Show mount points for a disk in AIX
lspv -l hdisk13
Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory
du -ks * | sort -n| tail
Display total file space in a directory
du -ks .
Cleanup any unwanted trace files more than seven days old
find . *.trc -mtime +7 -exec rm {} \;
Locate Oracle files that contain certain strings
find . -print | xargs grep rollback
Locate recently created UNIX files (in the past one day)
find . -mtime -1 -print
Finding large files on the server (more than 100MB in size)
find . -size +102400 -print
Crontab :
To submit a task every Tuesday (day 2) at 2:45PM
45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1
To submit a task to run every 15 minutes on weekdays (days 1-5)
15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1
To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)
15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1
ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}
Locating Files under a particular directory
find . -print |grep -i test.sql
Using AWK in UNIX
To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)
ps -ef |grep -i oracle |awk '{ print $2 }'
Changing the standard prompt for Oracle Users
Edit the .profile for the oracle user
PS1="`hostname`*$ORACLE_SID:$PWD>"
Display top 10 CPU consumers using the ps command
/usr/ucb/ps auxgw | head -11
Show number of active Oracle dedicated connection users for a particular ORACLE_SID
ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l
Total number of semaphores held by all instances on server
ipcs -as | awk '{sum += $9} END {print sum}'
View allocated RAM memory segments
ipcs -pmb
Manually deallocate shared memeory segments
ipcrm -m '
Show mount points for a disk in AIX
lspv -l hdisk13
Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory
du -ks * | sort -n| tail
Display total file space in a directory
du -ks .
Cleanup any unwanted trace files more than seven days old
find . *.trc -mtime +7 -exec rm {} \;
Locate Oracle files that contain certain strings
find . -print | xargs grep rollback
Locate recently created UNIX files (in the past one day)
find . -mtime -1 -print
Finding large files on the server (more than 100MB in size)
find . -size +102400 -print
Crontab :
To submit a task every Tuesday (day 2) at 2:45PM
45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1
To submit a task to run every 15 minutes on weekdays (days 1-5)
15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1
To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)
15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1
Difference Between LMTS and DMTS
Locally Managed Tablespaces:
A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables (like sys.uet$, sys.fet$) in the data dictionary (except for special cases such as tablespace quota information).
When you create a locally managed tablespace, header bitmaps are created for each datafile. If more datafiles are added, new header bitmaps are created for each added file.
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.
Dictionary Managed Tablespaces:
In DMT, to keep track of the free or used status of blocks, oracle uses data dictionry tables. When an extent is allocated or freed for reuse, free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.
Advantages of Locally Managed Tablespaces(LMT) over Dictionary Managed Tablespaces(DMT):
1. Reduced recursive space management
2. Reduced contention on data dictionary tables
3. No rollback generated
4. No coalescing required
Converting DMT to LMT:
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local(‘ts1′);
PL/SQL procedure successfully completed.
Converting LMT to DMT:
SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local(‘ts2′);
PL/SQL procedure successfully completed.
Important Points:
1. LMTs can be created as
a) AUTOALLOCATE: specifies that the tablespace is system managed. Users cannot specify an extent size.
b) UNIFORM: specifies that the tablespace is managed with uniform extents of SIZE bytes. The default SIZE is 1 megabyte.
2. One cannot create a locally managed SYSTEM tablespace in 8i.
3. This is possible with in 9.2.0.X, where SYSTEM tablespace is created by DBCA as locally managed by default. With a locally managed SYSTEM tablespace, the rest of the tablespaces in such database have to be locally managed as well.
4. Locally managed temporary tablespaces can not be of type “permanent”.
A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables (like sys.uet$, sys.fet$) in the data dictionary (except for special cases such as tablespace quota information).
When you create a locally managed tablespace, header bitmaps are created for each datafile. If more datafiles are added, new header bitmaps are created for each added file.
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.
Dictionary Managed Tablespaces:
In DMT, to keep track of the free or used status of blocks, oracle uses data dictionry tables. When an extent is allocated or freed for reuse, free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.
Advantages of Locally Managed Tablespaces(LMT) over Dictionary Managed Tablespaces(DMT):
1. Reduced recursive space management
2. Reduced contention on data dictionary tables
3. No rollback generated
4. No coalescing required
Converting DMT to LMT:
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local(‘ts1′);
PL/SQL procedure successfully completed.
Converting LMT to DMT:
SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local(‘ts2′);
PL/SQL procedure successfully completed.
Important Points:
1. LMTs can be created as
a) AUTOALLOCATE: specifies that the tablespace is system managed. Users cannot specify an extent size.
b) UNIFORM: specifies that the tablespace is managed with uniform extents of SIZE bytes. The default SIZE is 1 megabyte.
2. One cannot create a locally managed SYSTEM tablespace in 8i.
3. This is possible with in 9.2.0.X, where SYSTEM tablespace is created by DBCA as locally managed by default. With a locally managed SYSTEM tablespace, the rest of the tablespaces in such database have to be locally managed as well.
4. Locally managed temporary tablespaces can not be of type “permanent”.
Subscribe to:
Posts (Atom)