1Z0-060 - Upgrade to Oracle Database 12c
Go back to Oracle
You are connected to a pluggable database (PDB) as a common user with DBA privileges. The STATISTICS_LEVEL parameter is PDB_MODIFIABLE. You execute the following: SQL > ALTER SYSTEM SET STATISTICS_LEVEL = ALL SID = `*' SCOPE = SPFILE; Which is true about the result of this command?
The STATISTICS_LEVEL parameter is set to all whenever the multitenant container database (CDB) is restarted.
You Execute the Following command to create a password file in the database server: $ orapwd file = `+DATA/PROD/orapwprod entries = 5 ignorecase = N format = 12' Which two statements are true about the password file?
Is used by Oracle to authenticate users for remote database administration.
It supports the SYSBACKUP, SYSDG, and SYSKM system privileges.
In a recent Automatic Workload Repository (AWR) report for your database, you notice a high number of buffer busy waits. The database consists of locally managed tablespaces with free list managed segments. On further investigation, you find that buffer busy waits is caused by contention on data blocks. Which option would you consider first to decrease the wait event immediately?
Using Automatic Segment Space Management (ASSM)
In order to exploit some new storage tiers that have been provisioned by a storage administrator, the partitions of a large heap table must be moved to other tablespaces in your Oracle 12c database? Both local and global partitioned B-tree Indexes are defined on the table. A high volume of transactions access the table during the day and a medium volume of transactions access it at night and during weekends. Minimal disrupt ion to availability is required. Which three statements are true about this requirement?
The partitions can be moved online to new tablespaces.
Global indexes must be rebuilt manually after moving the partitions.
The partitions can be compressed in the new tablespaces.
Your are the DBA supporting an Oracle 11g Release 2 database and wish to move a table containing several DATE, CHAR, VARCHAR2, and NUMBER data types, and the table's indexes, to another tablespace. The table does not have a primary key and is used by an OLTP application. Which technique will move the table and indexes while maintaining the highest level of availability to the application?
Online Table Redefinition.
Identify three valid options for adding a pluggable database (PDB) to an existing multitenant container database (CDB).
Use the CREATE PLUGGABLE DATABASE statement to create a PDB using the files from the SEED.
Use the DBMS_PDB package to clone an existing PDB.
Use the DBMS_PDB package to plug an Oracle 12c non-CDB database into an existing CDB.
To enable the Database Smart Flash Cache, you configure the following parameters: DB_FLASH_CACHE_FILE = `/dev/flash_device_1 , `/dev/ flash_device_2 DB_FLASH_CACHE_SIZE=64G What is the result when you start up the database instance?
One 64G flash cache file will be used.
Your multitenant container database (CDB) contains pluggable databases (PDBs), you are connected to the HR_PDB. You execute the following command: SQL > CREATE UNDO TABLESPACE undotb01 DATAFILE `u01/oracle/rddb1/undotbs01.dbf' SIZE 60M AUTOEXTEND ON; What is the result?
It executes successfully but neither tablespace nor the data file is created.
What are three purposes of the RMAN "FROM" clause?
to support synchronization of a standby database with the primary database in a Data environment
To support PULL-based active database duplication
To support file recovery over the network in a Data Guard environment
You have installed two 64G flash devices to support the Database Smart Flash Cache feature on your database server that is running on Oracle Linux. You have set the DB_SMART_FLASH_FILE parameter: DB_FLASH_CACHE_FILE= `/dev/flash_device_1 `,' /dev/ flash_device_2 How should the DB_FLASH_CACHE_SIZE be configured to use both devices?
Set DB_FLASH_CACHE_ZISE = 64G, 64G
You need to create a table for a banking application. One of the columns in the table has the following requirements: 1) You want a column in the table to store the duration of the credit period. 2) The data in the column should be stored in a format such that it can be easily added and subtracted with date data type without using conversion functions. 3) The maximum period of the credit provision in the application is 30 days. 4) The interest has to be calculated for the number of days an individual has taken a credit for. Which data type would you use for such a column in the table?
INTERVAL DAY TO SECOND
You are administering a database and you receive a requirement to apply the following restrictions: 1. A connection must be terminated after four unsuccessful login attempts by user. 2. A user should not be able to create more than four simultaneous sessions. 3. User session must be terminated after 15 minutes of inactivity. 4. Users must be prompted to change their passwords every 15 days. How would you accomplish these requirements?
by granting a secure application role to the users
In your multitenant container database (CDB) with two pluggable database (PDBs). You want to create a new PDB by using SQL Developer. Which statement is true?
The CDB must be open.
In your multitenant container database (CDB) containing pluggable database (PDBs), you granted the CREATE TABLE privilege to the common user C # # A_ADMIN in root and all PDBs. You execute the following command from the root container: SQL > REVOKE create table FROM C # # A_ADMIN; What is the result?
It executes successfully and the CREATE TABLE privilege is revoked from C # # A_ADMIN in root only.
Examine the following command: ALTER SYSTEM SET enable_ddl_logging=FALSE; Which statement is true?
None of the data definition language (DDL) statements are logged in the trace file.
Which statement is true concerning dropping a pluggable database (PDB)?
The PDB must be in mount state.
You upgraded from a previous Oracle database version to Oracle Database version to Oracle Database 12c. Your database supports a mixed workload. During the day, lots of insert, update, and delete operations are performed. At night, Extract, Transform, Load (ETL) and batch reporting jobs are run. The ETL jobs perform certain database operations using two or more concurrent sessions. After the upgrade, you notice that the performance of ETL jobs has degraded. To ascertain the cause of performance degradation, you want to collect basic statistics such as the level of parallelism, total database time, and the number of I/O requests for the ETL jobs. How do you accomplish this?
Enable real-time database operation monitoring using the DBMS_SQL_MONITOR.BEGIN_OPERATION function, and then use the DBMS_SQL_MONITOR.REPORT_SQL_MONITOR function to view the required information.
You support Oracle Database 12c Oracle Database 11g, and Oracle Database log on the same server. All databases of all versions use Automatic Storage Management (ASM). Which three statements are true about the ASM disk group compatibility attributes that are set for a disk group?
You notice a high number of waits for the db file scattered read and db file sequential read events in the recent Automatic Database Diagnostic Monitor (ADDM) report. After further investigation, you find that queries are performing too many full table scans and indexes are not being used even though the filter columns are indexed. Identify three possible reasons for this.
Missing or stale histogram statistics
High clustering factor for the indexes
High value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter
Your database has the SRV1 service configured for an application that runs on middle-tier application server. The application has multiple modules. You enable tracing at the service level by executing the following command: SQL > exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (`SRV1'); The possible outcome and actions to aggregate the trace files are as follows: 1. The command fails because a module name is not specified. 2. A trace file is created for each session that is running the SRV1 service. 3. An aggregated trace file is created for all the sessions that are running the SRV1 service. 4. The trace files may be aggregated by using the trcess utility. 5. The trace files be aggregated by using the tkprof utility. Identify the correct outcome and the step to aggregate by using tkprof utility?
2 and 4
You are administering a database stored in Automatic Storage management (ASM). The files are stored in the DATA disk group. You execute the following command: SQL > ALTER DISKGROUP data ADD ALIAS `+data/prod/myfile.dbf' FOR `+data.231.45678'; What is the result?
The file `+data.231.54769' remains in the same location and a synonym 'myfile.dbf' is created.
Which three are true about the large pool for an Oracle database instance that supports shared server connections?
Allocates memory for RMAN backup and restore operations
Allocates memory for shared and private SQL areas
Contains a cursor area for storing runtime information about cursors
You are about to plug a multi-terabyte non-CDB into an existing multitenant container database (CDB) as a pluggable database (PDB). The characteristics of the non-CDB are as follows: - Version:Oracle Database 12c Releases 1 64-bit - Character set: WE8ISO8859P15 - National character set: AL16UTF16 - O/S: Oracle Linux6 64-bit The characteristics of the CDB are as follows: - Version: Oracle Database 12c Release 1 64-bit - Character set: AL32UTF8 - O/S:OracleLinux 6 64-bit Which technique should you use to minimize down time while plugging this non-CDB into the CDB?
The DBMS_PDB package
Examine the following steps of privilege analysis for checking and revoking excessive, unused privileges granted to users: 1. Create a policy to capture the privilege used by a user for privilege analysis. 2. Generate a report with the data captured for a specified privilege capture. 3. Start analyzing the data captured by the policy. 4. Revoke the unused privileges. 5. Compare the used and unused privileges' lists. 6. Stop analyzing the data. Identify the correct sequence of steps.
1, 3, 6, 2, 5, 4
An application accesses a small lookup table frequently. You notice that the required data blocks are getting aged out of the default buffer cache. How would you guarantee that the blocks for the table never age out?
Configure the KEEP buffer pool and alter the table with the corresponding storage clause.
Which three statements are true about Automatic Workload Repository (AWR)?
The AWR data is stored in memory and in the database.
The snapshots collected by AWR are used by the self-tuning components in the database
AWR contains system wide tracing and logging information.
Which three activities are supported by the Data Recovery Advisor?
Advising on block checksum failures
Advising on inaccessible control files
Advising on invalid block header field values
You administer an online transaction processing (OLTP) system whose database is stored in Automatic Storage Management (ASM) and whose disk group use normal redundancy. One of the ASM disks goes offline, and is then dropped because it was not brought online before DISK_REPAIR_TIME elapsed. When the disk is replaced and added back to the disk group, the ensuing rebalance operation is too slow. Which two recommendations should you make to speed up the rebalance operation if this type of failure happens again?
Increase the value of the ASM_POWER_LIMIT parameter.
Specify the statement that adds the disk back to the disk group.
You execute the following commands to audit database activities: SQL > ALTER SYSTEM SET AUDIT_TRIAL=DB, EXTENDED SCOPE=SPFILE; SQL > AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY JOHN By SESSION WHENEVER SUCCESSFUL; Which statement is true about the audit record that generated when auditing after instance restarts?
One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command on a table, and contains the SQL text for the SQL Statements.
Which two statements are true when row archival management is enabled?
The ORA_ARCHIVE_STATE column visibility is controlled by the ROW ARCHIVAL VISIBILITY session parameter.
The ORA_ARCHIVE_STATE column is updated manually or by a program that could reference activity tracking columns, to indicate that a row is no longer considered active.
Flashback is enabled for your multitenant container database (CDB), which contains two pluggable database (PDBs). A local user was accidently dropped from one of the PDBs. You want to flash back the PDB to the time before the local user was dropped. You connect to the CDB and execute the following commands: SQL > SHUTDOWN IMMEDIATE SQL > STARTUP MOUNT SQL > FLASHBACK DATABASE to TIME "TO_DATE (`08/20/12' , `MM/DD/YY')"; Examine following commands: 1. ALTER PLUGGABLE DATABASE ALL OPEN; 2. ALTER DATABASE OPEN; 3. ALTER DATABASE OPEN RESETLOGS; Which command or commands should you execute next to allow updates to the flashback back schema?
3 and 1
You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference. Which method or feature should you use?
AWR Compare Period report
Which three functions are performed by the SQL Tuning Advisor?
Building and implementing SQL profiles
Checking query objects for missing and stale statistics
Recommending the restructuring of SQL queries that are using bad plans
In which two scenarios do you use SQL* Loader to load data?
Load data into multiple tables during the same load statement.
Generate unique sequential key values in specified columns.
Which two statements are true concerning the Resource Manager plans for individual pluggable databases (PDB plans) in a multitenant container database (CDB)?
If no PDB plan is enabled for a pluggable database, then all sessions for that PDB are treated to an equal degree of the resource share of that PDB.
If no PDB plan is enabled for a pluggable database, then the PDB share in the CDB plan is dynamically calculated.
If a PDB plan is enabled for a pluggable database, then resources are allocated to consumer groups based on the shares provided to the PDB in the CDB plan and the shares provided to the consumer groups in the PDB plan.
You have installed two 64G flash devices to support the Database Smart Flash Cache feature on your database server that is running on Oracle Linux. You have set the DB_SMART_FLASH_FILE parameter: DB_FLASH_CACHE_FILE= /dev/flash_device_1 ,` /dev/flash_device_2` How should the DB_FLASH_CACHE_SIZE be configured to use both devices?
Set DB_FLASH_CACHE_ZISE = 64G, 64G
You must track all transactions that modify certain tables in the sales schema for at least three years. Automatic undo management is enabled for the database with a retention of one day. Which two must you do to track the transactions?
Create a Flashback Data Archive in any suitable tablespace.
Enable Flashback Data Archiving for the tables that require tracking.
Examine this command: SQL > exec DBMS_STATS.SET_TABLE_PREFS (`SH', `CUSTOMERS', `PUBLISH', `false'); Which three statements are true about the effect of this command?
Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time.
Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending statistics.
Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending statistics.
You want to capture column group usage and gather extended statistics for better cardinality estimates for the CUSTOMERS table in the SH schema. Examine the following steps: 1. Issue the SELECT DBMS_STATS.CREATE_EXTENDED_STATS (`SH', `CUSTOMERS') FROM dual statement. 2. Execute the DBMS_STATS.SEED_COL_USAGE (null, `SH', 500) procedure. 3. Execute the required queries on the CUSTOMERS table. 4. Issue the SELECT DBMS_STATS.REPORT_COL_USAGE (`SH', `CUSTOMERS') FROM dual statement. Identify the correct sequence of steps.
2, 3, 4, 1
What are two benefits of installing Grid Infrastructure software for a stand-alone server before installing and creating an Oracle database?
Automatically registers the database with Oracle Restart.
Enables the Installation of Grid Infrastructure files on block or raw devices.
Which statement is true about Oracle Net Listener?
Service registration with the listener is performed by the process monitor (PMON) process of each database instance.
You wish to enable an audit policy for all database users, except SYS, SYSTEM, and SCOTT. You issue the following statements: SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SYS; SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SYSTEM; SQL> AUDIT POLICY ORA_DATABASE_PARAMETER EXCEPT SCOTT; For which database users is the audit policy now active?
All users except SCOTT
A new report process containing a complex query is written, with high impact on the database. You want to collect basic statistics about query, such as the level of parallelism, total database time, and the number of I/O requests. For the database instance STATISTICS_LEVEL, the initialization parameter is set to TYPICAL and the CONTROL_MANAGEMENT_PACK_ACCESS parameter is set to DIAGNOSTIC+TUNING. What should you do to accomplish this task?
Create a database operation, execute the query, and use the DBMS_SQL_MONITOR.REPORT_SQL_MONITOR function to view the report.
Which two statements are true about the use of the procedures listed in the v$sysaux_occupants.move_procedure column?
The procedure may be used for some components to relocate component data from the SYSAUX tablespace to another tablespace.
All the components may be moved from the SYSAUX tablespace.
A senior DBA asked you to execute the following command to improve performance: SQL> ALTER TABLE subscribe log STORAGE (BUFFER_POOL recycle); You checked the data in the SUBSCRIBE_LOG table and found that it is a large table containing one million rows. What could be a reason for this recommendation?
The data blocks in the SUBSCRIBE_LOG table are rarely accessed.
To enable the Database Smart Flash Cache, you configure the following parameters: DB_FLASH_CACHE_FILE = `/dev/flash_device_1' , `/dev/flash_device_2' DB_FLASH_CACHE_SIZE=64G What is the result when you start up the database instance?
One 64G flash cache file will be used.
Which two statements are true about variable extent size support for large ASM files?
The metadata used to track extents in SGA is reduced.
An ASM Instance automatically allocates an appropriate extent size.
Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?
By default, EM express is available for a database after database creation.
You are administering a database stored in Automatic Storage management (ASM). The files are stored in the DATA disk group. You execute the following command: SQL > ALTER DISKGROUP data ADD ALIAS +data/prod/myfile.dbf` FOR `+data/prod/myfile.dbf' What is the result?
The file `+data.231.54769' remains in the same location and a synonym 'myfile.dbf' is created.
You are planning the creation of a new multitenant container database (CDB) and want to store the ROOT and SEED container data files in separate directories. You plan to create the database using SQL statements. Which three techniques can you use to achieve this?
Specify the PDB_FILE_NAME_CONVERT initialization parameter.
Specify the DB_FILE_NAMECONVERT initialization parameter.
Specify all files in the CREATE DATABASE statement without using Oracle managed Files (OMF).