[心得] Oracle OCP 資料庫認證專家 考試心得經驗分享, 真得是花時間

[前言]

  工作快18年了, 當了DBA 也將近8年, 一直都有在思考, 要再去進修,
 自己從簡單SQL使用 ==> Store Procedure 撰寫 ==>  DBA, 實戰經驗應該夠,
 oracle版本則 從 9i ==> 10g ==> 11g 玩到 12c .

 但是考試就是考試, 工作上常用的會考, 不常用的也會考, 所以去外面上課是必要的,
 簡單介紹一下oracle 認證步驟.

一. 認證介紹, 

• OCA (Oracle Cerified Associate)  

         目前市面上常用的還是oracle 12c ,  所以認證建議考12c為主,

     要通過OCA, 要先經過兩個考試 1ZO-071 跟 1ZO-062

     看圖就懂了, OCA要通過兩科, 自己準備大概要半年, 每三個月讀一科考一個

     

      (A) 1Z0-071,  Oracle Database SQL   

        通過標準, 80題, 120分鐘, 通過63%即可

        只求通過就好了, 滿分是神才會做的事情.

          Oracle官方是建議要讀 Oracle Database 12c: Introduction to SQL.

       內容大概有

• Understand the basic concepts of relational databases ensure refined code by developers.
• Create reports of sorted and restricted data.
• Run data manipulation statements (DML).
• Control database access to specific objects.
• Manage schema objects.
• Manage objects with data dictionary views.
• Retrieve row and column data from tables.
• Control privileges at the object and system level.
• Create indexes and constraints; alter existing schema objects.
• Create and query external tables

   

       其實看起來很複雜, 只要用過SQL, 寫過簡單SQL script, 不難通過,

     但書還是要讀啊!  網路上找的題型

題型範例:

Which expressions do not return NULL values? (Choose all that apply.)
A. select ((10 + 20) * 50) + null from dual;
B. select null/0 from dual;
C. select 'this is a ' || null || 'test with nulls' from dual;
D. select null || 'test' || null as "Test" from dual;
Answer: CD

       不會很難的, 也會有不少 insert  table ..... select table 之類的,

       有SQL 實戰經驗2~3年的朋友通過率很高, 若還有store procedure 的實戰經驗,

       更加不用擔心了. 這科我覺得可以自修, 不難通過.

     (B) 1Z0-062 , Oracle Database 12c Administration

       

        通過標準, 80題, 120分鐘, 通過64%即可

        雖然是說12c, 但說實在的cloud的部分, 暫時可以不用理它, 比重很低,

        官方建議要讀的如下面, 自行參考. 1Z0-062我建議要補習, 

        有些工作上少用的功能也是會考的. 如 CDB /PDB 等等的觀念.

Oracle Database Instance

• Understand initialization parameter files
• Start up and shut down an Oracle database instance
• View the alert log and access dynamic performance views

Administering User Security

• Create and manage database user accounts
• Grant and revoke privileges
• Create and manage roles
• Create and manage profiles

Create and manage tablespaces

• Explain how Oracle database server automatically manages space
• Save space by using compression
• Use the Segment Advisor
• Reclaim wasted space from tables and indexes by using the segment shrink functionality

Managing Data Concurrency

• Describe the locking mechanism and how Oracle manages data concurrency
• Monitor and resolve locking conflicts

Backup and Recovery Concepts

• Identify the importance of checkpoints, redo log files, and archive log files

Performing Database Backups

• Create consistent database backups
• Back up your database without shutting it down
• Create incremental backups
• Automate database backups
• Manage backups

Moving Data

• Describe ways to move data
• Use SQL*Loader to load data from a non-Oracle database
• Use external tables to move data via platform-independent files
• Explain the general architecture of Oracle Data Pump
• Use Data Pump Export and Import to move data between Oracle databases

Managing Performance: SQL Tuning

• Use the SQL Tuning advisor

Creating an Oracle Database Using DBCA 

• Generate database creation scripts by using DBCA 
• Manage database design templates by using DBCA

Introduction to DBAAS

• Recognize the difference between a DBaaS Instance and a Database Instance
• Navigate with My Services Dashboard
• User authentication and roles
• Generate a Secure Shell (SSH) Public/Private Key Pair

Oracle Database Management Tools

• Use database management tools

Configuring the Oracle Network Environment

• Configure Oracle Net Services
• Use tools for configuring and managing the Oracle network
• Configure client-side network
• Configure communication between databases

Managing Database Storage Structures

• Describe the storage of table row data in blocks
• Create and manage tablespaces

Managing Undo Data

• Explain DML and undo data generation
• Monitor and administer undo data
• Describe the difference between undo data and redo data
• Configure undo retention

Implementing Oracle Database Auditing

• Enable standard database auditing and unified auditing

Backup and Recovery Configuration

• Configure the fast recovery area
• Configure ARCHIVELOG mode

Performing Database Recovery

• Determine the need for performing recovery
• Use Recovery Manager (RMAN) and the Data Recovery Advisor to perform recovery of the control file, redo log file and data file

Performing Database Maintenance

• Use server-generated alerts

Automating Tasks by Using Oracle Scheduler

• Use Oracle Scheduler to simplify management tasks
• Use job chains to perform a series of related tasks
• Use Scheduler jobs on remote systems
• Use advanced Scheduler features to prioritize jobs

Upgrading Oracle Database Software  

• Describe data migration methods
• Describe the upgrade process

        看了上面的考試的範圍, 就知道複雜了, 當DBA都要懂且熟.

         不會考指令或是錯字, 大部分都考觀念..觀念對比較重要.

       

網路上抓的範例


Which two statements are true?
A. A role cannot be assigned external authentication.
B. A role can be granted to other roles.
C. A role can contain both system and object privileges.
D. The predefined resource role includes the unlimited_tablespace privilege.
E. All roles are ownedby the sys user.
F. The predefined connect role is always automatically granted to all new users at the time of their creation.

Answer: B,C

         通過兩科就可以取得 Oracle OCA, 1Z0-62真的比較難, 要多花點時間讀書.
       

• OCP   (Oracle Certified Professional)

        通過1Z0-062, 再來就是 1Z0-063 , 這一科我讀半年, 這難度就高了.

    (1) 1Z0-063, Oracle Database 12c: Advanced Administration
   
         通過標準, 80題, 120分鐘, 通過60%即可

        自己剛看也是霧濛濛, Advanced是啥意思?  看了下面的考試建議, 秒懂了,
     考備份(RMAN) , 硬體故障(media failure)如何拯救資料庫, 如何restore/recover 等等.
     範圍更多更複雜, 當然我也是建議去補習讀書. 機會才會大,

     一樣還是會考CDB/PDB這是12c的基礎, RMAN要很熟,  雲cloud一樣考很少, 
     其它Oracle Secure Backup (OSB), 加密的功能都要知道.

Backup and Recovery

Oracle Data Protection Solutions

• Explain Oracle backup and recovery solutions
  • Describe types of database failures
  • Describe the tools available for backup and recovery tasks
  • Describe RMAN and maximum availability architecture
  • Use the SYSBACK privilege
  • Use RMAN stand-alone and job commands

Configuring for Recoverability

• Configure and manage RMAN settings
  • Configure persistent settings for RMAN
  • View persistent settings
  • Specify a retention policy
• Configure the Fast Recovery Area
  • Explain the Fast Recovery Area
  • Configure the Fast Recovery Area
• Configure control files and redo log files for recoverability
  • Multiplex control files
  • Multiplex redo log files

Implementing Backup Strategies

• Use various RMAN backup types and strategies
  • Enable ARCHIVELOG mode
  • Create tape and disk based backups
  • Create whole database backups
  • Create consistent and inconsistent backups
  • Create backup sets and image copies
  • Create backups of read-only tablespaces
  • Employ best practices for data warehouse backups

Configuring RMAN Backup Options and Creating Backup of Non-Database Files

• Use techniques to improve backups
  • Create compressed backups
  • Create multi-section backups of very large files
  • Create proxy copies
  • Create duplexed backup sets
  • Create backups of backup sets
  • Create archival backups
• Perform backup of non-database files
  • Back up a control file to trace
  • Back up archived redo log files
  • Back up ASM diskgroup metadata

Diagnosing Failures

• Describe the Automatic Diagnostic Workflow
  • Use the Automatic Diagnostic Repository
  • Use ADRCI
  • Find and interpret message output and error stacks
  • Use the Data Recovery Advisor
• Handle block corruption
  • Detect block corruption using RMAN
  • Perform block recovery using RMAN

Recovering Files Using RMAN

• Perform recovery for spfile, control file, redo log files
• Perform table recovery from backups
• Perform recovery of index and read-only tablespaces, temp file
• Restore a database to a new host

Using Flashback Technologies

• Describe the Flashback technologies
  • Configure a database to use Flashback technologies
  • Guarantee undo retention
• Use Flashback to query data
  • Use Flashback Query
  • Use Flashback Version Query
  • Use Flashback Transaction Query
  • Flash back a transaction
• Perform Flashback Table operations
  • Perform Flashback Table
  • Restore tables from the recycle bin
• Describe and use Flashback Data Archive
  • Use Flashback Data Archive
  • Use DBMS_FLASHBACK_ARCHIVE package

Transporting Data

• Describe and use transportable tablespaces and databases
  • Transport tablespaces between databases using image copies or backup sets
  • Transport databases using data files or backup sets
  • Transport data across platforms

Monitoring and Tuning of RMAN Operations

• Tune RMAN performance
  • Interpret RMAN error stacks
  • Diagnose performance bottlenecks
  • Tune RMAN backup performance

Creating Multitenant Container and Pluggable Databases

• Configure and create a CDB
• Create a PDB using different methods
• Unplug and drop a PDB
• Migrate a non-CDB database to PDB

Managing Storage in a CDB and PDBs

• Manage permanent and temporary tablespaces in CDB and PDBs

Managing Availability

• Perform backups of a CDB and PDBs
• Recover PDB from PDB datafiles loss
• Use Data Recovery Advisor
• Duplicate PDBs using RMAN

Moving Data, Performing Security Operations and Interacting with Other Oracle Products

• Use Data Pump
• Use SQL*Loader
• Audit operations
• Use Other Products with CDB and PDBs - Database Vault, Data Guard, LogMiner

Performing Basic Backup and Recovery

• Back up and recover a NOARCHIVELOG database
• Perform backup and recovery in NOARCHIVELOG mode
• Use SQL in RMAN

Using the RMAN Recovery Catalog

• Create and use an RMAN recovery catalog
  • Configure a recovery catalog
  • Register target databases in a recovery catalog
  • Catalog additional backup files
  • Resynchronize a recovery catalog
  • Use and maintain RMAN stored scripts
  • Upgrade and drop a recovery catalog
• Protect the RMAN recovery catalog
  • Back up the recovery catalog
  • Re-create an unrecoverable recovery catalog
  • Export and import the recovery catalog

Performing Backups

• Perform full and incremental backups
  • Create full and incremental backups
  • Use the Oracle-suggested backup strategy
• Manage backups
  • Configure and monitor block change tracking
  • Report on backups using LIST, REPORT commands
  • Manage backups using CROSSCHECK, DELETE commands

Using RMAN-Encrypted Backups

• Create RMAN-encrypted backups
  • Use transparent-mode encryption
  • Use password-mode encryption
  • Use dual-mode encryption
  • Restore encrypted backups

Performing Restore and Recovery Operations

• Describe and tune instance recovery
• Perform complete and incomplete recovery
  • Use RMAN RESTORE and RECOVER commands
  • Restore ASM disk groups
  • Recover from media failures
  • Perform complete and incomplete or “point-in-time” recoveries using RMAN

Using Oracle Secure Backup

• Configure and use Oracle Secure Backup

Using Flashback Database

• Perform Flashback Database
  • Configure Flashback Database
  • Perform Flashback Database

Duplicating a Database

• Choose a technique for duplicating a database
  • From an active database, connected to the target and auxiliary instances
  • From backup, connected to the target and auxiliary instances
  • From backup, connected to the auxiliary instance, not connected to the target, but with recovery catalog connection
  • From backup, connected to the auxiliary instance, not connected to the target and the recovery catalog
  • Duplicate a database with RMAN
• Create a backup-up based duplicate database
• Duplicate a database based on a running instance

Managing Pluggable and Container Databases

Multitenant Container and Pluggable Database Architecture

• Describe the multitenant container database architecture
• Explain pluggable database provisioning

Managing a CDB and PDBs

• Establish connections to CDB/PDB
• Start up and shut down a CDB and open and close PDBs
• Evaluate the impact of parameter value changes

Managing Security in a CDB and PDBs

• Manage common and local users
• Manage common and local privileges
• Manage common and local roles
• Enable common users to access data in specific PDBs

Managing Performance

• Monitor operations and performance in a CDB and PDBs
• Manage allocation of resources between PDBs and within a PDB
• Perform Database Replay

OCP認證考試, 主要就是再考備份backup/還原Restore等觀念, 這方面內容相當的多.

能夠實作練習的話, 效果會好很多, 雖然死背能有效果但容易搞亂了

網路上找的題型.

Which two statements are true about Flashback Version Query?

A. The result of a query can be used as part of a DML statement.

B. It can be used to create views.

C. It can be used only if Flashback Data Archive is enabled for a table.

D. It retrieves all versions of rows that exist in a time interval, including the start time and end time.

E. It can be used to retrieve the SQL that is required to undo a row change and the user responsible

for the change.

Answer: BD

總結:

要考OCP真的不是一天兩天的功夫, 是一年的功夫, 實在要很有決心,
還好Oracle的考試算便宜 一科都是 US150美金, 不算貴的了,
之前考PMP 都要快上萬了 US405 美金, 非會員US555

最後祝大家考試順利!

=====================
2020/06/23
  雲端的oracle解決方案, 這一兩年也陸續推出,
  今天多考了一張aws SAP (解決方案架構師Profession),
  有興趣的朋友也可以參考看看!!!