Monthly Archives: January 2017

12c, audit

Oracle 12c – Create and enable audit policies

Leave a comment

Oracle 12c has implemented the unified auditing which is based off of system and user level policies.   This article describes the creation and enabling audit policies.

 

  1. Logon to oracle server and logon to sqlplus as sysdba.

Verify that unified auditing is enabled with command: select value from v$option where parameter=’Unified Auditing’;

 

SQL> select value from v$option where parameter=’Unified Auditing’;

 

VALUE

—————————————————————-

TRUE

 

SQL>

 

  1. Create audit policy test_user, which will audit session creation and all RDBMS actions.

 

create audit policy test_user privileges create session

ACTIONS all;

 

  1. Enable the audit policy for the user test with the command: This enabling will only be for actions performed by user test.

 

audit policy test_user by test;

 

SQL> audit policy test_user by test;

 

Audit succeeded.

 

SQL>

 

  1. View the active policies.

 

SQL> select policy_name from audit_unified_enabled_policies;

 

POLICY_NAME

——————————————————————————–

TEST_USER

 

SQL>

 

  1. This completes the creation and enablement of Unified Audit policies.

 

Larry Catt

OCP

12c, CDB, Multitenant, Users

Creating Common Users in Oracle Multitenant Databases

Leave a comment

In Oracle 12c multitenant databases, a user defined in the CDB is considered a Common user.    Common users with correct privileges can access all PDBs in the CDB.   This article looks at the creation of Common users and their use in the multitenant databases.

 

  1. Logon to your server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Oct 26 10:07:02 EDT 2016 on pts/2

[oracle@linux2 ~]$

 

  1. Logon to oracle as the sys user.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 26 14:30:43 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL>

 

  1. Create the user cdb_dba

 

SQL> create user c##cdb_dba identified by password;

 

User created.

 

  1. Just as in a traditional Oracle database a user has no privileges until they are granted and what a user can do is limited by these privileges. But an additional clause exists in CDB and PDB which determines where a common user can use their privileges, this clause is CONTAINER.

 

Larry Catt

OCP

12c, backup/recovery, data pump

Oracle 12c – Export Data Pump

Leave a comment

The Oracle EXPDP utility moves data outside of the database.   The utility can be executed in a command line fashion, by a parameter file, or interactive-command mode.

  • Command-line – Enables you to specify most of the export parameters
  • Parameter File – Allows you to specify command line parameters in a PARFILE
  • Interactive-Command – Displays an export prompt and you enter various commands.

The EXPDP utility can create up to 4 external file types.   This file types include the actual data captured by EXPDP utility, logs of current progress, SQL statements and actual complete data files depending on the parameters passed to the utility.   The four possible output files of an EXPDP operation are:

  • Dump File – Contains data and metadata being moved.
  • Log File – Records log of operation.
  • SQL files – Records output of SQLFILE operation.
  • Data files – Used with transportable import and displays files using DATA_FILES

The EXPDP utility can be run in various modes to extract data at different levels of the database.     This includes exporting the entire database, a single user contents, a single and/or multiple tables, and various types of tablespaces.    The various types of EXPDP modes are:

  • Full – Entire database is unloaded. Need DATAPUMP_EXP_FULL_DATABASE role. FULL parameter
  • SCHEMA – Default export mode. If you have DATAPUMP_EXP_FULL_DATABASE role you give list of schemas if not only your own.   SCHEMAS parameter.
  • TABLE – Only specific tables, partitions and dependent objects are unloaded. TABLES parameter.
  • TABLESPACES – All contents of a tablespaces are unloaded. If you have full privilege you get everything if not only your own objects.   TABLESPACES parameter.
  • TRANSPORTABLE TABLESPACE – Only metadata of tables and associated objects are unloaded. Data is moved in another operation.  TRANSPORT_TABLESPACES parameter.

Finally EXPDP utility can except the following parameters to define and control  its operations.    The acceptable parameters are:

  • ABORT_STEP – Stop the job after it is initialized or at the indicated object. Valid values are -1 or N where N is zero or greater. N corresponds to the object’s process order number in the master table.
  • ACCESS_METHOD – Instructs Export to use a particular method to unload data. Valid keyword values are: [AUTOMATIC], DIRECT_PATH and EXTERNAL_TABLE.
  • ATTACH – Attach to an existing job. For example, ATTACH=job_name.
  • CLUSTER – Utilize cluster resources and distribute workers across the Oracle RAC [YES].
  • COMPRESSION – Reduce the size of a dump file. Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
  • COMPRESSION_ALGORITHM – Specify the compression algorithm that should be used. Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.
  • CONTENT – Specifies data to unload. Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.
  • DATA_OPTIONS – Data layer option flags. Valid keyword values are: XML_CLOBS.
  • DIRECTORY – Directory object to be used for dump and log files.
  • DUMPFILE – Specify list of destination dump file names [expdat.dmp]. For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
  • ENCRYPTION – Encrypt part or all of a dump file. Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.
  • ENCRYPTION_ALGORITHM – Specify how encryption should be done. Valid keyword values are: [AES128], AES192 and AES256.
  • ENCRYPTION_MODE – Method of generating encryption key. Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].
  • ENCRYPTION_PASSWORD – Password key for creating encrypted data within a dump file.
  • ENCRYPTION_PWD_PROMPT – Specifies whether to prompt for the encryption password [NO]. Terminal echo will be suppressed while standard input is read.
  • ESTIMATE – Calculate job estimates. Valid keyword values are: [BLOCKS] and STATISTICS.
  • ESTIMATE_ONLY – Calculate job estimates without performing the export [NO].
  • EXCLUDE – Exclude specific object types. For example, EXCLUDE=SCHEMA:”=’HR'”.
  • FILESIZE – Specify the size of each dump file in units of bytes.
  • FLASHBACK_SCN – SCN used to reset session snapshot.
  • FLASHBACK_TIME – Time used to find the closest corresponding SCN value.
  • FULL – Export entire database [NO].
  • HELP – Display Help messages [NO].
  • INCLUDE – Include specific object types. For example, INCLUDE=TABLE_DATA.
  • JOB_NAME – Name of export job to create.
  • KEEP_MASTER – Retain the master table after an export job that completes successfully [NO].
  • LOGFILE – Specify log file name [export.log].
  • LOGTIME – Specifies that messages displayed during export operations be timestamped. Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.
  • METRICS – Report additional job information to the export log file [NO].
  • NETWORK_LINK – Name of remote database link to the source system.
  • NOLOGFILE – Do not write log file [NO].
  • PARALLEL – Change the number of active workers for current job.
  • PARFILE – Specify parameter file name.
  • QUERY – Predicate clause used to export a subset of a table. For example, QUERY=employees:”WHERE department_id > 10″.
  • REMAP_DATA – Specify a data conversion function. For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
  • REUSE_DUMPFILES – Overwrite destination dump file if it exists [NO].
  • SAMPLE – Percentage of data to be exported.
  • SCHEMAS – List of schemas to export [login schema].
  • SERVICE_NAME – Name of an active Service and associated resource group to constrain Oracle RAC resources.
  • SOURCE_EDITION – Edition to be used for extracting metadata.
  • STATUS – Frequency (secs) job status is to be monitored where the default [0] will show new status when available.
  • TABLES – Identifies a list of tables to export. For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
  • TABLESPACES – Identifies a list of tablespaces to export.
  • TRANSPORTABLE – Specify whether transportable method can be used. Valid keyword values are: ALWAYS and [NEVER].
  • TRANSPORT_FULL_CHECK – Verify storage segments of all tables [NO].
  • TRANSPORT_TABLESPACES – List of tablespaces from which metadata will be unloaded.
  • VERSION – Version of objects to export. Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.
  • VIEWS_AS_TABLES – Identifies one or more views to be exported as tables. For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.

—————–

 

The following commands are valid while in interactive mode.

Note: abbreviations are allowed.

 

  • ADD_FILE – Add dumpfile to dumpfile set.
  • CONTINUE_CLIENT – Return to logging mode. Job will be restarted if idle.
  • EXIT_CLIENT – Quit client session and leave job running.
  • FILESIZE – Default filesize (bytes) for subsequent ADD_FILE commands.
  • HELP – Summarize interactive commands.
  • KILL_JOB – Detach and delete job.
  • PARALLEL – Change the number of active workers for current job.
  • REUSE_DUMPFILES – Overwrite destination dump file if it exists [NO].
  • START_JOB – Start or resume current job. Valid keyword values are: SKIP_CURRENT.
  • STATUS – Frequency (secs) job status is to be monitored where the default [0] will show new status when available.
  • STOP_JOB – Orderly shutdown of job execution and exits the client. Valid keyword values are: IMMEDIATE.

 

 

 

The following is a simple example of the EXPDP utility in exporting a single table.

 

  1. Logon to your server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Tue Jan 10 09:26:15 EST 2017 on pts/2

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$

 

  1. Logon to your database as the user scott default password tiger.

 

[oracle@linux2 ~]$ sqlplus scott/tiger

 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 08:56:36 2017

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Last Successful login time: Fri Jan 06 2017 11:01:31 -05:00

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL> create directory dmpdir as ‘/home/oracle’;

 

Directory created.

 

SQL>

 

 

  1. Log out of SQLPLUS and change directory to oracle software owners home directory.

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 ~]$ cd

[oracle@linux2 ~]$

 

  1. Execute the command below to export the table scott.emp to file scott_emp.dmp.

 

expdp scott/tiger DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir tables=emp

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 ~]$ cd

[oracle@linux2 ~]$ expdp scott/tiger DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir tables=emp

 

Export: Release 12.1.0.2.0 – Production on Wed Jan 11 09:04:43 2017

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

Starting “SCOTT”.”SYS_EXPORT_TABLE_01″:  scott/******** DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir tables=emp

Estimate in progress using BLOCKS method…

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

. . exported “SCOTT”.”EMP”                               8.820 KB      15 rows

Master table “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /home/oracle/scott_emp.dmp

Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at Wed Jan 11 09:06:01 2017 elapsed 0 00:01:16

 

[oracle@linux2 ~]$

 

  1. Now the table scott_emp.dmp contains all the data and metadata from scott.emp table.

 

[oracle@linux2 ~]$ ls -l scott*

-rw-r—–. 1 oracle dba 204800 Jan 11 09:06 scott_emp.dmp

[oracle@linux2 ~]$

 

  1. This completes simple example of EXPDP utility.

 

 

Larry Catt

OCP

12c, backup/recovery, RMAN

Oracle 12c – Recover Database with RMAN

Leave a comment

 

This article gives the steps in performing a RMAN recovery of a database in 12c.    This procedure was performed on a OEL 7.2 and assumes that a full backup has already successfully completed.

 

 

  1. Logon to your database server as the Oracle software owner.

 

mylinux# su – oracle

Password:

Last login: Sun Sep 18 13:45:52 UTC 2016 on pts/1

mylinux# 

 

  1. Start RMAN and connect to target database.

 

mylinux# rman

Recovery Manager: Release 12.1.0.2.0 – Production on Mon Sep 19 13:46:06 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: ORCL (DBID=3153769526)

RMAN>

 

  1. Issue a startup mount command, do not open the database.

 

RMAN> startup mount;

 

  1. Use the SHOW command to determine channels to use, or the CONFIGURE command to create channel.

 

RMAN> show all;

 

  1. Issue the RESTORE command.

 

RMAN> restore database;

 

  1. Issue the RECOVER command.

 

RMAN> recover database;

 

  1. Issue the database OPEN command.

 

RMAN> alter database open;

 

  1. This completes the recovery of Oracle 12c database.

Larry Catt

OCP

12c

Oracle 12c – Perform post upgrade tasks

Leave a comment

Oracle recommended that the following tasks be performed after the upgrade of Oracle RDBMS to 12c.

 

 

  1. Backup the database – make a full backup of the system after upgrade.
  2. Execute postupgrade_fixups.sql – The Database Upgrade Assistance BDUA executes this script as a normal upgrade process.  Execute this script anytime or after manual upgrade process.  This script generates three types of upgrade info for administrator.
    1. General Warning.
    2. Errors
  3. Gather Fixed Objects statistics – Execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS post upgrade and after the database has been running in normal operation for a few days. This can improve overall performance of the database.
  4. Reset Passwords to Enforce Case-Sensitivity – Execute DBMS_VERIFIER.EXPIRE_ACCOUNTS_WITHOUT_LATEST_VERIFIER procedure to force users which are not using the latest password restrictions to reset password on next logon.
  5. Set Threshold Values for Tablespace Alerts – After upgrade tablespace alerts are shutoff. Id tablespaces which need to have alerts set and set them.
  6. Migrate from rollback segments to Automatic Undo Mode – If migrated database was before 11g, you were using rollback segments, you must migrate database to Automatic Undo to take advantage of increased performance.
  7. Migrate Tables from LONG to LOB – LOB of type (BFILE,BLOB, CLOB, NCLOB) have a lot of advantage over LONG. Use ALTER TABLE command to convert LONG to CLOB and LONG RAW to BLOB.

 

 

Larry Catt

OCP

12c, Administration

Oracle 12c – Use the Redo Logfile Size Advisor

Leave a comment

Sizing of the redo logs has a large impact on database performance because the redo log size has a heavy impact on the behavior of DBW and ARCH processes.  Generally, large redo logs redo provide better performance by decreasing the amount of check points.   However, the parameter FAST_START_MTTR_TARGET is used to limit the amount of time required to recover the database and more frequent checkpoints make for a shorter recovery time, thus it is a balance between recovery time and performance.

 

V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE shows the optimal size of the redo log file.  This info is also available in Redo Log Group page of OEM.  As a general rule, redo logs should switch once every 20 minutes.  NOTE:  the initialization parameter FAST_START_MTTR_TARGET must be set to have this column populated.

 

SQL> select optimal_logfile_size from v$instance_recovery;

 

OPTIMAL_LOGFILE_SIZE

——————–

               41425

 

SQL>

 

 

Larry Catt

OCP