I wish to create details steps " how to clone database without using RMAN in hotbackup mode"
SQL> conn tom/tom
Connected.
SQL> select * from tab;
A TABLE
B TABLE
SQL> select * from b;
8
8
8
8
8
SQL>
1. Make sure the database is in archive log mode
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/jetway/jatway/arch
Oldest online log sequence 40
Next log sequence to archive 42
Current log sequence 42
2. Make a note of the current archive log change number
Because the restored files will require recovery, some archive logs will be needed.
This applies even if you are not intending to put the cloned database into archive log mode.
Work out which will be the first required log by running the following query on the source database.
Make a note of the change number that is returned:
SQL> select max(first_change#) Latest_chng from v$archived_log;
LATEST_CHNG
-----------
1134000
3. View the current location of the data files
SQL> Select tablespace_name, file_name from dba_data_files order by 1;
SAMTBS1 /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SYSAUX /u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/jetway/jatway/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
USERS /u01/app/oracle/oradata/jetway/jatway/users01.db
4. Check whether the data files are in backup mode or not
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
NAME FILE# STATUS
------------------------------ ---------- ------------------
SYSTEM 1 NOT ACTIVE
SYSAUX 2 NOT ACTIVE
UNDOTBS1 3 NOT ACTIVE
USERS 4 NOT ACTIVE
SAMTBS1 5 NOT ACTIVE
The above result shows that the data files are not in backup mode
5. Generate a script to bring the data file in backup mode using the following command:
SQL> !pwd
/home/oracle
[oracle@sfo ~]$ cd /home/oracle/
[oracle@sfo ~]$ vi enable_hotbackup_mode.sql
set lines 999 pages 999
set verify off
set feedback off
set heading off
spool begin_backup.sql
select 'alter tablespace ' || tablespace_name || ' begin backup;' tsb_backup_mode
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
spool off
SQL> @enable_hotbackup_mode.sql
alter tablespace SAMTBS1 begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace SYSTEM begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace USERS begin backup;
(or) -- if you do not want a particular tablespace in backup mode,
the entire database can be brought to backup mode using the following command
SQL>alter database begin backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 ACTIVE
SYSAUX 2 ACTIVE
UNDOTBS1 3 ACTIVE
USERS 4 ACTIVE
SAMTBS1 5 ACTIVE
The above result shows all the tablespaces with backup mode enabled
6. Check the database using server parameter file
SQL> show parameter spfile;
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilejatway.ora
7. The below steps helps to bring individual tablespaces out of backup mode
SQL> alter tablespace SAMTBS1 end backup;
a)
SQL> alter tablespace SAMTBS1 end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 ACTIVE
SYSAUX 2 ACTIVE
UNDOTBS1 3 ACTIVE
USERS 4 ACTIVE
SAMTBS1 5 NOT ACTIVE
b)
SQL> alter tablespace SYSTEM end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 NOT ACTIVE
SYSAUX 2 ACTIVE
UNDOTBS1 3 ACTIVE
USERS 4 ACTIVE
SAMTBS1 5 NOT ACTIVE
c)
SQL> alter tablespace SYSAUX end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 NOT ACTIVE
SYSAUX 2 NOT ACTIVE
UNDOTBS1 3 ACTIVE
USERS 4 ACTIVE
SAMTBS1 5 NOT ACTIVE
d)
SQL> alter tablespace UNDOTBS1 end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 NOT ACTIVE
SYSAUX 2 NOT ACTIVE
UNDOTBS1 3 NOT ACTIVE
USERS 4 ACTIVE
SAMTBS1 5 NOT ACTIVE
e)
SQL> alter tablespace USERS end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 NOT ACTIVE
SYSAUX 2 NOT ACTIVE
UNDOTBS1 3 NOT ACTIVE
USERS 4 NOT ACTIVE
SAMTBS1 5 NOT ACTIVE
8. The following query can also be used to find whether the tablespaces is in backup mode or not
SQL> Select * from v$backup;
1 NOT ACTIVE 1179330 25-SEP-12
2 NOT ACTIVE 1179330 25-SEP-12
3 NOT ACTIVE 1179330 25-SEP-12
4 NOT ACTIVE 1179330 25-SEP-12
5 NOT ACTIVE 1179330 25-SEP-12
9. Copy the following tablespaces to the clone location (except the temp tablespace)
I am moving all the files to the "backup" folder
SQL> Select tablespace_name, file_name from dba_data_files order by 1;
SAMTBS1 /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SYSAUX /u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/jetway/jatway/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
USERS /u01/app/oracle/oradata/jetway/jatway/users01.db
[oracle@sfo backup]$ pwd
/u01/app/oracle/oradata/jetway/jatway/backup
[oracle@sfo backup]$ ls
initclohot.ora samtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
10. Create the TAR file of the copied tablesapces. To avoid future errors I am creating another
copy of the tablespace backup folder and moving across the clone location
[oracle@sfo jatway]$ cp -r backup backup01
[oracle@sfo jatway]$ ls
arch backup backup01 backupinitclohot.ora control01.ctl redo01.log redo02.log redo03.log redo04.log redo05.log redo06.log redo07.log samtbs01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@sfo jatway]$ tar -cvzf backup.tar.gz backup01
backup01/
backup01/system01.dbf
backup01/control01.ora
backup01/sysaux01.dbf
backup01/initclohot.ora
backup01/samtbs01.dbf
backup01/undotbs01.dbf
backup01/users01.dbf
backup01/1_41_793069279.dbf
[oracle@sfo jatway]$
[oracle@sfo jatway]$ ls
arch backup01 backup.tar.gz redo01.log redo03.log redo05.log redo07.log sysaux01.dbf temp01.dbf users01.dbf
backup backupinitclohot.ora control01.ctl redo02.log redo04.log redo06.log samtbs01.dbf system01.dbf undotbs01.dbf
[oracle@sfo jatway]$ scp backup.tar.gz oracle@192.168.168.1:/u01/app/oracle/oradata
oracle@192.168.168.1's password:
backup.tar.gz 100% 245MB 17.5MB/s 00:14
100% 245MB 17.5MB/s 00:14
11. move the password file for the clone database across network
[oracle@sfo jatway]$
[oracle@sfo jatway]$ cd $ORACLE_HOME/dbs
[oracle@sfo dbs]$ ls orapw*
orapwdeccan orapwdelta orapwjatway
[oracle@sfo dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@sfo dbs]$ scp orapwjatway oracle@192.168.168.1:/u01/app/oracle/product/11.2.0/db_1/dbs
oracle@192.168.168.1's password:
orapwjatway 100% 1536 1.5KB/s 00:00 100% 1536 1.5KB/s 00:00
[oracle@sfo dbs]$
[oracle@sfo backup]$
12. Create a backup control file for the clone location and move across the clone location.
SQL> show parameter control
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/jetway
/jatway/control01.ctl, /u01/ap
p/oracle/flash_recovery_area/j
atway/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>Alter database backup controlfile to trace as '/u01/app/oracle/oradata/jetway/jatway/backup/control01.ora';
[oracle@sfo backup]$ pwd
/u01/app/oracle/oradata/jetway/jatway/backup
[oracle@sfo backup]$ ls
control01.ora initclohot.ora samtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
[oracle@sfo backup]$
oracle@sfo dbs]$ scp control01.ora oracle@192.168.168.1:/u01/app/oracle/oradata/backup/control01.ora
oracle@192.168.168.1's password:
control01.ora 100% 1536 1.5KB/s 00:00
13. Copy archive logs for clone database
It is only necessary to copy archive logs created during the time the source database was in backup mode.
Begin by archiving the current redo: floow the step 2 to get archive log current.
SQL>alter system archive log current;
select name from v$archived_log where first_change# >= &change_no
order by name
/
Enter value for change_no: 1134000
/u01/app/oracle/oradata/jetway/jatway/arch/1_41_793069279.dbf
14. In the clone database location (name of the clone database is "clohot") and UNTAR the zip file
[oracle@nyc oradata]$cd /u01/app/oracle/oradata
[oracle@nyc oradata]$ ls
backup.tar.gz
[oracle@nyc oradata]$ ls
backup.tar.gz
[oracle@nyc oradata]$ tar -xvzf backup.tar.gz
backup01/
backup01/system01.dbf
backup01/control01.ora
backup01/sysaux01.dbf
backup01/initclohot.ora
backup01/samtbs01.dbf
backup01/undotbs01.dbf
backup01/users01.dbf
backup01/1_41_793069279.dbf
[oracle@nyc oradata]$
[oracle@nyc oradata]$ mv backup01 clohot/
[oracle@nyc oradata]$ ls -lrt
total 250916
drwxr-xr-x 4 oracle oinstall 4096 Sep 2 23:29 delta
drwxr-xr-x 3 oracle oinstall 4096 Sep 9 05:48 deccan
-rw-r--r-- 1 oracle oinstall 256665891 Sep 25 05:11 backup.tar.gz
drwxr-xr-x 4 oracle oinstall 4096 Sep 25 09:35 clohot
[oracle@nyc oradata]$ cd clohot/
[oracle@nyc clohot]$ ls -lrt
total 8
drwxr-xr-x 2 oracle oinstall 4096 Sep 25 05:06 backup01
drwxr-xr-x 2 oracle oinstall 4096 Sep 25 09:33 arch
[oracle@nyc clohot]$
15. Prepare the init parameter file for clone database("clohot")
Actul init parameter file
*.audit_file_dest='/u01/app/oracle/admin/jatway/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/jetway/jatway/control01.ctl','/u01/app/oracle/flash_recovery_area/jatway/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.db_name='jatway'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='jatway'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jatwayXDB)'
*.fal_client='delta'
*.fal_server='deccan'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(jatway,deccan)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jatway'
*.LOG_ARCHIVE_DEST_2='SERVICE=deccan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=deccan'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.log_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
~
(note: My source database has dataguard so i am just database cone with archivelog and flashback enabled)
create the required dicectory as mentined in the parameter file
[oracle@nyc backup01]$ mkdir -p /u01/app/oracle/admin/clohot/adump
[oracle@nyc backup01]$ mkdir -p /u01/app/oracle/oradata/clohot
16. Modify the parameter as per the clone database base directory setup and add entry in /etc/oratab
[oracle@nyc backup01]$ cat initclohot.ora
*.audit_file_dest='/u01/app/oracle/admin/clohot/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/clohot/control01.ctl'
*.db_block_size=8192
*.db_domain=''
#*.db_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.db_name='clohot'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
#*.db_unique_name='jatway'
*.diagnostic_dest='/u01/app/oracle'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=jatwayXDB)'
#*.fal_client='delta'
#*.fal_server='deccan'
#*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(jatway,deccan)'
#*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jatway'
#*.LOG_ARCHIVE_DEST_2='SERVICE=deccan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=deccan'
#*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
#*.log_archive_dest_state_2='ENABLE'
#*.log_archive_format='%t_%s_%r.dbf'
#*.LOG_ARCHIVE_MAX_PROCESSES=30
#*.log_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
#*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
~
(Note: change the db_domain, db_unique_name,)
[oracle@nyc clohot]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
17. Add entry in /etc/oratab
[oracle@nyc dbs]$ vi /etc/oratab
clohot:/u01/app/oracle/product/11.2.0/db_1:N
[oracle@nyc flash_recovery_area]$ . oraenv
ORACLE_SID = [oracle] ? clohot
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@nyc flash_recovery_area]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 25 10:27:57 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1235959808 bytes
Fixed Size 2212896 bytes
Variable Size 738200544 bytes
Database Buffers 486539264 bytes
Redo Buffers 9007104 bytes
SQL>
18. Create the control file script.
actual control file look like this---
[oracle@nyc clohot]$ cat control01.ora
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="jatway"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE'
-- LOG_ARCHIVE_CONFIG='DG_CONFIG=("deccan")'
-- LOG_ARCHIVE_MAX_PROCESSES=30
-- STANDBY_FILE_MANAGEMENT=auto
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=delta
-- FAL_SERVER=deccan
--
-- LOG_ARCHIVE_DEST_2='SERVICE=deccan'
-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_2='LGWR NOAFFIRM NOEXPEDITE NOVERIFY ASYNC=61440'
-- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_2='DB_UNIQUE_NAME=deccan'
-- LOG_ARCHIVE_DEST_2='VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
-- LOG_ARCHIVE_DEST_STATE_2=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_1='DB_UNIQUE_NAME=jatway'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JATWAY" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/jetway/jatway/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/jetway/jatway/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/jetway/jatway/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
-- GROUP 4 '/u01/app/oracle/oradata/jetway/jatway/redo04.log' SIZE 50M BLOCKSIZE 512,
-- GROUP 5 '/u01/app/oracle/oradata/jetway/jatway/redo05.log' SIZE 50M BLOCKSIZE 512,
-- GROUP 6 '/u01/app/oracle/oradata/jetway/jatway/redo06.log' SIZE 50M BLOCKSIZE 512,
-- GROUP 7 '/u01/app/oracle/oradata/jetway/jatway/redo07.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/jetway/jatway/system01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/users01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT ''/u01/app/oracle/oradata/jetway/jatway/backup/%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_694916203.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_793069279.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/jetway/jatway/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo04.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo05.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo06.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo07.log'
SIZE 50M BLOCKSIZE 512 REUSE;
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JATWAY" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/jetway/jatway/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/jetway/jatway/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/jetway/jatway/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/jetway/jatway/system01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/users01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT ''/u01/app/oracle/oradata/jetway/jatway/backup/%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_694916203.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_793069279.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Create log files for threads other than thread one.
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/jetway/jatway/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo04.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo05.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo06.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo07.log'
SIZE 50M BLOCKSIZE 512 REUSE;
19. using following points you can create the control file script
The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:
Remove any lines that start with '#', '--',
Remove any blank lines in the 'CREATE CONTROLFILE' section and 'RECOVER DATABASE USING BACKUP CONTROLFILE' and 'ALTER DATABASE OPEN RESETLOGS;'
Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file.
Make sure that you hang onto the command, it will be used later.
The word 'REUSE' needs to be changed to 'SET' and 'NORESETLOGS' changed to 'RESETLOGS'
The database name needs setting to the new database name ("CLOHOT") (if it is being changed).
Decide whether the database will be put into archivelog mode or not. IF YES USE "ARCHIVELOG", IF NO USE "NOARCHIVELOG"
If the file paths are being changed, alter the file to reflect the changes.
20. changes are made as per the requirement
[oracle@nyc clohot]$ cat control01.ora
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CLOHOT" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/clohot/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/clohot/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/clohot/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/clohot/system01.dbf',
'/u01/app/oracle/oradata/clohot/sysaux01.dbf',
'/u01/app/oracle/oradata/clohot/undotbs01.dbf',
'/u01/app/oracle/oradata/clohot/users01.dbf',
'/u01/app/oracle/oradata/clohot/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> @/u01/app/oracle/oradata/clohot/contro1_create.sql
ORACLE instance started.
Total System Global Area 1235959808 bytes
Fixed Size 2212896 bytes
Variable Size 738200544 bytes
Database Buffers 486539264 bytes
Redo Buffers 9007104 bytes
Control file created
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
21. now apply the archivelog to perfornm the recovery.
SQL> recover database using backup controlfile until cancel;
(note: The above show the suggested archive log so you need to give full path of the arhivelog)
ORA-00279: change 1179330 generated at 09/25/2012 04:35:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_42_%u_.arc
ORA-00280: change 1179330 for thread 1 is in sequence #42
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
SQL> RECOVER DATABASE using backup controlfile until cancel;
ORA-00279: change 1179330 generated at 09/25/2012 04:35:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf
ORA-00280: change 1179330 for thread 1 is in sequence #42
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf
ORA-00279: change 1196618 generated at 09/25/2012 09:00:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_43_%u_.arc
ORA-00280: change 1196618 for thread 1 is in sequence #43
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf'
no longer needed for this recovery
(note: archivelog "1_42*" applied now it required "1_43*"
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> RECOVER DATABASE using backup controlfile until cancel;
ORA-00279: change 1196618 generated at 09/25/2012 09:00:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_43_%u_.arc
ORA-00280: change 1196618 for thread 1 is in sequence #43
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
22. Now open the database with resetlog.
(if archivelog not applied preperly it thow throw the error when open the database
Re: ORA-01195: online backup of file 1 needs more recovery to be consistent )
SQL> alter database open resetlogs;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
23. create spfile from pfile and shutdown and startup the database toapply the change.
SQL> create spfile from pfile;
File created.
24. create the temporary table for the clone database
CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/u01/app/oracle/oradata/clohot/temp_01.tmp' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
SQL> select NAME, TS# from v$TEMPFILE ;
NAME TS#
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/clohot/temp_01.tmp 7
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;
Database altered.
SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP01 4194304 16777216
SQL>shutdown immdiate
25. change the database ID (DBID) if we clone tha database using rman it will change the DBID automatically.
due to the manual clone we need to change the DBID using "nid utility"
SQL> startup mount
ORACLE instance started.
Total System Global Area 1235959808 bytes
Fixed Size 2212896 bytes
Variable Size 738200544 bytes
Database Buffers 486539264 bytes
Redo Buffers 9007104 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@nyc flash_recovery_area]$ nid target=/
DBNEWID: Release 11.2.0.1.0 - Production on Tue Sep 25 12:16:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database CLOHOT (DBID=3196377116)
Connected to server version 11.2.0
Control Files in database:
/u01/app/oracle/oradata/clohot/control01.ctl
Change database ID of database CLOHOT? (Y/[N]) => y (note: here we have to give to give "Y" to change the DBID)
Proceeding with operation
Changing database ID from 3196377116 to 2915369990
Control File /u01/app/oracle/oradata/clohot/control01.ctl - modified
Datafile /u01/app/oracle/oradata/clohot/system01.db - dbid changed
Datafile /u01/app/oracle/oradata/clohot/sysaux01.db - dbid changed
Datafile /u01/app/oracle/oradata/clohot/undotbs01.db - dbid changed
Datafile /u01/app/oracle/oradata/clohot/users01.db - dbid changed
Datafile /u01/app/oracle/oradata/clohot/samtbs01.db - dbid changed
Datafile /u01/app/oracle/oradata/clohot/temp_01.tm - dbid changed
Control File /u01/app/oracle/oradata/clohot/control01.ctl - dbid changed
Instance shut down
Database ID for database CLOHOT changed to 2915369990.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
26. Finally mount and open the database with resetlog option
SQL>startup mount
SQL>alter database open resetlogs
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfileclohot.ora
SQL> select max(first_change#) Latest_chng from v$archived_log;
LATEST_CHNG
-----------
(not showing any value)
SQL> conn tom/tom
Connected.
SQL> select * from tab;
A TABLE
B TABLE
SQL> select * from b;
8
8
8
8
8
SQL>
27. Configure the listener and tns as per the requirement
SQL> conn tom/tom
Connected.
SQL> select * from tab;
A TABLE
B TABLE
SQL> select * from b;
8
8
8
8
8
SQL>
1. Make sure the database is in archive log mode
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/jetway/jatway/arch
Oldest online log sequence 40
Next log sequence to archive 42
Current log sequence 42
2. Make a note of the current archive log change number
Because the restored files will require recovery, some archive logs will be needed.
This applies even if you are not intending to put the cloned database into archive log mode.
Work out which will be the first required log by running the following query on the source database.
Make a note of the change number that is returned:
SQL> select max(first_change#) Latest_chng from v$archived_log;
LATEST_CHNG
-----------
1134000
3. View the current location of the data files
SQL> Select tablespace_name, file_name from dba_data_files order by 1;
SAMTBS1 /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SYSAUX /u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/jetway/jatway/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
USERS /u01/app/oracle/oradata/jetway/jatway/users01.db
4. Check whether the data files are in backup mode or not
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
NAME FILE# STATUS
------------------------------ ---------- ------------------
SYSTEM 1 NOT ACTIVE
SYSAUX 2 NOT ACTIVE
UNDOTBS1 3 NOT ACTIVE
USERS 4 NOT ACTIVE
SAMTBS1 5 NOT ACTIVE
The above result shows that the data files are not in backup mode
5. Generate a script to bring the data file in backup mode using the following command:
SQL> !pwd
/home/oracle
[oracle@sfo ~]$ cd /home/oracle/
[oracle@sfo ~]$ vi enable_hotbackup_mode.sql
set lines 999 pages 999
set verify off
set feedback off
set heading off
spool begin_backup.sql
select 'alter tablespace ' || tablespace_name || ' begin backup;' tsb_backup_mode
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
spool off
SQL> @enable_hotbackup_mode.sql
alter tablespace SAMTBS1 begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace SYSTEM begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace USERS begin backup;
(or) -- if you do not want a particular tablespace in backup mode,
the entire database can be brought to backup mode using the following command
SQL>alter database begin backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 ACTIVE
SYSAUX 2 ACTIVE
UNDOTBS1 3 ACTIVE
USERS 4 ACTIVE
SAMTBS1 5 ACTIVE
The above result shows all the tablespaces with backup mode enabled
6. Check the database using server parameter file
SQL> show parameter spfile;
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilejatway.ora
7. The below steps helps to bring individual tablespaces out of backup mode
SQL> alter tablespace SAMTBS1 end backup;
a)
SQL> alter tablespace SAMTBS1 end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 ACTIVE
SYSAUX 2 ACTIVE
UNDOTBS1 3 ACTIVE
USERS 4 ACTIVE
SAMTBS1 5 NOT ACTIVE
b)
SQL> alter tablespace SYSTEM end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 NOT ACTIVE
SYSAUX 2 ACTIVE
UNDOTBS1 3 ACTIVE
USERS 4 ACTIVE
SAMTBS1 5 NOT ACTIVE
c)
SQL> alter tablespace SYSAUX end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 NOT ACTIVE
SYSAUX 2 NOT ACTIVE
UNDOTBS1 3 ACTIVE
USERS 4 ACTIVE
SAMTBS1 5 NOT ACTIVE
d)
SQL> alter tablespace UNDOTBS1 end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 NOT ACTIVE
SYSAUX 2 NOT ACTIVE
UNDOTBS1 3 NOT ACTIVE
USERS 4 ACTIVE
SAMTBS1 5 NOT ACTIVE
e)
SQL> alter tablespace USERS end backup;
SQL> SELECT t.name, d.file# as, b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE#;
SYSTEM 1 NOT ACTIVE
SYSAUX 2 NOT ACTIVE
UNDOTBS1 3 NOT ACTIVE
USERS 4 NOT ACTIVE
SAMTBS1 5 NOT ACTIVE
8. The following query can also be used to find whether the tablespaces is in backup mode or not
SQL> Select * from v$backup;
1 NOT ACTIVE 1179330 25-SEP-12
2 NOT ACTIVE 1179330 25-SEP-12
3 NOT ACTIVE 1179330 25-SEP-12
4 NOT ACTIVE 1179330 25-SEP-12
5 NOT ACTIVE 1179330 25-SEP-12
9. Copy the following tablespaces to the clone location (except the temp tablespace)
I am moving all the files to the "backup" folder
SQL> Select tablespace_name, file_name from dba_data_files order by 1;
SAMTBS1 /u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf
SYSAUX /u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/jetway/jatway/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf
USERS /u01/app/oracle/oradata/jetway/jatway/users01.db
[oracle@sfo backup]$ pwd
/u01/app/oracle/oradata/jetway/jatway/backup
[oracle@sfo backup]$ ls
initclohot.ora samtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
10. Create the TAR file of the copied tablesapces. To avoid future errors I am creating another
copy of the tablespace backup folder and moving across the clone location
[oracle@sfo jatway]$ cp -r backup backup01
[oracle@sfo jatway]$ ls
arch backup backup01 backupinitclohot.ora control01.ctl redo01.log redo02.log redo03.log redo04.log redo05.log redo06.log redo07.log samtbs01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@sfo jatway]$ tar -cvzf backup.tar.gz backup01
backup01/
backup01/system01.dbf
backup01/control01.ora
backup01/sysaux01.dbf
backup01/initclohot.ora
backup01/samtbs01.dbf
backup01/undotbs01.dbf
backup01/users01.dbf
backup01/1_41_793069279.dbf
[oracle@sfo jatway]$
[oracle@sfo jatway]$ ls
arch backup01 backup.tar.gz redo01.log redo03.log redo05.log redo07.log sysaux01.dbf temp01.dbf users01.dbf
backup backupinitclohot.ora control01.ctl redo02.log redo04.log redo06.log samtbs01.dbf system01.dbf undotbs01.dbf
[oracle@sfo jatway]$ scp backup.tar.gz oracle@192.168.168.1:/u01/app/oracle/oradata
oracle@192.168.168.1's password:
backup.tar.gz 100% 245MB 17.5MB/s 00:14
100% 245MB 17.5MB/s 00:14
11. move the password file for the clone database across network
[oracle@sfo jatway]$
[oracle@sfo jatway]$ cd $ORACLE_HOME/dbs
[oracle@sfo dbs]$ ls orapw*
orapwdeccan orapwdelta orapwjatway
[oracle@sfo dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@sfo dbs]$ scp orapwjatway oracle@192.168.168.1:/u01/app/oracle/product/11.2.0/db_1/dbs
oracle@192.168.168.1's password:
orapwjatway 100% 1536 1.5KB/s 00:00 100% 1536 1.5KB/s 00:00
[oracle@sfo dbs]$
[oracle@sfo backup]$
12. Create a backup control file for the clone location and move across the clone location.
SQL> show parameter control
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/jetway
/jatway/control01.ctl, /u01/ap
p/oracle/flash_recovery_area/j
atway/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>Alter database backup controlfile to trace as '/u01/app/oracle/oradata/jetway/jatway/backup/control01.ora';
[oracle@sfo backup]$ pwd
/u01/app/oracle/oradata/jetway/jatway/backup
[oracle@sfo backup]$ ls
control01.ora initclohot.ora samtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
[oracle@sfo backup]$
oracle@sfo dbs]$ scp control01.ora oracle@192.168.168.1:/u01/app/oracle/oradata/backup/control01.ora
oracle@192.168.168.1's password:
control01.ora 100% 1536 1.5KB/s 00:00
13. Copy archive logs for clone database
It is only necessary to copy archive logs created during the time the source database was in backup mode.
Begin by archiving the current redo: floow the step 2 to get archive log current.
SQL>alter system archive log current;
select name from v$archived_log where first_change# >= &change_no
order by name
/
Enter value for change_no: 1134000
/u01/app/oracle/oradata/jetway/jatway/arch/1_41_793069279.dbf
14. In the clone database location (name of the clone database is "clohot") and UNTAR the zip file
[oracle@nyc oradata]$cd /u01/app/oracle/oradata
[oracle@nyc oradata]$ ls
backup.tar.gz
[oracle@nyc oradata]$ ls
backup.tar.gz
[oracle@nyc oradata]$ tar -xvzf backup.tar.gz
backup01/
backup01/system01.dbf
backup01/control01.ora
backup01/sysaux01.dbf
backup01/initclohot.ora
backup01/samtbs01.dbf
backup01/undotbs01.dbf
backup01/users01.dbf
backup01/1_41_793069279.dbf
[oracle@nyc oradata]$
[oracle@nyc oradata]$ mv backup01 clohot/
[oracle@nyc oradata]$ ls -lrt
total 250916
drwxr-xr-x 4 oracle oinstall 4096 Sep 2 23:29 delta
drwxr-xr-x 3 oracle oinstall 4096 Sep 9 05:48 deccan
-rw-r--r-- 1 oracle oinstall 256665891 Sep 25 05:11 backup.tar.gz
drwxr-xr-x 4 oracle oinstall 4096 Sep 25 09:35 clohot
[oracle@nyc oradata]$ cd clohot/
[oracle@nyc clohot]$ ls -lrt
total 8
drwxr-xr-x 2 oracle oinstall 4096 Sep 25 05:06 backup01
drwxr-xr-x 2 oracle oinstall 4096 Sep 25 09:33 arch
[oracle@nyc clohot]$
15. Prepare the init parameter file for clone database("clohot")
Actul init parameter file
*.audit_file_dest='/u01/app/oracle/admin/jatway/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/jetway/jatway/control01.ctl','/u01/app/oracle/flash_recovery_area/jatway/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.db_name='jatway'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='jatway'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jatwayXDB)'
*.fal_client='delta'
*.fal_server='deccan'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(jatway,deccan)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jatway'
*.LOG_ARCHIVE_DEST_2='SERVICE=deccan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=deccan'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.log_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
~
(note: My source database has dataguard so i am just database cone with archivelog and flashback enabled)
create the required dicectory as mentined in the parameter file
[oracle@nyc backup01]$ mkdir -p /u01/app/oracle/admin/clohot/adump
[oracle@nyc backup01]$ mkdir -p /u01/app/oracle/oradata/clohot
16. Modify the parameter as per the clone database base directory setup and add entry in /etc/oratab
[oracle@nyc backup01]$ cat initclohot.ora
*.audit_file_dest='/u01/app/oracle/admin/clohot/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/clohot/control01.ctl'
*.db_block_size=8192
*.db_domain=''
#*.db_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.db_name='clohot'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
#*.db_unique_name='jatway'
*.diagnostic_dest='/u01/app/oracle'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=jatwayXDB)'
#*.fal_client='delta'
#*.fal_server='deccan'
#*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(jatway,deccan)'
#*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jatway'
#*.LOG_ARCHIVE_DEST_2='SERVICE=deccan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=deccan'
#*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
#*.log_archive_dest_state_2='ENABLE'
#*.log_archive_format='%t_%s_%r.dbf'
#*.LOG_ARCHIVE_MAX_PROCESSES=30
#*.log_file_name_convert='/u01/app/oracle/oradata/deccan/','/u01/app/oracle/oradata/jetway/jatway'
*.memory_target=1234173952
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
#*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
~
(Note: change the db_domain, db_unique_name,)
[oracle@nyc clohot]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
17. Add entry in /etc/oratab
[oracle@nyc dbs]$ vi /etc/oratab
clohot:/u01/app/oracle/product/11.2.0/db_1:N
[oracle@nyc flash_recovery_area]$ . oraenv
ORACLE_SID = [oracle] ? clohot
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@nyc flash_recovery_area]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 25 10:27:57 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1235959808 bytes
Fixed Size 2212896 bytes
Variable Size 738200544 bytes
Database Buffers 486539264 bytes
Redo Buffers 9007104 bytes
SQL>
18. Create the control file script.
actual control file look like this---
[oracle@nyc clohot]$ cat control01.ora
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="jatway"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE'
-- LOG_ARCHIVE_CONFIG='DG_CONFIG=("deccan")'
-- LOG_ARCHIVE_MAX_PROCESSES=30
-- STANDBY_FILE_MANAGEMENT=auto
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=delta
-- FAL_SERVER=deccan
--
-- LOG_ARCHIVE_DEST_2='SERVICE=deccan'
-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_2='LGWR NOAFFIRM NOEXPEDITE NOVERIFY ASYNC=61440'
-- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_2='DB_UNIQUE_NAME=deccan'
-- LOG_ARCHIVE_DEST_2='VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
-- LOG_ARCHIVE_DEST_STATE_2=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/jetway/jatway/arch'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_1='DB_UNIQUE_NAME=jatway'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JATWAY" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/jetway/jatway/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/jetway/jatway/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/jetway/jatway/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
-- GROUP 4 '/u01/app/oracle/oradata/jetway/jatway/redo04.log' SIZE 50M BLOCKSIZE 512,
-- GROUP 5 '/u01/app/oracle/oradata/jetway/jatway/redo05.log' SIZE 50M BLOCKSIZE 512,
-- GROUP 6 '/u01/app/oracle/oradata/jetway/jatway/redo06.log' SIZE 50M BLOCKSIZE 512,
-- GROUP 7 '/u01/app/oracle/oradata/jetway/jatway/redo07.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/jetway/jatway/system01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/users01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT ''/u01/app/oracle/oradata/jetway/jatway/backup/%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_694916203.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_793069279.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/jetway/jatway/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo04.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo05.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo06.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo07.log'
SIZE 50M BLOCKSIZE 512 REUSE;
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JATWAY" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/jetway/jatway/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/jetway/jatway/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/jetway/jatway/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/jetway/jatway/system01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/sysaux01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/undotbs01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/users01.dbf',
'/u01/app/oracle/oradata/jetway/jatway/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT ''/u01/app/oracle/oradata/jetway/jatway/backup/%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_694916203.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/jetway/jatway/arch/1_1_793069279.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Create log files for threads other than thread one.
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/jetway/jatway/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo04.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo05.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo06.log'
SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/jetway/jatway/redo07.log'
SIZE 50M BLOCKSIZE 512 REUSE;
19. using following points you can create the control file script
The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:
Remove any lines that start with '#', '--',
Remove any blank lines in the 'CREATE CONTROLFILE' section and 'RECOVER DATABASE USING BACKUP CONTROLFILE' and 'ALTER DATABASE OPEN RESETLOGS;'
Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file.
Make sure that you hang onto the command, it will be used later.
The word 'REUSE' needs to be changed to 'SET' and 'NORESETLOGS' changed to 'RESETLOGS'
The database name needs setting to the new database name ("CLOHOT") (if it is being changed).
Decide whether the database will be put into archivelog mode or not. IF YES USE "ARCHIVELOG", IF NO USE "NOARCHIVELOG"
If the file paths are being changed, alter the file to reflect the changes.
20. changes are made as per the requirement
[oracle@nyc clohot]$ cat control01.ora
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CLOHOT" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/clohot/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/clohot/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/clohot/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/clohot/system01.dbf',
'/u01/app/oracle/oradata/clohot/sysaux01.dbf',
'/u01/app/oracle/oradata/clohot/undotbs01.dbf',
'/u01/app/oracle/oradata/clohot/users01.dbf',
'/u01/app/oracle/oradata/clohot/samtbs01.dbf'
CHARACTER SET WE8MSWIN1252
;
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> @/u01/app/oracle/oradata/clohot/contro1_create.sql
ORACLE instance started.
Total System Global Area 1235959808 bytes
Fixed Size 2212896 bytes
Variable Size 738200544 bytes
Database Buffers 486539264 bytes
Redo Buffers 9007104 bytes
Control file created
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
21. now apply the archivelog to perfornm the recovery.
SQL> recover database using backup controlfile until cancel;
(note: The above show the suggested archive log so you need to give full path of the arhivelog)
ORA-00279: change 1179330 generated at 09/25/2012 04:35:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_42_%u_.arc
ORA-00280: change 1179330 for thread 1 is in sequence #42
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
SQL> RECOVER DATABASE using backup controlfile until cancel;
ORA-00279: change 1179330 generated at 09/25/2012 04:35:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf
ORA-00280: change 1179330 for thread 1 is in sequence #42
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf
ORA-00279: change 1196618 generated at 09/25/2012 09:00:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_43_%u_.arc
ORA-00280: change 1196618 for thread 1 is in sequence #43
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/1_42_793069279.dbf'
no longer needed for this recovery
(note: archivelog "1_42*" applied now it required "1_43*"
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> RECOVER DATABASE using backup controlfile until cancel;
ORA-00279: change 1196618 generated at 09/25/2012 09:00:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/CLOHOT/archivelog/2012_09_25/o1_mf_1_43_%u_.arc
ORA-00280: change 1196618 for thread 1 is in sequence #43
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
22. Now open the database with resetlog.
(if archivelog not applied preperly it thow throw the error when open the database
Re: ORA-01195: online backup of file 1 needs more recovery to be consistent )
SQL> alter database open resetlogs;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
23. create spfile from pfile and shutdown and startup the database toapply the change.
SQL> create spfile from pfile;
File created.
24. create the temporary table for the clone database
CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/u01/app/oracle/oradata/clohot/temp_01.tmp' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
SQL> select NAME, TS# from v$TEMPFILE ;
NAME TS#
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/clohot/temp_01.tmp 7
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;
Database altered.
SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP01 4194304 16777216
SQL>shutdown immdiate
25. change the database ID (DBID) if we clone tha database using rman it will change the DBID automatically.
due to the manual clone we need to change the DBID using "nid utility"
SQL> startup mount
ORACLE instance started.
Total System Global Area 1235959808 bytes
Fixed Size 2212896 bytes
Variable Size 738200544 bytes
Database Buffers 486539264 bytes
Redo Buffers 9007104 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@nyc flash_recovery_area]$ nid target=/
DBNEWID: Release 11.2.0.1.0 - Production on Tue Sep 25 12:16:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database CLOHOT (DBID=3196377116)
Connected to server version 11.2.0
Control Files in database:
/u01/app/oracle/oradata/clohot/control01.ctl
Change database ID of database CLOHOT? (Y/[N]) => y (note: here we have to give to give "Y" to change the DBID)
Proceeding with operation
Changing database ID from 3196377116 to 2915369990
Control File /u01/app/oracle/oradata/clohot/control01.ctl - modified
Datafile /u01/app/oracle/oradata/clohot/system01.db - dbid changed
Datafile /u01/app/oracle/oradata/clohot/sysaux01.db - dbid changed
Datafile /u01/app/oracle/oradata/clohot/undotbs01.db - dbid changed
Datafile /u01/app/oracle/oradata/clohot/users01.db - dbid changed
Datafile /u01/app/oracle/oradata/clohot/samtbs01.db - dbid changed
Datafile /u01/app/oracle/oradata/clohot/temp_01.tm - dbid changed
Control File /u01/app/oracle/oradata/clohot/control01.ctl - dbid changed
Instance shut down
Database ID for database CLOHOT changed to 2915369990.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
26. Finally mount and open the database with resetlog option
SQL>startup mount
SQL>alter database open resetlogs
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfileclohot.ora
SQL> select max(first_change#) Latest_chng from v$archived_log;
LATEST_CHNG
-----------
(not showing any value)
SQL> conn tom/tom
Connected.
SQL> select * from tab;
A TABLE
B TABLE
SQL> select * from b;
8
8
8
8
8
SQL>
27. Configure the listener and tns as per the requirement
No comments:
Post a Comment