Category Archives: backup/recovery

Oracle 12c -RMAN Backup Overview

March 21, 2017 Larry Catt Leave a comment

Oracle provides the Recovery Manager utility (RMAN) to perform two types of database backups: Image Backup and Backup Set. RMAN command BACKUP AS COPY command creates an image copy or bit for bit copy of the data files, archived redo log and controlfiles.

RMAN> BACKUP AS COPY DEVICE TYPE DISK DATABASE;

You can set the default backup to image copy with CONFIGURE DEVICE TYPE command

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;

Backup sets are the other type of RMAN backups, each backup set has one or more binary files called backup pieces, in proprietary format that can be restored by RMAN. You can limit the size of size of backup pieces by the MAXPIECESIZE option. Use the RMAN command BACKUP AS BACKUPSET to create backup copy of database.

RMAN> BACKUP AS BACKUPSET DATABASE.

You can set the default backup to backup set with the CONIFGURE DEVICE TYPE command.

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

You can backup the entire database with the command:

RMAN> BACKUP DATABASE;

Add PLUS ARCHIVELOGS clause will cause a log switch that allows for full media recovery to the point of starting the backup.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Incremental Backups are part of a backup set that only contains changes to the database since the last full backup. The benefit of an incremental backup is size of the backup and time required to tack the backup. Incremental Backups are only good if combined with the last full backup and any incremental backup between the last full backup.

You can create an incremental backup with the RMAN command BACKUP INCREMENTAL in three ways

Level 0 – Identical to full back

Level 1 Differential – backups all changed blocks since most recent incremental backup.

Level 1 Cumulative – backups all changed block since most recent Level 0 incremental backup

Restoring incremental backup use level 0 at start and apply level 1 cumulative or all differential.

Example of incremental Level 1:

RMAN> backup incremental level 1 cumulative database;

RMAN> backup incremental level 1 database;

Larry Catt

OCP

11g, 12c, Administration, backup/recovery, RMAN

Oracle 12c – Recovery Manager (RMAN) Utility Commands

February 14, 2017 Larry Catt Leave a comment

RMAN is the preferred method used for backup and recovery of Oracle database. This article gives a brief overview of commands used to Configure, Display Configurations, Reset to Default, and take simple backups.

The SHOW command gives current settings for RMAN utility to the associate target database.
The CLEAR command resets to RMAN defaults.
The following command allows for three backup copies of all data files and control files. the forth oldest will be considered obsolete and deleted. default is 1.
- RMAN> configure retention policy to redundancy 3;
The following ensures that backup window of recovery is at least 7 days in the past, backups will be kept to allow for this regardless of redundancy policy.
- RMAN> configure retention policy to recovery window of 7 days;
The following changes the default device type to tape, the default is disk.
- RMAN> configure default device type to sbt;
Regardless of default device type, you can perform backup to specific backup with ‘device type’ clause.
- RMAN> backup device type sbt database;
- RMAN> backup device type disk database;
RMAN can backup as an image copy or backup set.
- RMAN> configure device type disk backup type to backupset;
- RMAN> configure device type disk backup type to copy;
TAPE backups can only be backup sets.
binary compression can be used with backupsets with COMPRESSED option.
- RMAN> configure device type disk backup type to compressed backupset;
- RMAN> configure device type sbt backup type to compressed backupset;
An RMAN channel is a connection to db server process, use the configure channel command to create and change channel settings.
- RMAN> configure channel device type disk maxpiecesize 1g;
- RMAN> configure channel device type disk format /tmp/%U;
By default RMAN allocates a single disk channel for all operations, you can specify a distinct file name for a channel, but RMAN does not create a backup in the fast recovery area.
- RMAN> configure channel device type disk format ‘/u01/oradata/orcl_df%t_s%s_s%p’;
- RMAN> configure channel device type disk format ‘+dgroup1’;
You can perform parallel backups by defining multiple channels for use.
- RMAN> RUN
- {
- allocate channel u01 device type disk format ‘/u01/%u’;
- allocate channel u02 device type disk format ‘/u02/%u’;
- backup database plus archivelog;
- }
RMAN can be configured to autobackup control files and if in archivelog mode and a change occurs to controlfiles, they will be automatically backed up.
- RMAN> configure controlfile autobackup on;
- RMAN> configure controlfile autobackup off;
RMAN configuration can be reset to default values by using the CLEAR option.
- RMAN> configure default device type clear;
- RMAN> configure retention policy clear;
- RMAN> configure controlfile autobackup clear;
When RMAN optimization is enabled, it will skip files that have not changed since the last backup set. RMAN determines this by:
- Datafile must have same DBID, checkpoint SCN, creation SCN, resetlogs scn and time as one already in backup set.
- Datafile must be offline, read-only or closed.
- Archived log – must have same DBID, thread, sequence number, resetlogs scn and time.
- Backup Set – must have the same DBID, backup set record id, and stamp.
RMAN will still take a backup of retention policy requires and if TO DESTINATION is used with BACKUP RECOVERY AREA or BACKUP RECOVERY FILES.
Turn RMAN optimization on and off with:
- RMAN> configure backup optimization on;
Once optimization is enabled, backup will not be performed, if nothing has changed since the last valid backup.
Backup optimization can be overridden by the FORCE option.
- RMAN> backup database force;
- RMAN> backup archivelog all force;

Larry Catt

OCP

12c, backup/recovery, data pump

Oracle 12c – Export Data Pump

January 12, 2017 Larry Catt 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.

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 >

[oracle@linux2 ~]$

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

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>

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 ~]$

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

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 ~]$

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 ~]$

This completes simple example of EXPDP utility.

Larry Catt

OCP

12c, backup/recovery, RMAN

Oracle 12c – Recover Database with RMAN

January 11, 2017 Larry Catt 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.

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#

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

RMAN> connect target /

connected to target database: ORCL (DBID=3153769526)

RMAN>

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

RMAN> startup mount;

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

RMAN> show all;

Issue the RESTORE command.

RMAN> restore database;

Issue the RECOVER command.

RMAN> recover database;

Issue the database OPEN command.

RMAN> alter database open;

This completes the recovery of Oracle 12c database.

Larry Catt

OCP

12c, archiving, backup/recovery

Oracle 12c – In-Database archiving

October 16, 2016 Larry Catt Leave a comment

Oracle 12c supports in-database archiving to compress data that is no longer readily needed but is still kept online. This article covers the rules of in-Database archiving with a brief example.

Use In-Database archiving rules–

Allows rows to be kept in the database but invisible from applications.
Idea is the data is available for compliance but minimize impact to the performance.
Archive data can be compressed to help improve backup performance.
The column clause ROW ARCHIVAL of the table must be enabled and the ORA_ARCHIVE_STATE must be set to non-zero value.
The session parameter ROW ARCHIVAL VISIBILITY is set to ACTIVE, only rows where ORA_ARCHIVE_STATE equals zero will be displayed.
The session parameter ROW_ARCHIVAL VISIBILITY is set to ALL, all rows will be displayed.

In-Database archiving Eample.

Logon to sqlplus as sysdba.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 15 09:10:34 2016

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>

Alter session to set row archival visibility to active.

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

Session altered.

SQL>

Create table test.archival_test with following statement including ROW ARCHIVAL clause. This creates a hidden column call ORA_ARCHIVE_STATE which determines the archival status of the row.

CREATE TABLE test.archival_test (col1 NUMBER, col2 VARCHAR2( 20)) ROW ARCHIVAL;

SQL> CREATE TABLE test.archival_test (col1 NUMBER, col2 VARCHAR2( 20)) ROW ARCHIVAL;

Table created.

SQL>

Insert records into test.archival_test table.

INSERT INTO test.archival_test (col1, col2) VALUES (1, ‘Record One’);

INSERT INTO test.archival_test (col1, col2) VALUES (2, ‘Record Two’);

INSERT INTO test.archival_test (col1, col2) VALUES (3, ‘Record Three’);

INSERT INTO test.archival_test (col1, col2) VALUES (4, ‘Record Four’);

Select values in test.archival_test table to determine ora_archive_state column value.

column col1 format 9999

column col2 format a12

column ora_archive_state format a15

SQL> column col1 format 9999

column col2 format a12

column ora_archive_state format a15

SELECT col1, col2, ora_archive_state FROM test.archival_test;

SQL> SQL> SQL>

COL1 COL2 ORA_ARCHIVE_STA

—– ———— —————

1 Record One 0

2 Record Two 0

3 Record Three 0

4 Record Four 0

SQL>

Update the column ora_archive_state from 0 to 5 for record 3.

UPDATE test.archival_test SET ora_archive_state = ‘5’ WHERE col1 = 3;

SQL> UPDATE test.archival_test SET ora_archive_state = ‘5’ WHERE col1 = 3;

1 row updated.

SQL>

Perform another select of the table ora_archive_state and note that record three does not appear in result set.

SQL> SELECT col1, col2, ora_archive_state FROM test.archival_test;

COL1 COL2 ORA_ARCHIVE_STA

—– ———— —————

1 Record One 0

2 Record Two 0

4 Record Four 0

SQL>

Set the ROW ARCHIVAL VISIBILITY to all.

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SQL>

Now slect all values in archival_test and not that all values are displayed.

SELECT * FROM test.archival_test;

SQL> SELECT * FROM test.archival_test;

COL1 COL2

—– ————

1 Record One

2 Record Two

3 Record Three

4 Record Four

SQL>

This completes the example of In-Database archiving in Oracle 12c.

Larry Catt

OCP

12c, backup/recovery, data pump

Oracle 12c – Import data pump

September 6, 2016 Larry Catt Leave a comment

IMPDP utility is used to read in the contents of an EXPDP dump file. It can be run in three modes:

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 import prompt and you enter various commands.

IMPDP utility can accept the following parameters during execution:

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 Import to use a particular method to load data.Valid keyword values are: [AUTOMATIC], CONVENTIONAL, DIRECT_PATHand 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].
CONTENT – Specifies data to load. Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.
DATA_OPTIONS – Data layer option flags. Valid keywords are: DISABLE_APPEND_HINT and SKIP_CONSTRAINT_ERRORS.
DIRECTORY – Directory object to be used for dump, log and SQL files.
DUMPFILE – List of dump files to import from [expdat.dmp]. For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD – Password key for accessing encrypted data within a dump file. Not valid for network import jobs.
ENCRYPTION_PWD_PROMPT – Specifies whether to prompt for the encryption password [NO]. Terminal echo will be suppressed while standard input is read.
ESTIMATE – Calculate network job estimates. Valid keywords are: [BLOCKS] and STATISTICS.
EXCLUDE – Exclude specific object types. For example, EXCLUDE=SCHEMA:”=’HR'”.
FLASHBACK_SCN – SCN used to reset session snapshot.
FLASHBACK_TIME – Time used to find the closest corresponding SCN value.
FULL – Import everything from source [YES].
HELP – Display help messages [NO].
INCLUDE – Include specific object types. For example, INCLUDE=TABLE_DATA.
JOB_NAME – Name of import job to create.
KEEP_MASTER – Retain the master table after an import job that completes successfully [NO].
LOGFILE – Log file name [import.log].
LOGTIME – Specifies that messages displayed during import operations be timestamped. Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.
MASTER_ONLY – Import just the master table and then stop the job [NO].
METRICS – Report additional job information to the import 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.
PARTITION_OPTIONS – Specify how partitions should be transformed. Valid keywords are: DEPARTITION, MERGE and [NONE].
QUERY – Predicate clause used to import 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.
REMAP_DATAFILE – Redefine data file references in all DDL statements.
REMAP_SCHEMA – Objects from one schema are loaded into another schema.
REMAP_TABLE – Table names are remapped to another table. For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.
REMAP_TABLESPACE – Tablespace objects are remapped to another tablespace.
REUSE_DATAFILES – Tablespace will be initialized if it already exists [NO].
SCHEMAS – List of schemas to import.
SERVICE_NAME – Name of an active Service and associated resource group to constrain Oracle RAC resources.
SKIP_UNUSABLE_INDEXES – Skip indexes that were set to the Index Unusable state.
SOURCE_EDITION – Edition to be used for extracting metadata.
SQLFILE – Write all the SQL DDL to a specified file.
STATUS – Frequency (secs) job status is to be monitored where the default [0] will show new status when available.
STREAMS_CONFIGURATION – Enable the loading of Streams metadata [YES].
TABLE_EXISTS_ACTION – Action to take if imported object already exists. Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
TABLES – Identifies a list of tables to import. For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
TABLESPACES – Identifies a list of tablespaces to import.
TARGET_EDITION – Edition to be used for loading metadata.
TRANSFORM – Metadata transform to apply to applicable objects. Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE, LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, STORAGE, and TABLE_COMPRESSION_CLAUSE.
TRANSPORTABLE – Options for choosing transportable data movement. Valid keywords are: ALWAYS and [NEVER]. Only valid in NETWORK_LINK mode import operations.
TRANSPORT_DATAFILES – List of data files to be imported by transportable mode.
TRANSPORT_FULL_CHECK – Verify storage segments of all tables [NO]. Only valid in NETWORK_LINK mode import operations.
TRANSPORT_TABLESPACES – List of tablespaces from which metadata will be loaded. Only valid in NETWORK_LINK mode import operations.
VERSION – Version of objects to import. Valid keywords are: [COMPATIBLE], LATEST, or any valid database version. Only valid for NETWORK_LINK and SQLFILE.
VIEWS_AS_TABLES – Identifies one or more views to be imported as tables. For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW. Note that in network import mode, a table name may be appended to the view name.

——————————————————————————

The following commands are valid while in interactive mode.

Note: abbreviations are allowed.

CONTINUE_CLIENT – Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT – Quit client session and leave job running.
HELP – Summarize interactive commands.
KILL_JOB – Detach and delete job.
PARALLEL – Change the number of active workers for current job.
START_JOB – Start or resume current job. Valid keywords 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 keywords are: IMMEDIATE.

This article will give a brief example of IMPDP utility by importing an export of table scott.emp into a new table scott.emp2.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

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

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$

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 2016

Last Successful login time: Fri Jan 06 2016 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>

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 ~]$

Execute the command below to export the table scott.emp to file scott_emp.dmp. NOTE: you may have some errors with name of constraints from original table, but the data will be imported.

impdp scott/tiger DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir REMAP_TABLE=scott.emp:emp2

[oracle@linux2 ~]$ impdp scott/tiger DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir REMAP_TABLE=scott.emp:emp2

Import: Release 12.1.0.2.0 – Production on Wed Jan 11 10:03:13 2016

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

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

Starting “SCOTT”.”SYS_IMPORT_FULL_01″: scott/******** DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir REMAP_TABLE=scott.emp:emp2

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported “SCOTT”.”EMP2″ 8.820 KB 15 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

ORA-31684: Object type INDEX:”SCOTT”.”BT_SCOTT_EMP_JOB” already exists

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

ORA-31684: Object type CONSTRAINT:”SCOTT”.”PK_EMP” already exists

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

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

ORA-31684: Object type REF_CONSTRAINT:”SCOTT”.”FK_DEPTNO” already exists

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

ORA-31684: Object type INDEX:”SCOTT”.”BM_SCOTT_EMP_JOB” already exists

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

Job “SCOTT”.”SYS_IMPORT_FULL_01″ completed with 4 error(s) at Wed Jan 11 10:04:08 2016 elapsed 0 00:00:53

[oracle@linux2 ~]$

Now logon to SQLPLUS as the user scott and describe your new table emp2.

[oracle@linux2 ~]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 10:06:05 2016

Last Successful login time: Wed Jan 11 2016 10:03:13 -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> desc emp2

Name Null? Type

—————————————– ——– —————————-

EMPNO NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

DEPTNO NUMBER(2)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

SQL> select count(*) from emp2;

COUNT(*)

———-

SQL>

This completes a simple example of IMPDP utility in Oracle 12c.

Larry Catt

OCP

12c, backup/recovery

Oracle 12c – Block Change Tracking

August 20, 2016 Larry Catt Leave a comment

Oracle 12c provides block change tracking to increase performance of incremental backups by recording change blocks for each datafile, thus stopping RMAN from scanning each datafile to determine changes. This feature must be enabled by default it is disabled in the database. Tracking information on each changed block is stored in a file located in the directory defined by parameter DB_CREATE_FILE_DEST and is only used by level 1 backups. Additionally, the tracking must be enabled before the level 0 backup is performed in order for level 1 backups to use it. Use ALTER DATABASE command to turn on Block Change Tracking:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

You can also specify the location of the tracking file:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/linux1/rman_change_tracking.file’ REUSE;

Disable Block tracking with:

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Larry Catt

OCP

12c, backup/recovery, flashback, RMAN

Oracle 12c – Enabling Flashback Database

May 26, 2016 Larry Catt Leave a comment

In Oracle 12c Flashback Database must be configured which allow for tracking of changes for the database to revert to. This article details the steps in enabling flashback database feature.

Logon to server as the oracle software owner.

[root@orclc-db1 devadmin]# su – oracle

Last login: Mon Feb 19 18:31:43 UTC 2016 on pts/0

mylinux#

Open or mount the oracle database.

mylinux# sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 20 12:41:43 2016

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 5.1540E+10 bytes

Fixed Size 2938792 bytes

Variable Size 3.4628E+10 bytes

Database Buffers 1.6777E+10 bytes

Redo Buffers 131276800 bytes

Database mounted.

SQL>

Set the DB_FLASHBACK_RETENTION_TARGET initialization parameter to time period of recovery, default is 1440 minutes or 1 day.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=2880 scope=both;

System altered.

SQL>

Set the initialization parameter DB_RECOVERY_FILE_DEST_SIZE to a suitable size. NOTE: This is simple a test system so we area setting the recovery size to 4GB, a real system will require a larger size.

SQL> alter system set db_recovery_file_dest_size = 4g scope=both;

System altered.

Set the initialization parameter DB_RECOVERY_FILE_DEST to a suitable large enough space one disk.

SQL> alter system set db_recovery_file_dest=’ /u04/oradata/ORCL/flash_recovery_area’ scope=both;

System altered.

SQL>

Ensure that archivelog mode is enabled and enable if not.

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /opt/app/oracle/orcl_db/dbs/arch

Oldest online log sequence 1613

Current log sequence 1633

Archiving log is not currently enabled, restart the database in mount mode and enable archiving.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 549453824 bytes

Fixed Size 2926616 bytes

Variable Size 268437480 bytes

Database Buffers 272629760 bytes

Redo Buffers 5459968 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL>

Enable flashback for database with ALTER DATABASE command, before opening database.

SQL> alter database flashback on;

Database altered.

SQL>

Alter the database to open.

SQL> alter database open;

Database altered.

SQL>

This completes enabling flashback for your Oracle RDBMS.

Larry Catt

OCP

12c, Administration, backup/recovery, RMAN

Oracle 12c New Feature – RMAN

February 27, 2016 Larry Catt Leave a comment

Table Level Recovery from backup – 12c now has the ability to recover a single table from an RMAN backup with the user of RECOVER TABLE option.

RMAN Command-Line SQL – RMAN utility now allows for the execution of SQL commands against target database without using SQL key word. RMAN also now support the SQLPLUS DESCRIBE command for tables and views.

Active Database Duplication – RMAN supports the ability to clone a source database to an auxiliary database over the network. Retrieving all backup sets from source database over network and allows for use of block compression during the duplication process.

NOOPEN option for Duplicate database creation, which allows for recovered database to not be open with resetlogs before administrative tasks can be performed.

duplicate target database

to newdatabase

from active database

noopen;

SECTION SIZE option with Image Copies – RMAN now allows you to divide data files into subsections and split upon multiple recovery channels.

backup

as copy

section size 500M

database;

Incremental Backups can include the SECTION SIZE option which allows for parallel channels to be used, thus reducing the time for backup.

backup

incremental level 1

section size 500m

Datafile ‘/u01/oradata/orcl/data01.dbf’;

SNAPSHOT with the use of BACKUP mode, 12c allows for the use of VM snapshots or storage snapshot without placing database in backup mode. The snapshot requires 3 things for use:

Database is consistent at time of snapshot.
Write order is maintained for snapshot.
Snapshot stores time of snapshot.

SNAPSHOT TIME option has become part of RECOVER command which allows for recovery from OS snapshot to a consistent point.

Larry Catt

OCP

12c, backup/recovery, Multitenant, PDB, RMAN

RMAN Backup of PDB in Oracle 12c Multitenant Database

February 7, 2016 Larry Catt Leave a comment

RMAN backup of a PDB in Oracle 12c Multitenant Database can be performed from the Recovery Manager utility or EM Cloud Control. This procedure shows the steps of performing a PDB backup from RMAN utility.

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

[root@linux2 larry]# su – oracle

Last login: Thu Jan 3 11:02:25 EDT 2016 on pts/1

[oracle@linux2 ~]$

Enter the RMAN utility.

[oracle@linux2 ~]$ rman

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jan 15 12:26:22 2016

RMAN>

You can now either connect to the CDB and issue BACKUP PLUGGABLE DATABASE command or connect directly to PDB and use BACKUP DATABASE

Connect to your target CDB database with SYSBACKUP privilege.

RMAN> connect target ‘”sys/@cdb1 as sysbackup”‘

target database Password:

connected to target database: CDB1 (DBID=898165058)

RMAN>

From the CDB issue BACKUP PLUGGABLE DATABASE
- Single PDB backup

RMAN> BACKUP PLUGGABLE DATABASE PDB1;

Or multiple PDB backup

RMAN> BACKUP PLUGGABLE DATABASE PDB1, PDB2;

Additionally you can connect directly to the PDB. To do this you must connect as a common user with sysdba or sysbackup privilege, in this example we are using C##DBA.

RMAN> connect target ‘”c##dba/@pdb1 as sysdba”‘

target database Password:

connected to target database: CDB1 (DBID=898165058)

RMAN> backup database;

This completes backup of PDBs in the Oracle 12c Multitenant Database.

Larry Catt

OCP

Catt Solutions

Category Archives: backup/recovery

Oracle 12c -RMAN Backup Overview

Oracle 12c – Recovery Manager (RMAN) Utility Commands

Oracle 12c – Export Data Pump

Oracle 12c – Recover Database with RMAN

Oracle 12c – In-Database archiving

Oracle 12c – Import data pump

Oracle 12c – Block Change Tracking

Oracle 12c – Enabling Flashback Database

Oracle 12c New Feature – RMAN

RMAN Backup of PDB in Oracle 12c Multitenant Database

Oracle tips and tricks.