Which four actions are possible during an Online Data file Move operation? (Choose four.)
A C E F
Explanation:
- You can now move On line Datafile without hove to stop Monoged Recovery and manually copy and rename Files. This
can even be used to move Datafiles from or to ASM.
- New in Oracle Database 12c: FROM METAUNK. Physical Standby Database is in Active Data Guard Mode (opened READ
ONLY and Managed Recovery is running):
It is now possible to online move a Datafile while Managed Recovery is running, ie. the Physical Standby Database is in
Active Data Guard Mode. You can use this Command to move the
Datafile
- A flashback operation does not relocate a moved data file to its previous location. If you move a data file online from one
location to another and later flash back the database to a point in time
before the move, then the Data file remains in the new location, but the contents of the Data file ore changed to the contents
at the time specified in the flashback. Oracle0 Database
Administrator's Guide 12c Release 1 (12.1)
You ran this command on a source database:
$> expdp hr/hr DIRECTORY=dumpdir DUMPFILE=emp1.dmp VIEWS_AS_TABLES=emp_dept
On the target database, you run this command:
$> impdp hr/hr DIRECTORY=dumpdir DUMPFILE=emp1.dmp VIEWS_AS_TABLES=emp_dept
Which two statements are true? (Choose two.)
D E
Automatic Shared Memory Management is enabled for your database instance. You notice that there are SQL statements
performing poorly because of repeated parsing activity.
Which action generates recommendations to overcome the performance issues?
B
Your database is configured in ARCHIVELOG mode, and a daily full database backup is taken by using RMAN. Control file
autobackup is configured.
Loss of which three database files can lead to an incomplete recovery? (Choose three.)
A E F
You plan to install the Oracle Database 12c software. You want to use Oracle Automatic Storage Management (ASM) for
storage and Oracle Restart for managing components. Oracle Grid Infrastructure for a Standalone Server is already installed
on the server.
Which three statements must be true for successful installation of the Oracle Database 12c software? (Choose three.)
B C E
Examine the following commands for redefining a table with Virtual Private Database (VPD) policies:
Which two statements are true about redefining the table? (Choose two.)
B C
Explanation:
C (not D): CONS_VPD_AUTO
Used to indicate to copy VPD policies automatically
* DBMS_RLS.ADD_POLICY
/ The DBMS_RLS package contains the fine-grained access control administrative interface, which is used to implement
Virtual Private Database (VPD).DBMS_RLS is available with the Enterprise Edition only.
Note:
* CONS_USE_PK and CONS_USE_ROWID are constants used as input to the "options_flag" parameter in both the
START_REDEF_TABLE Procedure and CAN_REDEF_TABLE Procedure.CONS_USE_ROWID is used to indicate that the
redefinition should be done using rowids while CONS_USE_PK implies that the redefinition should be done using primary
keys or pseudoprimary keys (which are unique keys with all component columns having NOT NULL constraints). *
DBMS_REDEFINITION.START_REDEF_TABLE
To achieve online redefinition, incrementally maintainable local materialized views are used. These logs keep track of the
changes to the master tables and are used by the materialized views during refresh synchronization.
* START_REDEF_TABLE Procedure
Prior to calling this procedure, you must manually create an empty interim table (in the same schema as the table to be
redefined) with the desired attributes of the post-redefinition table, and then call this procedure to initiate the redefinition.
Examine the memory-related parameters set in the SPFILE of an Oracle database:
Which statement is true?
B
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.
B
Explanation:
Step 1 (2). Seed column usage
Oracle must observe a representative workload, in order to determine the appropriate column groups. Using the new
procedure DBMS_STATS.SEED_COL_USAGE, you tell Oracle how long it should observe the workload.
Step 2: (3) You don't need to execute all of the queries in your work during this window. You can simply run explain plan for
some of your longer running queries to ensure column group information is recorded for these queries.
Step 3. (1) Create the column groups
At this point you can get Oracle to automatically create the column groups for each of the tables based on the usage
information captured during the monitoring window. You simply have to call the
DBMS_STATS.CREATE_EXTENDED_STATS function for each table.This function requires just two arguments, the schema
name and the table name. From then on, statistics will be maintained for each column group whenever statistics are
gathered on the table.
Note:
* DBMS_STATS.REPORT_COL_USAGE reports column usage information and records all the SQL operations the
database has processed for a given object.
* The Oracle SQL optimizer has always been ignorant of the implied relationships between data columns within the same
table. While the optimizer has traditionally analyzed the distribution ofvalues within a column, he does not collect value-
based relationships between columns.
* Creating extended statisticsHere are the steps to create extended statistics for related table columns
withdbms_stats.created_extended_stats:
1 - The first step is to create column histograms for the related columns.2 Next, we run dbms_stats.create_extended_stats
to relate the columns together. Unlike a traditional procedure that is invoked via an execute (exec) statement, Oracle
extended statistics are created via a select statement.
Your database is in ARCHIVELOG mode and you want to automate the backup scheduling for your database.
Which two tools or utilities would you use to achieve this? (Choose two.)
B D
In your database, USERS is the default permanent tablespace.
Examine the commands and their outcome:
You plan to execute the commands:
Which two statements are true? (Choose two.)
B E
You use a recovery catalog for maintaining your database backups.
You execute the following command:
$rman TARGET / CATALOG rman / cat@catdb
RMAN > BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
Which two statements are true? (Choose two.)
B D
Explanation:
B (not C): You can validate that all database files and archived redo logs can be backed up by running a command as
follows:
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
This form of the command would check for physical corruption. To check for logical corruption,
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
D: You can use the VALIDATE keyword of the BACKUP command to do the following:
Check datafiles for physical and logical corruption
Confirm that all database files exist and are in the correct locations.
Note:
You can use the VALIDATE option of the BACKUP command to verify that database files exist and are in the correct
locations (D), and have no physical or logical corruptions that would prevent RMAN from creating backups of them. When
performing a BACKUP...VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup.
It does not, however, actually produce any backup sets or image copies (Not A, not E).
Examine the current value for the following parameters in your database instance:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 700M
DB_8K_CACHE_SIZE = 124M LOG_BUFFER = 200M
You issue the following command to increase the value of DB_8K_CACHE_SIZE:
SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE=140M;
Which statement is true?
D
Explanation:
* The SGA_TARGET parameter can be dynamically increased up to the value specified for the SGA_MAX_SIZE parameter,
and it can also be reduced.
* Example:
For example, suppose you have an environment with the following configuration:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this example, the value of SGA_TARGET can be resized up to 1024M and can also be reduced until one or more of the
automatically sized components reaches its minimum size. The exact value depends on environmental factors such as the
number of CPUs on the system. However, the value of DB_8K_CACHE_SIZE remains fixed at all times at 128M
* DB_8K_CACHE_SIZE
Size of cache for 8K buffers
* For example, consider this configuration:
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this example, increasing DB_8K_CACHE_SIZE by 16 M to 144M means that the 16M is taken away from the
automatically sized components. Likewise, reducing DB_8K_CACHE_SIZE by 16M to 112M means that the 16M is given to
the automatically sized components.
Examine these steps:
CONNECT / AS SYSDBA
CREATE DIRECTORY dumpdir AS '/u01/app/'; Directory created.
GRANT READ, WRITE ON DIRECTORY dumpdir TO PUBLIC; Grant succeeded.
CREATE USER TEST IDENTIFIED BY test; User created.
GRANT CREATE SESSION, RESOURCE, UNLIMITED TABLESPACE TO test; Grant succeeded.
CONN test/test
Connected.
CREATE TABLE test_employees (id NUMBER(3), name VARCHAR2(20), salary NUMBER(7)); Table created.
SQL> CREATE SYNONYM emp FOR test_employees;
Synonym created.
Now examine this command:
$ expdp test/test DIRECTORY=dumpdir DUMPFILE=test_emp.dmp LOGFILE=test.log TABLES=emp CONTENT=data_only
query='EMP:"WHERE salary=12000"'
What is true about the execution of this command?
C
Examine the following query output:
You issue the following command to import tables into the hr schema:
$ > impdp hr/hr directory = dumpdir dumpfile = hr_new.dmp schemas=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING: Y
Which statement is true?
C
Explanation:
Oracle Data Pump disable redo logging when loading data into tables and when creating indexes.
The new TRANSFORM option introduced in data pumps import provides the flexibility to turn off the redo generation for the
objects during the course of import. The Master Table is used to track the detailed progress information of a Data Pump job.
The Master Table is created in the schema of the current user running the Pump Dump export or import, and it keeps tracks
of lots of detailed information.
Examine the command:
SQL> CONNECT hr/hr@orcl
Which two configurations allow this command to execute successfully? (Choose two.)
B C