Duplicate target database from active database

1. Исходная промышленная СУБД (SPOD)

$ export ORACLE_SID=spod
$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 1 11:34:02 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oradb/oradata/spod/system/system01.dbf
/u03/app/oradb/oradata/spod/sysaux/sysaux01.dbf
/u03/app/oradb/oradata/spod/undotbs/undotbs01.dbf
/u03/app/oradb/oradata/spod/users/users01.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u03/app/oradb/oradata/spod/temp/temp01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u02/app/oradb/oradata/spod/redoa/redo01.log
/u02/app/oradb/oradata/spod/redoa/redo02.log
/u02/app/oradb/oradata/spod/redoa/redo03.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u03/app/oradb/oradata/spod/control1/control01.ctl
/u03/app/oradb/oradata/spod/control2/control02.ctl

SQL> exit

$ strings spfilespod.ora
spod.__db_cache_size=1409286144
spod.__java_pool_size=16777216
spod.__large_pool_size=16777216
spod.__oracle_base='/u01/app'#ORACLE_BASE set from environment
spod.__pga_aggregate_target=1342177280
spod.__sga_target=2013265920
spod.__shared_io_pool_size=0
spod.__shared_pool_size=520093696
spod.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/admin/spod/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u03/app/oradb/oradata/spod/control1/control01.ctl','/u03/app/oradb/oradata/spod/control2/control02.ctl'
*.db_block_size=8192
*.db_domain='oracle.sun.com'
*.db_name='spod'
*.db_recovery_file_dest='/u01/app/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=spodXDB)'
*.log_archive_dest_1='LOCATION=/u01/app/oradb/archlogs/arch01/spod/'
*.log_archive_dest_2='LOCATION=/u01/app/oradb/archlogs/arch02/spod OPTIONAL'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
$

2. Создаем структуру каталогов для тестовой базы данных (SPODT1)

mkdir -p /u03/app/oradb/oradata/spodt1/system
mkdir -p /u03/app/oradb/oradata/spodt1/sysaux
mkdir -p /u03/app/oradb/oradata/spodt1/undotbs
mkdir -p /u03/app/oradb/oradata/spodt1/users
mkdir -p /u03/app/oradb/oradata/spodt1/temp
mkdir -p /u02/app/oradb/oradata/spodt1/redoa
mkdir -p /u03/app/oradb/oradata/spodt1/control1
mkdir -p /u03/app/oradb/oradata/spodt1/control2

mkdir -p /u01/app/oradb/archlogs/arch01/spodt1
mkdir -p /u01/app/oradb/archlogs/arch02/spodt1


3. Внесем изменения в фалйы: listener.ora и tnsnames.ora

$ more /u01/app/11.2.0.2/grid/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/11.2.0.2/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = spodt1.oracle.sun.com)
(ORACLE_HOME = /u01/app/oradb/product/11.2.0.2/dbhome_1)
(SID_NAME = spodt1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = kr400.oracle.sun.com)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u01/app/oragrid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
$


$ more /u01/app/oradb/product/11.2.0.2/dbhome_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oradb/product/11.2.0.2/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SPOD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kr400.oracle.sun.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = spod.oracle.sun.com)
)
)

SPODT1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kr400.oracle.sun.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = spodt1.oracle.sun.com)
)
)

$


4. Подготовим скрипт makeSPODT1.sh для создания тестовой СУБД (SPODT1)

#!/bin/bash

# Create temporary password file, must have same password as primary
rm /u01/app/oradb/product/11.2.0.2/dbhome_1/dbs/orapwspodt1
orapwd file=/u01/app/oradb/product/11.2.0.2/dbhome_1/dbs/orapwspodt1 password=oracle

# Create temporary parameter file
echo "db_name=spodt1" > /u01/app/oradb/product/11.2.0.2/dbhome_1/dbs/initspodt1.ora

export ORACLE_SID=spodt1
export ORACLE_HOME=/u01/app/oradb/product/11.2.0.2/dbhome_1

# Start up the auxiliary instance nomount
sqlplus '/ as sysdba' << EOF
shutdown immediate;
STARTUP PFILE='/u01/app/oradb/product/11.2.0.2/dbhome_1/dbs/initspodt1.ora' NOMOUNT;
EOF

export ORACLE_SID=spod

rman << EOF
connect target sys/oracle@spod; # Production
connect auxiliary sys/oracle@spodt1; # Пароль должен совпадать с Production

run {
allocate channel C1 type disk;
allocate auxiliary channel DUP type disk;
duplicate target database to SPODT1 from active database
spfile
set db_unique_name='SPODT1'
set db_file_name_convert='/u03/app/oradb/oradata/spod','/u03/app/oradb/oradata/spodt1'
set log_file_name_convert='/u02/app/oradb/oradata/spod','/u02/app/oradb/oradata/spodt1'
set control_files='/u03/app/oradb/oradata/spodt1/control1/control01.ctl','/u03/app/oradb/oradata/spodt1/control2/control02.ctl'
set log_archive_max_processes='5'
set log_archive_dest_1='LOCATION=/u01/app/oradb/archlogs/arch01/spodt1'
set log_archive_dest_2='LOCATION=/u01/app/oradb/archlogs/arch02/spodt1 OPTIONAL'
;
}
EOF
exit 0

5. Выполним этот скрипт :

$ ./makeSPODT1.sh

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 1 12:16:08 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2225064 bytes
Variable Size 159386712 bytes
Database Buffers 50331648 bytes
Redo Buffers 5214208 bytes
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Sep 1 12:16:32 2011

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

RMAN>
connected to target database: SPOD (DBID=1675227431)

RMAN>
connected to auxiliary database: SPODT1 (not mounted)

RMAN>
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=34 device type=DISK

allocated channel: DUP
channel DUP: SID=67 device type=DISK

Starting Duplicate Db at 01-SEP-11

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oradb/product/11.2.0.2/dbhome_1/dbs/spfilespod.ora' auxiliary format
'/u01/app/oradb/product/11.2.0.2/dbhome_1/dbs/spfilespodt1.ora' ;
sql clone "alter system set spfile= ''/u01/app/oradb/product/11.2.0.2/dbhome_1/dbs/spfilespodt1.ora''";
}
executing Memory Script

Starting backup at 01-SEP-11
Finished backup at 01-SEP-11

sql statement: alter system set spfile= ''/u01/app/oradb/product/11.2.0.2/dbhome_1/dbs/spfilespodt1.ora''

contents of Memory Script:
{
sql clone "alter system set db_name =
''SPODT1'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''SPODT1'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/u03/app/oradb/oradata/spod'', ''/u03/app/oradb/oradata/spodt1'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/u02/app/oradb/oradata/spod'', ''/u02/app/oradb/oradata/spodt1'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u03/app/oradb/oradata/spodt1/control1/control01.ctl'', ''/u03/app/oradb/oradata/spodt1/control2/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
5 comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/u01/app/oradb/archlogs/arch01/spodt1'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''LOCATION=/u01/app/oradb/archlogs/arch02/spodt1 OPTIONAL'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_name = ''SPODT1'' comment= ''duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''SPODT1'' comment= '''' scope=spfile

sql statement: alter system set db_file_name_convert = ''/u03/app/oradb/oradata/spod'', ''/u03/app/oradb/oradata/spodt1'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/u02/app/oradb/oradata/spod'', ''/u02/app/oradb/oradata/spodt1'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/u03/app/oradb/oradata/spodt1/control1/control01.ctl'', ''/u03/app/oradb/oradata/spodt1/control2/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u01/app/oradb/archlogs/arch01/spodt1'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_2 = ''LOCATION=/u01/app/oradb/archlogs/arch02/spodt1 OPTIONAL'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2225064 bytes
Variable Size 159386712 bytes
Database Buffers 50331648 bytes
Redo Buffers 5214208 bytes
allocated channel: DUP
channel DUP: SID=156 device type=DISK

contents of Memory Script:
{
sql clone "alter system set db_name =
''SPOD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''SPODT1'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u03/app/oradb/oradata/spodt1/control1/control01.ctl';
restore clone controlfile to '/u03/app/oradb/oradata/spodt1/control2/control02.ctl' from
'/u03/app/oradb/oradata/spodt1/control1/control01.ctl';
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ''SPOD'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''SPODT1'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2225064 bytes
Variable Size 159386712 bytes
Database Buffers 50331648 bytes
Redo Buffers 5214208 bytes
allocated channel: DUP
channel DUP: SID=156 device type=DISK

Starting backup at 01-SEP-11
channel C1: starting datafile copy
copying current control file
output file name=/u03/app/oradb/backups/rman/spod/snapcf_syslog.f tag=TAG20110901T121720 RECID=35 STAMP=760709841
channel C1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-SEP-11

Starting restore at 01-SEP-11

channel DUP: copied control file copy
Finished restore at 01-SEP-11

database mounted

contents of Memory Script:
{
set newname for datafile 1 to
"/u03/app/oradb/oradata/spodt1/system/system01.dbf";
set newname for datafile 2 to
"/u03/app/oradb/oradata/spodt1/sysaux/sysaux01.dbf";
set newname for datafile 3 to
"/u03/app/oradb/oradata/spodt1/undotbs/undotbs01.dbf";
set newname for datafile 4 to
"/u03/app/oradb/oradata/spodt1/users/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u03/app/oradb/oradata/spodt1/system/system01.dbf" datafile
2 auxiliary format
"/u03/app/oradb/oradata/spodt1/sysaux/sysaux01.dbf" datafile
3 auxiliary format
"/u03/app/oradb/oradata/spodt1/undotbs/undotbs01.dbf" datafile
4 auxiliary format
"/u03/app/oradb/oradata/spodt1/users/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 01-SEP-11
channel C1: starting datafile copy
input datafile file number=00001 name=/u03/app/oradb/oradata/spod/system/system01.dbf
output file name=/u03/app/oradb/oradata/spodt1/system/system01.dbf tag=TAG20110901T121727
channel C1: datafile copy complete, elapsed time: 00:00:25
channel C1: starting datafile copy
input datafile file number=00003 name=/u03/app/oradb/oradata/spod/undotbs/undotbs01.dbf
output file name=/u03/app/oradb/oradata/spodt1/undotbs/undotbs01.dbf tag=TAG20110901T121727
channel C1: datafile copy complete, elapsed time: 00:00:25
channel C1: starting datafile copy
input datafile file number=00002 name=/u03/app/oradb/oradata/spod/sysaux/sysaux01.dbf
output file name=/u03/app/oradb/oradata/spodt1/sysaux/sysaux01.dbf tag=TAG20110901T121727
channel C1: datafile copy complete, elapsed time: 00:00:25
channel C1: starting datafile copy
input datafile file number=00004 name=/u03/app/oradb/oradata/spod/users/users01.dbf
output file name=/u03/app/oradb/oradata/spodt1/users/users01.dbf tag=TAG20110901T121727
channel C1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-SEP-11

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/app/oradb/archlogs/arch01/spod/1_183_760029095.dbf" auxiliary format
"/u01/app/oradb/archlogs/arch02/spodt1/1_183_760029095.dbf" archivelog like
"/u01/app/oradb/archlogs/arch02/spod/1_183_760029095.dbf" auxiliary format
"/u01/app/oradb/archlogs/arch02/spodt1/1_183_760029095.dbf" ;
catalog clone archivelog "/u01/app/oradb/archlogs/arch02/spodt1/1_183_760029095.dbf";
catalog clone archivelog "/u01/app/oradb/archlogs/arch02/spodt1/1_183_760029095.dbf";
switch clone datafile all;
}
executing Memory Script

Starting backup at 01-SEP-11
channel C1: starting archived log copy
input archived log thread=1 sequence=183 RECID=187 STAMP=760709925
output file name=/u01/app/oradb/archlogs/arch02/spodt1/1_183_760029095.dbf RECID=0 STAMP=0
channel C1: archived log copy complete, elapsed time: 00:00:01
channel C1: starting archived log copy
input archived log thread=1 sequence=183 RECID=188 STAMP=760709925
output file name=/u01/app/oradb/archlogs/arch02/spodt1/1_183_760029095.dbf RECID=0 STAMP=0
channel C1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 01-SEP-11

cataloged archived log
archived log file name=/u01/app/oradb/archlogs/arch02/spodt1/1_183_760029095.dbf RECID=187 STAMP=760709927

cataloged archived log
archived log file name=/u01/app/oradb/archlogs/arch02/spodt1/1_183_760029095.dbf RECID=188 STAMP=760709928

datafile 1 switched to datafile copy
input datafile copy RECID=35 STAMP=760709928 file name=/u03/app/oradb/oradata/spodt1/system/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=36 STAMP=760709928 file name=/u03/app/oradb/oradata/spodt1/sysaux/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=37 STAMP=760709928 file name=/u03/app/oradb/oradata/spodt1/undotbs/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=38 STAMP=760709928 file name=/u03/app/oradb/oradata/spodt1/users/users01.dbf

contents of Memory Script:
{
set until scn 1868129;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 01-SEP-11

starting media recovery

archived log for thread 1 with sequence 183 is already on disk as file /u01/app/oradb/archlogs/arch02/spodt1/1_183_760029095.dbf
archived log file name=/u01/app/oradb/archlogs/arch02/spodt1/1_183_760029095.dbf thread=1 sequence=183
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-SEP-11
Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2225064 bytes
Variable Size 159386712 bytes
Database Buffers 50331648 bytes
Redo Buffers 5214208 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''SPODT1'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_name = ''SPODT1'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2225064 bytes
Variable Size 159386712 bytes
Database Buffers 50331648 bytes
Redo Buffers 5214208 bytes
allocated channel: DUP
channel DUP: SID=156 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SPODT1" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u02/app/oradb/oradata/spodt1/redoa/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u02/app/oradb/oradata/spodt1/redoa/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u02/app/oradb/oradata/spodt1/redoa/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u03/app/oradb/oradata/spodt1/system/system01.dbf'
CHARACTER SET CL8MSWIN1251


contents of Memory Script:
{
set newname for tempfile 1 to
"/u03/app/oradb/oradata/spodt1/temp/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u03/app/oradb/oradata/spodt1/sysaux/sysaux01.dbf",
"/u03/app/oradb/oradata/spodt1/undotbs/undotbs01.dbf",
"/u03/app/oradb/oradata/spodt1/users/users01.dbf";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u03/app/oradb/oradata/spodt1/temp/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u03/app/oradb/oradata/spodt1/sysaux/sysaux01.dbf RECID=1 STAMP=760709969
cataloged datafile copy
datafile copy file name=/u03/app/oradb/oradata/spodt1/undotbs/undotbs01.dbf RECID=2 STAMP=760709969
cataloged datafile copy
datafile copy file name=/u03/app/oradb/oradata/spodt1/users/users01.dbf RECID=3 STAMP=760709969

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=760709969 file name=/u03/app/oradb/oradata/spodt1/sysaux/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=760709969 file name=/u03/app/oradb/oradata/spodt1/undotbs/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=760709969 file name=/u03/app/oradb/oradata/spodt1/users/users01.dbf

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

database opened
Finished Duplicate Db at 01-SEP-11
released channel: C1
released channel: DUP

RMAN>

Recovery Manager complete.
$

6. Проверяем созданную базу данных (SPODT1)

$ ps -ef|grep pmon
oradb 4747 1 0 12:19 ? 00:00:00 ora_pmon_spodt1
oradb 5705 26526 0 12:29 pts/2 00:00:00 grep pmon
oragrid 14172 1 0 Aug31 ? 00:00:11 asm_pmon_+ASM
oradb 15172 1 0 Aug31 ? 00:00:12 ora_pmon_spod
$

$ export ORACLE_SID=spodt1
$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 1 12:32:10 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oradb/oradata/spodt1/system/system01.dbf
/u03/app/oradb/oradata/spodt1/sysaux/sysaux01.dbf
/u03/app/oradb/oradata/spodt1/undotbs/undotbs01.dbf
/u03/app/oradb/oradata/spodt1/users/users01.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u03/app/oradb/oradata/spodt1/temp/temp01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u02/app/oradb/oradata/spodt1/redoa/redo03.log
/u02/app/oradb/oradata/spodt1/redoa/redo02.log
/u02/app/oradb/oradata/spodt1/redoa/redo01.log


SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u03/app/oradb/oradata/spodt1/control1/control01.ctl
/u03/app/oradb/oradata/spodt1/control2/control02.ctl

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$