OCA/OCP: Oracle9i DBA Fundamentals I Study Guide

Oracle9i DBA Fundamentals I......Page 1
Frontmatter......Page 2
Acknowledgments......Page 6
Introduction......Page 7
Assessment Test......Page 17
Answers to Assessment Test......Page 25
1: Oracle9i New Features for Administrators......Page 30
High Availability......Page 31
Disaster Recovery......Page 32
LogMiner......Page 33
Backup and Recovery......Page 34
Scalability......Page 35
Architecture......Page 36
Session Management......Page 37
SQL and PL/SQL Optimization......Page 38
Java Enhancements......Page 39
Fine-Grained Auditing......Page 40
Undo Tablespace Management......Page 41
Fine-Grained Resource Management......Page 42
Enterprise Manager......Page 43
Unsupported and Deprecated Features......Page 44
Network......Page 45
Initialization Parameters......Page 46
Summary......Page 47
Key Terms......Page 49
Review Questions......Page 50
2: Oracle Overview and Architecture......Page 57
Oracle9i Server: An Overview......Page 58
Logical Storage Structures......Page 59
Physical Storage Structures......Page 61
Oracle Memory Structures......Page 63
System Global Area......Page 64
Program Global Area......Page 68
Oracle Background Processes......Page 69
Database Writer (DBWn)......Page 70
Log Writer (LGWR)......Page 71
System Monitor (SMON)......Page 72
Recoverer (RECO)......Page 73
Shared Server (Snnn)......Page 74
Connecting to an Oracle Instance......Page 76
Dedicated Server Configuration......Page 77
The Shared Server (Multithreaded) Configuration......Page 78
SQL Parse, Execute, and Fetch Stages......Page 80
Processing a COMMIT or ROLLBACK......Page 84
Summary......Page 86
Exam Essentials......Page 87
Key Terms......Page 88
Review Questions......Page 89
Answers to Review Questions......Page 95
3: Installing and Managing Oracle......Page 98
The Oracle Universal Installer......Page 99
Oracle Enterprise Manager......Page 101
The Console......Page 102
The Management Server and Common Services......Page 103
DBA Tools......Page 104
Optimal Flexible Architecture (OFA)......Page 105
Administrator Authentication Methods......Page 107
Operating System Authentication......Page 108
Password File Authentication......Page 109
Starting Up the Oracle Instance......Page 111
The Parameter File: PFILE......Page 113
The Parameter File: SPFILE......Page 114
Get Parameter Values......Page 115
Set Parameter Values......Page 118
Managing Sessions......Page 119
Shutting Down the Oracle Instance......Page 121
Instance Messages and Instance Alerts......Page 124
Oracle Managed Files (OMF)......Page 125
Summary......Page 126
Exam Essentials......Page 127
Key Terms......Page 128
Review Questions......Page 129
Answers to Review Questions......Page 134
4: Creating a Database and Data Dictionary......Page 137
Creating a Database......Page 138
Preparing Resources......Page 139
Parameters......Page 140
Environment Variables......Page 143
The CREATE DATABASE Command......Page 144
Using OMF to Create a Database......Page 147
The Data Dictionary......Page 148
Data Dictionary Scripts......Page 149
Administering Stored Procedures and Packages......Page 151
Completing the Database Creation......Page 152
Querying the Dictionary......Page 153
The Database Configuration Assistant......Page 155
Summary......Page 159
Exam Essentials......Page 160
Key Terms......Page 161
Review Questions......Page 162
Answers to Review Questions......Page 167
5: Control and Redo Log Files......Page 169
Maintaining the Control File......Page 170
Multiplexing Control Files......Page 172
Creating New Control Files......Page 174
Querying Control File Information......Page 178
Maintaining and Monitoring Redo Log Files......Page 181
Log Switch Operations......Page 183
Multiplexing Log Files......Page 186
Archiving Log Files......Page 191
Querying Log and Archive Information......Page 195
Summary......Page 199
Exam Essentials......Page 200
Key Terms......Page 201
Review Questions......Page 202
Answers to Review Questions......Page 208
6: Logical and Physical Database Structures......Page 211
Tablespaces and Data Files......Page 212
Managing Tablespaces......Page 214
Creating a Tablespace......Page 215
Altering a Tablespace......Page 225
Querying Tablespace Information......Page 230
Managing Data Files......Page 236
Sizing Files......Page 237
Oracle Managed Files......Page 238
Renaming and Relocating Files......Page 244
Querying Data File Information......Page 247
Summary......Page 250
Exam Essentials......Page 252
Key Terms......Page 253
Review Questions......Page 254
Answers to Review Questions......Page 260
7: Segments and Storage Structures......Page 263
Data Blocks......Page 264
Block Storage Parameters......Page 265
Automatic Space Management......Page 268
Extents......Page 269
Allocating Extents......Page 270
Querying Extent Information......Page 272
Segments......Page 273
Querying Segment Information......Page 275
Managing Undo Segments......Page 277
Creating Undo Segments......Page 278
Querying Undo Information......Page 281
Summary......Page 283
Exam Essentials......Page 284
Key Terms......Page 285
Review Questions......Page 286
Answers to Review Questions......Page 291
8: Managing Tables, Indexes, and Constraints......Page 293
Storing Data......Page 295
Creating Tables......Page 297
Altering Tables......Page 314
Analyzing Tables......Page 322
Querying Table Information......Page 326
The Structure of a Row......Page 328
Using ROWID......Page 329
Managing Indexes......Page 331
Creating Indexes......Page 333
Altering Indexes......Page 338
Querying Index Information......Page 342
Creating Constraints......Page 344
Enabling and Disabling Constraints......Page 349
Deferring Constraint Checks......Page 359
Querying Constraint Information......Page 360
Summary......Page 362
Exam Essentials......Page 363
Key Terms......Page 365
Review Questions......Page 366
Answers to Review Questions......Page 372
9: Managing Users, Security, and Globalization Support......Page 375
Managing Resources......Page 377
Managing Passwords......Page 380
Managing Profiles......Page 381
Querying Profile Information......Page 385
Users......Page 386
Managing Users......Page 387
Authenticating Users......Page 391
Complying with Oracle Licensing Terms......Page 392
Querying User Information......Page 393
Managing Privileges......Page 397
Object Privileges......Page 398
System Privileges......Page 400
Granting System Privileges......Page 405
Revoking Privileges......Page 406
Querying Privilege Information......Page 407
Managing Roles......Page 411
Using Predefined Roles......Page 412
Removing Roles......Page 413
Enabling and Disabling Roles......Page 414
Querying Role Information......Page 416
Auditing the Database......Page 418
Using Globalization Support......Page 420
The Unicode Character Set......Page 422
Using NLS Parameters......Page 423
Using NLS To Change Application Behavior......Page 426
Obtaining NLS Data Dictionary Information......Page 427
Summary......Page 430
Exam Essentials......Page 431
Key Terms......Page 433
Review Questions......Page 434
Answers to Review Questions......Page 439
Glossary......Page 441
A......Page 442
C......Page 443
D......Page 444
G......Page 446
L......Page 447
M......Page 449
O......Page 450
P......Page 451
R......Page 453
S......Page 455
U......Page 457
V......Page 458
Oracle 9i DBA Fundamentals II......Page 459
Frontmatter......Page 460
Acknowledgements......Page 464
Why Become an Oracle Certified Professional?......Page 465
What Does This Book Cover?......Page 471
About the Authors......Page 475
Assessment Test......Page 477
Answers to Assessment Test......Page 493
1: Introduction to Network Administration......Page 502
Network Complexity Issues......Page 504
Interfacing Existing Systems with New Systems......Page 505
Network Configurations......Page 506
Two-Tier Architecture......Page 507
N-Tier Architecture......Page 509
Connectivity: Multi-Protocol Support......Page 510
Directory Services: Directory Naming......Page 511
Scalability: Oracle Shared Server......Page 512
Scalability: Connection Manager......Page 513
Security: Firewall Support......Page 514
Accessibility: Heterogeneous Services......Page 516
The OSI Model......Page 517
The Oracle Communications Stack......Page 519
Oracle Net and Java Support......Page 522
Summary......Page 525
Exam Essentials......Page 526
Key Terms......Page 527
Review Questions......Page 528
Answers to Review Questions......Page 533
2: Configuring Oracle Net on the Server......Page 536
The Oracle Listener......Page 537
How Do Listeners Respond to Connection Requests?......Page 539
Managing Oracle Listeners......Page 542
Using Oracle Net Manager......Page 544
Configuring Listener Services Using the Oracle Net Manager......Page 545
Managing the Listener Using......Page 553
Starting the Listener......Page 554
Showing the Status of the Listener......Page 556
Listing the Services for the Listener......Page 558
Other Commands in Isnrctl......Page 559
set Commands in Isnrctl......Page 560
Stopping the Listener......Page 561
Dynamic Registration of Services......Page 562
Configure the Listener for Oracle9i JVM......Page 563
Troubleshooting Server-Side Connection Problems......Page 564
Server-Side Computer and Database Checks......Page 565
Server-Side Checks Network Checks......Page 568
Oracle Net Logging and Tracing on the Server......Page 571
Server Logging......Page 573
Server Tracing......Page 574
Summary......Page 578
Exam Essentials......Page 579
Key Terms......Page 580
Review Questions......Page 581
Answers to Review Questions......Page 586
3: Configuring Oracle Net for the Client......Page 590
Client-Side Names Resolution Options......Page 591
The Hostnaming Method......Page 592
The Localnaming Method......Page 598
Troubleshooting Client-Side Connection Problems......Page 605
Check Client/Listener Contact......Page 606
Verify Localnaming Configuration Files......Page 607
Check NAMES.DIRECTORY_PATH......Page 608
Check for Client-Side Error Codes......Page 609
Summary......Page 610
Exam Essentials......Page 611
Key Terms......Page 612
Review Questions......Page 613
Answers to Review Questions......Page 618
4: Configuring the Shared Server Option......Page 621
Shared Server Configuration......Page 622
Dedicated Server versus Shared Server......Page 623
Drawbacks of Shared Server......Page 625
Oracle Server Changes in a Shared Server Environment......Page 626
The Role of the Listener in a Shared Server Environment......Page 628
Configuring the Shared Server Option......Page 629
DISPATCHERS Parameter......Page 630
CIRCUITS Parameter......Page 632
Registering Dispatcher Information with the Listener......Page 634
Displaying Information about Shared Server Connections Using Isnrctl......Page 635
Data Dictionary Views for Shared Server......Page 636
Requesting a Dedicated Connection in a Shared Server Environment......Page 640
Tuning the Shared Server Option......Page 641
Sizing the Large Pool......Page 642
Determine Whether You Have Enough Dispatchers......Page 643
Determine Whether You Have Enough Shared Servers......Page 644
Summary......Page 645
Exam Essentials......Page 646
Key Terms......Page 647
Review Questions......Page 648
Answers to Review Questions......Page 654
5: Backup and Recovery Overview......Page 657
Types of Failure in Oracle Environments......Page 659
Media, or Disk, Failures......Page 660
Losing Data in a Database Failure......Page 661
Performing an Offline Backup......Page 662
Knowing Your Backup and Recovery Resources......Page 663
Undoing Changes to the Database......Page 664
Testing a Backup and Recovery Plan......Page 666
Summary......Page 668
Exam Essentials......Page 669
Key Terms......Page 670
Review Questions......Page 671
Answers to Review Questions......Page 675
6: Instance and Media Recovery Structures......Page 677
Recovery Processes......Page 679
Memory Structures......Page 680
File Structures......Page 682
Checkpoints, Redo Logs, and Archived Logs......Page 686
Ways to Tune the Instance Recovery......Page 688
Summary......Page 691
Exam Essentials......Page 692
Key Terms......Page 693
Review Questions......Page 694
Answers to Review Questions......Page 699
7: Configuring the Database Archiving Mode......Page 701
Choosing ARCHIVELOG Mode or NOARCHIVELOG Mode......Page 702
NOARCHIVELOG Mode......Page 703
Understanding Recovery Implications of NOARCHIVELOG......Page 704
Setting ARCHIVELOG Mode......Page 705
Setting Automatic Archiving......Page 709
Manually Archiving Logs......Page 712
Using init.ora Parameters for Multiple Archive Processes and Locations......Page 713
Multiple Archive Processes and Locations......Page 714
Remote Archived Log Locations......Page 716
Exam Essentials......Page 717
Key Terms......Page 718
Review Questions......Page 719
Answers to Review Questions......Page 724
8: Oracle Recovery Manager Overview and Configuration......Page 727
Identifying Features of Oracle Recovery Manager (RMAN)......Page 728
Exploring RMAN Components......Page 730
Storing Information Using the RMAN Repository and Control Files......Page 732
Describing Channel Allocation......Page 734
Exploring the Media Management Library Interface......Page 736
Connecting to RMAN without a Recovery Catalog......Page 738
Configuring the RMAN Environment......Page 739
Summary......Page 741
Exam Essentials......Page 742
Key Terms......Page 743
Review Questions......Page 744
Answers to Review Questions......Page 748
9: User-Managed and RMAN-Based Backups......Page 750
Working with Read-Only Tablespaces......Page 752
Understanding Closed and Opened Database Backups......Page 753
Performing Closed and Opened Database Backups......Page 755
Identifying User-Managed Control-File Backups......Page 759
Cleaning Up after Failed Online Backups......Page 762
The DBVERIFY Utility......Page 765
Full or Incremental Backups......Page 768
Using RMAN's BACKUP and COPY Commands......Page 769
Using RMAN to Create Sets......Page 770
Using RMAN to Create Image Copies......Page 773
Backing Up the Control File......Page 774
Summary......Page 775
Exam Essentials......Page 776
Key Terms......Page 777
Review Questions......Page 778
Answers to Review Questions......Page 782
10: User-Managed Complete Recovery and RMAN Complete Recovery......Page 784
Recovering Using NOACRHIVELOG and ARCHIVELOG Modes......Page 786
Performing User-Managed Recovery in NOARCHIVELOG Mode......Page 789
Performing User-Managed Complete Recovery in ARCHIVELOG Mode......Page 794
Restoring Data Files to Different Locations......Page 801
Relocate and Recover a Tablespace by Using Archived Redo Logs......Page 802
Describe Read-Only Tablespace Recovery......Page 806
Performing RMAN Recovery in NOARCHIVE Mode......Page 807
Performing RMAN Complete Recovery in ARCHIVELOG Mode......Page 811
Using RMAN to Restore Data Files to Different Locations......Page 817
Use RMAN to Relocate and Recover a Tablespace Using Archived Logs......Page 821
Summary......Page 824
Exam Essentials......Page 825
Key Terms......Page 826
Review Questions......Page 827
Answers to Review Questions......Page 831
11: User-Managed and RMAN-Based Incomplete Recovery......Page 833
Describing the User-Managed Incomplete Recovery......Page 834
Cancel-Based Recovery......Page 836
Time-Based Recovery......Page 839
Change-Based Recovery......Page 840
Recovering after Losing Current Redo Logs......Page 841
Performing an RMAN-Based Incomplete Recovery Using UNTIL TIME......Page 843
Performing RMAN-Based Incomplete Recoer Using UNTIL SEQUENCE......Page 848
Summary......Page 854
Exam Essentials......Page 855
Key Terms......Page 856
Review Questions......Page 857
Answers to Review Questions......Page 861
12: RMAN Maintenance......Page 863
Performing Cross Checking of Backups and Copies......Page 864
Updating the Repository When Backups Have Been Deleted......Page 867
Change the Availability Status of Backups and Copies......Page 868
Make a Backup or Copy Exempt from the Retention Policy......Page 871
Catalog Backups Made with Operating System Commands......Page 875
Exam Essentials......Page 876
Key Terms......Page 877
Review Questions......Page 878
Answers to Review Questions......Page 882
13: Recovery Catalog Creation and Maintenance......Page 884
Considerations for Using the Recovery Catalog......Page 886
The Components of Recovery Catalog......Page 887
Creating a Recovery Catalog......Page 888
Using RMAN to Register, Resynchronize, and Reset the Database......Page 890
Registering and Unregistering the Target Database......Page 891
Resetting the Recovery Catalog......Page 893
Resynchronizing the Recovery Catalog......Page 894
Using List Commands......Page 896
Using Report Commands......Page 899
Creating, Storing, and Running RMAN Scripts......Page 900
Methods for Backing Up and Recovering the Recovery Catalog......Page 904
Summary......Page 905
Exam Essentials......Page 906
Key Terms......Page 907
Review Questions......Page 908
Answers to Review Questions......Page 912
14: Transporting Data between Databases......Page 914
Using the Export and Import Utilities......Page 915
Export and Import Concepts and Structures......Page 918
Performing Simple Export and Import Operations......Page 921
Performing a Simple Export......Page 922
Performing a Simple Import......Page 925
Summary......Page 930
Exam Essentials......Page 931
Key Terms......Page 932
Review Questions......Page 933
Answers to Review Questions......Page 937
15: Using SQL*Loader to Load Data......Page 939
Using Direct-Load Insert Operations......Page 940
Using SQL*Loader......Page 942
Control Files, Log Files, and Input Data or Data Files......Page 943
Conventional Load, Direct-Path Load, and External-Path Load......Page 947
SQL*Loader Parallel Load Methods......Page 951
Performing Basic SQL*Loader Operations......Page 952
Guidelines for Using SQL*Loader and Direct- Load Insert......Page 956
Exam Essentials......Page 957
Key Terms......Page 958
Review Questions......Page 959
Answers to Review Questions......Page 963
Glossary......Page 965
A......Page 966
B......Page 967
C......Page 968
D......Page 970
E......Page 971
H......Page 972
I......Page 973
J......Page 974
L......Page 975
N......Page 977
O......Page 978
P......Page 980
R......Page 982
S......Page 985
T......Page 986
U......Page 987
W......Page 988