DBA AUTOMATION
SUBMITTED IN FULFILLMENT OF THE REQUIREMENT OF SUMMER TRAINING, 2012
BY PRATEEK BHARDWAJ JAIPUR ENGINEERING COLLEGE UNDER THE SUPERVISION OF
SACHIN TOMAR SENIOR ORACLE DBA PSB, WIPRO INFOTECH PROJECT WORK CARRIED OUT AT WIPRO INFOTECH, DELHI
JUNE-JULY, 2012
JAIPUR ENGINEERING COLLEGE, KUKAS
CERTIFICATE
This is to certify that the Project entitled DBA AUTOMATION And submitted by
PRATEEK BHARDWAJ
In partial fulfillment of the requirements of summer training, embodies the work done by him/her under my supervision.
Date: __________
Signature of the Supervisor Name : Designation
JAIPUR ENGINEERING COLLEGE, KUKAS
CERTIFICATE
This is to certify that the Project entitled DBA AUTOMATION And submitted by
PRATEEK BHARDWAJ
In partial fulfillment of the requirements of summer training, embodies the work done by him/her under my supervision.
Date: __________
Signature of the Supervisor Name : Designation
ACKNOWLEDGEMENTS This project extended my knowledge in an area that is of great interest to me both technically and functionally. I have benefited greatly from many people. I would like to express my heartfelt thanks to their never ending support. I would like to thank my mentor Sachin Tomar, Senior ORACLE DBA whose motivation and knowledge transfer has been invaluable to me. I would like to express my thanks and gratitude to Mr.Ravikant Sharma, Mr.Pravesh Pandey, Ms. Anshu Sharma, Mr. Amit Maheshwari who has guided me in functional as well as technical areas throughout the period despite their busy schedules. My heart-full thanks to my supervisor and DBA team, for their valuable time to inspect my dissertation work. I would like to acknowledge and express my gratitude to Wipro InfoTech and DBA team at Wipro InfoTech who provided provided generous amounts of support and cooperation during this training endeavour.
ABSTRACT DBA Automation is mainly designed to automate the DBA task in day to day operation. The Major focuses which I found in DBA, where they are most of time busy are... Handling some major issues related with control file Performance check as well as Health check though DBA
tools Databases Backup through RMAN
With the help of DBA tool user can… Automate the Backup. There will be no interaction
required by DBAs and Backup will be triggered automatically on its scheduled time and same will be informed to Backup Admin and to DBA Admin on its Completion. Database Health Check-up list would provide the
Database health status like tablespace etc… DBA can get reports on just a single click.
Table of Contents 1. Title Page 2. Certificate from Supervisor 3. Acknowledgement 4. Abstract 5. Organization of the Documents
Chapter 1: Introduction 1.1 Problem Introduction 1.1.1 Motivation 1.1.2 Project Objective 1.1.3 Scope of the Project 1.2. What is ORACLE 1.3. Brief History of ORACLE
Chapter 2: Architecture 2.1 Purpose of the document 2.2 ORACLE Architecture components 2.2.1 ORACLE INSTANCE 2.2.2 ORACLE DATABASE 2.2.3 PHYSICAL STRUCTURE 2.2.4 MEMORY STRUCTURE 2.2.4.1 SYSTEM GLOBAL AREA (SGA) 2.2.4.2 SHARED POOL 2.2.5 DATABASE BUFFER CACHE 2.2.6 REDO LOG BUFFER CACHE 2.2.7 LARGE POOL 2.2.8 BACKUP AND RESTORE 2.2.9 PROGRAM GLIBAL AREA COMPONENTS 2.2.10 BACKGROUNG PROCESS
Chapter 3:Control file12 3.1 control file – introduction 3.2 control file contents 3.3 multiplexing control file 3.4 ISSUE- creation of database through a control file
Chapter 4:DBA Tools Implementation & Results 4.1 DBA Tools 4.2 How to Launch DBA Tools 4.3 Health check 4.4 control file backup 4.5 Performance check 4.6 Backup through RMAN 4.7 ISSUE: Datafile SYSTEM01.DBF has been deleted 4.8 ISSUE : If a newly created datafile has been deleted.
Chapter 5 Conclusion 5.1 Conclusions 5.2 Appendix: 5.3 BIBLIOGRAPHY
Organization of the document
This document includes the following chapters: Chapter 1. Introduction . This chapter explains why automation is required. Chapter 2. Architecture. This chapter discusses the components of Oracle
and their functionality. Chapter 3. Control file . This chapter presents the functionality and
requirements of control file as well as various issues that can arise with the control file. Chapter 4: DBA Tools implementation and Results. This chapter presents
the controlling of database through OEM (Oracle Enterprise Manager) and Backup through RMAN (Recovery Manager)
Chapter 5 : Conclusion, Appendix, Bibliography .
CHAPTER 1 Introduction
This chapter contains the following topics: 1.1 Problem Introduction 1.1.1 Motivation 1.1.2 Project Objective 1.1.3 Scope of the Project
1.2. What is ORACLE 1.3. Brief History of ORACLE
1.1 Problem Introduction 1.1.1 Motivation: While studying DBA I found most of time DBA’s are busy with day to day maintenance activity, which required a separate resource to perform fix task as well as reflect as an extra cost to support the day to day operation.
1.1.2 Project Objective: the objective behind this project is to focus on certain issues that can arise while working as a DBA at any site and reduce the workload of a DBA so he also can focus on other part of operation 1.1.3 Scope of the Project: This Project can be used at any side for ORACLE database.
1.2 What is ORACLE:
The Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is an object-relational database management system (ORDBMS)[2] produced and marketed by Oracle Corporation. Larry Ellison and his friends and former co-workers Bob Miner and Ed Oates started the consultancy Software Development Laboratories (SDL) in 1977. SDL developed the original version of the Oracle software. The name Oracle comes from the code-name of a CIA-funded project Ellison had worked on while previously employed by Ampex.[3] 1.3 Brief History of ORACLE
In 1977, Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories to undertake development work .After reading a paper by Codd in IBM Journal of Research and Development, they created Oracle. The first version was never released. It was written in Assembly language of PDP which ran in 128kb of RAM. Version 2.0 of Oracle was released in 1979 and it became first commercial relational database and first SQL database. The company changed its name to Relational Software Inc. (RSI). In 1981, RSI started developing tools for Oracle. In 1982, RSI was renamed to Oracle Corporation. It held its first user conference in San Francisco. In
1983, Oracle released version 3.0, which was rewritten in C language and ran on multiple platforms.
In 1984, Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency etc. By 1985, Oracle released version 5.0 and became the first relational database that worked in client/server environment. In 1986, Oracle goes public on the NASDAQ exchange. In 1987, Oracle wanted to create enterprise applications that take advantage of their database. In 1988, Oracle version 6.0 was released. It provided row-level locking, hot backup and PL/SQL as main features. By 1989, Oracle moved to new headquarters in Redwood Shores, California. In 1990, they released Oracle Applications Release 8, which included account software for client/server .In 1992, they released Oracle 7.0. It provided better performance, administrative utilities, application development tools, security features, stored procedures, triggers and declarative integrity. In 1995, Oracle became the first major company to announce a comprehensive internet strategy. In 1997, Oracle released Oracle 8.0 and Oracle Applications 10.7. It started embracing Java. Partitioning, support for different types of data like images, large text, external data etc.(lobs) are provided. It also started providing support for Object in database becoming an Object-Relational DBMS .By 1999, Oracle realized that "Internet Changes Everything". Oracle released Oracle 8i and Oracle Applications 11i. They supported open standards like XML. Oracle8i provided Java Virtual Machine (JVM) to run Java program in Oracle Database and also scalability, which was required for internet databases. . In 2000, Oracle9iAS was released. Oracle Corporation is no longer a company providing only database management system and instead started providing all that it takes to develop and deploy a complete application. AS(Application Server) runs on middle tier in 3-tier Client/Server architecture boosting the performance. In 2001, Oracle9i was released. It allows Oracle to run on RAC (Real Application Cluster), which is a collection of low-cost servers. It also allowed XML documents to be stored and queried in Oracle Database. In 2003, Oracle10g was released, where g stands for Grid computing, which servers computing power across the enterprise as a utility, automatically shifting processing load based on demand. Oracle10g also made a lot of administrative tasks automatic. In 2007, Oracle has released Oracle11g. The new version focused on better partitioning, easy migration etc. As of now, Oracle has 68,000 employees, 2, 75,000 customers with US$14 billion revenue. It is run by Larry Ellison (CEO), Charles Phillips (president),Safra Catz (president and CFO).
CHAPTER 2 Architecture This chapter contains the following topics: 2.1 Purpose of the document 2.2 ORACLE Architecture components 2.2.1 ORACLE INSTANCE 2.2.2 ORACLE DATABASE 2.2.3 PHYSICAL STRUCTURE 2.2.4 MEMORY STRUCTURE 2.2.4.1 SYSTEM GLOBAL AREA (SGA) 2.2.4.2 SHARED POOL 2.2.5 DATABASE BUFFER CACHE 2.2.6 REDO LOG BUFFER CACHE 2.2.7 LARGE POOL 2.2.8 BACKUP AND RESTORE 2.2.9 PROGRAM GLIBAL AREA COMPONENTS 2.2.10 BACKGROUNG PROCESS
2.1 Purpose of this document This document provides a description of how the query run in the oracle environment, how the data is fetched and fed into the oracle server from the user.
2.2 ORACLE ARCHITECTURAL COMPONENTS
2.2.1 Oracle instance: An Oracle instance is the combination of the background
processes and memory structures. The instance must be started to access the data in the database. Every time an instance is started, a System Global Area (SGA) is allocated and Oracle background processes are started. Background processes perform functions on behalf of the invoking process. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user. The background processes perform input/output (I/O) and monitor other Oracle processes to provide increased parallelism for better performance and reliability. 2.2.2 Oracle database: An Oracle database consists of operating system files, also known as database files,that provide the actual physical storage for database information. The database files are used to ensure that the data is kept consistent and can be recovered in the event of a failure of the instance .
2.2.3 Physical Structure The physical structure of an Oracle database is determined by the operating system files that provide the actual physical storage for database information. • Control files • Data files • Redo log files
2.2.4 Memory Structure Oracle’s memory structure consists of two memory areas known as: • System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance. • Program Global Area (PGA): Allocated when the server process is started
2.2.4.1 System Global Area (SGA) • The SGA consists of several memory structures: – Shared pool – Database buffer cache – Redo log buffer – Other structures (e.g. lock and latch management, statistical data) • There are two optional memory structures that can be configured within the SGA: – Large pool – Java pool
System Global Area
The SGA is also called the shared global area. It is used to store database information that is shared by database processes. It contains data and control information for the Oracle server and is allocated in the virtual memory of the computer where Oracle resides.
2.2.4.2 Shared Pool The shared pool is used to store the most recently executed SQL statements and the most recently used data definitions.
• It consists of two key performance-related memory structures: – Library cache – Data dictionary cache
• Sized by the parameter SHARED_POOL_SIZE. The shared pool environment contains both fixed and variable structures. The fixed structures remain relatively the same size, whereas the variable structures grow and shrink based on user and program requirements. The actual sizing for the fixed and variable structures is based on an initialization parameter and the work of an Oracle internal algorithm. Library Cache
The library cache size is based on the sizing defined for the shared pool. Memory is allocated when a statement is parsed or a program unit is called. If the size of the shared pool is too small, statements are continually reloaded into the library cache, which affects performance. The library cache is managed by a least recently used (LRU) algorithm. As the cache fills, less recently used execution paths and parse trees are removed from the library cache to make room for the new entries. If the SQL or PL/SQL statements are not reused, they eventually are aged out. The library cache consists of two structures: • Shared SQL : The Shared SQL stores and shares the execution plan and parse
tree for SQL statements run against the database. The second time that an identical SQL statement is run, it is able to take advantage of the parse information available in the shared SQL to expedite its execution. To ensure that SQL statements use a shared SQL area whenever possible, the text, schema, and bind variables must be exactly the same.
• Shared PL/SQL : The shared PL/SQL area stores and shares the most recently
executed PL/SQL statements. Parsed and compiled program units and procedures (functions, packages, and triggers) are stored in this area.
2.2.5 Database Buffer Cache When a query is processed, the Oracle server process looks in the database buffer cache for any blocks it needs. If the block is not found in the database buffer cache, the server process reads the block from the data file and places a copy in the database buffer cache. Because subsequent requests for the same block may find the block in memory, the requests may not require physical reads. The Oracle server uses a least recently used algorithm to age out buffers that have not been accessed recently to make room for new blocks in the database buffer cache.
2.2.6 Redo Log Buffer Cache
The redo log buffer cache is a circular buffer that contains changes made to data file blocks. This information is stored in redo entries. Redo entries contain the information necessary to recreate the data prior to the change made by INSERT, UPDATE, DELETE, CREATE,ALTER, or DROP operations. 2.2.7 Large Pool
When users connect through the shared server, Oracle needs to allocate additional space in the shared pool for storing information about the connections between the user processes, dispatchers, and servers. The large pool relieves the burden on areas within the shared pool. The shared pool does not have to give up memory for caching SQL parse trees in favour of shared server session information, I/O, and backup and recovery processes. The performance gain is from the reduction of overhead from increasing and shrinkage of the shared SQL cache. 2.2.8 Backup and Restore
Recovery Manager (RMAN) uses the large pool when the BACKUP_DISK_IO= n and BACKUP_TAPE_IO_SLAVE = TRUE parameters are set. If the large pool is configured but is not large enough, the allocation of memory from the large pool fails. RMAN writes an error message to the alert log file and does not use I/O slaves for backup or restore . 2.2.9 Program Global Area Components
The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and de-
allocated when the process is terminated. In contrast to the SGA, which is shared by several processes, the PGA is an area that is used by only one process. In a dedicated server configuration, the PGA includes these Components: • Sort area: Used for any sorts that may be required to process the SQL statement • Session information: Includes user privileges and performance statistics for the session • Cursor state: Indicates the stage in the processing of the SQL statements that are currently used by the session • Stack space: Contains other session variables
2.2.10 Background Processes The relationship between the physical and memory structures is maintained and enforced by Oracle’s background processes. • Mandatory background processes DBWn PMON CKPT LGWR SMON RECO • Optional background processes ARCn LMON Snnn QMNn LMDn CJQ0 Pnnn LCKn Dnnn • QMNn: Advanced Queuing • ARCn: Archiver • LCKn: RAC Lock Manager — Instance Locks • LMON: RAC DLM Monitor— Global Locks • LMDn: RAC DLM Monitor— Remote Locks • CJQ0: Snapshot Refresh • Dnnn: Dispatcher • Snnn: Shared Server • Pnnn: Parallel Query Slaves Database Writer
The server process records changes to rollback and data blocks in the buffer cache. Database Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. It ensures that a sufficient number of free buffers — buffers that can be overwritten when server processes need to read in blocks from the data files — are available in the database buffer cache. Database performance is improved because server processes make changes only in the buffer cache. DBWn defers writing to the data files until one of the following events occurs: • Incr emental or normal checkpoint • The number of dirty buffers reaches a threshold value
• A process scans a specified number of blocks when scanning for free buffers and cannot find any. • Timeout occurs. • A ping request in Real Application Clusters environment. • Placing a normal or temporary tablespace offline. • Placing a tablespace in read only mode. • Dropping or Truncating a table. LOG Writer
LGWR performs sequential writes from the redo log buffer cache to the redo log file under the following situations: • When a transaction commits • When the redo log buffer cache is one-third full • When there is more than a megabyte of changes records in the redo log buffer cache • Before DBWn writes modified blocks in the database buffer cache to the data files • Every 3 seconds. Because the redo is needed for recovery, LGWR confirms the commit only after the redo is written to disk. LGWR can also call on DBWn to write to the data files. System Monitor
If the Oracle instance fails, any information in the SGA that has not been written to disk is lost. For example, the failure of the operating system causes an instance failure. After the loss of the instance, the background process SMON automatically performs instance recovery when the database is reopened. Instance recovery consists of the following steps: 1. Rolling forward to recover data that has not been recorded in the data files but that has been recorded in the online redo log. This data has not been written to disk because of the loss of the SGA during instance failure. During this process, SMON reads the redo log files and applies the changes recorded in the redo log to the data blocks. Because all committed transactions have been written to the redo logs, this process completely recovers these transactions. 2. Opening the database so that users can log on. Any data that is not locked by unrecovered transactions is immediately available. 3. Rolling back uncommitted transactions. They are rolled back by SMON or by the individual server processes as they access locked data. SMON also performs some space maintenance functions: • It combines, or coalesces, adjacent areas of free space in the data files. • It de-allocates temporary segments to return them as free space in data files. Temporary segments are used to store data during SQL statement processing.
Process Monitor
The background process PMON cleans up after failed processes by: • Rolling back the user’s current transaction • Releasing all currently held table or row locks • Freeing other resour ces currently reserved by the user • Restarts dead dispatchers Checkpoint
An event called a checkpoint occurs when the Oracle background process DBWn writes all the modified database buffers in the SGA, including both committed and uncommitted data, to the data files. Checkpoints are implemented for the following reasons: • Checkpoints ensure that data blocks in memory that change frequently are written to data files regularly. Because of the least recently used algorithm of DBWn, a data block that changes frequently might never qualify as the least recently used block and thus might never be written to disk if checkpoints did not occur. • Because all database changes up to the checkpoint have been recorded in the data files, redo log entries before the checkpoint no longer need to be applied to the data files if instance recovery is required. Therefore, checkpoints are useful because they can expedite instance recovery. The Archiver Process
All other background processes are optional, depending on the configuration of the database; however, one of them, ARCn, is crucial to recovering a database after the loss of a disk. As online redo log files fill, the Oracle server begins writing to the next online redo log file. The process of switching from one redo log to another is called a log switch. The ARCn process initiates backing up, or archiving, of the filled log group at every log switch. It automatically archives the online redo log before the log can be reused, so that all of the changes made to the database are preserved. This enables the DBA to recover the database to the point of failure, even if a disk drive is damaged Archiving Redo Log Files
One of the important decisions that a DBA has to make is whether to configure the database to operate in ARCHIVELOG or in NOARCHIVELOG mode. NOARCHIVELOG Mode: In NOARCHIVELOG mode, the online redo log files are overwritten each time a log switch occurs. LGWR does not overwrite a redo log group until the checkpoint for that group is complete. This ensures that committed data can be recovered if there is an instance crash. During the instance crash, only the SGA is lost. There is no loss of disks, only memory. For example, an operating system crash causes an instance crash.
ARCHIVELOG Mode: If the database is configured to run in
ARCHIVELOG mode, inactive groups of filled online redo log files must be archived before they can be used again. Since changes made to the database are recorded in the online redo log files, the database administrator can use the physical backup of the data files and the archived online redo log files to recover the database without losing any committed data because of any single point of failure, including the loss of a disk. Usually, a production database is configured to run in ARCHIVELOG mode.
CHAPTER 3 Control File
3.1 control file – introduction 3.2 control file contents 3.3 multiplexing control file 3.4 ISSUE- creation of database through a control file
3.1 CONTROL FILE – Introduction
The control file is a binary file that defines the current state of the physical database.. • Loss of the control file requires recovery • Is read at MOUNT stage • Is required to operate • Is linked to a single database • Should be multiplexed • Maintains integrity of database • Sized initially by CREATE DATABASE The control file is a small binary file necessary for the database to start and operate successfully. Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to use. A control file is updated continuously by the Oracle server during database use, so it must be available for writing whenever the database is open. The information in the control file can be modified only by the Oracle server; no database administrator or end user can edit th e control file. If for some reason the control file is not accessible, the database does not function properly. If all copies of a database’s control files are lost, the database must be recovered before it can be opened. At least one control file is required, but control files can be multiplexed up to eight times. 3.2 Control File Contents
The information in the control file includes: • Database name is taken from either the name specified by the initialization parameter DB_NAME or the name used in the CREATE DATABASE statement. • Database identifier is recorded when the database is created. • Time stamp of database creation is also recorded at database creation. • Names and locations of associated data files and online redo log files are updated when a data file or redo log is added to, renamed in, or dropped from the database. • Tablespace information is updated as tablespaces are added or dropped. • Redo log history is recorded during log switches. • Location and status of archived logs are recorded when archiving occurs. • Location and status of backups are recorded by the Recovery Manager utility. • Current log sequence number is recorded when log switches occur. • Checkpoint information is recorded as checkpoints are made.
The control file consists of two types of sections: • Reusable • Not reusable Reusable sections store Recovery Manager information, such as backup data file names and backup redo log file names. They are used in a circular manner and can be reused only by Recovery Manager. 3.3 Multiplexing the Control File
To safeguard against a single point of failure of the control file, it is strongly recommended that the control file be multiplexed, storing each copy on a different physical disk. If a control file is lost, a copy of the control file can be used to restart the instance without database recovery. Control files can be multiplexed up to eight times. The Oracle server creates and maintains all files listed in this parameter when the instance is started. The database administrator can multiplex control files by: • Creating multiple control files when the database is created by including the control file names in the CONTROL_FILES initialization parameter You can create a backup of a control file, but you cannot bring a control file back from a backup without its appropriate data files. The control file is a living file that corresponds to current database status. ALTER DATABASE BACKUP CONTROLFILE TO 'FILENAME'
You can also backup your control file to a trace file. This will create a file with the SQL statements required to recreate your control file. ALTER DATABASE BACKUP CONTROLFILE TO TRACE
3.4 ISSUE: CREATION OF A DATABASE FROM THE CONTROL FILE OF ANOTHER DATABASE
STEPS TO FOLLOW: 1. CREATE TRACE FILE FROM THE BACKUP CONTROL FILE SQL> alter database backup control file to trace; Database altered. 2. Copy Pfile from source database (orcl) and then make changes in that regarding path settings (make them according to your target database (leopard)). 3. Save that file as “name of the file.ora” 4. Copy data files from source database to target database in data folder of leopard 5. Copy redolog files from source database to target database in redolog folder leopard 6.
Run command prompt as administrator and create an instance of leopard using command
Oradim –new –sid lion 7. Set the sid lion using command set ORACLE_SID=leopard 8. Make changes in the trace file as follows 9. STARTUP NOMOUNT 10. CREATE CONTROLFILE SET DATABASE "LEOPARD" RESETLOGS NOARCHIVELOG 11. MAXLOGFILES 16 12. MAXLOGMEMBERS 3
13. MAXDATAFILES 100 14. MAXINSTANCES 8 15. MAXLOGHISTORY 292 16. LOGFILE 17. GROUP 1 ( 18. 'C:\APP\PRATEEK\ADMIN\LEOPARD\REDO\LOG1A.RDO', 19. 'C:\APP\PRATEEK\ADMIN\LEOPARD\REDO\LOG1B.RDO' 20. ) SIZE 50M BLOCKSIZE 512, 21. GROUP 2 ( 22. 'C:\APP\PRATEEK\ADMIN\LEOPARD\REDO\REDO02.LOG', 23. 'C:\APP\PRATEEK\ADMIN\LEOPARD\REDO\LOG2B.RDO' 24. ) SIZE 50M BLOCKSIZE 512, 25. GROUP 3 ( 26. 'C:\APP\PRATEEK\ADMIN\LEOPARD\REDO\LOG31A.RDO', 27. 'C:\APP\PRATEEK\ADMIN\LEOPARD\REDOLOG31B.RDO' 28. ) SIZE 50M BLOCKSIZE 512, 29. GROUP 4 ( 30. 'C:\APP\PRATEEK\ADMIN\LEOPARD\REDO\LOG41A.RDO', 31. 'C:\APP\PRATEEK\ADMIN\LEOPARD\REDO\LOG41B.RDO' 32. ) SIZE 50M BLOCKSIZE 512 33. -- STANDBY LOGFILE 34. DATAFILE 35. 'C:\APP\PRATEEK\ADMIN\LEOPARD\DATA\SYSTEM01.DBF', 36. 'C:\APP\PRATEEK\ADMIN\LEOPARD\DATA\SYSAUX01.DBF', 37. 'C:\APP\PRATEEK\ADMIN\LEOPARD\DATA\UNDOTBS01.DBF', 38. 'C:\APP\PRATEEK\ADMIN\LEOPARD\DATA\USERS01.DBF', 39. 'C:\APP\PRATEEK\ADMIN\LEOPARD\DATA\EXAMPLE01.DBF', 40. 'C:\APP\PRATEEK\ADMIN\LEOPARD\DATA\FINANCE1.DBF' 41. CHARACTER SET AL32UTF8 42. ; 43. Control file created This will generate a new control file for the database leopard 44. use commands Alter system switch logfile; Recover database until cancel using backup control file; Alter database open resetlogs.
CHAPTER 4 DBA Tools implementation and results
4.1 DBA Tools 4.2 How to Launch DBA Tools 4.3 Health check 4.4 control file backup 4.5 Performance check 4.6 Backup through RMAN 4.7 ISSUE: Datafile SYSTEM01.DBF has been deleted 4.8 Issue : If a newly created datafile has been deleted but we have the well as the archive logs.
backup as
4.1 DBA Tools Standard applications that can be launched from the Console: Instance Manager • •
Security Manager
•
Storage Manager
•
Schema Manager
•
SQL*Plus Worksheet
The DBA Tools
The standard applications that are supplied with Oracle Enterprise Manager include the following: • Instance Manager: Performs startup, shutdown and monitor databases • Security Manager: Used to manage users and privileges • Storage Manager: Maintains tablespaces, data files, rollback segments and log groups • Schema Manager: Used to create and maintain objects such as tables, indexes, and views • SQL*Plus Worksheet: Provides the capability to issue SQL statements against any Database 4.2 How to Launch DBA Tools
• Launch the Console in standalone mode • Expand the databases folder and expand the relevant database • Select tools such as Instance Manager, Schema Manager
4.3 HEALTH CHECK OF THE DATABSE AS WELL AS THE MACHINE THROUGH OEM
4.4 Control file backup option on grid control
Snapshot for the Control file record information
4.5 Performance of the instance orcl
4.6
BACKUP OF DATABASE THROUGH RMAN RMAN> backup database format 'C:\Users\prateek\Documents\backup\%U';
4.7 ISSUE: DATAFILE SYSTEM01.DBF HAS BEEN DELETED
Enter below commands to recover the SYSTEM01.DBF RMAN> restore database; RMAN> recover database; RMAN>alter database open;
4.8 Issue : If a newly created datafile has been deleted but we have the backup as well as the archive logs. Sql>alter database create datafile 'C:\app\prateek\oradata\orcl\manU.dbf' as 'C:\app\prateek\oradata\orcl\manU.dbf' size 500m;
ManU= the deleted datafile . Start RMAN and Follow the below commands
RMAN>restore database;
RMAN>recover database;
RMAN>alter database open;
Sql> select * from manU.wayne; This will show all the contents of the table stored in datafile manu under the user nani
CHAPTER 5 CONCLUSION
5.1 Conclusions 5.2 Appendix: 5.3 BIBLIOGRAPHY
5.1 Conclusions This Automation will help to generate database from the control file of other database, automate the backup of databases. There will be saving of resource cost while using this tool. Databases health checklist is also most beneficial part for DBA and can be used at any location with different queries.
5.2 Appendix: RDMS: Relational Database Management System, Which is the backbone of all the databases like Oracle, SQL etc. Online backup (Hot Backup): Backup that is taken when database is up and running. Offline backup (Cold Backup): Backups that are taken when the database is offline. This backup is consistent. 5.3 BIBLIOGRAPHY http://www.docs.oracle.com http://www.myitguid.com http://www.oracle.com
