Blog

Oracle RMAN Database Incremental Backup Script

This is the third post/script in this series, the first script was Oracle RMAN Database Full Hot Backup Script

The second script was Oracle RMAN Database Full Cold Backup Script

The following script will incrementally backup the database while it is open (although it may be used while the database is in the mount mode also).


#*****************************************************************************
# $Header: abc_rman_incr_hot_bkp.sh *
# **************************************************************************************
# * Author: Ahmed Abdel Fattah *
# **************************************************************************************
# * DESCRIPTION: Take rman incremental hot backup(incremental level 1 ) *
# * PLATFORM: Linux/Solaris/HP-UX/AIX *
# **************************************************************************************

#!/bin/bash

# Check the login user to be “oracle”
# ————————————
usr=`id |cut -d”(” -f2 | cut -d “)” -f1`

if [ $usr != “oracle” ]
then
echo “You should login as oracle user”
exit 1
fi

# Check that ORACLE_HOME was set
# ——————————
# oh=$ORACLE_HOME
# if [ -z $oh ]
# then
# echo “You should set the oracle environment”
# exit 1
# fi

if [ $# -lt 1 ] ; then #not given enough parameters to script
cat <<USAGEINFO

******************************************************************
** USAGE: abc_rman_incr_hot_bkp.sh **
** where Is the SID of Database to be backed up. **
** **
** Prerequisits: **
** – Login as oracle user **
** – Ensure that the DB is open **
** – Ensure that the DB is in the archivelog mode **
** – Configure FRA , if not already configured **
******************************************************************

USAGEINFO
exit 1
fi

# Set the Oracle Environment
# —————————
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export ORACLE_SID=$1
export PATH=$ORACLE_HOME/bin:$PATH:.
oh=$ORACLE_HOME

dt=”_`date ‘+%Y%m%d’`”
sid=$1
lg=”/backup/$sid/log/rman_incr_hot$dt.log”
#lg=”/home/oracle/$sid/log/rman_incr_hot$dt.log”

echo Script $0 >> $lg
echo >> $lg
echo ========== started on `date` ========== >> $lg
echo “ORACLE_SID: $sid” >> $lg
echo “ORACLE_USER: $usr” >> $lg
echo “ORACLE_HOME: $oh” >> $lg
echo “RMAN Log File: $lg” >> $lg
echo ============================================================ >> $lg

rman msglog $lg append << EOF

connect target /

set echo on;

configure controlfile autobackup on;

run { sql “alter session set nls_date_format=”dd-mm-yyyy hh24:mi:ss””;

allocate channel c1 device type disk ;

allocate channel c2 device type disk ;

allocate channel c3 device type disk ;

allocate channel c4 device type disk ;

backup as compressed backupset incremental level 1 database tag ‘daily_db_incr_hot_backup’ plus archivelog tag ‘daily_archive_hot_backup’;

release channel c1 ;

release channel c2 ;

release channel c3 ;

release channel c4 ; }

exit

EOF

echo ========== Completed at `date` ========== >> $lg

 

Advertisements

Oracle RMAN Database Full Cold Backup Script

This is the second post/script in this series, the first script is Oracle RMAN Database Full Hot Backup Script

The following script will fully backup the database while it is closed (in the mount state). This backup will be a consistent backup that needs no archive logs to be recovered.


# **************************************************************************************
# $Header: abc_rman_full_cold_bkp.sh *
# **************************************************************************************
# * Author: Ahmed Abdel Fattah *
# **************************************************************************************
# * DESCRIPTION: Take rman full cold backup(incremental level 0 ) *
# * Backing up archive logs is just redundant *
# * PLATFORM: Linux/Solaris/HP-UX/AIX *
# **************************************************************************************

#!/bin/bash

# Check the login user to be “oracle”
# ————————————
usr=`id |cut -d”(” -f2 | cut -d “)” -f1`

if [ $usr != “oracle” ]
then
echo “You should login as oracle user”
exit 1
fi

# Check that ORACLE_HOME was set
# ——————————
# oh=$ORACLE_HOME
# if [ -z $oh ]
# then
# echo “You should set the oracle environment”
# exit 1
# fi

# Check that the user passed one parameter
# —————————————-
if [ $# -lt 1 ] ; then #not given enough parameters to script
cat <<USAGEINFO

******************************************************************
** USAGE: abc_rman_full_cold_bkp.sh **
** where Is the SID of Database to be backed up. **
** **
** Prerequisits: **
** – Login as oracle user **
** – Ensure that the DB is open **
** – Configure FRA , if not already configured **
******************************************************************

USAGEINFO
exit 1
fi

# Set the Oracle Environment
# —————————
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
export ORACLE_SID=$1
export PATH=$ORACLE_HOME/bin:$PATH:.
oh=$ORACLE_HOME

dt=”_`date ‘+%Y%m%d’`”
sid=$1
lg=”/backup/$sid/log/rman_full_cold$dt.log”
#lg=”/home/oracle/$sid/log/rman_full_cold$dt.log”

echo Script $0 >> $lg
echo >> $lg
echo ========== started on `date` ========== >> $lg

echo “ORACLE_SID: $sid” >> $lg
echo “ORACLE_USER: $usr” >> $lg
echo “ORACLE_HOME: $oh” >> $lg
echo “RMAN Log File: $lg” >> $lg
echo ======================================== >> $lg

rman msglog $lg append << EOF

connect target /

set echo on;

shutdown immediate;

startup mount;

configure controlfile autobackup on;

run

{

sql “alter session set nls_date_format=”dd-mm-yyyy hh24:mi:ss””;

allocate channel c1 device type disk ;

allocate channel c2 device type disk ;

allocate channel c3 device type disk ;

allocate channel c4 device type disk ;

backup as compressed backupset incremental level 0 database tag ‘weekly_cold_backup’ plus archivelog tag ‘weekly_cold_backup’;

release channel c1 ;

release channel c2 ;

release channel c3 ;

release channel c4 ;

}

sql ‘alter database open’;

exit

EOF

echo ========== Completed at `date` ========== >> $lg
 

Oracle RMAN Database Full Hot Backup Script

I was asked by one of my customers to develop some rman backup/restore scripts.

These scripts are a modified versions from some old scripts developed by me + some enhancement from other similar scripts .

Following is the first script in the series, it to fully backup the database while it is open.
— {Please fully test these scripts before using them in your specific environment} —

# ***********************************************************************
# $Header: abc_rman_full_hot_bkp.sh *
#************************************************************************
# * Author: Ahmed Abdel Fattah *
#************************************************************************
# * DESCRIPTION: Take rman full hot backup(incremental level 0 ) *
# * PLATFORM: Linux/Solaris/HP-UX/AIX *
#***********************************************************************

#!/bin/bash

# Check the login user to be “oracle”
# ————————————
usr=`id |cut -d”(” -f2 | cut -d “)” -f1`

if [ $usr != “oracle” ]
then
echo “You should login as oracle user”
exit 1
fi

# Check that ORACLE_HOME was set
# ——————————
#oh=$ORACLE_HOME
#if [ -z $oh ]
#then
# echo “You should set the oracle environment”
# exit 1
#fi

# Check that the user passed one parameter
# —————————————-

if [ $# -lt 1 ] ; then #not given enough parameters to script
cat <> $lg
echo >> $lg
echo ========== started on `date` ========== >> $lg
echo “ORACLE_SID: $sid” >> $lg
echo “ORACLE_USER: $usr” >> $lg
echo “ORACLE_HOME: $oh” >> $lg
echo “RMAN Log File: $lg” >> $lg
echo ============================================================ >> $lg

rman msglog $lg append << EOF

connect target / set echo on;

configure controlfile autobackup on;

run

{

sql "alter session set nls_date_format=''dd-mm-yyyy hh24:mi:ss''";

allocate channel c1 device type disk ;

allocate channel c2 device type disk ;

allocate channel c3 device type disk ;

allocate channel c4 device type disk ;
backup as compressed backupset incremental level 0 database tag 'weekly_hot_backup' plus archivelog tag 'weekly_hot_backup';

release channel c1 ;

release channel c2 ;

release channel c3 ;

release channel c4 ;

}

exit

EOF

echo ========== Completed at `date` ========== >> $lg

Installing Oracle Apex 5.1.4 on Oracle Database 12c R2

This is a quick post on how to install Oracle Apex version 5.1.4 on Oracle Database 12.2 on Windows platform.

12cR2 Update:

From Oracle Database 12c Release 2 (12.2) onward APEX is not installed by default, so you no longer need to worry about uninstalling APEX before creating PDBs. The software is still shipped with the database ($ORACLE_HOME/apex), but not installed.

  • In Oracle 12.2 you should always do the following.
    Download the latest version of APEX , rather than using the version shipped with the database from:

Downlaod the Latest Apex Release

  • Install APEX locally in a PDB, rather than in the root container. You should not install APEX in the root container in Oracle 12.2.

Install Oracle Apex 5.1.4:

Open “cmd” window

cd d:\apex514\apex      — This is where you extracted the Apex software

sqlplus / as sysdba

CREATE TABLESPACE apex DATAFILE SIZE 300M AUTOEXTEND ON NEXT 10M;

@apexins.sql APEX APEX TEMP /i/

.

.

.

.

Thank you for installing Oracle Application Express 5.1.4.00.08
Oracle Application Express is installed in the APEX_050100 schema.
The structure of the link to the Application Express administration services is as follows: http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql) http://host:port/apex/apex_admin       (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

http://host:port/apex/apex_admin     (Oracle REST Data Services)
The structure of the link to the Application Express development interface is as follows: http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)

http://host:port/apex     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

http://host:port/apex     (Oracle REST Data Services)
timing for: Phase 3 (Switch)

Elapsed: 00:01:30.59

timing for: Complete Installation

Elapsed: 00:10:32.48
PL/SQL procedure successfully completed.

 

@apxchpwd.sql ============================================================================ This script can be used to change the password of an Application Express instance administrator. If the user does not yet exist, a user record will be created. ============================================================================ Enter the administrator’s username [ADMIN]

User “ADMIN” does not yet exist and will be created.

Enter ADMIN’s email [ADMIN]

Enter ADMIN’s password []

Created instance administrator ADMIN.
@apex_rest_config.sql

Enter a password for the APEX_LISTENER user              []

Enter a password for the APEX_REST_PUBLIC_USER user              []

…create APEX_LISTENER and APEX_REST_PUBLIC_USER users
@apex_epg_config.sql d:\apex514

. Loading images directory: d:\apex514/apex/images

timing for: Load Images

Elapsed: 00:01:46.18

 

SELECT DBMS_XDB.gethttpport FROM DUAL;

GETHTTPPORT

———–

0

EXEC DBMS_XDB.sethttpport(8080);

SELECT DBMS_XDB.gethttpport FROM DUAL;

GETHTTPPORT

———–

8080
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

ALTER USER XDB ACCOUNT UNLOCK;

ALTER USER FLOWS_FILES ACCOUNT UNLOCK;

ALTER USER APEX_INSTANCE_ADMIN_USER ACCOUNT UNLOCK;

Combining Resource Consumer Groups with Application Modules in #Oracle

Uwe Hesse

This article contains a complete working example for the Resource Manager on the command line for those of you who can’t use the Enterprise Manager fort it. Believe me, I feel your pain 😉

As a good practice, PL/SQL procedures should be using DBMS_APPLICATION_INFO to mark their modules and actions. Not only for monitoring purpose but also to provide a way to tweak the system if things start going ugly in terms of performance. Here’s where the Resource Manager steps in.

Sessions can be assigned to different consumer groups depending on the module. Say we have an application with certain modules that sometimes consume an awful lot of CPU resources or way too much parallel processes. When the problem surfaces, you may not have enough time to fix the coding because it’s a live production run. The mentioned tweak – if prepared beforehand – may save the day. Let’s look…

View original post 853 more words

Oracle RAC 12c Installation on Two Linux Virtual Machines

Introduction:

This post is just a screen cast for the process of:

  • Installing Oracle Grid Infrastructure 12c on two nodes cluster
  • Creating additional Disk Groups.
  • Creating optional ACFS.
  • Installing Oracle Database 12c with RAC option.
  • Database creation.

This post will not include any details about the following tasks:

  • Virtual machine creation
  • Network configuration
  • Software download and staging
  • Prerequisite review and fulfilment
  • Shared storage setup
  • DNS setup.
  • Any error troubleshooting!

I may add these topics in a future post, I hope!

This post is just screen shot documentation for the RAC installation process.

Enjoy!

 

Architecture

Cluster

Oracle Grid Infrastructure Installation

grid-1grid-2grid-3grid-4grid-5grid-6grid-7grid-8grid-9grid-10grid-11grid-12grid-13grid-14grid-15grid-16grid-17grid-18grid-19grid-20grid-21grid-22grid-23grid-24grid-25grid-26grid-27grid-28grid-29grid-30grid-31grid-32grid-33grid-34grid-35grid-36grid-37grid-38grid-39grid-40grid-41grid-42grid-43

Creating Additional ASM Disk Group

asm-1asm-2asm-3asm-4asm-5

 

Creating ACFS:

asm-6asm-7asm-8asm-9asm-10asm-11asm-12asm-13asm-14asm-15asm-16asm-17asm-18asm-19

Oracle Database 12c Installation with RAC Option

db-1db-2db-3db-4db-5db-6db-7db-8db-9db-10db-11db-12db-13db-14db-15db-16db-17db-18db-19db-20db-21db-22db-23db-24

Oracle Database Creation:

cr-db-1cr-db-2cr-db-3cr-db-4cr-db-5cr-db-6cr-db-7cr-db-8cr-db-9cr-db-10

….. Done !

Oracle Database 12c R2 Partitioning New Features – Automatic List Partitioning

Oracle Database 12.2 introduces new nice and powerful partitioning features. This post will introduce one of them “Automatic List Partitioning”. We may talk about other features in future posts.

Before 12.2, if you have a list partitioned table in specific discrete values, and there is a need to add a new list value, then you have to create a new partition manually for the new added value.

In 12.2, you don’t have to do that, Oracle will do it automatically on behave of you.

When you create the table, you just need to know one of the list values, not all of them and then let Oracle do the magic.

This is similar to the “Interval Partitioning” (introduced in Oracle 11g) relative to “Range Partitioning”. With “Interval Partitioning”, you don’t have to create new partitions for new periods manually, Oracle will do it for you automatically.

Let us take an example:

create table employees

(      empno                     number,

ename                      varchar2(100),

salary                        number,

marital_status       varchar2(2)

)

partition by list (marital_status) automatic

(      partition p1 values (‘MA’)                       — ‘MA’ = Married

)

/

1

Note the new keyword “automatic” introduced in 12.2. Also, note that when you create a table this way, you can’t create a default partition.

Let us insert some data in employees table:

insert into employees values (10,’Mohamed’,10000,’MA’);

==> Row was inserted successfully

Now let us insert another row:

insert into employees values (20,’Ahmed’,5000,’SI’);                       — ‘SI’ = Single

==>Row was inserted successfully

Although, we didn’t create a partition for the ‘SI’ value, but its partition was created automatically by Oracle.

Let us confirm this:

select partition_name, high_value

from user_tab_partitions

where table_name = ‘EMPLOYEES’;

2

So, Oracle automatically created a new partition called “SYS_P30192” at run time for the “SI” value.

Let us confirm also, the correct mapping between data and partitions:

select * from employees partition (SYS_P30192);

3

So, the new row was inserted correctly on the newly created partition.

So, with this new feature, you don’t have to know all the possible values for the list partitioning key. This feature is only available for list-partitions but is not available for list-subpartitions.

 

Additional points:

  • You may use the following syntax “extended partition naming convention” to get the partition data without knowing the partition name:

select * from employees partition for (‘SI’);

 

4

  • If you don’t like the partition’s system generated name, you can rename it as follows:

alter table employees rename partition for (‘SI’) to P_SI;

 

5

Remarks:

If the list-partitioned table is not configured for automatic partitioning, you can change this by:

ALTER TABLE employees SET PARTITIONING AUTOMATIC;

Also, if you want to check if a table is configured for automatic list partitioning or not:

SELECT TABLE_NAME, partitioning_type, autolist FROM user_part_tables;


 

 

How do Nologging Operations affect Recovery and Data Guard?

What is Redo Information:

Each Oracle Database instance has redo logs, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

redo

What is Nologging:

NOLOGGING can be used to prevent bulk operations from logging too much information to Oracle’s Redo log files.

NOLOGGING can be used to minimize the amount of redo generated by Oracle (It will not prevent the redo completely).

What operations can use NOLOGGING?

  • Create Table As Select (CTAS)
  • ALTER TABLE operationen
  • ALTER TABLE statements (add/move/merge/split partitions)
  • INSERT /*+APPEND*/
  • CREATE INDEX
  • ALTER INDEX statements (add/move/merge/split partitions)

 

What is the benefits of settings NOLOGGING:

  • To speed up the transactions (e.g. Bulk loading of huge number of records)
  • Reduce the I/O contention on the redo log files.
  • It will save a disk space when the database is in the ARCHIVELOG mode.

So, NOLOGGING is good from performance side, but it is bad from recoverability prospective, because Oracle will not have the necessary information to recover.

So, NOLOGGING allows you to suppress (or minimize) the redo generated for some statements like (CREATE TABLE AS SELECT, INSERT INTO TABLE… SELECT and CREATE INDEX).

After doing some NOLOGGING operations:

If you take backups of the related datafiles after these operations è You are in the safe side, and you saved time during these operations and also save the space of the corresponding archive logs.

If the available backups were taken before these operations, and some files – with nologging operations – got damaged, then you are in trouble (unless you are able to upload the same data manually) !.

Let us have a quick demo:

sqlplus / as sysdba

SQL> select force_logging from v$database;

NO

SQL> grant dba to ahmed identified by ahmed;

Grant succeeded.

SQL > connect ahmed/ahmed

AHMED @ XPS > create tablespace tbs_01 datafile ‘D:\APP\AFATTAH\ORADATA\XPS\tbs_01.dbf’ size 100m;

Tablespace created.

AHMED @ XPS > create table t1 tablespace tbs_01 as select rownum as id, ‘Hello world’ as col from dual connect by level <= 1e5;

Table created.

 

RMAN> backup tablespace tbs_01;

Starting backup at 24-AUG-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=286 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00024 name=D:\APP\AFATTAH\ORADATA\XPS\TBS_01.DBF

channel ORA_DISK_1: starting piece 1 at 24-AUG-17

channel ORA_DISK_1: finished piece 1 at 24-AUG-17

piece handle=D:\APP\12CDB\FAST_RECOVERY_AREA\XPS\BACKUPSET\2017_08_24\O1_MF_NNNDF_TAG20170824T211143_DSY97JMX_.BKP tag=TAG20170824T211143 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 24-AUG-17

Starting Control File and SPFILE Autobackup at 24-AUG-17

piece handle=D:\APP\12CDB\FAST_RECOVERY_AREA\XPS\AUTOBACKUP\2017_08_24\O1_MF_S_952895505_DSY97KJH_.BKP comment=NONE

Finished Control File and SPFILE Autobackup at 24-AUG-17

 

Check for nologging or unrecoverable operations:

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations

File Type of Backup Required Name

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

AHMED @ XPS >  select file#,to_char(UNRECOVERABLE_TIME,’yyyy-mm-dd:hh24:mi:ss’) from v$datafile where file#=24;

FILE#  TO_CHAR(UNRECOVERABLE_TIME,’YYYY-MM-DD:HH24:MI:SS’)

24

No nologging/unrecoverable operations yet

 

Now let us start doing some nologging operations:

AHMED @ XPS > alter table t1 nologging;

Table T1 altered.

AHMED @ XPS > insert /*+ append */ into t1 select * from t1;

100,000 rows inserted.

AHMED @ XPS > commit;

Commit complete.

AHMED @ XPS >select count(*) from t1 where rownum<=1e5;

COUNT(*)

100000

 

Now , let us check for for nologging or unrecoverable operations:

AHMED @ XPS > select file#,to_char(UNRECOVERABLE_TIME,’yyyy-mm-dd:hh24:mi:ss’) from v$datafile where file#=24;

FILE#  TO_CHAR(UNRECOVERABLE_TIME,’YYYY-MM-DD:HH24:MI:SS’)

24     2017-08-24:21:19:07

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations

File Type of Backup Required    Name

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

24   full or incremental             D:\APP\AFATTAH\ORADATA\XPS\TBS_01.DBF

 

So, no problem so far from application side when doing nologging operation, but both RMAN & v$datafile will report unrecoverable operations. Also, there is nothing reported in the alert file so far.

RMAN> backup tablespace tbs_01;

Starting backup at 24-AUG-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00024 name=D:\APP\AFATTAH\ORADATA\XPS\TBS_01.DBF

channel ORA_DISK_1: starting piece 1 at 24-AUG-17

channel ORA_DISK_1: finished piece 1 at 24-AUG-17

piece handle=D:\APP\12CDB\FAST_RECOVERY_AREA\XPS\BACKUPSET\2017_08_24\O1_MF_NNNDF_TAG20170824T212522_DSYB13MR_.BKP tag=TAG20170824T212522 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 24-AUG-17

Starting Control File and SPFILE Autobackup at 24-AUG-17

piece handle=D:\APP\12CDB\FAST_RECOVERY_AREA\XPS\AUTOBACKUP\2017_08_24\O1_MF_S_952896324_DSYB14HW_.BKP comment=NONE

Finished Control File and SPFILE Autobackup at 24-AUG-17

 

Now , let us check for for nologging or unrecoverable operations after having a backup:

 

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations

File Type of Backup Required Name

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

 

AHMED @ XPS >select file#,to_char(UNRECOVERABLE_TIME,’yyyy-mm-dd:hh24:mi:ss’) from v$datafile where file#=24;

FILE#  TO_CHAR(UNRECOVERABLE_TIME,’YYYY-MM-DD:HH24:MI:SS’)

24     2017-08-24:21:19:07

 

Having the required backups/archivelogs to recover a file, will remove it from the RMAN report (RMAN> report unrecoverable;), but it will not remove it from the v$datafile

Now let us assume that the tablespace tbs_01 get damaged and we need to restore/recover it but before that let us do some nologging operations above the last backup:

AHMED @ XPS >insert /*+ append */ into t1 select * from t1;

200,000 rows inserted.

 

AHMED @ XPS >commit;

Commit complete.

 

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations

File Type of Backup Required Name

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

24   full or incremental     D:\APP\AFATTAH\ORADATA\XPS\TBS_01.DBF

AHMED @ XPS >select file#,to_char(UNRECOVERABLE_TIME,’yyyy-mm-dd:hh24:mi:ss’) from v$datafile where file#=24;

FILE#  TO_CHAR(UNRECOVERABLE_TIME,’YYYY-MM-DD:HH24:MI:SS’)

24     2017-08-24:21:28:00

Let us simulate the file damage, restore and recovery:

RMAN> alter database datafile 24 offline;

Statement processed

RMAN> restore datafile 24;

Starting restore at 24-AUG-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00024 to D:\APP\AFATTAH\ORADATA\XPS\TBS_01.DBF

channel ORA_DISK_1: reading from backup piece D:\APP\12CDB\FAST_RECOVERY_AREA\XPS\BACKUPSET\2017_08_24\O1_MF_NNNDF_TAG20170824T212522_DSYB13MR_.BKP

channel ORA_DISK_1: piece handle=D:\APP\12CDB\FAST_RECOVERY_AREA\XPS\BACKUPSET\2017_08_24\O1_MF_NNNDF_TAG20170824T212522_DSYB13MR_.BKP tag=TAG20170824T212522

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 24-AUG-17

 

RMAN> recover datafile 24;

Starting recover at 24-AUG-17

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 24-AUG-17

RMAN> alter database datafile 24 online;

Statement processed

 

Restore/Recover will be completed successfully for datafiles with nologging blocks, but RMAN and v$datafile still report unrecoverable.

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations

File Type of Backup Required Name

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

24   full or incremental     D:\APP\AFATTAH\ORADATA\XPS\TBS_01.DBF

 

AHMED @ XPS >select file#,to_char(UNRECOVERABLE_TIME,’yyyy-mm-dd:hh24:mi:ss’) from v$datafile where file#=24;

FILE#  TO_CHAR(UNRECOVERABLE_TIME,’YYYY-MM-DD:HH24:MI:SS’)

24     2017-08-24:21:28:00

 

Now what if you want to access the table:

AHMED @ XPS >select count (*) from t1;

Error starting at line : 1 in command –

select count (*) from t1

Error report –

ORA-01578: ORACLE data block corrupted (file # 24, block # 752)

ORA-01110: data file 24: ‘D:\APP\AFATTAH\ORADATA\XPS\TBS_01.DBF’

ORA-26040: Data block was loaded using the NOLOGGING option

 

So problem happened once you started to access a table with “restored/recovered” nologging blocks.

If no recovery happend, you will not feel any difference.

If recovery happened – as in case of Data Guard – you will not feel any issue until you start accessing the tables.

 

The solution with the Data Guard – which run in continuous recovery mode – is to enable force logging at the database level. Please remember to do that before taking the initial backup which will be used to build the standby database, otherwise you will face the above block corruption issue after failing over to the standby database.

 

On the Data Guard environments, Redo Apply processes will mark relevant blocks as corrupted block. Once you query on data that references these blocks, you will get ORA-01578. By issuing DBVERIFY agaist the datafiles on physical standby database, you can get a list of corrupted blocks (dbv file=D:\APP\AFATTAH\ORADATA\XPS\TBS_01.DBF). On the primary these blocks are not marked as corrupted (unless you recover them) but they will be marked as unrecoverable.

 

References:

https://uhesse.com/2009/09/15/how-do-nologging-operations-affect-recovery/

 

Oracle Database Migration to Exadata – Case Study

Objective:

This report will show the detailed steps that we followed to migrate a real client production database from its current production server to the Exadata machine.

Environment:

  Source Target
Server Name prod-n-214 exa-db-101
Database Name PROD PROD
Oracle version 11.2.0.3 11.2.0.3
O.S Linux OEL 6.5 Exadata OEL 6.8
Character set WE8ISO8859P1 WE8ISO8859P1
Clustered: Single Instance Single Instance

Migration High Level Plan:

  • Migration Preparation.
  • Backup the production database.
  • Duplicate the production database to Exadata.
  • Post Migration steps
  • Validate the migration.
  • Application specific setup.

 

Migration Preparation: Source Database Preparation:

  • Review the current production database PROD’s alert.log file for any issues.
  • Collect detailed information about PROD (Tablespaces, Users, Tables, Segments, Database Links, etc.)
  • Compile invalid objects (if any).
  • Decide the media/location that will be used to hold the Production database backup.{Database will be duplicated from the latest production backup on the EMC storage}

Migration Preparation: Exadata Preparation

  • Ensure that Oracle 11.2.0.3 binaries were already installed and that it uses RDS protocol (Note that in our case 11.2.0.3 is an additional home that was installed after the standard ACS installation).

/u01/app/oracle/product/11.2.0.4/dbhome/bin/skgxpinfo -v

You should get ==> Oracle RDS/IP (generic)

If you get ==>  Oracle RDS/IP (generic) , then you have to relink the home as        follows:

          make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk ipc_rds ioracle

  • It is recommended to use RDS on Exadata as it uses IB network which provides greater bandwidth

{There is a bug between 12c GI & 11.2.0.3 with RDS (Bug: 16844086)}

Pre-Migration Preparation:

The following steps should be followed during the Go-Live (not the test migration):

  • <CUSTOMER> should submit a request to the firewall team to open the any required ports (ports: 1521, 8081 & 1648) è This should be done early enough before the Go-Live date
  • Agree with all the stakeholders on the Go-Live Date/Time.
  • <CUSTOMER> to inform all concerned parties with the Go-Live Date/Time (Network, Backup, Security, Application teams).

Migration Approach:

We are going to use a physical migration approach (database duplicate), as the logical migration approach (export/Import) has some limitations with the Oracle XML database. PROD application is heavily using the Oracle XML database.

Backup the Production database

rman target /

shutdown immediate;

startup mount;

sql ‘alter system enable restricted session’;

alter database open;

sql ‘alter system checkpoint’;

sql ‘alter system switch logfile’;

sql ‘alter system archive log current’;

shutdown immediate;

startup mount;

{Leave the database in the mount mode (to prevent any new transactions while migrating the database}

RUN {

ALLOCATE CHANNEL CH1  TYPE ‘SBT_TAPE’ MAXOPENFILES=1;

ALLOCATE CHANNEL CH2  TYPE ‘SBT_TAPE’ MAXOPENFILES=1;

ALLOCATE CHANNEL CH3  TYPE ‘SBT_TAPE’ MAXOPENFILES=1;

ALLOCATE CHANNEL CH4  TYPE ‘SBT_TAPE’ MAXOPENFILES=1;

 

BACKUP INCREMENTAL  LEVEL 0

FILESPERSET 10

FORMAT ‘%d_%u_%s_%p

DATABASE

INCLUDE CURRENT CONTROLFILE;

BACKUP filesperset 10 archivelog all;

BACKUP CURRENT CONTROLFILE;

}

 

Duplicate the production database to Exadata

Preparation: Listener Configuration

Add the following static entry to the listener.ora file (so as we can to connect to the database from rman while being in the shutdown mode):

 

Login as “grid” user.

Set the environment for the grid user

. oraenv

+ASM1

As grid user edit the Listener.ora on the new Exadata server:

1

$ lsnrctl reload

$ lsnrctl stat

Preparation: tnsnames.ora Configuration

Add the following TNS entry for both the source database and new database (Edit the  tnsnames.ora on the new Exadata server)

As “oracle” user go to $ORACLE_HOME/network/admin on the target server and edit tnsnames.ora   as follows (PROD214.world is the source entry, PROD.worls is the Exadata entry):

2

Preparation: init.ora

Create a parameter file for the target database under $ORACLE_HOME/dbs with the name initPROD.ora and add the following lines ,save and exit:

*.audit_file_dest=’/u01/app/oracle/admin/PROD/adump’

*.audit_trail=’DB’

*.compatible=’11.2.0.3.0′

*.control_files=’+DATA_ED01/PROD/CONTROLFILE/ctl1.ctl’,’+RECO_ED01/PROD/CONTROLFILE/ctl2.ctl’

*.db_block_size=8192

*.db_create_file_dest=’+DATA_ED01′

*.db_domain=’WORLD’

*.db_name=’PROD’

*.db_recovery_file_dest=’+RECO_ED01′

*.db_recovery_file_dest_size=322122547200

*.diagnostic_dest=’/u01/app/oracle’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)’

*.open_cursors=500

*.processes=500

*.remote_login_passwordfile=’EXCLUSIVE’

*.sga_target=10737418240

*.pga_aggregate_target=5368709120

*.undo_management=’AUTO’

*.undo_tablespace=’UNDOTBS1′

Preparation: dump directories

mkdir –p /u01/app/oracle/admin/PROD/adump

Preparation: password file

Under directory ($ORACLE_HOME\dbs) on target Exadata server , create password file via orapwd utility (or you may copy the source password file):

Login to the source server prod-n-214

cd $ORACLE_HOME/dbs

scp orapwPROD oracle@exa-db-101:/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs

Preparation: Startup the new Database

Startup the new database in nomount state on target Exadata server (101):

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1

export ORACLE_SID=PROD

export PATH=$PATH:$ORACLE_HOME/bin

sqlplus / as sysdba

sql> startup nomount;

3

Database Duplication

Run RMAN connecting to both source database (PROD214) as a target and new database (PROD) as auxiliary

export NLS_DATE_FORMAT=”DD-MON-YYYY HH24:MI:SS”

rman target orabkup/xxxx@PROD214.world auxiliary orabkup/xxxx@PROD.world

4

On the source system (mounted database):

sqlplus / as sysdba

ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

select * from (

select NEXT_CHANGE#,FIRST_TIME,NEXT_TIME from v$archived_log order by 1 desc )

where rownum <2;

On the Exadata server, run the duplicate command as follows and set until time clause in case you need to set duplicate to a specific point in time (Make sure no spaces in parms syntax):

run {

set until scn 423041993;

allocate auxiliary channel ch1 type ‘sbt_tapeparms ‘ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=prod-n-214)’;

allocate auxiliary channel ch2 type ‘sbt_tapeparms ‘ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=prod-n-214)’;

allocate auxiliary channel ch3 type ‘sbt_tapeparms ‘ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=prod-n-214)’;

allocate auxiliary channel ch4 type ‘sbt_tapeparms ‘ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=prod-n-214)’;

duplicate target database to PROD pfile ‘/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initPROD.oranofilenamecheck; }

Make sure RMAN finished the Duplicate command with no errors

Once RMAN finished, the new database PROD should be up and running.

Post Migration Steps

Create spfile

Exit RMAN and create spfile for the duplicated database:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
export ORACLE_SID=PROD
export PATH=$PATH:$ORACLE_HOME/bin

sqlplus / as sysdba
sql> create spfile='+DATA_ED01' from pfile;

This will create spfile at:
+DATA_ED01/PROD/PARAMETERFILE/spfile.527.938200795

now :
cd $ORACLE_HOME/dbs
mv initPROD.ora initPROD.ora.org
vi initPROD.ora
   spfile='+DATA_ED02/DTSDEV/PARAMETERFILE/spfile.527.938200795'

Register database with the cluster

On the terminal issue the following to add database to Oracle Restart

$ srvctl add database -d PROD -n PROD -o /u01/app/oracle/product/11.2.0.3/dbhome_1 -c SINGLE -a DATA_ED01,RECO_ED01 -p '+DATA_ED01/PROD/PARAMETERFILE/spfile.527.938200795' -i PROD -x exa-dbadm-101
$ srvctl stop database -d PROD
$ srvctl start database -d PROD
$ srvctl status database -d PROD
$ srvctl config database -d PROD

Full Database Backup

Ask the backup team to take a full cold backup for the newly migrated database before going live.

Distribute the new client TNS Entry

Below is the TNS entry that should be used by the clients:
PROD.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = exa-dbvip-101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD.WORLD)
    )
  )

 

RMAN Backup Setup

Modify/Change any relevant RMAN settings:

RMAN> Show all;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

SQL> alter system set control_file_record_keep_time=31 scope=both;

 

Migration Validation

  • Ensure that there is no errors reported out from the “Duplicate” operation.
  • Make sure new Database become registered with Listener (lsnrctl status).
  • Ensure that you can connect to the database and it is in the “Read Write” mode:

56

 

  • Ensure that all datafiles are online:

Select name, status from v$datafile;

  • Review the new database alert.log file for any errors:

  /u01/app/oracle/diag/rdbms/PROD/PROD/trace/alert_PROD.log

  • Check Invalid objects
  • Restart the database
  • Test database connection from server and from clients

 

Appendix A: Duplicate Command Output

[oracle@exa-dbadm-101 dbs]$ rman target sys/xxxx@PROD202.world auxiliary sys/xxxx@PROD.world

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 9 18:53:05 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=3964826197, not open)
connected to auxiliary database: PROD (not mounted)

RMAN> run {
set until scn 423041993;
allocate auxiliary channel ch1 type 'sbt_tape' parms 'ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=exa-db-101)';
allocate auxiliary channel ch2 type 'sbt_tape' parms 'ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=exa-db-101)';
allocate auxiliary channel ch3 type 'sbt_tape' parms 'ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=exa-db-101)';
allocate auxiliary channel ch4 type 'sbt_tape' parms 'ENV=(NSR_SERVER=bkp-emc-100.corp,NSR_CLIENT=exa-db-101)';
duplicate target database to PROD pfile '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initPROD.ora' nofilenamecheck;
}

2> 3> 4> 5> 6> 7> 8>

executing command: SET until clause
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=685 device type=SBT_TAPE
channel ch1: NMDA Oracle v8.2.4
allocated channel: ch2
channel ch2: SID=703 device type=SBT_TAPE
channel ch2: NMDA Oracle v8.2.4
allocated channel: ch3
channel ch3: SID=721 device type=SBT_TAPE
channel ch3: NMDA Oracle v8.2.4
allocated channel: ch4
channel ch4: SID=739 device type=SBT_TAPE
channel ch4: NMDA Oracle v8.2.4

Starting Duplicate Db at 09-MAR-17
contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:

{
   shutdown clone immediate;
   startup clone nomount;
}

executing Memory Script

Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started

Total System Global Area   10689474560 bytes

Fixed Size                     2237776 bytes
Variable Size               1644169904 bytes
Database Buffers            9026142208 bytes
Redo Buffers                  16924672 bytes

allocated channel: ch1
channel ch1: SID=667 device type=SBT_TAPE
channel ch1: NMDA Oracle v8.2.4
allocated channel: ch2
channel ch2: SID=685 device type=SBT_TAPE
channel ch2: NMDA Oracle v8.2.4
allocated channel: ch3
channel ch3: SID=703 device type=SBT_TAPE
channel ch3: NMDA Oracle v8.2.4
allocated channel: ch4
channel ch4: SID=721 device type=SBT_TAPE
channel ch4: NMDA Oracle v8.2.4

contents of Memory Script:
{
   set until scn  423041993;
   sql clone "alter system set  db_name =
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script
executing command: SET until clause
sql statement: alter system set  db_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set  db_unique_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down
Oracle instance started

Total System Global Area   10689474560 bytes

Fixed Size                     2237776 bytes
Variable Size               1644169904 bytes
Database Buffers            9026142208 bytes
Redo Buffers                  16924672 bytes

allocated channel: ch1
channel ch1: SID=667 device type=SBT_TAPE
channel ch1: NMDA Oracle v8.2.4
allocated channel: ch2
channel ch2: SID=685 device type=SBT_TAPE
channel ch2: NMDA Oracle v8.2.4
allocated channel: ch3
channel ch3: SID=703 device type=SBT_TAPE
channel ch3: NMDA Oracle v8.2.4
allocated channel: ch4
channel ch4: SID=721 device type=SBT_TAPE
channel ch4: NMDA Oracle v8.2.4

Starting restore at 09-MAR-17
channel ch1: starting datafile backup set restore
channel ch1: restoring control file
channel ch1: reading from backup piece PROD_05runeku_5_1
channel ch1: piece handle=PROD_05runeku_5_1 tag=TAG20170309T174708
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
output file name=+DATA_ED02/PROD/controlfile/ctl1.ctl
output file name=+RECO_ED02/PROD/controlfile/ctl2.ctl
Finished restore at 09-MAR-17

database mounted

contents of Memory Script:
{
   set until scn  423041993;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   set newname for clone datafile  8 to new;
   set newname for clone datafile  9 to new;
   set newname for clone datafile  10 to new;
   set newname for clone datafile  11 to new;
   set newname for clone datafile  12 to new;
   set newname for clone datafile  13 to new;
   set newname for clone datafile  14 to new;
   set newname for clone datafile  15 to new;
   set newname for clone datafile  16 to new;

   restore
   clone database
   ;
}

executing Memory Script

executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 09-MAR-17
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00003 to +DATA_ED02
channel ch1: restoring datafile 00006 to +DATA_ED02
channel ch1: restoring datafile 00012 to +DATA_ED02
channel ch1: restoring datafile 00016 to +DATA_ED02
channel ch1: reading from backup piece PROD_02rune8s_2_1
channel ch2: starting datafile backup set restore
channel ch2: specifying datafile(s) to restore from backup set
channel ch2: restoring datafile 00001 to +DATA_ED02
channel ch2: restoring datafile 00009 to +DATA_ED02
channel ch2: restoring datafile 00011 to +DATA_ED02
channel ch2: restoring datafile 00014 to +DATA_ED02
channel ch2: reading from backup piece PROD_03rune8s_3_1
channel ch3: starting datafile backup set restore
channel ch3: specifying datafile(s) to restore from backup set
channel ch3: restoring datafile 00002 to +DATA_ED02
channel ch3: restoring datafile 00007 to +DATA_ED02
channel ch3: restoring datafile 00008 to +DATA_ED02
channel ch3: restoring datafile 00013 to +DATA_ED02
channel ch3: reading from backup piece PROD_01rune8s_1_1
channel ch4: starting datafile backup set restore
channel ch4: specifying datafile(s) to restore from backup set
channel ch4: restoring datafile 00004 to +DATA_ED02
channel ch4: restoring datafile 00005 to +DATA_ED02
channel ch4: restoring datafile 00010 to +DATA_ED02
channel ch4: restoring datafile 00015 to +DATA_ED02
channel ch4: reading from backup piece PROD_04rune8s_4_1
channel ch1: piece handle=PROD_02rune8s_2_1 tag=TAG20170309T174708
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:08:05
channel ch3: piece handle=PROD_01rune8s_1_1 tag=TAG20170309T174708
channel ch3: restored backup piece 1
channel ch3: restore complete, elapsed time: 00:10:25
channel ch2: piece handle=PROD_03rune8s_3_1 tag=TAG20170309T174708
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 00:11:15
channel ch4: piece handle=PROD_04rune8s_4_1 tag=TAG20170309T174708
channel ch4: restored backup piece 1
channel ch4: restore complete, elapsed time: 00:12:00
Finished restore at 09-MAR-17

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=32 STAMP=938199954 file name=+DATA_ED02/PROD/datafile/system.534.938199899
datafile 2 switched to datafile copy
input datafile copy RECID=33 STAMP=938199954 file name=+DATA_ED02/PROD/datafile/undotbs1.535.938199843
datafile 3 switched to datafile copy
input datafile copy RECID=34 STAMP=938199954 file name=+DATA_ED02/PROD/datafile/sysaux.538.938199707
datafile 4 switched to datafile copy
input datafile copy RECID=35 STAMP=938199955 file name=+DATA_ED02/PROD/datafile/users.533.938199945
datafile 5 switched to datafile copy
input datafile copy RECID=36 STAMP=938199955 file name=+DATA_ED02/PROD/datafile/proddata.546.938199239
datafile 6 switched to datafile copy
input datafile copy RECID=37 STAMP=938199955 file name=+DATA_ED02/PROD/datafile/proddata.548.938199239
datafile 7 switched to datafile copy
input datafile copy RECID=38 STAMP=938199955 file name=+DATA_ED02/PROD/datafile/proddata.547.938199239
datafile 8 switched to datafile copy
input datafile copy RECID=39 STAMP=938199956 file name=+DATA_ED02/PROD/datafile/proddata.544.938199441
datafile 9 switched to datafile copy
input datafile copy RECID=40 STAMP=938199956 file name=+DATA_ED02/PROD/datafile/proddata.542.938199499
datafile 10 switched to datafile copy
input datafile copy RECID=41 STAMP=938199956 file name=+DATA_ED02/PROD/datafile/proddata.541.938199529
datafile 11 switched to datafile copy
input datafile copy RECID=42 STAMP=938199957 file name=+DATA_ED02/PROD/datafile/proddata.545.938199239
datafile 12 switched to datafile copy
input datafile copy RECID=43 STAMP=938199957 file name=+DATA_ED02/PROD/datafile/proddata.543.938199449
datafile 13 switched to datafile copy
input datafile copy RECID=44 STAMP=938199957 file name=+DATA_ED02/PROD/datafile/proddata.539.938199641
datafile 14 switched to datafile copy
input datafile copy RECID=45 STAMP=938199958 file name=+DATA_ED02/PROD/datafile/proddata.537.938199711
datafile 15 switched to datafile copy
input datafile copy RECID=46 STAMP=938199958 file name=+DATA_ED02/PROD/datafile/proddata.536.938199759
datafile 16 switched to datafile copy
input datafile copy RECID=47 STAMP=938199958 file name=+DATA_ED02/PROD/datafile/proddata.540.938199639

contents of Memory Script:
{
   set until scn  423041993;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause

Starting recover at 09-MAR-17
starting media recovery
archived log for thread 1 with sequence 131 is already on disk as file +RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_131.23318.938197719
archived log for thread 1 with sequence 132 is already on disk as file +RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_132.23319.938197719
archived log for thread 1 with sequence 133 is already on disk as file +RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_133.23320.938197719
archived log file name=+RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_131.23318.938197719 thread=1 sequence=131
archived log file name=+RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_132.23319.938197719 thread=1 sequence=132
archived log file name=+RECO_ED02/PROD/archivelog/2017_03_09/thread_1_seq_133.23320.938197719 thread=1 sequence=133
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-MAR-17

Oracle instance started
Total System Global Area   10689474560 bytes

Fixed Size                     2237776 bytes
Variable Size               1610615472 bytes
Database Buffers            9059696640 bytes
Redo Buffers                  16924672 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY     4674
 LOGFILE
  GROUP   1  SIZE 50 M ,
  GROUP   2  SIZE 50 M ,
  GROUP   3  SIZE 50 M
 DATAFILE
  '+DATA_ED02/PROD/datafile/system.534.938199899'
 CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   set newname for clone tempfile  2 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA_ED02/PROD/datafile/undotbs1.535.938199843",
 "+DATA_ED02/PROD/datafile/sysaux.538.938199707",
 "+DATA_ED02/PROD/datafile/users.533.938199945",
 "+DATA_ED02/PROD/datafile/proddata.546.938199239",
 "+DATA_ED02/PROD/datafile/proddata.548.938199239",
 "+DATA_ED02/PROD/datafile/proddata.547.938199239",
 "+DATA_ED02/PROD/datafile/proddata.544.938199441",
 "+DATA_ED02/PROD/datafile/proddata.542.938199499",
 "+DATA_ED02/PROD/datafile/proddata.541.938199529",
 "+DATA_ED02/PROD/datafile/proddata.545.938199239",
 "+DATA_ED02/PROD/datafile/proddata.543.938199449",
 "+DATA_ED02/PROD/datafile/proddata.539.938199641",
 "+DATA_ED02/PROD/datafile/proddata.537.938199711",
 "+DATA_ED02/PROD/datafile/proddata.536.938199759",
 "+DATA_ED02/PROD/datafile/proddata.540.938199639";
   switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +DATA_ED02 in control file
renamed tempfile 2 to +DATA_ED02 in control file

cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/undotbs1.535.938199843 RECID=1 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/sysaux.538.938199707 RECID=2 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/users.533.938199945 RECID=3 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.546.938199239 RECID=4 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.548.938199239 RECID=5 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.547.938199239 RECID=6 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.544.938199441 RECID=7 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.542.938199499 RECID=8 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.541.938199529 RECID=9 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.545.938199239 RECID=10 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.543.938199449 RECID=11 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.539.938199641 RECID=12 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.537.938199711 RECID=13 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.536.938199759 RECID=14 STAMP=938199972
cataloged datafile copy
datafile copy file name=+DATA_ED02/PROD/datafile/proddata.540.938199639 RECID=15 STAMP=938199972

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/undotbs1.535.938199843
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/sysaux.538.938199707
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/users.533.938199945
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.546.938199239
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.548.938199239
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.547.938199239
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.544.938199441
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.542.938199499
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.541.938199529
datafile 11 switched to datafile copy
input datafile copy RECID=10 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.545.938199239
datafile 12 switched to datafile copy
input datafile copy RECID=11 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.543.938199449
datafile 13 switched to datafile copy
input datafile copy RECID=12 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.539.938199641
datafile 14 switched to datafile copy
input datafile copy RECID=13 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.537.938199711
datafile 15 switched to datafile copy
input datafile copy RECID=14 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.536.938199759
datafile 16 switched to datafile copy
input datafile copy RECID=15 STAMP=938199972 file name=+DATA_ED02/PROD/datafile/proddata.540.938199639

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 09-MAR-17