Explore Oracle Stuff

In my last post Storage optimization using acfs I have explained how to create snap clone database using gDBclone utility. Honestly I am not against that tool which is an awesome innovation but that has some limitation for certain conditions.

Lets say you don't want your test master database to be actively duplicated from prod or standby database.

You want to clone using some timestamp using backup based duplication without catalog connection.

Unfortunately gDBClone will fail throwing below error

RMAN-06136: Oracle error from auxiliary database: ORA-01152: file 2 was not restored from a sufficiently old backup

ORA-01110: data file 2: '/ccbdata/.ACFS/snaps/ccbmstr/CCBMSTR/datafile/o1_mf_cists_01_mrc8bpth_.dbf'

One more observation is after applying incremental level1 it does not restore and apply archivelog.

This is because of the following bug and when I worked with oracle support I git below update.

Bug 34210234 : RMAN DUPLICATE UNTIL TIMESTAMP USING BACKUP FAILS WITH ORA-01152
<<--- still no fix found, only workaround can be implemented

This limitation has forced me to remove the dependency with the tool and do it manually.If you still have enough patience to continue please follow below manual steps.

1. Create 3 volumes like below

asmcmd volcreate -G data -s 9120G DATA

asmcmd volcreate -G reco -s 500G RECO

asmcmd volcreate -G data -s 200G REDO

2. Create file system out of that volume

asmcmd volinfo -G DATA DATA

Diskgroup Name: DATA

Volume Name: DATA

Volume Device: /dev/asm/DATA-132

State: ENABLED

Size (MB): 5242880

Resize Unit (MB): 64

Redundancy: MIRROR

Stripe Columns: 8

Stripe Width (K): 1024

Usage:

Mountpath:

/sbin/mkfs -t acfs /dev/asm/DATA-132

mkfs.acfs: version = 19.0.0.0.0

mkfs.acfs: on-disk version = 46.0

mkfs.acfs: volume = /dev/asm/DATA-132

mkfs.acfs: volume size = 5497558138880 ( 5.00 TB )

mkfs.acfs: Format complete.

asmcmd volinfo -G RECO RECO

Diskgroup Name: RECO

Volume Name: RECO

Volume Device: /dev/asm/RECO-262

State: ENABLED

Size (MB): 512000

Resize Unit (MB): 64

Redundancy: MIRROR

Stripe Columns: 8

Stripe Width (K): 1024

Usage:

Mountpath:

/sbin/mkfs -t acfs /dev/asm/RECO-262

mkfs.acfs: version = 19.0.0.0.0

mkfs.acfs: on-disk version = 46.0

mkfs.acfs: volume = /dev/asm/RECO-262

mkfs.acfs: volume size = 536870912000 ( 500.00 GB )

mkfs.acfs: Format complete.

asmcmd volinfo -G DATA REDO

Diskgroup Name: DATA

Volume Name: REDO

Volume Device: /dev/asm/REDO-132

State: ENABLED

Size (MB): 204800

Resize Unit (MB): 64

Redundancy: MIRROR

Stripe Columns: 8

Width (K): 1024

Usage:

Mountpath:

/sbin/mkfs -t acfs /dev/asm/REDO-132

mkfs.acfs: version = 19.0.0.0.0

mkfs.acfs: on-disk version = 46.0

mkfs.acfs: volume = /dev/asm/REDO-132

mkfs.acfs: volume size = 214748364800 ( 200.00 GB )

mkfs.acfs: Format complete.

3. Create below mount point from root user

mkdir /DATA;chown oracle:dba /DATA;ls -ldr /DATA

drwxr-xr-x 2 oracle dba 4096 Jan 7 14:13 /DATA

mkdir /RECO;chown oracle:dba /RECO;ls -ldr /RECO

drwxr-xr-x 2 oracle dba 4096 Jan 7 14:15 /RECO

mkdir /REDO;chown oracle:dba /REDO;ls -ldr /REDO

4. Mount the acfs file syetm using below command

grep grid /u01/app/oraInventory/ContentsXML/inventory.xml

/u01/app/19.0.0.0/grid/bin/srvctl add filesystem -device /dev/asm/DATA-132 -path /DATA -user oracle -mountowner oracle -mountgroup dba -mountperm 755

/u01/app/19.0.0.0/grid/bin/srvctl add filesystem -device /dev/asm/RECO-262 -path /RECO -user oracle -mountowner oracle -mountgroup dba -mountperm 755

/u01/app/19.0.0.0/grid/bin/srvctl add filesystem -device /dev/asm/REDO-132 -path /REDO -user oracle -mountowner oracle -mountgroup dba -mountperm 755

5. Start the acfs file system

/u01/app/19.0.0.0/grid/bin/srvctl start filesystem -d /dev/asm/DATA-132

/u01/app/19.0.0.0/grid/bin/srvctl start filesystem -d /dev/asm/RECO-262

/u01/app/19.0.0.0/grid/bin/srvctl start filesystem -d /dev/asm/REDO-132

6. Check the acfs file system status

/u01/app/19.0.0.0/grid/bin/crsctl stat res ora.data.DATA.acfs ora.data.REDO.acfs ora.reco.RECO.acfs -t

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.data.DATA.acfs

ONLINE ONLINE tuslsoda01a mounted on /DATA,

STABLE

ONLINE ONLINE tuslsoda01b mounted on /DATA,

STABLE

ora.data.REDO.acfs

ONLINE ONLINE tuslsoda01a mounted on /REDO,

STABLE

ONLINE ONLINE tuslsoda01b mounted on /REDO,

STABLE

ora.reco.RECO.acfs

ONLINE ONLINE tuslsoda01a mounted on /RECO,

STABLE

ONLINE ONLINE tuslsoda01b mounted on /RECO,

STABLE

df -h

/dev/asm/DATA-132 5.0T 11G 5.0T 1% /DATA

/dev/asm/RECO-262 500G 1.6G 499G 1% /RECO

/dev/asm/REDO-132 200G 1020M 200G 1% /REDO

7. Identify DB home to use for clone

cat /u01/app/oraInventory/ContentsXML/inventory.xml

<?xml version="1.0" standalone="yes" ?>

<VERSION_INFO>

<SAVED_WITH>12.2.0.7.0</SAVED_WITH>

<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

</VERSION_INFO>

<HOME_LIST>

</HOME_LIST>

<COMPOSITEHOME_LIST>

</COMPOSITEHOME_LIST>

</INVENTORY>

Make an entry in /etc/oratab for new clone database.Not needed for existing database being cloned

ccbdev:/u01/app/oracle/product/19.0.0.0/dbhome_1:N

8. Create password file which can be used later

Set the oracle environment using oraenv/oset

orapwd file=$ORACLE_HOME/dbs/orapwCCBMSTR dbuniquename=ccbmstr

Enter password for SYS:

SYS password file created as /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwCCBMSTR

9. Prepare for clone from CC&B database

From grid user create snapshot like this and validate

/sbin/acfsutil snap create -w ccbmstr /DATA

acfsutil snap info ccbmstr /DATA'

snapshot name: ccbmstr

snapshot location: /DATA/.ACFS/snaps/ccbmstr

RO snapshot or RW snapshot: RW

parent name: /DATA

snapshot creation time: Tue Jan 14 13:15:46 2025

file entry table allocation: 8781824 ( 8.38 MB )

storage added to snapshot: 5885567029248 ( 5.35 TB )

Please use attached rman script to clone from source ASM to acfs file system.

10. Execute rman clone

Create a rman command file like below attached file.Now execute below rman command in background

You must set your oracle home environment before running rman command

nohup rman target sys/*****@targetdb auxiliary / cmdfile=</path/rman_commands.txt> log=rman_output.log &

Please find below rman script

run {

allocate auxiliary channel aux1 device type disk;

allocate auxiliary channel aux2 device type disk;

allocate auxiliary channel aux3 device type disk;

allocate auxiliary channel aux4 device type disk;

allocate auxiliary channel aux5 device type disk;

allocate auxiliary channel aux6 device type disk;

allocate auxiliary channel aux7 device type disk;

allocate auxiliary channel aux8 device type disk;

SET NEWNAME FOR DATABASE TO NEW;

DUPLICATE TARGET DATABASE

TO 'ccbmstr'

UNTIL TIME "TO_DATE('07-JAN-2025_03:00:00','DD-MON-YYYY_HH24:MI:SS')"

SPFILE

PARAMETER_VALUE_CONVERT='cist4','ccbmstr'

SET CONTROL_FILES='/ccbredo/ccbmstr/CCBMSTR/controlfile/control01.ctl'

SET DB_CREATE_FILE_DEST="/ccbdata/.ACFS/snaps/ccbmstr"

SET DB_RECOVERY_FILE_DEST="/ccbreco/ccbmstr"

SET DB_CREATE_ONLINE_LOG_DEST_1="/ccbredo/ccbmstr"

SET FILESYSTEMIO_OPTIONS='setall'

SET DB_RECOVERY_FILE_DEST_SIZE='10G'

SET SERVICE_NAMES=""

SET DB_CREATE_ONLINE_LOG_DEST_2=""

SET DB_CREATE_ONLINE_LOG_DEST_3=""

SET DB_CREATE_ONLINE_LOG_DEST_4=""

SET DB_CREATE_ONLINE_LOG_DEST_5=""

SET LOG_ARCHIVE_DEST=""

SET LOG_ARCHIVE_DEST_1="LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)"

SET LOG_ARCHIVE_DEST_2=""

SET LOG_ARCHIVE_DEST_3=""

SET LOG_ARCHIVE_DEST_4=""

SET LOG_ARCHIVE_DEST_5=""

SET LOG_ARCHIVE_FORMAT='%r_%s_%t.arc'

SET DB_NAME="ccbmstr"

SET DB_UNIQUE_NAME="ccbmstr"

SET INSTANCE_NAME="ccbmstr"

SET CLUSTER_DATABASE='false'

SET REMOTE_LISTENER="tuslsoda01:1521"

SET DIAGNOSTIC_DEST="/u01/app/oracle"

SET AUDIT_FILE_DEST="/u01/app/oracle/admin/ccbmstr/adump"

SET DB_DOMAIN=''

SET DB_FLASH_CACHE_FILE='/dev/null'

SET DB_FLASH_CACHE_SIZE='0'

SET LISTENER_NETWORKS=''

SET LOCAL_LISTENER=''

NOFILENAMECHECK;

}

11. Post rman clone

If we have database in asm and we are moving to acfs then remove the database and add it back

srvctl remove database -d ccbmstr

srvctl add database -d ccbmstr -oraclehome /u01/app/oracle/product/19.0.0.0/dbhome_1 -dbtype SINGLE -spfile /DATA/.ACFS/snaps/ccbmstr/CCBMSTR/parameterfile/spfileccbmstr.ora -pwfile /DATA/.ACFS/snaps/ccbmstr/CCBMSTR/password/orapwccbmstr -acfspath /DATA,/RECO,/REDO -node tuslsoda01b -i ccbmstr

Note:This step is not needed if we already have database in acfs and we are suing the same filesystem

Verify all datafiles,controlfiles and online redo log files

12. Prepare for snap db creation

To get a consistent snap it is always recommended to shutdown the db and then take a snap.

Before shutting down the db generate pfile from spfile like below.

SQL>Create pfile from spfile;

Shutdown the db manually or using srvctl command like below

srvctl stop database -d ccbmstr

Now take a snap from grid user.You can see that a new snapshot ccbdev has been created whose parent is showing as ccbmstr

su -grid -c ‘/sbin/acfsutil snap create -w -p ccbmstr ccbdev /DATA’

su - grid -c 'acfsutil snap info /DATA'

snapshot name: ccbmstr

snapshot location: /DATA/.ACFS/snaps/ccbmstr

RO snapshot or RW snapshot: RW

parent name: /DATA

snapshot creation time: Tue Jan 14 13:15:46 2025

file entry table allocation: 8781824 ( 8.38 MB )

storage added to snapshot: 5885567025152 ( 5.35 TB )

snapshot name: ccbdev

snapshot location: /DATA/.ACFS/snaps/ccbdev

RO snapshot or RW snapshot: RW

parent name: ccbmstr

snapshot creation time: Tue Jan 14 15:30:09 2025

file entry table allocation: 8781824 ( 8.38 MB )

storage added to snapshot: 2858209280 ( 8.38 MB )

number of snapshots: 2

kilosnap state: ENABLED

snapshot space usage: 5890843222016 ( 5.36 TB )

Note:If you go inside the directory /DATA/.ACFS/snaps/ccbdev you can see CCBMSTR directory.To avoid naming confusion lets rename so that ccbdev database files will have only under CCBDEV directory.Also need to create directories under /RECO and /REDO

mv /DATA/.ACFS/snaps/ccbdev/CCBMSTR /DATA/.ACFS/snaps/ccbdev/CCBDEV

mkdir -p /REDO/ccbdev

mkdir -p /RECO/ccbdev

13. Clean up pfile and validate all the path

Remove all reference of source(cist4) from pfile like below

sed -i '/^cist4/d' initccbdev.ora

search and replace ccbmstr with ccbdev

sed -i 's/ccbmstr/ccbdev/g' initccbdev.ora

Check whether these path can be accessed

grep "[/+]" initccbdev.ora

# Oracle init.ora parameter file generated by instance ccbdev on 01/15/2025 10:38:27

__oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment

_diag_adr_trace_dest='/u01/app/oracle/diag/rdbms/ccbdev/ccbdev/trace'

audit_file_dest='/u01/app/oracle/admin/ccbdev/adump'

control_files='/REDO/ccbdev/CCBDEV/controlfile/control01.ctl' # Restore Controlfile

core_dump_dest='/u01/app/oracle/diag/rdbms/ccbdev/ccbdev/cdump'

db_create_file_dest='/DATA/.ACFS/snaps/ccbdev'

db_create_online_log_dest_1='/REDO/ccbdev'

db_flash_cache_file='/dev/null'

db_recovery_file_dest='/RECO/ccbdev'

diagnostic_dest='/u01/app/oracle'

14. Mount source (ccbmstr) database and create controlfile script

sqlplus>startup mount

sqlplus> alter session set tracefile_identifier='ctrlbackup';

sqlplus>alter database backup controlfile to trace;

sqlplus> oradebug setmypid;

sqlplus> oradebug tracefile_name;

15. Copy controlfile script and modify

We can see there are 2 sections of controlfile creation one without resetlogs and another with resetlogs like this

-- Set #1. NORESETLOGS case

-- Set Database Guard and/or Supplemental Logging

-- Set #2. RESETLOGS case

-- Set Database Guard and/or Supplemental Logging

We need to consider resetlogs options.Remove all lines above Set2

Get the line number

grep -n "Set #2." ccbmstr_ora_93333_ctrlbackup.trc

347:-- Set #2. RESETLOGS case

sed -i.bak '1,346d' ccbmstr_ora_93333_ctrlbackup.trc

change below statement from

CREATE CONTROLFILE REUSE DATABASE "CCBMSTR" RESETLOGS FORCE LOGGING ARCHIVELOG

CREATE CONTROLFILE SET DATABASE "CCBDEV" RESETLOGS FORCE LOGGING ARCHIVELOG

Now search and replace ccbmstr and CCBMSTR with ccbdev and CCBDEV respectively in vi editor or using sed

Remove all junk sitting at tail end and execute it.

16. Bring up new db using the pfile and execute above controlfile script

AtVerify all datafiles,controlfiles and resologfiles.

Bounce the database

Verify snapshot storage size now

su - grid -c 'acfsutil snap info /DATA'

snapshot name: ccbmstr

snapshot location: /DATA/.ACFS/snaps/ccbmstr

RO snapshot or RW snapshot: RW

parent name: /DATA

snapshot creation time: Tue Jan 14 13:15:46 2025

file entry table allocation: 8781824 ( 8.38 MB )

storage added to snapshot: 5885567025152 ( 5.35 TB ) Size of ccbmstr db files

snapshot name: ccbdev

snapshot location: /DATA/.ACFS/snaps/ccbdev

RO snapshot or RW snapshot: RW

parent name: ccbmstr

snapshot creation time: Tue Jan 14 15:30:09 2025

file entry table allocation: 8781824 ( 8.38 MB )

storage added to snapshot: 2859307008 ( 2.66 GB ) Size of snapdb ccbdev

number of snapshots: 2

kilosnap state: ENABLED

snapshot space usage: 5890851774464 ( 5.36 TB )

Hope you have learnt something useful...

Explore Oracle Stuff

Friday, January 24, 2025

Manual snap database creation

When SQL plan baseline is not used

Report Abuse

Labels