Oracle Database 10g: Backup and Recovery

(1)

1

Oracle Database 10g:

Backup and Recovery

Volume I • Student Guide

D22057GC10 Production 1.0 June 2006 D46524

(2)

2 Authors Donna Keesling Maria Billings Technical Contributors and Reviewers Christopher Andrews Tammy Bednar Tom Best

Harald van Breederode Mary Jane Bryksa Marielle Canning Tim Chien Donna Cooksey Judy Ferstenberg Gerlinde Frenzen Joel Goodman Pete Jones Isabelle Marchand Sabiha Miri Manish Pawar Jim Spiller George Stabler Anthony Woodell Editor Daniel Milne Graphic Designer Satish Bettegowda Publisher Jobi Varghese

(3)

Disclaimer

This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle.

The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free.

Restricted Rights Notice

If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS

The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract.

Trademark Notice

Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

(4)

4

Practice for Lesson 1

(5)

5

Practice for Lesson 2

(6)

6

Practice 2-1: Configure Your Database

1) Use Enterprise Manager to configure your database in ARCHIVELOG mode.

2) Use Enterprise Manager to verify that the Flash Recovery Area has been configured for your database and increase the Flash Recovery Area size to 3 GB.

3) Set Preferred Credentials in Enterprise Manager.

4) Use Recovery Manager (RMAN) to connect to your target database. Make note of thedatabase identifier (DBID) of your database.Database Identifier: ____________________________ Make note of the database identifier (DBID) of your database.

5) Use the RMAN SHOW ALL command to view the configuration settings in yourdatabase and then exit from your RMAN session.

(7)

7

Practice for Lesson 3

In this practice, you configure automatic backup of the control file. You also configure a backup of your database using the Oracle-Suggested Backup Strategy feature in

(8)

8

Practice 3-1: Use RMAN to Create and Manage Backups

1) Use Enterprise Manager Database Control to configure autobackup of the control file and the server parameter file.

2) Use Enterprise Manager Database Control to configure backup optimization and enable block change tracking. Specify /u01/app/oracle/oradata/orcl/chg_track.f for the name of the blockchange tracking file.

3) Use Enterprise Manager Database Control to create a whole database backup using the Oracle-suggested backup strategy.

4) Use Enterprise Manager to view information about your backups.

5) Use RMAN to create a duplexed backup set of the EXAMPLE tablespace.

(9)

9

Practice for Lesson 4

(10)

10

Practice 4-1: Use RMAN to Recover a Datafile

In this practice, you use RMAN to recover a lost datafile. Notice how you are prompted by Enterprise Manager to recover the lost datafile.

1) Use SQL*Plus to query the HR.REGIONS table. Make note of the number of rows in the HR.REGIONS table.

2) At the operating system prompt, execute the lab_04_01_02_01.sh script to simulate a failure in your database. This script deletes the EXAMPLE tablespace datafile.

3) Use SQL*Plus to query the HR.JOBS table.

4) Use Enterprise Manager to perform database recovery of the EXAMPLE tablespace datafile.

(11)

11

Practice 4-2: Use the Flash Recovery Area to Quickly Recover a

Datafile

In this practice, you recover a lost datafile by using the Flash Recovery Area for fast recovery.

3) Use SQL*Plus to query the HR.DEPARTMENTS table.

4) Use the RMAN SWITCH TO COPY command to recover the datafile. 5) Query the HR.JOBS table.

6) Using Enterprise Manager Database Control, verify that the datafile being used for the EXAMPLE tablespace is in the Flash Recovery Area.

7) Make a copy of the datafile in the original location and switch back to it. 8) Use Enterprise Manager Database Control to verify the file.

(12)

12

Practice 4-3: Recover Control Files

In this practice, you recover your control file using an autobackup.

1) Use SQL*Plus to view files information for the control files in your database. Query V$CONTROLFILE.

2) Simulate a failure in your environment by executing the lab_04_03_02_01.sh script to delete all your control files.

3) You need some more information about your control files. Query

V$CONTROLFILE_RECORD_SECTION to learn more about the contents of your control file.

4) You have lost all your control files and will need to recover them from the control file autobackup. Use Recovery Manager to recover the control files.

(13)

13

Practice 4-4: Delete Obsolete Backups

1) Use Recovery Manager to view obsolete backups.

2) Use Enterprise Manager Database Control to delete obsolete backups. 3) You can also use RMAN to verify that your obsolete backups were deleted.

(14)

14

Practice for Lesson 5

In this practice, you use Oracle Flashback features to recover from errors in your database.

(15)

15

Practice 5-1: Enable Flashback Database

1) Use Enterprise Manager to enable Flashback Database.

(16)

16

Practice 5-2: Set Restore Points and Perform Flashback Table

Restore points are a way to “bookmark” database points in time. Set a restore point to remember a significant change so that you can quickly recover to that point in time without having to record an SCN or time.

1) You must enable row movement to use restore points. Use Enterprise Manager to enable row movement for the HR.LOCATIONS table.

2) Create a normal restore point.

3) Use SQL*Plus to query the POSTAL_CODE column in the HR.LOCATIONS table. 4) Execute the lab_05_02_04_01.sql script to update the POSTAL_CODE column

in the HR.LOCATIONS table so that all postal codes are set to 11111.

5) Execute the lab_05_02_05_01.sql script to query the POSTAL_CODE column in the HR.LOCATIONS table again.

6) Restore the POSTAL_CODE column values using the restore point.

7) Return to your SQL*Plus session. Execute the lab_05_02_07_01.sql script to

query the POSTAL_CODE column in HR.LOCATIONS again to be sure the correct values have been restored.

(17)

17

Practice 5-3: Use Flashback Query and Flashback Versions

Query

1. Execute the lab_05_03_01_01.sql script to query the HR.LOCATIONS table for location ID 1400.

2. Execute the lab_05_03_02_01.sql script to update the POSTAL_CODE column in the HR.LOCATIONS table, simulating user error.

3. Execute the lab_05_03_03_01.sql script to query the POSTAL_CODE column in HR.LOCATIONS and view the change.

4. Execute the lab_05_03_04_01.sql script to update the POSTAL_CODE column in the HR.LOCATIONS table, simulating user error.

5. Use Enterprise Manager to perform Flashback Versions Query to correct the user errors. 6. Return to your SQL*Plus session. Query the HR.LOCATIONS table to confirm the Flashback

(18)

18

Practice 5-4: Use Flashback Database

1) Use Enterprise Manager to verify that Flashback Database is enabled. 2) Use Enterprise Manager to create a Guaranteed Restore Point.

3) Execute the lab_05_04_03_01.sql script to determine the number of rows in the HR.JOB_HISTORY table. Record the number of rows: ___________

4) Execute the lab_05_04_04_01.sql script to truncate the HR.JOB_HISTORY table.

4) Execute the lab_05_04_05_01.sql script to determine the number of rows in the HR.JOB_HISTORY table.

5) Use Flashback Database to restore the HR.JOB_HISTORY table rows.

6) Return to your SQL*Plus session. Execute the lab_05_04_07_02.sql script to query the HR.JOB_HISTORY table again to be sure the data has been restored.

(19)

19

Practice for Lesson 6

In this practice, you create a duplicate database.

(20)

20

Practice 6-1: Create a Duplicate Database

In this practice, you use RMAN to create a duplicate database on the same host as your database.

1) Create an Oracle password file for your auxiliary instance.

2) Use Oracle Net Manager to create an entry called AUXDB in the tnsnames.ora file.

3) Create an initialization parameter file for the auxiliary instance.

4) Start the auxiliary instance in NOMOUNT mode using the initAUX.ora file. 5) Create a server parameter file (SPFILE).

6) Verify that your target database (orcl database) is mounted or open.

7) Start RMAN with a connection to the target database (orcl) and the auxiliary instance.

8) Create the duplicate database by executing the DUPLICATE command. 9) Use SQL*Plus to log in to your AUX database and execute a query against the

HR.REGIONS table.

10) Now that you have completed the test recovery by creating a duplicate database, shut down the aux instance.

(21)

21

Practice for Lesson 7

In this practice, you use tablespace point-in-time recovery to recover from unwanted changes to your database.

(22)

22

Practice 7-1: Use Tablespace Point-in-time Recovery

1) Execute the lab_07_01_01_01.sh script to export the HR schema.

2) Execute the lab_07_01_02_02.sql script to create a new tablespace and a new user in your database.

3) Execute the lab_07_01_03_01.sh script to populate the new tablespace with a copy of the data from the HR schema.

Note: The import should complete successfully. You will receive error messages

because the import excludes the COUNTRIES, REGIONS, and LOCATIONS tables. These messages can be ignored.

4) Create a backup of your database using RMAN. 5) Record the current SCN: ____________ 6) Record the current time.

7) Execute the lab_07_01_07_01.sql script to query the HRTEST.EMPLOYEES table and view information about employees in department 60.

Make note of the highest salary that is displayed: ________

8) Execute the lab_07_01_08_01.sql script to update the salaries for the employees in department 60 and note the highest salary displayed.

Highest salary: _____________

9) You now want to perform TSPITR for the HRTEST tablespace to return it to the state prior to the updates. Execute the lab_07_01_09_01.sql script to determine whether there are any dependencies that will prevent the TSPITR operation. 10) You can use Enterprise Manager Database Control or RMAN command line to

perform tablespace point-in-time recovery. 11) Verify that the HRTEST tablespace is online.

12) Verify that the SALARY columns in HRTEST.EMPLOYEES for the employees in department 60 contain the correct values.

13) Execute the lab_07_01_13_02.sql script to add a constraint to the HRTEST.DEPARTMENTS table.

14) Assume you need to perform TSPITR on the HRTEST tablespace again. Execute the lab_07_01_14_02.sql query to determine whether there are any dependencies outside the recovery set.

15) If you wanted to complete the tablespace point-in-time recovery for the HRTEST tablespace now, what would you need to do?

(23)

23

Note: You will not perform another tablespace point-in-recovery in this practice.

You would need to disable the DEPT_LOC_ID_FK constraint that was added to your HRTEST.DEPARTMENTS table or you would need to add

(24)

24

Practice for Lesson 8

In this practice, you create a recovery catalog in your instructor’s database and register

(25)

25

Practice 8-1: Create the Recovery Catalog and Register the

Database

The tablespace for the recovery catalog and the recovery catalog owner have been created in the instructor database. The tablespaces are named RCTS01 – RCTS12. The users are named RCUSER01 – RCUSER12.

1) Connect to the recovery catalog database (instructor’s database) with the appropriate recovery catalog owner name (if you are using PC01, connect as RCUSER01) using RMAN. Create the recovery catalog in your assigned tablespace. (If you are using PC01, your assigned tablespace is RCTS01.) The service name is RCDB.

2) Using RMAN, connect to your target database and the recovery catalog database.

3) Using RMAN, execute the command to resynchronize the control file and recovery catalog. What happens? Why?

4) Register the target database in the recovery catalog. You can use RMAN commandline or Enterprise Manager for this step.

5) Create an RMAN script named whole_backup to make a whole database backup. Do not execute the whole_backup script at this time.

6) Use the PRINT command to query the recovery catalog and verify the creation of your whole_backup script.

7) In preparation for later practices, use RMAN to unregister your database from the

recovery catalog.

(26)

26

Practice for Lesson 9

In this practice, you monitor the progress of your RMAN backup jobs.

(27)

27

Practice 9-1: Use SQL to Monitor the Progress of RMAN

Backups

1) Invoke RMAN and delete all obsolete backups.

2) Open a second terminal window. Change to the labs directory. Invoke SQL*Plus and connect as SYSDBA. You will use this second session to monitor a database backup.

3) Return to your first terminal window. In your RMAN session, begin a whole database backup.

4) Use your SQL*Plus session to monitor the progress of the whole database backup by querying the V$SESSION_LONGOPS view. By using this view you can determine whether the backup is progressing normally or hanging. If the backup is progressing normally, the TIME_REMAINING column should be decreasing. Execute the lab_09_01_04_01.sql script to query V$SESSION_LONGOPS.

(28)

28

Practice 9-2: Use Enterprise Manager to Monitor RMAN Jobs

You can easily monitor RMAN jobs in Enterprise Manager.

1) Start Enterprise Manager and log in as SYS/ORACLE as SYSDBA. 2) Delete obsolete backups.

(29)

29

(30)

30

Solutions for Practice 2-1: Configure Your Database ... 5

Solutions for Practice 3-1: Use RMAN to Create and Manage Backups ... 10

Solutions for Practice 4-1: Use RMAN to Recover a Datafile ... 17

Solutions for Practice 4-2: Use the Flash Recovery Area to Quickly Recover a Datafile

... 21

Solutions for Practice 4-3: Recover Control Files ... 26

Solutions for Practice 4-4: Delete Obsolete Backups ... 33

Solutions for Practice 5-1: Enable Flashback Database ... 36

Solutions for Practice 5-2: Set Restore Points and Perform Flashback Table... 38

Solutions for Practice 5-3: Use Flashback Query and Flashback Versions Query... 46

Solutions for Practice 5-4: Use Flashback Database ... 52

Solutions for Practice 6-1: Create a Duplicate Database ... 60

Solutions for Practice 7-1: Use Tablespace Point-in-time Recovery... 74

Solutions for Practice 8-1: Create the Recovery Catalog and Register the Database... 89

Solutions for Practice 9-1: Use SQL to Monitor the Progress of RMAN Backups... 96

(31)

31

Practice Solutions for Lesson 1

There are no practices for Lesson 1.

(32)

32

Practice Solutions for Lesson 2

In this practice, you configure your database for recoverability.

(33)

33

Solutions for Practice 2-1: Configure Your Database

1) Use Enterprise Manager to configure your database in ARCHIVELOG mode.

Answer:

1. Start the Web browser and enter http://your host name:1158/em. 2. Enter SYS in the User Name field and oracle in the Password field. Select SYSDBA in the Connect As menu. Click Login.

3. Click “I agree” on the Oracle Database 10g Licensing Information page. 4. Select the Maintenance page.

5. Select Recovery Settings in the Backup/Recovery Settings section.

6. In the Media Recovery section, select “ARCHIVELOG Mode” and click Apply.

7. The Confirmation page is displayed. Click Yes to restart the database instance. 8. On the Restart Database: Specify Host and Target Database Credentials page. In the Host Credentials section, enter oracle in the Username and Password fields. In the Database Credentials section, enter SYS in the Username field and

oracle in the Password field. Select “Save as Preferred Credential.” Click OK. 9. The Restart Database: Confirmation page is displayed. Click Yes to confirm the restart of the database instance.

10. The Restart Database: Activity Information page is displayed. Wait a few minutes and then click Refresh.

2) Use Enterprise Manager to verify that the Flash Recovery Area has been configured for your database and increase the Flash Recovery Area size to 3 GB.

Answer:

1. Select the Maintenance page.

(34)

34

3. Scroll to the Flash Recovery Area and verify that the Flash Recovery Area is enabled.

4. Enter 3 in the Flash Recovery Area Size field and click Apply.

5. The Update Message is displayed. Click the Database instance link to return to the Maintenance page.

3) Set Preferred Credentials in Enterprise Manager.

Answer:

1. Select Preferences at the top of the Maintenance page. 2. Select Preferred Credentials on the left side of the page. 3. Click the Set Credentials icon for the database instance. 4. Supply the following values:

Normal Username: system Normal Password: oracle SYSDBA Username: sys SYSDBA Password: oracle Host Username: oracle Host Password: oracle

5. Click Test. The “Credentials successfully verified for orcl.oracle.com” message is displayed. If you have any errors, correct them and click Test again.

6. Click Apply to save the settings.

7. Click Database to return to the Database home page.

4) Use Recovery Manager (RMAN) to connect to your target database. Make note of the database identifier (DBID) of your database.

Database Identifier: ____________________________

(35)

35

1.

Open a terminal window and log in as oracle/oracle.

2. Start RMAN and connect to the target database by entering the following

command at the operating system prompt: rman target /

[oracle@edrsr10p1 oracle]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 7 12:51:53 2006

Make note of the database identifier (DBID) of your database.

3. Use the RMAN SHOW ALL command to view the configuration settings in your

database and then exit from your RMAN session.

Answer:

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # deft

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT '%U'; CONFIGURE MAXSETSIZE TO UNLIMITED; # default

(36)

36

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO

'/u01/app/oracle/product/10.2.0/db_1/dbst RMAN> exit

(37)

37

Practice Solutions for Lesson 3

In this practice, you configure automatic backup of the control file. You also configure a

backup of your database using the Oracle-Suggested Backup Strategy feature in Enterprise Manager Database Control.

(38)

38

Solutions for Practice 3-1: Use RMAN to Create and Manage Backups

1) Use Enterprise Manager Database Control to configure autobackup of the control file

and the server parameter file.

Answer:

1. Select Maintenance > High Availability > Backup/Recovery Settings > Backup Settings.

2. On the Backup Settings page, select the Policy tab.

3. In the Backup Policy section, select “Automatically backup the control file and server parameter file (SPFILE) with every backup and database structural change.” Click OK.

4. You are returned to the Maintenance page.

2) Use Enterprise Manager Database Control to configure backup optimization and enable block change tracking. Specify /u01/app/oracle/oradata/orcl/chg_track.f for the name of the block change tracking file.

Answer:

1. Select Maintenance > High Availability > Backup/Recovery Settings > Backup Settings. 2. On the Backup Settings page, select the Policy tab.

3. In the Backup Policy section, select “Optimize the whole database backup by skipping unchanged files such as read-only and offline datafiles that have been backed up.”

4. Select “Enable block change tracking for faster incremental backups” and enter

a. /u01/app/oracle/oradata/orcl/chg_track.f in the Block Change Tracking File field. Click OK.

(39)

39 5. You are returned to the Maintenance page.

3) Use Enterprise Manager Database Control to create a whole database backup using the Oracle-suggested backup strategy.

Answer:

1. Select Maintenance > High Availability > Backup/Recovery > Schedule Backup.

2. In the Suggested Backup section of the Schedule Backup page, click Schedule Oracle-Suggested Backup.

3. On the Schedule Oracle-Suggested Backup: Destination page, select Disk. Click Next.

4. Review the information on the Schedule Oracle-Suggested Backup: Setup page. Note that a full database copy will be performed during the first backup. After that, an incremental backup to disk will be performed every day. Click Next.

(40)

40

5. On the Schedule Oracle-Suggested Backup: Schedule page, you can specify the time for your backups. Set the Time Zone field to correspond to your time zone.

6. Review the information on the Schedule Oracle-Suggested Backup: Review page.

Click Submit Job.

7. The Status page is displayed indicating that the job has been submitted. Click View Job

to monitor the status of the backup job.

8. Click the Refresh button on your browser to refresh the Execution page. Click the

Backup link to view the output log.

9. In the output log, you can see that RMAN has made a datafile copy backup of

each datafile in your database.

(41)

41

10. Click the Database tab to return to the Database Home page.

4) Use Enterprise Manager to view information about your backups.

Answer:

1.

Select Maintenance > High Availability > Backup/Recovery > Backup Reports.

2.

Click the link for the backup you took in Question 3 to view detailed information about the backup.

3. Click the Database Instance link to return to the Maintenance page.

5) Use RMAN to create a duplexed backup set of the EXAMPLE tablespace.

Answer:

1. Start RMAN and connect to the target database.

[oracle@edrsr10p1 backup1]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 22 10:38:18 2006

(42)

42

/home/oracle/backup1 directory and one copy should be in the /home/oracle/backup2 directory.

RMAN> @sol_03_01_05_02.rmn RMAN> backup device type disk 2> copies 2

3> tablespace example

4> format '/home/oracle/backup1/%U', '/home/oracle/backup2/%U';

Starting backup at 22-MAR-06 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=00005

name=/u01/app/oracle/oradata/orcl/example01.dbf channel ORA_DISK_1: starting piece 1 at 22-MAR-06

channel ORA_DISK_1: finished piece 1 at 22-MAR-06 with 2 copies and tag TAG20063

piece handle=/home/oracle/backup1/0ehejks7_1_1 comment=NONE

piece handle=/home/oracle/backup2/0ehejks7_1_2 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 22-MAR-06

Starting Control File and SPFILE Autobackup at 22-MAR-06 piece

handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup /2006_03_22/o1_E

Finished Control File and SPFILE Autobackup at 22-MAR-06 RMAN> **end-of-file**

RMAN>

3. Use the RMAN LIST BACKUP SUMMARY command to view a listing of the backup sets and pieces. The #Copies column shows the duplexed backup set copies you made. Exit from RMAN.

RMAN> @sol_03_01_05_03.rmn RMAN> list backup summary;

using target database control file instead of recovery catalog List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

--- -- -- - --- --- --- --- - --- ---

5 B F A DISK 21-MAR-06 1 1

(43)

43 7 B F A DISK 22-MAR-06 1 1 NO TAG200607 8 B F A DISK 22-MAR-06 1 2 NO TAG200603 9 B F A DISK 22-MAR-06 1 1 NO TAG200608 RMAN> **end-of-file** RMAN> exit

Recovery Manager complete

6) Use Enterprise Manager Database Control to perform a cross-check of your backups.

Answer:

1. Select Maintenance > High Availability > Backup/Recovery > Manage Current Backups.

2. Click Crosscheck All.

3. Review the information on the Crosscheck All: Specify Job Parameters page and accept the default values. Click Submit Job.

4. You receive the “Job submission succeeded” message. You can click View Job to monitor the cross-check job. After it completes successfully, return to the

Database Home page.

(44)

44

Practice Solutions for Lesson 4

In this practice, you use RMAN to perform recovery.

(45)

45

Solutions for Practice 4-1: Use RMAN to Recover a Datafile

In this practice, you use RMAN to recover a lost datafile. Notice how you are prompted by Enterprise Manager to recover the lost datafile.

Answer:

1. Open a terminal window and log in to SQL*Plus and connect as the HR user with HR as the password.

2. Query the HR.REGIONS table and record the number of rows. Number of rows: ___________ [oracle@edrsr10p1 oracle]$ sqlplus hr/hr

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 23 12:51:00 2006

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options SQL> select * from regions;

REGION_ID REGION_NAME

--- --- 1 Europe

2 Americas 3 Asia

4 Middle East and Africa 3. Exit from your SQL*Plus session.

Answer:

1. At the operating system prompt, change to the labs directory and execute the lab_04_01_02_01.sh script.

[oracle@edrsr10p1 labs]$ ./lab_04_01_02_01.sh EXAMPLE tablespace file deleted

3) Use SQL*Plus to query the HR.JOBS table.

(46)

46

1. Log in to SQL*Plus and connect as the HR user with HR as the password. 2. Query the HR.JOBS table.

SQL> select * from jobs; select * from jobs

* ERROR at line 1:

ORA-00376: file 5 cannot be read at this time ORA-01110: data file 5:

'/u01/app/oracle/oradata/orcl/example01.dbf'

4) Use Enterprise Manager to perform database recovery of the EXAMPLE tablespace datafile.

Answer:

1. Start Enterprise Manager and log in as SYS/ORACLE as SYSDBA. 2. Navigate to the Maintenance page.

3. Select Perform Recovery on the Maintenance page. 4. Click the “Datafiles Need Media Recovery” link.

(47)

47

6. Select “No. Restore the files to the default location.” Click Next.

7. On the “Perform Object Level Recovery: Review” page, click Edit RMAN Script to view the script that will be executed. Click Submit.

8. The Processing page is displayed. After the recovery operation completes, the Result page is displayed indicating that the recovery operation succeeded.

9. Click OK to return to the Maintenance page.

5) Return to your SQL*Plus session and again attempt to query the HR.JOBS table.

Answer:

(48)

48 SQL> select * from jobs;

JOB_ID JOB_TITLE MIN_SALARY

MAX_SALARY

--- --- --- ---

AD_PRES President 20000

40000

AD_VP Administration Vice President 15000 30000

AD_ASST Administration Assistant 3000 6000

FI_MGR Finance Manager 8200

16000 …

MK_REP Marketing Representative 4000 9000

HR_REP Human Resources Representative 4000 9000

PR_REP Public Relations Representative 4500 10500

(49)

49

Solutions for Practice 4-2: Use the Flash Recovery Area to Quickly Recover a

Datafile

In this practice, you recover a lost datafile by using the Flash Recovery Area for fast recovery.

Answer:

1. Log in to SQL*Plus and connect as the HR user with HR as the password. 2. Query the HR.REGIONS table.

3. Exit from SQL*Plus.

Answer:

1. At the operating system prompt, change to the labs directory and execute the lab_04_02_02_01.sh script.

[oracle@edrsr10p1 labs]$ ./lab_04_02_02_01.sh EXAMPLE tablespace file deleted

[oracle@edrsr10p1 labs]$

3) Use SQL*Plus to query the HR.DEPARTMENTS table.

Answer:

1. Log in to SQL*Plus and connect as the HR user with HR as the password. 2. Query the HR.DEPARTMENTS table.

SQL> select * from departments; select * from departments

*

ERROR at line 1:

ORA-01116: error in opening database file 5 ORA-01110: data file 5:

'/u01/app/oracle/oradata/orcl/example01.dbf' ORA-27041: unable to open file

Linux Error: 2: No such file or directory Additional information: 3

(50)

50 4. Exit from your SQL*Plus session.

4) Use the RMAN SWITCH TO COPY command to recover the datafile.

Answer:

1. Log in to your target database using RMAN. 2. Take the datafile offline.

RMAN> SQL 'alter database datafile 5 offline'; sql statement: alter database datafile 5 offline 3. Execute the SWITCH TO COPY command for the datafile you noted in step 3.

RMAN> switch datafile 5 to copy; datafile 5 switched to datafile copy

"/u01/app/oracle/flash_recovery_area/ORCL/"

4. Recover the datafile.

RMAN> recover datafile 5; Starting recover at 24-MAR-06 allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=132 devtype=DISK allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=130 devtype=SBT_TAPE channel ORA_SBT_TAPE_1: Oracle Secure Backup starting media recovery

media recovery complete, elapsed time: 00:00:03 Finished recover at 24-MAR-06

5. Bring the datafile online.

RMAN> sql 'alter database datafile 5 online'; sql statement: alter database datafile 5 online RMAN>

6. Exit from your RMAN session. 5) Query the HR.JOBS table.

Answer:

1. Log in to SQL*Plus as HR/HR and query the HR.JOBS table. (Output has been formatted to fit the code box.)

(51)

51 SQL> select * from jobs;

JOB_ID JOB_TITLE MIN_S MAX_SALARY

--- --- --- ---

AD_PRES President 20000 40000

AD_VP Administration Vice President 15000 30000 …

HR_REP Human Resources Representative 4000 9000 PR_REP Public Relations Representative 4500 10500 19 rows selected.

6) Using Enterprise Manager Database Control, verify that the datafile being used for the

EXAMPLE tablespace is in the Flash Recovery Area.

Answer:

1. Select Tablespaces on the Administration page.

2.

Select the Example tablespace and click View.

3.

Click Return to return to the Tablespaces page.

7) Make a copy of the datafile in the original location and switch back to it.

Answer:

1. Log in to RMAN and connect to your target database. 2. Create an image copy of the datafile in the original location.

RMAN> @sol_04_02_07_02.rmn RMAN> backup as copy datafile 5

2> format '/u01/app/oracle/oradata/orcl/example01.dbf'; Starting backup at 24-MAR-06

(52)

52 catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=129 devtype=DISK channel ORA_DISK_1: starting datafile copy input datafile fno=00005

name=/u01/app/oracle/flash_recovery_area/ORCL/datafilef output filename=/u01/app/oracle/oradata/orcl/example01.dbf tag=TAG20060324T13447

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 24-MAR-06

Starting Control File and SPFILE Autobackup at 24-MAR-06 piece

Finished Control File and SPFILE Autobackup at 24-MAR-06 RMAN> **end-of-file**

3. Take the datafile offline.

RMAN> sql 'alter database datafile 5 offline'; sql statement: alter database datafile 5 offline

4. Switch to the new copy you made.

RMAN> switch datafile 5 to copy; datafile 5 switched to datafile copy

"/u01/app/oracle/oradata/orcl/example01.dbf"

5. Recover the datafile.

RMAN> recover datafile 5; Starting recover at 24-MAR-06 using channel ORA_DISK_1

allocated channel: ORA_SBT_TAPE_1

6. Bring the datafile back online. Exit from RMAN.

RMAN> sql 'alter database datafile 5 online'; sql statement: alter database datafile 5 online

(53)

53

8) Use Enterprise Manager Database Control to verify the file.

Answer:

1. Select Tablespace on the Administration page.

2. Select the EXAMPLE tablespace and click View.

3. Note that the datafile is the one you switched to.

(54)

54

Solutions for Practice 4-3: Recover Control Files

In this practice, you recover your control file by using an autobackup.

1) Use SQL*Plus to view files information for the control files in your database. Query V$CONTROLFILE.

Answer:

1. Log in to SQL*Plus as SYSDBA.

2. Query the NAME column in V$CONTROLFILE. Exit from SQL*Plus. SQL> SELECT name FROM v$controlfile;

NAME

--- /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl /u01/app/oracle/oradata/orcl/control03.ctl

2) Simulate a failure in your environment by executing the lab_04_03_02_01.sh script to delete all your control files.

Answer:

1. In your terminal window session, change to the labs directory and execute the lab_04_03_02_01.sh script.

[oracle@edrsr10p1 oracle]$ cd labs

[oracle@edrsr10p1 labs]$ ./lab_04_03_02_01.sh Control files deleted

[oracle@edrsr10p1 labs]$

3) You need some more information about your control files. Query

V$CONTROLFILE_RECORD_SECTION to learn more about the contents of your control file.

Answer:

2. Query the V$CONTROLFILE_RECORD_SECTION view.

SQL> SELECT * FROM v$controlfile_record_section; SELECT * FROM v$controlfile_record_section

*

ERROR at line 1:

ORA-00210: cannot open the specified control file ORA-00202: control file:

'/u01/app/oracle/oradata/orcl/control01.ctl' ORA-27041: unable to open file

Linux Error: 2: No such file or directory Additional information: 3

(55)

55

4) You have lost all your control files and will need to recover them from the control file autobackup. Use Recovery Manager to recover the control files.

Answer:

1. Use SQL*Plus to shut down your instance. Exit from your SQL*Plus session. [oracle@edrsr10p1 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 28 10:20:26 2006

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options SQL> shutdown abort

ORACLE instance shut down. SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pn

With the Partitioning, OLAP and Data Mining options [oracle@edrsr10p1 oracle]$

2. Use RMAN to connect to your target database. [oracle@edrsr10p1 oracle]$ rman

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 28 10:23:17 2006

connected to target database (not started) RMAN>

3. Restart the instance in NOMOUNT mode.

RMAN> startup nomount

Oracle instance started

Total System Global Area 285212672 bytes Fixed Size 1218992 bytes

Variable Size 109053520 bytes Database Buffers 171966464 bytes Redo Buffers 2973696 bytes

(56)

56

4. Set the database identifier.

Note: Your database identifier will be a different value from that shown in the

solution. You recorded the database identifier in Practice 2-1.

RMAN> set dbid 1090770270;

executing command: SET DBID

5. Restore the control file from the autobackup.

RMAN> restore controlfile from autobackup; Starting restore at 28-MAR-06

channel ORA_DISK_1: sid=156 devtype=DISK recovery area destination:

/u01/app/oracle/flash_recovery_area

database name (or database unique name) used for search: ORCL

channel ORA_DISK_1: autobackup found in the recovery area channel ORA_DISK_1: autobackup found:

/u01/app/oracle/flash_recovery_area/ORCL/p

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/u01/app/oracle/oradata/orcl/control01.ctl output filename=/u01/app/oracle/oradata/orcl/control02.ctl output filename=/u01/app/oracle/

Finished restore at 28-MAR-06 6. Mount the database.

RMAN> alter database mount; database mounted

released channel: ORA_DISK_1

7. Recover the database by issuing the RECOVER DATABASE command. RMAN> recover database;

Starting recover at 28-MAR-06

Starting implicit crosscheck backup at 28-MAR-06 allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK Crosschecked 11 objects

(57)

57

Finished implicit crosscheck backup at 28-MAR-06 Starting implicit crosscheck copy at 28-MAR-06 using channel ORA_DISK_1

Crosschecked 5 objects

Finished implicit crosscheck copy at 28-MAR-06 searching for all files in the recovery area cataloging files...

cataloging done

List of Cataloged Files ======================= File Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2006_0 3_28/o1_mfc File Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2006_0 3_28/o1_mfc File Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2006_0 3_28/o1_mfc File Name: /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2006_0 3_27/o1_mfp

using channel ORA_DISK_1

allocated channel: ORA_SBT_TAPE_1

archive log thread 1 sequence 116 is already on disk as file /u01/app/oracle/flc

archive log thread 1 sequence 119 is already on disk as file /u01/app/oracle/org archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivel og/2006_06 archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivel og/2006_07 archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivel og/2006_08 archive log filename=/u01/app/oracle/oradata/orcl/redo01.log thread=1

(58)

58 sequence=9

8. Open the database with the RESETLOGS option. RMAN> alter database open resetlogs; database opened

9. Back up the current online redo log file and back up all the archived redo log files. RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT RMAN> backup archivelog all;

Starting backup at 10-APR-06 current log archived

channel ORA_DISK_1: sid=159 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=15 recid=53 stamp=587378250

channel ORA_DISK_1: starting piece 1 at 10-APR-06 channel ORA_DISK_1: finished piece 1 at 10-APR-06 piece

(59)

59 2006_04_10/o1_mE

channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set

channel ORA_DISK_1: starting piece 1 at 10-APR-06 channel ORA_DISK_1: finished piece 1 at 10-APR-06 piece

handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/ 2006_04_10/o1_mE

Finished backup at 10-APR-06

Starting Control File and SPFILE Autobackup at 10-APR-06 piece

Finished Control File and SPFILE Autobackup at 10-APR-06

10. Create a whole database backup.

RMAN> backup database;

Starting backup at 10-APR-06 released channel: ORA_SBT_TAPE_1 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=/u01/app/oracle/oradata/orcl/system01.dbf input datafile fno=00003

name=/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafile fno=00005

name=/u01/app/oracle/oradata/orcl/example01.dbf input datafile fno=00002

name=/u01/app/oracle/oradata/orcl/undotbs01.dbf input datafile fno=00004

name=/u01/app/oracle/oradata/orcl/users01.dbf input datafile fno=00006

name=/u01/app/oracle/oradata/orcl/example02.dbf channel ORA_DISK_1: starting piece 1 at 10-APR-06 channel ORA_DISK_1: finished piece 1 at 10-APR-06 piece

handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/ 2006_04_10/o1_mE

(60)

60

Finished backup at 10-APR-06

Starting Control File and SPFILE Autobackup at 10-APR-06 piece

(61)

61

Solutions for Practice 4-4: Delete Obsolete Backups

1) Use Recovery Manager to view obsolete backups.

Answer:

1. In your RMAN session, issue the REPORT OBSOLETE command to determine whether you have any obsolete backups.

Note: Your results may vary from the following output.

RMAN> report obsolete;

RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1

Report of obsolete backups and copies

Type Key Completion Time

Filename/Handle

--- --- --- --- ---

Archive Log 19 15-MAR-06 /u01/app/oracle/flash_recovery_ac Archive Log 20 16-MAR-06 /u01/app/oracle/flash_recovery_ac Archive Log 21 20-MAR-06 /u01/app/oracle/flash_recovery_ac Archive Log 22 20-MAR-06 /u01/app/oracle/flash_recovery_ac Archive Log 23 20-MAR-06 /u01/app/oracle/flash_recovery_ac Archive Log 24 21-MAR-06 /u01/app/oracle/flash_recovery_ac Datafile Copy 4 21-MAR-06 /u01/app/oracle/flash_recovery_af Backup Set 5 21-MAR-06 Backup Piece 5 21-MAR-06 /u01/app/oracle/flash_recovery_ap

Backup Set 7 22-MAR-06

Backup Piece 7 22-MAR-06 /u01/app/oracle/flash_recovery_ap

Backup Set 9 22-MAR-06

Backup Piece 10 22-MAR-06 /u01/app/oracle/flash_recovery_ap

2) Use Enterprise Manager Database Control to delete obsolete backups.

Answer:

1. Invoke Enterprise Manager and log in as sys/oracle as SYSDBA.

(62)

62

3. Click Delete All Obsolete to remove obsolete backups.

4. Review the information on the Delete All Obsolete: Specify Job Parameters page and

click Submit Job.

5. You receive the “Job submission succeeded” message. You can click View Job to

monitor the job. After it completes successfully, return to the Database Home page.

6. Access the Manage Current Backups page again to view the backup sets and image

copies that were retained.

3) You can also use RMAN to verify that your obsolete backups were deleted.

Answer:

1. In your RMAN session, execute the REPORT OBSOLETE command.

RMAN> report obsolete;

RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1

(63)

63

Practice Solutions for Lesson 5

In this practice, you use Oracle Flashback features to recover from errors in your database.

(64)

64

Solutions for Practice 5-1: Enable Flashback Database 1) Use Enterprise Manager to enable Flashback Database.

Answer:

1. Start Enterprise Manager and connect as SYS/ORACLE as SYSDBA. 2. Click the Maintenance tab.

3. Select Recovery Settings in the Backup/Recovery Settings section. 4. Scroll to the Flash Recovery section.

5. Select “Enable Flashback Database - flashback logging can be used for fast database point-in-time recovery” and click Apply.

6. The Confirmation message is displayed. You must restart the database instance to enable Flashback Database. Click Yes to restart the database instance.

(65)

65

8. The “Restart Database: Confirmation” page is displayed. Click Yes.

9. The “Restart Database:Activity Information” page is displayed. Wait a few minutes and click Refresh.

10. Log in to Enterprise Manager as SYS/ORACLE as SYSDBA.

2) Use the ALTER DATABASE command to enable supplemental logging.

Answer:

1. In a terminal window, log in to SQL*Plus as SYSDBA.

[oracle@edrsr10p1 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 18 09:10:07 2006

With the Partitioning, OLAP and Data Mining options 2. Execute the ALTER DATABASE command to enable supplemental logging.

SQL> ALTER DATABASE add supplemental log data; Database altered.

(66)

66

Solutions for Practice 5-2: Set Restore Points and Perform Flashback Table

Restore points are a way to “bookmark” database points in time. Set a restore point to

remember a significant change so that you can quickly recover to that point in time without having to record an SCN or time.

1) You must enable row movement to use restore points. Use Enterprise Manager to enable row movement for the HR.LOCATIONS table.

Answer:

1. Navigate to the Administration page. Select Tables in the Database Objects section. 2. Enter HR in the Schema field and LOCATIONS in the Object Name field. Click Go. 3. Select the HR.LOCATIONS table and click Edit.

4. Select the Options subtab.

(67)

67

6. The Update Message is displayed indicating that the change has been made to the HR.LOCATIONS table.

7. Click the Database tab to return to the home page. 2) Create a normal restore point.

Answer:

1. Select the Maintenance tab.

2. Select Manage Restore Points in the Backup/Recovery section. 3. Click Create on the Manage Restore Points page.

4. Enter Before_LOC_Update in the Restore Point Name field. Ensure that “Normal Restore Point” is selected. Click OK.

(68)

68

3) Use SQL*Plus to query the POSTAL_CODE column in the HR.LOCATIONS table.

Answer:

2. Execute the lab_05_02_03_02.sql script to query the POSTAL_CODE column of the HR.LOCATIONS table.

SQL> @lab_05_02_03_02

SQL> SELECT unique (postal_code) 2 FROM hr.locations 3 / POSTAL_CODE --- 10934 YSW 9T2 2901 26192 M5V 2L7 01307-002 … 1730 3095 99236 80925 23 rows selected.

4) Execute the lab_05_02_04_01.sql script to update the POSTAL_CODE column in the HR.LOCATIONS table so that all postal codes are set to 11111.

Answer:

1. Execute the lab_05_02_04_01.sql script. SQL> @lab_05_02_04_01

SQL> UPDATE hr.locations 2 SET postal_code = 11111 3 /

23 rows updated.

5) Execute the lab_05_02_05_01.sql script to query the POSTAL_CODE column in the HR.LOCATIONS table again.

Answer:

(69)

69 SQL> @lab_05_02_05_01

SQL> SELECT unique (postal_code) 2 FROM hr.locations

3 / POSTAL_CODE --- 11111

6) Restore the POSTAL_CODE column values using the restore point.

Answer:

1. Return to your Enterprise Manager session.

2. Copy the SCN value in the Creation SCN field to the buffer.

3. Click the database instance link to return to the Maintenance page. 4. Click Perform Recovery.

5. In the “Object Level Recovery” section, select Tables in the Object Type menu. Click “Perform Object Level Recovery.”

(70)

70

6. Select “Flashback to a known SCN” and paste the SCN that you copied in step 2.

Click Next.

7. Click Add Tables to add the HR.LOCATIONS table.

8. Enter HR in the Schema Name field and LOCATIONS in the Table field. Click Search.

9. Select the HR.LOCATIONS table and click OK.

(71)

71

10. The HR.LOCATIONS table is added to the “Tables to Flashback” field. Click

11. Accept the default of “Cascade: Flashback the selected tables and all dependent tables”

on the Dependency Options page. Click Next.

12. On the “Perform Object Level Recovery: Review” page, confirm the information. Click

Submit.

13. The Confirmation page is displayed. Click OK to return to the Maintenance page.

7) Return to your SQL*Plus session. Execute the lab_05_02_07_01.sql script to query the POSTAL_CODE column in HR.LOCATIONS again to be sure the correct values have been restored.

(72)

72 1. Execute the lab_05_02_07_01.sql script.

SQL> @lab_05_02_07_01

SQL> SELECT unique (postal_code) 2 FROM hr.locations 3 / POSTAL_CODE --- 10934 YSW 9T2 … 99236 80925 23 rows selected.

(73)

73

Solutions for Practice 5-3: Use Flashback Query and Flashback

Versions Query

1) Execute the lab_05_03_01_01.sql script to query the HR.LOCATIONS table for location ID 1400.

Answer:

1. In SQL*Plus, execute the lab_05_03_01_01.sql script. SQL> @lab_05_03_01_01 SQL> SELECT * 2 FROM hr.locations 3 WHERE location_id = 1400 4 / LOCATION_ID STREET_ADDRESS POSTAL_CODE --- --- --- --- CITY STATE_PROVINCE CO --- --- - - 1400 2014 Jabberwocky Rd 26192 Southlake Texas US

2) Execute the lab_05_03_02_01.sql script to update the POSTAL_CODE column in the HR.LOCATIONS table, simulating user error.

Answer:

1. Invoke SQL*Plus and execute the lab_05_03_02_01.sql script.

SQL> @lab_05_03_02_01

SQL> UPDATE hr.locations

2 SET postal_code = postal_code + 100 3 WHERE location_id = 1400 4 / 1 row updated. SQL> commit 2 / Commit complete.

3) Execute the lab_05_03_03_01.sql script to query the POSTAL_CODE column in HR.LOCATIONS and view the change.

Answer:

(74)

74 SQL> @lab_05_03_03_01 SQL> SELECT * 2 FROM hr.locations 3 WHERE location_id = 1400 4 / LOCATION_ID STREET_ADDRESS POSTAL_CODE --- --- --- --- CITY STATE_PROVINCE CO --- --- - - 1400 2014 Jabberwocky Rd 26292 Southlake Texas US

4) Execute the lab_05_03_04_01.sql script to update the POSTAL_CODE column in the HR.LOCATIONS

Answer:

1. Execute the lab_05_03_04_01.sql script.

SQL> @lab_05_03_04_01

SQL> UPDATE hr.locations

2 SET postal_code = postal_code + 100 3 WHERE location_id = 1400 4 / 1 row updated. SQL> commit 2 / Commit complete.

5) Use Enterprise Manager to perform Flashback Versions Query to correct the user

errors.

Answer:

1. Navigate to the Administration page.

2. Select Tables in the Database Objects section.

(75)

75

4. Select the HR.LOCATIONS table. Select Flashback Versions Query in the Actions

menu. Click Go.

5. Choose all the columns by selecting each in the Available Columns list and clicking

Move to move it to the Selected Columns list.

(76)

76 7. Select the oldest change to the table and click Next.

8. Confirm the flashback information. Click Next.

9. Accept the default of “Cascade: Flashback the selected tables and all dependent tables.” Click Next.

(77)

77 11. The Confirmation page is displayed. Click OK.

6) Return to your SQL*Plus session. Query the HR.LOCATIONS table to confirm the Flashback operation.

Answer:

1. Execute the lab_05_03_06_01.sql script to query the HR.LOCATIONS table. SQL> @lab_05_03_06_01 SQL> SELECT * 2 FROM hr.locations 3 WHERE location_id = 1400 4 / LOCATION_ID STREET_ADDRESS POSTAL_CODE --- --- --- --- CITY STATE_PROVINCE CO --- --- - - 1400 2014 Jabberwocky Rd 26192 Southlake Texas US

(78)

78

Solutions for Practice 5-4: Use Flashback Database

1) Use Enterprise Manager to verify that Flashback Database is enabled.

Answer:

1. Navigate to the Maintenance page.

2. Select Recovery Settings in the Backup/Recovery Settings section. Scroll to the Flash Recovery section. Verify that Flashback Database is enabled.

3. Click the Database Instance link to return to the Maintenance page. 2) Use Enterprise Manager to create a Guaranteed Restore Point.

Answer:

1. Select Manage Restore Points in the Backup/Recovery section. 2. Click Create to create a new restore point.

3. Enter “Before_Truncate” in the Restore Point Name field. Select “Guaranteed Restore Point.” Click OK.

(79)

79 4. The Restore Point is created.

3) Execute the lab_05_04_03_01.sql script to determine the number of rows in the HR.JOB_HISTORY table. Record the number of rows: ___________

Answer:

1. Execute the lab_05_04_03_01.sql script to determine the number of rows in the HR.JOB_HISTORY table. SQL> @lab_05_04_03_01 SQL> SELECT count(*) 2 FROM hr.job_history 3 / COUNT(*) --- 10

4) Execute the lab_05_04_04_01.sql script to truncate the HR.JOB_HISTORY table.

Answer:

1. Execute the lab_05_04_04_01.sql script. SQL> @lab_05_04_04_01

SQL> truncate table hr.job_history 2 /

Table truncated.

5) Execute the lab_05_04_05_01.sql script to determine the number of rows in the HR.JOB_HISTORY table.

(80)

80 1. Execute the lab_05_04_05_01.sql script.

SQL> @lab_05_04_05_01 SQL> SELECT count(*) 2 FROM hr.job_history 3 / COUNT(*) --- 0

6) Use Flashback Database to restore the HR.JOB_HISTORY table rows.

Answer:

1. Return to your Enterprise Manager window. 2. Navigate to the Maintenance page.

3. Select Manage Restore Points in the Backup/Recovery section.

4. Select the BEFORE_TRUNCATE restore point. Click “Recover Whole Database To.”

5. Enter the host credential information. Click Yes.

(81)

81 7. Click Perform Recovery.

8. Enter Host Credentials. Click Continue. 9. Enter Database login. Click Login.

10. Click “Perform Whole Database Recovery.”

11. Select “Recover to a prior point in time.”

12. Select Restore Point and click the Flashlight icon.

13. Select the BEFORE_TRUNCATE restore point. Click Select 14. The Restore Point field is populated. Click Next.

(82)

82 15. Select Yes. Click Next.

16. Review the information. Click Submit.

17. The “Processing: Perform Whole Database Recovery” page is displayed.

18. The operation is complete. You can scroll through the output to view the details of the operation. Click “Open Database” to open the database.

19. The “The database has been opened successfully” message is displayed. Click OK. 7) Return to your SQL*Plus session. Execute the lab_05_04_07_02.sql script to query the HR.JOB_HISTORY table again to be sure the data has been restored.

Answer:

1. Invoke SQL*Plus and log in as SYS/ORACLE as SYSDBA. 2. Execute the lab_05_04_07_02.sql script.

(83)

83 SQL> @query_job_hist SQL> SELECT count(*) 2 FROM hr.job_history 3 / COUNT(*) --- 10

(84)

84

Practice Solutions for Lesson 6

In this practice, you create a duplicate database.

(85)

85

Solutions for Practice 6-1: Create a Duplicate Database

In this practice, you use RMAN to create a duplicate database on the same host as your database. 1) Create an Oracle password file for your auxiliary instance.

Answer:

1. Change to the $ORACLE_HOME/dbs directory and use the orapwd utility to create a password file named orapwaux. Specify a password of oracle and 10 for the number of entries.

[oracle@edrsr10p1 oracle]$ cd $ORACLE_HOME/dbs [oracle@edrsr10p1 dbs]$ orapwd file=orapwaux password=oracle entries=10

[oracle@edrsr10p1 dbs]$ ls

hc_orcl.dat init.ora orapwaux snapcf_orcl.f initdw.ora lkORCL orapworcl spfileorcl.ora

2) Use Oracle Net Manager to create an entry called AUXDB in the tnsnames.ora file.

Answer:

1. Open a terminal window and enter netmgr at the operating system prompt. [oracle@edrsr10p1 oracle]$ netmgr

2. The Oracle Net Manager window appears. 3. Expand Local and Service Naming.

(86)

86

5. Enter auxdb in the Net Service Name field and click Next.

6. Select TCP/IP (default) as the protocol. Click Next.

7. Enter the host name of your PC in the Host Name field. Accept the default of 1521 for the port number. Click Next.

(87)

87 9. Click Finish.

10. Select File and Save Network Configuration to save the changes you made. 11. Click File and Exit to exit from Oracle Net Manager.

12. You can view your updated tnsnames.ora file in the

/u01/app/oracle/product/10.2.0/db_1/network/admin directory. AUXDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = edrsr10p1.us.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = aux.oracle.com) )

3) Create an initialization parameter file for the auxiliary instance.

Answer:

1. Create a text initialization parameter file named initAUX.ora from your server parameter file in the $HOME/auxinstance directory.

[oracle@edrsr10p1 oracle]$ cd $HOME/auxinstance [oracle@edrsr10p1 auxinstance]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 29 10:51:53 2006

With the Partitioning, OLAP and Data Mining options SQL> create pfile='$HOME/auxinstance/initAUX.ora' from spfile;

File created.

2. Edit the initAUX.ora initialization parameter file to make the following changes for the auxiliary instance:

audit_file_dest='/u01/app/oracle/admin/aux/adump' background_dump_dest='/u01/app/oracle/admin/aux/bdump control_files='/u01/app/oracle/oradata/aux/control01.c tl','/u01/app/oracle/oradata/aux/control02.ctl','/u01/

(88)

88

app/oracle/oradata/aux/control03.ctl' db_name='aux'

user_dump_dest='/u01/app/oracle/admin/aux/udump' Add the following parameters:

DB_FILE_NAME_CONVERT=‘/u01/app/oracle/oradata/orcl’, ‘/u01/app/oracle/oradata/aux’

LOG_FILE_NAME_CONVERT=‘/u01/app/oracle/oradata/orcl’, ‘/u01/app/oracle/oradata/aux’

Remove the lines at the beginning of the file that begin with orcl.__. Add the comment character, #, to this line:

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' 4) Start the auxiliary instance in NOMOUNT mode using the initAUX.ora file.

Answer:

1. Set the ORACLE_SID environment variable to aux.

[oracle@edrsr10p1 oracle]$ ORACLE_SID=aux [oracle@edrsr10p1 oracle]$ echo $ORACLE_SID Aux

2. Invoke SQL*Plus and connect as SYSDBA.

[oracle@edrsr10p1 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 29 11:38:53 2006

3. Start the instance in NOMOUNT mode using the initialization parameter file you created in step 2. SQL> startup nomount pfile='$HOME/auxinstance/initAUX.ora'

ORACLE instance started.

Total System Global Area 285212672 bytes Fixed Size 1218992 bytes

Variable Size 92276304 bytes Database Buffers 188743680 bytes Redo Buffers 2973696 bytes

5) Create a server parameter file (SPFILE).

Oracle Database 10g: Backup and Recovery