Prepare for your exams
Get points
Guidelines and tips

Sell on Docsity

Prepare for your exams

Study with the several resources on Docsity

Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips

Sell on Docsity

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search Store documents

The best documents sold by students who completed their studies

Search through all study resources

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents

20 Points

For each uploaded document

Answer questions

5 Points

For each given answer (max 1 per day)

All the ways to get free points

Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

University Rankings

Discover the best universities in your country according to Docsity users

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

From our blog

Exams and Study

Go to the blog

DATABASE ADMINISTRATOR, Study Guides, Projects, Research of Database Programming

Gujarat University Database Programming

DATABASE ADMINISTRATOR TOOL ANALYSIS OF RMAN (RECOVERY MANAGEMENT) AND STATSPACK

Typology: Study Guides, Projects, Research

2017/2018

Uploaded on 11/16/2018

unknown user 🇮🇳

(1)

2 documents

1 / 43

This page cannot be seen from the preview

Don't miss anything!

Fifth Year integrated MCA,

Semester-IX

COIT605 DATABASE

ADMINISTRATION

Tool Analysis: RMAN

(Recovery Management) &

STATSPACK

Group No.

Ishani Kothari (22)

Vatsal Patel (42)

Sanket Pawar (44)

INDEX

COIT605 DATABASE ADMINISTRATION Tool Analysis: RMAN (Recovery

Management) & STATSPACK

1Fifth Year integrated MCA, Semester-IX, Ishani Kothari [22], Vatsal Patel [42], Sanket Pawar [44]

Partial preview of the text

Download DATABASE ADMINISTRATOR and more Study Guides, Projects, Research Database Programming in PDF only on Docsity!

Fifth Year integrated MCA,

Semester-IX

COIT605 DATABASE

ADMINISTRATION

Tool Analysis: RMAN

(Recovery Management) &

STATSPACK

Group No.

Ishani Kothari (22)

Vatsal Patel (42)

Sanket Pawar (44)

INDEX

Management) & STATSPACK

Introduction to RMAN (RECOEVRY MANAGEMENT)
- How RMAN Backup Works ......................................................................................
- Login
- Connect to RMAN ..................................................................................................
- Creating table ..................................................................................................
- Creating tablespace ........................................................................................
- Create table and insert data in student1 ....................................................
- RMAN backup archive log ..............................................................................
- RMAN backup ...................................................................................................
- Backup copy for RMAN
- Archival backup .............................................................................................
- Restore and recovery
Introduction to Statspack ..........................................................................................
- How Statspack Works
Installing Statspack ..................................................................................................
Using Statspack .......................................................................................................
- Taking a Statspack Snapshot
- Running a Statspack Performance Report .................................................................
- Changing the Default Values for Snapshot Levels and SQL Thresholds
- Removing Unnecessary Data ...................................................................................
- Removing Statspack ..............................................................................................

C:\Windows\system32>rman target/ Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 22 10:42:57 2018 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1515197443) RMAN> connect catalog rman/rman

Creating table

SQL> create tablespace perfstat SQL> startup mount; Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 3373858816 bytes Fixed Size 2180424 bytes Variable Size 1929382584 bytes Database Buffers 1426063360 bytes Redo Buffers 16232448 bytes Database mounted. SQL> alter database open; Database altered. SQL> create table student(stdno number(10), stdname varchar (50)); Table created. SQL> insert into student values(1,'abc'); 1 row created. SQL> create table employee(empno number(10), empname varchar (50)); Table created. SQL> insert into employee values(1,'abc'); 1 row created.

Management) & STATSPACK

SQL> insert into employee values(2,'xaxasx'); 1 row created. SQL> insert into employee values(3,'rajaj'); 1 row created. SQL> insert into student values(2,'cata'); 1 row created. SQL> insert into student values(3,'alalal'); 1 row created. SQL> commit SQL> commit; Commit complete

Creating tablespace

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> CREATE BIGFILE TABLESPACE bigtbs_ 2 DATAFILE 'bigtbs_f1.dat' 3 SIZE 20M AUTOEXTEND ON; Tablespace created. SQL> CREATE TABLESPACE bigtbs_ 2 DATAFILE 'bigtbs_f2.dat' 3 SIZE 20M AUTOEXTEND ON; Tablespace created. SQL> CREATE TABLESPACE vatsal 2 DATAFILE 'vatsal.dat' 3 SIZE 20M AUTOEXTEND ON; Tablespace created.

Create table and insert data in student

Management) & STATSPACK

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode continuing other job steps, job failed will not be re-run channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 06-OCT- channel ORA_DISK_1: finished piece 1 at 06-OCT- piece handle=E:\APP\ISHANI\FLASH_RECOVERY_AREA\ORCAL\BACKUPSET \2018_10_06\O1_MF_NCSNF_TAG20181006T123145_FVJQGVPV_.BKP tag=TAG20181006T123145 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: ===========================================================

Management) & STATSPACK

RMAN backup

RMAN> SHUTDOWN IMMEDIATE;\

database closed database dismounted Oracle instance shut down

RMAN> STARTUP FORCE DBA;

Oracle instance started database mounted database opened

Total System Global Area 3373858816 bytes

Management) & STATSPACK

input datafile file number=00002 name=E:\APP\ISHANI\DBA\ORCAL \SYSAUX01.DBF input datafile file number=00003 name=E:\APP\ISHANI\DBA\ORCAL \UNDOTBS01.DBF input datafile file number=00005 name=E:\APP\ISHANI\PRODUCT \11.2.0\DBHOME_1\DATABASE\BIGTBS_F1.DAT input datafile file number=00006 name=E:\APP\ISHANI\PRODUCT \11.2.0\DBHOME_1\DATABASE\BIGTBS_F2.DAT input datafile file number=00007 name=E:\APP\ISHANI\PRODUCT \11.2.0\DBHOME_1\DATABASE\VATSAL.DAT input datafile file number=00004 name=E:\APP\ISHANI\DBA\ORCAL \USERS01.DBF channel ORA_DISK_1: starting piece 1 at 06-OCT- channel ORA_DISK_1: finished piece 1 at 06-OCT- piece handle=E:\APP\ISHANI\FLASH_RECOVERY_AREA\ORCAL\BACKUPSET \2018_10_06\O1_MF_NNNDF_TAG20181006T123613_FVJQQ6TK_.BKP tag=TAG20181006T123613 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01: channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 06-OCT- channel ORA_DISK_1: finished piece 1 at 06-OCT- piece handle=E:\APP\ISHANI\FLASH_RECOVERY_AREA\ORCAL\BACKUPSET \2018_10_06\O1_MF_NCSNF_TAG20181006T123613_FVJQS98W_.BKP tag=TAG20181006T123613 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00: Finished backup at 06-OCT-

Management) & STATSPACK

Backup copy for RMAN

RMAN> BACKUP DATABASE;

Starting backup at 06-OCT- allocated channel: ORA_DISK_ channel ORA_DISK_1: SID=191 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=E:\APP\ISHANI\DBA\ORCAL \SYSTEM01.DBF input datafile file number=00002 name=E:\APP\ISHANI\DBA\ORCAL \SYSAUX01.DBF

Management) & STATSPACK

OUPUT:-

RMAN-00571:

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

RMAN-00571:

RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "backup": expecting one of: "mount, open" RMAN-01007: at line 1 column 16 file: standard input

RMAN> ALTER DATABASE OPEN;

OUTPUT:-

database opened

RMAN> BACKUP INCEREMENTAL LEVEL 0 DATABASE;

RMAN-00571:

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

RMAN-00571:

RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "identifier": expecting one of: "archivelog, as, auxiliary, backupset, backup, channel, check, controlfilecopy, copies, copy, cumulative, current, database, datafilecopy, datafile, db_file_name_convert, db_recovery_file_dest, device, diskratio, duration,

Management) & STATSPACK

filesperset, force, format, for, from, full, incremental, keep, maxsetsize, nochecksum, noexclude, nokeep, not, pool, proxy, recovery, reuse, section, skip readonly, skip, spfile, tablespace, tag, to, validate, (" RMAN-01008: the bad identifier was: INCEREMENTAL RMAN-01007: at line 1 column 8 file: standard input

Archival backup

RMAN> backup database plus archivelog;

Starting backup at 06-OCT- ORACLE error from target database:

Management) & STATSPACK

RMAN> RESTORE DATABASE;

Starting restore at 06-OCT- using channel ORA_DISK_ channel ORA_DISK_1: restoring datafile 00001 input datafile copy RECID=1 STAMP=988807160 file name=E:\APP \ISHANI\FLASH_RECOVERY_AREA\ORCAL\DATAFILE \O1_MF_SYSTEM_FVJQWCDW_.DBF destination for restore of datafile 00001: E:\APP\ISHANI\DBA \ORCAL\SYSTEM01.DBF channel ORA_DISK_1: copied datafile copy of datafile 00001 output file name=E:\APP\ISHANI\DBA\ORCAL\SYSTEM01.DBF RECID= STAMP= channel ORA_DISK_1: restoring datafile 00002 input datafile copy RECID=2 STAMP=988807180 file name=E:\APP \ISHANI\FLASH_RECOVERY_AREA\ORCAL\DATAFILE \O1_MF_SYSAUX_FVJQX59M_.DBF destination for restore of datafile 00002: E:\APP\ISHANI\DBA \ORCAL\SYSAUX01.DBF channel ORA_DISK_1: copied datafile copy of datafile 00002 output file name=E:\APP\ISHANI\DBA\ORCAL\SYSAUX01.DBF RECID= STAMP= channel ORA_DISK_1: restoring datafile 00003 input datafile copy RECID=3 STAMP=988807191 file name=E:\APP \ISHANI\FLASH_RECOVERY_AREA\ORCAL\DATAFILE \O1_MF_UNDOTBS1_FVJQXYKC_.DBF destination for restore of datafile 00003: E:\APP\ISHANI\DBA \ORCAL\UNDOTBS01.DBF channel ORA_DISK_1: copied datafile copy of datafile 00003 output file name=E:\APP\ISHANI\DBA\ORCAL\UNDOTBS01.DBF RECID= STAMP= channel ORA_DISK_1: restoring datafile 00004

Management) & STATSPACK

input datafile copy RECID=8 STAMP=988807199 file name=E:\APP \ISHANI\FLASH_RECOVERY_AREA\ORCAL\DATAFILE \O1_MF_USERS_FVJQY6TZ_.DBF destination for restore of datafile 00004: E:\APP\ISHANI\DBA \ORCAL\USERS01.DBF channel ORA_DISK_1: copied datafile copy of datafile 00004 output file name=E:\APP\ISHANI\DBA\ORCAL\USERS01.DBF RECID= STAMP= channel ORA_DISK_1: restoring datafile 00005 input datafile copy RECID=4 STAMP=988807194 file name=E:\APP \ISHANI\FLASH_RECOVERY_AREA\ORCAL\DATAFILE \O1_MF_BIGTBS_0_FVJQY1QZ_.DBF destination for restore of datafile 00005: E:\APP\ISHANI \PRODUCT\11.2.0\DBHOME_1\DATABASE\BIGTBS_F1.DAT channel ORA_DISK_1: copied datafile copy of datafile 00005 output file name=E:\APP\ISHANI\PRODUCT\11.2.0\DBHOME_ \DATABASE\BIGTBS_F1.DAT RECID=0 STAMP= channel ORA_DISK_1: restoring datafile 00006 input datafile copy RECID=5 STAMP=988807195 file name=E:\APP \ISHANI\FLASH_RECOVERY_AREA\ORCAL\DATAFILE \O1_MF_BIGTBS_0_FVJQY2YG_.DBF destination for restore of datafile 00006: E:\APP\ISHANI \PRODUCT\11.2.0\DBHOME_1\DATABASE\BIGTBS_F2.DAT channel ORA_DISK_1: copied datafile copy of datafile 00006 output file name=E:\APP\ISHANI\PRODUCT\11.2.0\DBHOME_ \DATABASE\BIGTBS_F2.DAT RECID=0 STAMP= channel ORA_DISK_1: restoring datafile 00007 input datafile copy RECID=6 STAMP=988807197 file name=E:\APP \ISHANI\FLASH_RECOVERY_AREA\ORCAL\DATAFILE \O1_MF_VATSAL_FVJQY47L_.DBF destination for restore of datafile 00007: E:\APP\ISHANI \PRODUCT\11.2.0\DBHOME_1\DATABASE\VATSAL.DAT channel ORA_DISK_1: copied datafile copy of datafile 00007

Management) & STATSPACK

Statspack

Introduction to Statspack

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.

How Statspack Works

When you run the Statspack installation script, the PERFSTAT user is created automatically. PERFSTAT owns all objects needed by the Statspack package and is granted limited query-only privileges on the V$views required for performance tuning.

Installing Statspack

The first step in the installation is the creation of the PERFSTAT user, which owns all PL/ SQL code and database objects created, including the Statspack tables, constraints, and the Statspack package. During installation, you are prompted for the PERFSTAT user's password, default tablespace, and temporary tablespace. The default tablespace is used to create all Statspack objects, such as tables and indexes. The temporary tablespace is used for sort-type activities.

To install Statspack, perform the following:

Create tablespace

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Management) & STATSPACK

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

SYSTEM SYSAUX UNDOTBS TEMP USERS BIGTBS_ BIGTBS_ VATSAL TOOLS 9 rows selected. Tablespace creates

Installing Statspack Process

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

SYSTEM SYSAUX UNDOTBS TEMP USERS BIGTBS_ BIGTBS_ VATSAL TOOLS PERFSTAT

Management) & STATSPACK

DATABASE ADMINISTRATOR, Study Guides, Projects, Research of Database Programming

Related documents

Partial preview of the text

Download DATABASE ADMINISTRATOR and more Study Guides, Projects, Research Database Programming in PDF only on Docsity!

Fifth Year integrated MCA,

Semester-IX

COIT605 DATABASE

ADMINISTRATION

Tool Analysis: RMAN

(Recovery Management) &

STATSPACK

Group No.

Ishani Kothari (22)

Vatsal Patel (42)

Sanket Pawar (44)

INDEX

Management) & STATSPACK

RMAN> BACKUP DATABASE;

OUPUT:-

RMAN-00571:

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

RMAN-00571:

RMAN> ALTER DATABASE OPEN;

OUTPUT:-

RMAN> BACKUP INCEREMENTAL LEVEL 0 DATABASE;

RMAN-00571:

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

RMAN-00571:

RMAN> RESTORE DATABASE;

TABLESPACE_NAME

TABLESPACE_NAME