Oracle Database Management Tools & Maintenance (IT-004)

1. Oracle Database Management Tools & Maintenance (IT-004) Bobby Dye bdye@commandalkon.com 256-879-3282 EXT. 2183

2. Session Objective This session is intended for individuals who administer an Oracle COMMANDseries database for their company. Oracle 10g Database Maintenance and Enterprise Manager will be discussed and demonstrated. 11g will be introduced. Oracle Database Management Tools & Maintenance

3. OBJECTIVES • General overview • Basic Proactive Database Monitoring • Alerts • Listener • Exports • Oracle Enterprise Manager 10g Demonstration • Show warning and critical alert thresholds • Use tuning and diagnostic advisors • Use the Automatic Database Diagnostic Monitor (ADDM) • Backup and Recovery • Backup Types and Techniques • Backup/Recovery using Enterprise Manager • Backup Considerations Oracle Database Management Tools & Maintenance

4. OVERVIEW Overview of Oracle Database Management Tools • Allows Database Administrators to administer and manage an Oracle Server through the use of GUI programs and basic monitoring. • Can also be used to perform statistics gathering, performance tuning, Structure Querying Language (SQL) tracing, event handling and monitoring. • Oracle Enterprise Manager can be used to schedule jobs, examine database explain plans and other information for true enterprise-level maintenance. Oracle Database Management Tools & Maintenance

5. Basic Monitoring • ALERT LOG • First place to look for Database Issues • Common Issues • LISTENER LOG • Database Network connection messages • Common Issues • EXPORT LOG • Database Export success/failure messages Oracle Database Management Tools & Maintenance

6. Basic Monitoring ALERT LOG • Chronologically reports messages and errors arising from daily operation • Startup and Shutdowns of the instance • Create, alter and drop SQL statements on database • Errors causing trace files • The directory where it is found can be determined by the background_dump_dest initialization parameter • SQL> Show parameter background_dump_dest • C:\oracle\admin\orcl10g\bdump\alert_ORCL10G.log • Rename or Delete Large Alert logs – alert_ORCL10G_orig.log • Oracle recreates a new alert.log the next time it writes to it. Oracle Database Management Tools & Maintenance

7. Basic Monitoring ALERT LOG • Errors Encountered in Command Series Database Alert Log • ORA-01578 (Block Corruption) • Often happens after power outage and/or Server crash • ORA-00600 (internal errors) • May require DB initialization parameter adjustment, i.e. SGA • Thread 1 cannot allocate new log, sequence 1558 Checkpoint not complete. • Redo Logs may need reconfiguration • ORA-01652 (unable to extend temp segment) • May only be an issue if reported frequently. Oracle Database Management Tools & Maintenance

8. Basic Monitoring ALERT LOG Oracle Database Management Tools & Maintenance

9. Basic Monitoring Listener.log • If a network error occurs, applications that depend on network services from Oracle Net Services, will normally generate an error message. • C:\oracle\product\10.2.0\db_1\network\log\listener.log • To refresh Large logs without stopping listener: • > cd \oracle\product\10.2.0\db_1\network\log • > lsnrctl set log_status off • > rename listener.log listener.old • > lsnrctl set log_status on • Common Network Error Messages • ORA-03113: TNS:end-of-file on communication channel • ORA-12154: TNS:could not resolve service name • ORA-12170: TNS:Connect timeout occurred • TNS-12500/ORA-12500: TNS: listener failed to start a dedicated server process Oracle Database Management Tools & Maintenance

10. Basic Monitoring Listener.log • Errors Encountered in Command Series Listener.log • ORA-03113: TNS:end-of-file on communication channel • Database may be down or communication link issue • Check database alert.log • ORA-12154: TNS:could not resolve service name • DB Service name not defined properly in tnsnames.ora • ORA-12170: TNS:Connect timeout occurred • May be a result of network/system delays • TNS-12500/ORA-12500: TNS: listener failed to start a dedicated server process • Max number of processes allowed exceeded • Indicative of the 32bit Memory limitation for number of processes. Oracle Database Management Tools & Maintenance

11. Basic Monitoring Listener.log Oracle Database Management Tools & Maintenance

12. Basic Monitoring Export Log • Most commonly used Command Series backup strategy • Setup to run nightly by CAI at time of Install/Go-Live • Daily/Bi-Daily Exports in Scheduled Tasks • Snapshot of database at time of Export • Verify .dmp file exists • C:\CSBACKUPS\FRI\cmdseries.dmp • Verify other locations copied to. • Check Export Log file for errors. • C:\CSBACKUPS\FRI\cmdseries.log Oracle Database Management Tools & Maintenance

13. Basic Monitoring Export Log Oracle Database Management Tools & Maintenance

14. ENTERPRISE MANAGER 10g • Launch Enterprise Manager (EM) using Database Control under Oracle Home. • Graphically display internal information about the Oracle database and Host. • Monitor Alerts and Performance Statistics. • Proactive Maintenance • Job Scheduling • Advisors Oracle Database Management Tools & Maintenance

15. Demonstration Enterprise Manager ENTERPRISE MANAGER 10g Oracle Database Management Tools & Maintenance

16. PERFORMANCE MONITORING • Several hundred different performance statistics • Data dictionary • Object Status • Dynamic performance views • Session/System stats • File I/O • Instance Activity • Optimizer statistics • Number of rows • Average row length • Amount of empty space that is allocated to the table • Number of rows that are “chained” Oracle Database Management Tools & Maintenance

17. PERFORMANCE MONITORING • Reactive • Reactive monitoring is inevitably necessary sometimes, but your goal should be to detect and repair problems before they affect system operation • Proactive • Solving problems before they occur, or at least before they are widely noticed, is a proactive approach to system maintenance. • Server-generated alerts • Log monitoring • Automated Database Diagnostic Monitor (ADDM) Oracle Database Management Tools & Maintenance

18. REACTING TO PERFORMANCE ISSUES • Asking the Right Questions • Contact the parties affected by the problem and perform a little detective work • How many users are affected by the problem? • All users at a specific site • All sites are affected • A select group of users having a problem • How much slower is it? • Is the problem intermittent or continuous? • Has this problem occurred before? • If so, is it getting worse? • Does the problem occur at the same time every day? Oracle Database Management Tools & Maintenance

19. Automatic Database Diagnostic Monitor (ADDM) • ADDM is a "recommendation engine" that uses performance information stored in the Automatic Workload Repository (AWR) as input. • Equate snapshots stored in the AWR repository to its Statspack predecessor • By default, AWR snapshots occur every 60 minutes. Oracle Database Management Tools & Maintenance

20. ADDM Example Oracle Database Management Tools & Maintenance

21. ENTERPRISE MANAGER 10g SUMMARY • Automates monitoring, performance troubleshooting, and collecting of trend data. • Allows monitoring alerts for events such as database space allocation issues and outages. • Gives a graphical representation of the top database sessions, organizing them by such parameters as physical write, physical read, high CPU, and high I/O percentages. • Schedule and monitor RMAN full/intermediate weekly/daily backups. Oracle Database Management Tools & Maintenance

22. BACKUP AND RECOVERY Oracle Database Management Tools & Maintenance

23. Backup Types and Techniques • Export/Import • Datapump (10g) • Database File Backups • Hot (On-line) Backups (Archiving On) • Cold Backups • RMAN • Full/Incremental • Enterprise Manager • Database Recovery Demonstration • Flash Backup/Recovery (10g) Oracle Database Management Tools & Maintenance

24. Oracle Export/Import Database export import .dmp file .dmp file Oracle Database Management Tools & Maintenance

25. Oracle EXPORT • Start the operating system command prompt • Execute oracle export utility • EXP SYSTEM/MANAGER FILE=EXPDAT.DMP LOG=EXPDAT.LOG OWNER=CMDSERIES • Once the export is complete, store backup into a secure location Oracle Database Management Tools & Maintenance

26. Oracle IMPORT • Start the operating system command prompt • Execute oracle import utility • IMP SYSTEM/MANAGER FILE=EXPDAT.DMP LOG=EXPDAT.LOG FROMUSER=CMDSERIES TOUSER=CMDSERIES Oracle Database Management Tools & Maintenance

27. Oracle Export/Import • Most commonly used Oracle Database backup strategy • Setup to run nightly by CAI at time of Install/Go-Live • Daily/Bi-Daily Exports in Scheduled Tasks • Snapshot of database at time of Export • Will not provide recovery to last transaction • Check Export Log file for errors. • C:\CSBACKUPS\FRI\cmdseries.log Oracle Database Management Tools & Maintenance

28. ORACLE DATA PUMP Oracle Database Management Tools & Maintenance

29. Oracle Data Pump What is Oracle Data Pump? • Oracle Data Pump 10g technology enables very high-speed movement of data from one database to another. • Provides user interface that closely resembles the original export (exp) and import (imp) utilities, but files are not compatible. • The Data Pump clients, expdp and impdp, invoke the Data Pump Export and Import, respectively. • Because Data Pump is server-based, rather than client-based, files are accessed relative to server-based directory paths: • SQL> CREATE DIRECTORY dpump_dir AS ‘c:/CSBACKUPS/datapump’; • EXPDP and IMPDP example: • expdp system/manager directory=DPUMP_DIR dumpfile=expdp.dmp log=expdp.log schemas=CMDSERIES • impdp system/manager directory=DPUMP_DIR dumpfile=expdp.dmp log=impdp.log remap_schema=CMDSERIES:CMDSERIES Oracle Database Management Tools & Maintenance

30. Database File Backup Basics • Which Files to backup? • Datafiles • Redo Log Files • Control Files • Initialization Files • Where are Files located? • Select file_name from dba_data_files; • Select member from v$logfile; • Select name from v$controlfile; • ORACLE_HOME\database\INITorcl10g.ora, SPFILEorcl10g.ora and PWDorcl10g.ora Oracle Database Management Tools & Maintenance

31. Database Files Data Files • SQL>Select file_name from dba_data_files; • C:\ORACLE\ORADATA\ORCL10G\SYSTEM01.DBF • C:\ORACLE\ORADATA\ORCL10G\UNDOTBS01.DBF • C:\ORACLE\ORADATA\ORCL10G\DRSYS01.DBF • C:\ORACLE\ORADATA\ORCL10G\INDX01.DBF • C:\ORACLE\ORADATA\ORCL10G\TOOLS01.DBF • C:\ORACLE\ORADATA\ORCL10G\USERS01.DBF • C:\ORACLE\ORADATA\ORCL10G\XDB01.DBF • C:\ORACLE\ORADATA\ORCL10G\CSPROD01DAT01.DBF Oracle Database Management Tools & Maintenance

32. Database Files Redo Log and Control Files • SQL>Select member from v$logfile; • C:\ORACLE\ORADATA\ORCL10G\REDO01A.LOG • D:\ORACLE\ORADATA\ORCL10G\REDO01B.LOG • C:\ORACLE\ORADATA\ORCL10G\REDO02A.LOG • D:\ORACLE\ORADATA\ORCL10G\REDO02B.LOG • C:\ORACLE\ORADATA\ORCL10G\REDO03A.LOG • D:\ORACLE\ORADATA\ORCL10G\REDO03B.LOG • SQL>Select name from v$controlfile; • C:\ORACLE\ORADATA\ORCL10G\CONTROL01.CTL • D:\ORACLE\ORADATA\ORCL10G\CONTROL02.CTL • E:\ORACLE\ORADATA\ORCL10G\CONTROL03.CTL Oracle Database Management Tools & Maintenance

33. COLD BACKUP • Database must be shutdown cleanly. (Shutdown immediate/normal) • Copy All Files to backup area. (Another Disk, Tape, etc.) • Copy c:\oracle\oradata\orcl10g\system01.dbf b:\backup\system01.dbf • Like Export, Cold Backup will be snapshot of database at time of backup. • Archive logs may be applied for point-in-time recovery. • Simplest Approach for performing a full database backup. Oracle Database Management Tools & Maintenance

34. HOT BACKUP • Database stays up and accessible. • Archiving Must be on. • Set each tablespace to backup mode. • Alter tablespace SYSTEM begin backup; (end backup; after file copy) • Copy datafile to backup area (Another Disk, Tape, etc.) • Copy c:\oracle\oradata\orcl10g\system01.dbf b:\backup\system01.dbf • Write current redo log to archive log: • alter system switch logfile; • Backup control file: • alter database backup controlfile to trace as ‘b:\backup\ctltrc.sql’; • Backup Archive Logs and Initialization Files Oracle Database Management Tools & Maintenance

35. ARCHIVING • ARCHIVELOG mode is required to create backups for point-in-time recovery. • ARCHIVELOG mode allows the user to perform complete media (disk) recovery using database file backups. • When ARCHIVELOG mode is disabled, the database can be restored from backup in case of a failure, but you cannot roll forward changes to a point-in-time when the failure occurred. • SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oracle Database Management Tools & Maintenance

36. How to enable 10g Archivelog Mode • SQL>create pfile from spfile • Edit below parameters in pfile(init.ora) file. • LOG_ARCHIVE_START=TRUE (Pre-10g) • LOG_ARCHIVE_DEST=‘c:\oracle\arch’ • SQL> shutdown immediate; • SQL> create spfile from pfile; • SQL> startup mount; • SQL> alter database archivelog; • SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST • SHUTDOWN DATABASE • PERFORM COLD BACKUP • STARTUP DATABASE • Monitor Archive Logs and purge as needed. • Perform Weekly/Nightly Database Backups. Oracle Database Management Tools & Maintenance

37. Archiving Advantages/Disadvantages • Advantages • Minimal Data Loss • Hot (On-line) Backups • Use for Standby Database • Disadvantages • Maintenance • Slight Performance Decrease • Need Database Backup (Cold or Hot) Oracle Database Management Tools & Maintenance

38. Oracle Recovery Manager Oracle Database Management Tools & Maintenance

39. Oracle Recovery Manager What is Oracle Recovery Manager (RMAN)? • Oracle-preferred method for efficiently backing up and recovering your Oracle database • A command-line and Enterprise Manager-based tool • Can skip unused or corrupt blocks of data • Backup Compression • Incremental backups • Scripting capabilities Oracle Database Management Tools & Maintenance

40. RMAN COMMAND LINE EXAMPLE • Start the RMAN client and create a server session on the target database: > rman target sys/oracle Oracle Database Management Tools & Maintenance

41. RMAN COMMAND LINE EXAMPLE BACKUP DATABASE FILES AND ARCHIVE LOGS RMAN> BACKUP DATABASE PLUS ARCHIVELOG; Oracle Database Management Tools & Maintenance

42. RMAN COMMAND LINE EXAMPLE RECOVER DATABASE RMAN> RESTORE DATABASE; RMAN> RECOVER DATABASE; RMAN> ALTER DATABASE OPEN; Oracle Database Management Tools & Maintenance

43. RMAN IN ENTERPRISE MANAGER Oracle Database Management Tools & Maintenance

44. ENTERPRISE MANAGER EXAMPLE Oracle Database Management Tools & Maintenance

45. ENTERPRISE MANAGER EXAMPLE Oracle Database Management Tools & Maintenance

46. ENTERPRISE MANAGER EXAMPLE Oracle Database Management Tools & Maintenance

47. ENTERPRISE MANAGER EXAMPLE Oracle Database Management Tools & Maintenance

48. ENTERPRISE MANAGER EXAMPLE Oracle Database Management Tools & Maintenance

49. Demonstration Enterprise Manager ENTERPRISE MANAGER FOR RECOVERY Oracle Database Management Tools & Maintenance

50. ORACLE FLASH RECOVERY Oracle Database Management Tools & Maintenance