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
<HOME NAME="OraGrid19000"
LOC="/u01/app/19.0.0.0/grid" TYPE="O" IDX="1"
CRS="true"/>
/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" ?>
<!-- Copyright (c) 1999, 2024, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<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 NAME="OraGrid19000"
LOC="/u01/app/19.0.0.0/grid" TYPE="O" IDX="1"
CRS="true"/>
<HOME
NAME="OraDB19000_home1"
LOC="/u01/app/oracle/product/19.0.0.0/dbhome_1" TYPE="O" IDX="2"/>
</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 &
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;
}
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
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.
Login as sys using sqlplus and use
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 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
To
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
kilosnap state: ENABLED
snapshot space usage: 5890851774464
( 5.36 TB )
