Current Database
Name: test
Current DB location:
/u02/test/
New Database Name:
valent
New DB location:
/u02/valent/
Step 1: Get the below details from the database.
Names and Locations of below files:
- Data files
- Redo Log Files
- Control files
- Temp Files
SQL>
select * from v$version;
BANNER
--------------------------------------------------------------------
Oracle
Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL
Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS
for Linux: Version 11.2.0.4.0 - Production
NLSRTL
Version 11.2.0.4.0 - Production
SQL>
select name,database_role,open_mode,log_mode from v$database;
NAME
DATABASE_ROLE OPEN_MODE LOG_MODE
---------
---------------- -------------------- ------------
TEST PRIMARY READ
WRITE NOARCHIVELOG
SQL> col name for
a28
SQL> col member
for a28
SQL> select name
from v$datafile;
NAME
----------------------------
/u02/test/system01.dbf
/u02/test/sysaux01.dbf
/u02/test/undotbs01.dbf
/u02/test/users01.dbf
/u02/test/abc01.txt
SQL> select name
from v$tempfile;
NAME
----------------------------
/u02/test/temp01.dbf
SQL> select
member from v$logfile;
MEMBER
----------------------------
/u02/test/redo03.log
/u02/test/redo02.log
/u02/test/redo01.log
/u02/test/redo01a.log
/u02/test/redo01b.log
SQL> select name
from v$controlfile;
NAME
----------------------------
/u02/test/control01.ctl
Step 2: Shutdown
the database with immediate option.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut
down.
SQL>
This is the proper consistent backup with shutdown immediate
option.
Step 3: Copy parameter file from old one to new one and change below details for new database. Also, verify the details after making changes.
- db_name
- control_files
[oracle@dc dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@dc dbs]$ ls -ltr inittest.ora
-rw-r--r--. 1 oracle
oinstall 155 Feb 14 17:28 inittest.ora
[oracle@dc dbs]$ cat inittest.ora
*.compatible='11.2.0.4.0'
*.control_files='/u02/test/control01.ctl'
*.db_name='test'
[oracle@dc dbs]$ cp inittest.ora initvalentin.ora
[oracle@dc dbs]$ vi
initvalentin.ora
[oracle@dc dbs]$ cat initvalentin.ora
*.compatible='11.2.0.4.0'
*.control_files='/u02/valentine/control01.ctl'
*.db_name='valentin'
Step 4: Create SQL script for creating control file. Change below parameters with new locations.
- db_name
- logfile
- datafile
- character set
[oracle@dc dbs]$ cat
con.sql
CREATE CONTROLFILE SET DATABASE "valentin" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 3
MAXDATAFILES 32
MAXINSTANCES 1
MAXLOGHISTORY 449
LOGFILE
GROUP 1
('/u02/valentine/redo01.log','/u02/valentine/redo01a.log','/u02/valentine/redo01b.log')
SIZE 50m,
GROUP 2
('/u02/valentine/redo02.log') SIZE 50m,
GROUP 3
('/u02/valentine/redo03.log') SIZE 50m
DATAFILE
'/u02/valentine/system01.dbf',
'/u02/valentine/sysaux01.dbf',
'/u02/valentine/undotbs01.dbf',
'/u02/valentine/users01.dbf',
'/u02/valentine/abc01.txt'
CHARACTER SET
WE8MSWIN1252
;
where;
SET/REUSE DATABASE:
SET DATABASE: It is used to change the database name which can be maximum up to
eight bytes. OR
REUSE DATABASE: It is used to indicate that existing control files identified by
the CONTROL_FILES parameter can
be reused and it overwrites any information they may currently contain. If you
omit this clause and any of these control files already exists, then Oracle
Database returns an error.
RESETLOGS/NORESETLOGS:
RESETLOGS: Use this option if you want Oracle Database to ignore the
contents of the redo log files listed in the
LOGFILE clause. These files do not have to exist. After using this clause,
you must open the database using the RESETLOGS clause. It is used for creating new
database.
NORESETLOGS: Use this option if you want Oracle Database to use all redo log files
in the
LOGFILE clause as they
were when the database was last open. These files must exist and must be the
current online redo log files rather than restored backups. The database
reassigns the redo log file groups to the threads to which they were previously
assigned and re-enables the threads as they were previously enabled.
Step 5: Copy below Oracle Database
Files from old location to new location.
[oracle@dc
valentine]$ df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_dc-lv_root 26G
12G 13G 50% /
[oracle@dc valentine]$ cd $ORACLE_HOME/dbs
[oracle@dc dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@dc dbs]$ cd /u02/test/
[oracle@dc
test]$ ls -tlr
total
6089608
-rw-r-----.
1 oracle oinstall 9748480 Jan 30 16:57
bkp_control02.ctl
-rw-r-----.
1 oracle oinstall 13352960 Feb 1 18:11 stand.ctl
-rw-r-----.
1 oracle oinstall 52429312 Feb 14 17:38
redo01.log
-rw-r-----.
1 oracle oinstall 52429312 Feb 14 17:38
redo01a.log
-rw-r-----.
1 oracle oinstall 52429312 Feb 14 17:38
redo01b.log
-rw-r-----.
1 oracle oinstall 52429312 Feb 14 17:38
redo02.log
-rw-r-----.
1 oracle oinstall 387981312 Feb 14 17:40
temp01.dbf
-rw-r-----.
1 oracle oinstall 52429312 Feb 14 17:40
redo03.log
-rw-r-----.
1 oracle oinstall 775954432 Feb 14 17:40
system01.dbf
-rw-r-----.
1 oracle oinstall 534781952 Feb 14 17:40
sysaux01.dbf
-rw-r-----.
1 oracle oinstall 2165317632 Feb 14 17:40 undotbs01.dbf
-rw-r-----.
1 oracle oinstall 1973952512 Feb 14 17:40 users01.dbf
-rw-r-----.
1 oracle oinstall 104865792 Feb 14 17:40
abc01.txt
-rw-r-----.
1 oracle oinstall 9748480 Feb 14 17:40
control01.ctl
[oracle@dc
test]$
[oracle@dc test]$ cp *.dbf
*.ctl *.log *.txt /u02/valentine/
[oracle@dc
test]$
[oracle@dc test]$ pwd
/u02/test
[oracle@dc
test]$ cd /u02/valentine/
[oracle@dc
valentine]$ ls -ltr
total
6091680
-rw-r-----.
1 oracle oinstall 534781952 Feb 14 17:51
sysaux01.dbf
-rw-r-----.
1 oracle oinstall 775954432 Feb 14 17:51
system01.dbf
-rw-r-----.
1 oracle oinstall 387981312 Feb 14 17:51
temp01.dbf
-rw-r-----.
1 oracle oinstall 2165317632 Feb 14 17:51 undotbs01.dbf
-rw-r-----.
1 oracle oinstall 1973952512 Feb 14 17:51 users01.dbf
-rw-r-----.
1 oracle oinstall 9748480 Feb 14 17:51
bkp_control02.ctl
-rw-r-----.
1 oracle oinstall 9748480 Feb 14 17:51
control01.ctl
-rw-r-----.
1 oracle oinstall 13352960 Feb 14 17:51
stand.ctl
-rw-r-----.
1 oracle oinstall 52429312 Feb 14 17:51
redo01a.log
-rw-r-----.
1 oracle oinstall 52429312 Feb 14 17:51
redo01b.log
-rw-r-----.
1 oracle oinstall 52429312 Feb 14 17:51
redo01.log
-rw-r-----.
1 oracle oinstall 52429312 Feb 14 17:51
redo02.log
-rw-r-----.
1 oracle oinstall 52429312 Feb 14 17:51
redo03.log
-rw-r-----.
1 oracle oinstall 104865792 Feb 14 17:51
abc01.txt
[oracle@dc
valentine]$
Step 6: Source
the environment variables and login to database as sysdba. Also, startup the
database in nomount mode and run
create control file script “con.sql”.
[oracle@dc dbs]$ export ORACLE_SID=valentin
[oracle@dc dbs]$ sqlplus / as sysdba
SQL*Plus: Release
11.2.0.4.0 Production on Wed Feb 14 17:55:48 2018
Copyright (c) 1982,
2013, Oracle. All rights reserved.
Connected to an idle
instance.
SQL> startup
nomount
ORACLE instance
started.
Total System Global
Area 217157632 bytes
Fixed Size
2251816 bytes
Variable Size
159384536 bytes
Database Buffers
50331648 bytes
Redo Buffers
5189632 bytes
SQL>
If you receive below error message while
creating controlfile then move your controlfile with different name or remove
it.
SQL> @con.sql
CREATE CONTROLFILE
SET DATABASE "valentin" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE
CONTROLFILE failed
ORA-00200: control
file could not be created
ORA-00202: control
file: '/u02/valentine/control01.ctl'
ORA-27038: created
file already exists
Additional
information: 1
SQL> @con.sql
Control file created.
Step 7: Verify
below file locations in a newly created database.
SQL> set lines
300 pages 3000
SQL> col name for
a28
SQL> col member
for a28
SQL> select name
from v$datafile;
NAME
----------------------------
/u02/valentine/system01.dbf
/u02/valentine/sysaux01.dbf
/u02/valentine/undotbs01.dbf
/u02/valentine/users01.dbf
/u02/valentine/abc01.txt
SQL> select name
from v$tempfile;
no rows selected
SQL> select
member from v$logfile;
MEMBER
----------------------------
/u02/valentine/redo03.log
/u02/valentine/redo02.log
/u02/valentine/redo01.log
/u02/valentine/redo01a.log
/u02/valentine/redo01b.log
SQL> select name
from v$controlfile;
NAME
----------------------------
/u01/valentine/control01.ctl
Step 8: Check the
database mode and resetlog details.
SQL> select
name,open_mode from v$database;
NAME
OPEN_MODE
----------------------------
--------------------
VALENTIN
MOUNTED
SQL> select
file#,status,error,recover,RESETLOGS_CHANGE#,RESETLOGS_TIME,CHECKPOINT_CHANGE#,CHECKPOINT_TIME
from v$datafile_header;
FILE# STATUS ERROR
REC RESETLOGS_CHANGE# RESETLOGS CHECKPOINT_CHANGE# CHECKPOIN
---------- -------
---------- --- ----------------- --------- ------------------ ---------
1
ONLINE 925702 18-JAN-18 1246166 14-FEB-18
2
ONLINE 925702 18-JAN-18 1246166 14-FEB-18
3
ONLINE 925702 18-JAN-18 1246166 14-FEB-18
4
ONLINE 925702 18-JAN-18 1246166 14-FEB-18
5
ONLINE 925702 18-JAN-18 1246166 14-FEB-18
Step 9: As you
used option “RESETLOGS” in create control file script, you need to open your
database using resetlog option. If
you try to open it in noresetlog
option then you will receive an error message.
SQL> alter database open resetlogs;
Database altered.
SQL> select
name,open_mode from v$database;
NAME
OPEN_MODE
----------------------------
--------------------
VALENTIN
READ WRITE
Alert Log File
Details=>
Wed
Feb 14 17:58:59 2018
CREATE
CONTROLFILE SET DATABASE "valentin" RESETLOGS NOARCHIVELOG
MAXLOGFILES
32
MAXLOGMEMBERS
3
MAXDATAFILES
32
MAXINSTANCES
1
MAXLOGHISTORY
449
LOGFILE
GROUP
1 ('/u02/valentine/redo01.log','/u02/valentine/redo01a.log','/u02/valentine/redo01b.log')
SIZE 50m,
GROUP
2 ('/u02/valentine/redo02.log') SIZE 50m,
GROUP
3 ('/u02/valentine/redo03.log') SIZE 50m
DATAFILE
'/u02/valentine/system01.dbf',
'/u02/valentine/sysaux01.dbf',
'/u02/valentine/undotbs01.dbf',
'/u02/valentine/users01.dbf',
'/u02/valentine/abc01.txt'
CHARACTER
SET WE8MSWIN1252
WARNING:
Default Temporary Tablespace not specified in CREATE DATABASE command
Default
Temporary Tablespace will be necessary for a locally managed database in future
release
Wed
Feb 14 17:59:00 2018
Successful
mount of redo thread 1, with mount id 1700570851
Completed:
CREATE CONTROLFILE SET DATABASE "valentin" RESETLOGS NOARCHIVELOG
MAXLOGFILES
32
MAXLOGMEMBERS
3
MAXDATAFILES
32
MAXINSTANCES
1
MAXLOGHISTORY
449
LOGFILE
GROUP
1
('/u02/valentine/redo01.log','/u02/valentine/redo01a.log','/u02/valentine/redo01b.log')
SIZE 50m,
GROUP
2 ('/u02/valentine/redo02.log') SIZE 50m,
GROUP
3 ('/u02/valentine/redo03.log') SIZE 50m
DATAFILE
'/u02/valentine/system01.dbf',
'/u02/valentine/sysaux01.dbf',
'/u02/valentine/undotbs01.dbf',
'/u02/valentine/users01.dbf',
'/u02/valentine/abc01.txt'
CHARACTER
SET WE8MSWIN1252
Wed
Feb 14 18:03:34 2018
alter
database open
Errors
in file /u01/app/oracle/diag/rdbms/valentin/valentin/trace/valentin_ora_6109.trc:
ORA-01589:
must use RESETLOGS or NORESETLOGS option for database open
ORA-1589
signalled during: alter database open...
Wed
Feb 14 18:03:44 2018
alter
database open noresetlogs
ORA-1588
signalled during: alter database open noresetlogs...
alter
database open resetlogs
RESETLOGS
after incomplete recovery UNTIL CHANGE 1246166
Clearing
online redo logfile 1 /u02/valentine/redo01.log
Clearing
online log 1 of thread 1 sequence number 0
Clearing
online redo logfile 1 complete
Clearing
online redo logfile 2 /u02/valentine/redo02.log
Clearing
online log 2 of thread 1 sequence number 0
Clearing
online redo logfile 2 complete
Clearing
online redo logfile 3 /u02/valentine/redo03.log
Clearing
online log 3 of thread 1 sequence number 0
Clearing
online redo logfile 3 complete
Online
log /u02/valentine/redo01.log: Thread 1 Group 1 was previously cleared
Online
log /u02/valentine/redo01a.log: Thread 1 Group 1 was previously cleared
Online
log /u02/valentine/redo01b.log: Thread 1 Group 1 was previously cleared
Online
log /u02/valentine/redo02.log: Thread 1 Group 2 was previously cleared
Online
log /u02/valentine/redo03.log: Thread 1 Group 3 was previously cleared
Wed
Feb 14 18:03:54 2018
Setting
recovery target incarnation to 2
Initializing
SCN for created control file
Database
SCN compatibility initialized to 1
Wed
Feb 14 18:03:54 2018
Assigning
activation ID 1700570851 (0x655ca6e3)
Thread
1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0:
/u02/valentine/redo01.log
Current log# 1 seq# 1 mem# 1:
/u02/valentine/redo01a.log
Current log# 1 seq# 1 mem# 2:
/u02/valentine/redo01b.log
Successful
open of redo thread 1
MTTR
advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed
Feb 14 18:03:54 2018
SMON:
enabling cache recovery
[6109]
Successfully onlined Undo Tablespace 2.
Undo
initialization finished serial:0 start:1859244 end:1859414 diff:170 (1 seconds)
Dictionary
check beginning
Tablespace
'TEMP' #3 found in data dictionary,
but
not in the controlfile. Adding to controlfile.
Dictionary
check complete
Verifying
file header compatibility for 11g tablespace encryption..
Verifying
11g file header compatibility for tablespace encryption completed
SMON:
enabling tx recovery
*********************************************************************
WARNING:
The following temporary tablespaces contain no files.
This condition can occur when a backup
controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE
<tablespace_name> ADD TEMPFILE
Alternatively, if these temporary
tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database
Characterset is WE8MSWIN1252
Create
Relation IPS_PACKAGE_UNPACK_HISTORY
No
Resource Manager plan active
Wed
Feb 14 18:03:55 2018
replication_dependency_tracking
turned off (no async multimaster replication found)
Starting
background process QMNC
Wed
Feb 14 18:03:55 2018
QMNC
started with pid=18, OS id=6252
LOGSTDBY:
Validating controlfile with logical metadata
LOGSTDBY:
Validation complete
Global Name changed to
VALENTIN
Completed: alter database
open resetlogs
Wed
Feb 14 18:03:56 2018
Starting
background process CJQ0
Wed
Feb 14 18:03:56 2018
CJQ0
started with pid=21, OS id=6266
Step 10: Add temp
file to existing temporary tablespace in a newly created database with REUSE option.
SQL> ALTER
TABLESPACE TEMP ADD TEMPFILE '/u02/test/temp01.dbf' size 370m reuse;
Tablespace altered.
SQL> select name
from v$tempfile;
NAME
--------------------------------------------------------------
/u02/test/temp01.dbf
OR
Use below procedure to create new temporary tablespace:
- Create new with different name
- Mark new one as default one.
- Check any running active sessions using existing TEMP tablespace space. If YES then kill that session.
- Drop existing one.
SQL> create
temporary tablespace TEMP tempfile '/u02/valentine/temp01.dbf' size 200m;
create temporary
tablespace TEMP tempfile '/u02/valentine/temp01.dbf' size 200m
*
ERROR at line 1:
ORA-01543:
tablespace 'TEMP' already exists
SQL> select name
from v$tempfile;
no rows selected
SQL> alter
tablespace TEMP add tempfile '/u02/valentine/temp01.dbf' size 200m;
alter tablespace
TEMP add tempfile '/u02/valentine/temp01.dbf' size 200m
*
ERROR at line 1:
ORA-01119: error in
creating database file '/u02/valentine/temp01.dbf'
ORA-27038: created
file already exists
Additional
information: 1
SQL> create
temporary tablespace TEMP_NEW tempfile '/u02/valentine/temp_new01.dbf' size
200m;
Tablespace created.
SQL> ALTER
DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;
Database altered.
SQL> col username
for a12
SQL> col
tablespace for a10
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser,a.status
FROM v$session
a,v$sort_usage b
WHERE a.saddr =
b.session_addr; 2 3
4
TABLESPACE SEGFILE#
SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
----------
---------- ---------- ---------- ---------- ---------- ------------
------------------------------ --------
TEMP
201 128 128 22 59
SYS
oracle ACTIVE
SQL> alter system
kill session '22,59' immediate;
System altered.
SQL> drop
tablespace temp including contents and datafiles;
Tablespace dropped.
SQL> select name from
v$tempfile;
NAME
--------------------------------------------------------------
/u02/valentine/temp_new01.dbf
Thanks for reading this post ! Please comment if you like this post !
Thank you for your comment !