Step by Step Oracle Database Hot Backup Cloning.
Source Database :-
[oracle@testserver oracle]$ cd /oracle/product/10.2.0/db_1/
[oracle@testserver db_1]$ . profile
[oracle@testserver db_1]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 12:37:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 2021440 bytes
Variable Size 159385536 bytes
Database Buffers 281018368 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@testserver db_1]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 16-MAY-2011 12:38:03
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testserver.muthu.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 16-MAY-2011 12:38:05
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testserver.muthu.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@testserver db_1]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 12:39:21 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/archive
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/oracle/create.bkp';
Database altered.
SQL>exit
[oracle@testserver oracle]$ mv create.bkp create.sql
[oracle@testserver oracle]$ chmod 755 create.sql
[oracle@testserver oracle]$ vi create.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/TEST/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/TEST/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/TEST/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/TEST/system01.dbf',
'/oracle/oradata/TEST/undotbs01.dbf',
'/oracle/oradata/TEST/sysaux01.dbf',
'/oracle/oradata/TEST/users01.dbf'
CHARACTER SET AL32UTF8
;
:wq
Creating Password File :-
Export New Databse Home and Sid
[oracle@testserver dbs]$ export ORACLE_HOME=/oracle/product/10.2.0/db_1
[oracle@testserver dbs]$ export ORACLE_SID=TEST
[oracle@testserver dbs]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@testserver dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwTEST password=oracle
Source DATABASE :-
[oracle@testserver TEST]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 14:52:56 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database begin backup;
Database altered.
SQL>exit
Copy Source Location to Destination Location:-
[oracle@testserver oradata]$ cp -r ORA10g/ /oracle/oradata/TEST
[oracle@testserver oradata]$ cd TEST/
[oracle@testserver TEST]$ ls
control01.ctl control02.ctl control03.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@testserver TEST]$ rm -rf *.ctl
[oracle@testserver TEST]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 15:41:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database end backup;
Database altered.
SQL>ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Execute the below query you can get the archive log file location and Change: -
File change :-
select max(first_change#) chng
from v$archived_log;
Output:-
CHNG
614593
Archivelog file name and location :-
select name
from v$archived_log
where first_change# >= 614593
order by name;
Output :-
NAME
/oracle/archive/1_10_744307208.dbf
[oracle@testserver TEST]$cd /oracle/product/10.2.0/db_1/admin
[oracle@testserver admin]$ mkdir TEST
[oracle@testserver admin]$ cd TEST/
[oracle@testserver TEST]$ mkdir adump bdump cdump dpdump pfile udump
[oracle@testserver TEST]$ ls
adump bdump cdump dpdump pfile udump
[oracle@testserver TEST]$cd dbs/
[oracle@testserver dbs]$ cp initORA10g.ora initTEST.ora
[oracle@testserver dbs]$ cp orapwORA10g orapwTEST
[oracle@testserver dbs]$ ls
hc_ORA10g.dat initdw.ora init.ora initORA10g.ora initTEST.ora lkORA10G orapwORA10g orapwTEST snapcf_ORA10g.f spfileORA10g.ora
Init.ora:-
Change you old dbname to New name Old DB Name :- ORA10G New DB Name :- TEST
[oracle@testserver dbs]$ vi initTEST.ora
TEST.__db_cache_size=306184192
TEST.__java_pool_size=4194304
TEST.__large_pool_size=4194304
TEST.__shared_pool_size=125829120
TEST.__streams_pool_size=0
*.audit_file_dest='/oracle/product/10.2.0/db_1/admin/TEST/adump'
*.background_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/oradata/TEST/control01.ctl','/oracle/oradata/TEST/control02.ctl','/oracle/oradata/TEST/control03.ctl'
*.core_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='TEST'
*.db_recovery_file_dest='/oracle/product/10.2.0/db_1/flash_recovery_area1'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=146800640
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=441450496
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/udump'
:wq!
[oracle@testserver dbs]$cd ../
[oracle@testserver db_1]$ mkdir flash_recovery_area1
[oracle@testserver oracle]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 14:20:30 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 2021440 bytes
Variable Size 134219712 bytes
Database Buffers 306184192 bytes
Redo Buffers 2170880 bytes
SQL> CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/oracle/oradata/TEST/redo01.log' SIZE 50M,GROUP 2 '/oracle/oradata/TEST/redo02.log' SIZE 50M,GROUP 3 '/oracle/oradata/TEST/redo03.log' SIZE 50M
9 DATAFILE
10 '/oracle/oradata/TEST/system01.dbf','/oracle/oradata/TEST/undotbs01.dbf','/oracle/oradata/TEST/sysaux01.dbf','/oracle/oradata/TEST/users01.dbf'
11 CHARACTER SET AL32UTF8;
Control file created.
SQL>recover database using backup controlfile until cancel;
ORA-00279: change 617205 generated at 05/16/2011 15:42:39 needed for thread 1
ORA-00289: suggestion :
/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2011_05_16/o1_mf
_1_11_%u_.arc
ORA-00280: change 617205 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/archive/1_10_744307208.dbf AUTO
ORA-00310: archived log contains sequence 10; sequence 11 required
ORA-00334: archived log: '/oracle/archive/1_10_744307208.dbf'
SQL> alter database open resetlogs;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/TEST/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select distinct status from v$datafile;
STATUS
-------
ONLINE
SYSTEM
SQL> alter database rename global_name to TEST;
Database altered.
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 2021440 bytes
Variable Size 134219712 bytes
Database Buffers 306184192 bytes
Redo Buffers 2170880 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@testserver dbs]$ nid target=/
DBNEWID: Release 10.2.0.1.0 - Production on Mon May 16 16:01:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database TEST (DBID=4063179336)
Connected to server version 10.2.0
Control Files in database:
/oracle/oradata/TEST/control01.ctl
/oracle/oradata/TEST/control02.ctl
/oracle/oradata/TEST/control03.ctl
Change database ID of database TEST? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 4063179336 to 2049482059
Control File /oracle/oradata/TEST/control01.ctl - modified
Control File /oracle/oradata/TEST/control02.ctl - modified
Control File /oracle/oradata/TEST/control03.ctl - modified
Datafile /oracle/oradata/TEST/system01.dbf - dbid changed
Datafile /oracle/oradata/TEST/undotbs01.dbf - dbid changed
Datafile /oracle/oradata/TEST/sysaux01.dbf - dbid changed
Datafile /oracle/oradata/TEST/users01.dbf - dbid changed
Datafile /oracle/oradata/TEST/temp01.dbf - dbid changed
Control File /oracle/oradata/TEST/control01.ctl - dbid changed
Control File /oracle/oradata/TEST/control02.ctl - dbid changed
Control File /oracle/oradata/TEST/control03.ctl - dbid changed
Instance shut down
Database ID for database TEST changed to 2049482059.
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.
[oracle@testserver dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 16:01:35 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 2021440 bytes
Variable Size 134219712 bytes
Database Buffers 306184192 bytes
Redo Buffers 2170880 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@testserver dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 16:03:25 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 TEST
testserver.muthu.com
10.2.0.1.0 16-MAY-11 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
[oracle@testserver dbs]$ ps -ef |grep smon
oracle 7404 1 0 12:37 ? 00:00:06 ora_smon_ORA10g
oracle 17265 1 0 16:02 ? 00:00:01 ora_smon_TEST
oracle 17380 16346 0 16:16 pts/2 00:00:00 grep smon
[oracle@testserver dbs]$
Listerner Entry :-
[oracle@testserver db_1]$ cd network/admin/
[oracle@testserver admin]$ vi listener.ora
# listener.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/listener.ora
SID_LIST_TEST =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TEST)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(SID_NAME = TEST)
)
)
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testserver.muthu.com)(PORT = 1522))
)
:wq
Tns Entry :-
[oracle@testserver admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testserver.muthu.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
:wq
No comments:
Post a Comment