[心得] 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
有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
不會考指令或是錯字, 大部分都考觀念..觀念對比較重要.
網路上抓的範例
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
能夠實作練習的話, 效果會好很多, 雖然死背能有效果但容易搞亂了
網路上找的題型.
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),
有興趣的朋友也可以參考看看!!!
2020/06/23
雲端的oracle解決方案, 這一兩年也陸續推出,
今天多考了一張aws SAP (解決方案架構師Profession),
有興趣的朋友也可以參考看看!!!
