Wednesday 8 March 2017

convert oracle ASM database instance to normal database instance step by step.

OS details of both node:-
Linux DB11G.example.com 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:48 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
DB name:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;
NAME
---------
DB11G
Node1 hostname:
ASM.example.com
Node2 hostname:
DB11g.example.com
step1)
create pfile for spfile for duplicate database.
SQL> create pfile from spfile;
File created.
step2)
Create directory structure, same as DB11Gory database.
$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump
step3)
Make the following changes in pfile.

Replace the ASM disk with file system path.
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/DB11G/control01.CTL','/u01/app/oracle/fast_recovery_area/DB11G/control02.CTL'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_create_online_log_dest_1='/u01/app/oracle/fast_recovery_area'
*.db_domain=''
*.db_name='DB11G'
*.diagnostic_dest='/u01/app/oracle'

Step4)
Move password file and pfile to target location.
[oracle@ASM dbs]$ scp initDB11G.ora oracle@clone:/u01/app/oracle/product/11.2.0/db_1/dbs/
oracle@clone's password:
initDB11G.ora 100% 997 1.0KB/s 00:00
Monday, February 20, 2017
[oracle@ASM dbs]$ scp orapwDB11G oracle@clone:/u01/app/oracle/product/11.2.0/db_1/dbs/
oracle@clone's password:
orapwDB11G 100% 1536 1.5KB/s 00:00
[oracle@ASM dbs]$

Step5)
tnsname.ora on primary database server(DB11G.example.com)
# Generated by Oracle configuration tools.
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB11G.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
DB11G_tar =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = clone.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
(UR = A)
)
)

Step6)
Start duplicate (tareget) database in nomount.
[oracle@DB11G admin]$ export $ORACLE_SID=DB11G
[oracle@DB11G admin]$ echo $ORACLE_SID
DB11G
[oracle@DB11G admin]$ sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 1 21:50:36 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 503319896 bytes
Database Buffers 331350016 bytes
Redo Buffers 2379776 bytes
SQL> exit
[oracle@asm dbs]$ rman TARGET sys/******@DB11G AUXILIARY sys/******@DB11G_tar
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 8 02:40:24 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB11G (DBID=404263693)
connected to auxiliary database: DB11G (not mounted)
RMAN> DUPLICATE DATABASE TO DB11G
FROM ACTIVE DATABASE
NOFILENAMECHECK;2> 3>

Starting Duplicate Db at 08-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     839282688 bytes
Fixed Size                     2233000 bytes
Variable Size                515902808 bytes
Database Buffers             318767104 bytes
Redo Buffers                   2379776 bytes
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB11G'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DB11G'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/oradata/DB11G/CONTROL01.CTL';
   restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/DB11G/control02.CTL' from
 '/u01/app/oracle/oradata/DB11G/CONTROL01.CTL';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DB11G'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set  db_unique_name =  ''DB11G'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area     839282688 bytes
Fixed Size                     2233000 bytes
Variable Size                515902808 bytes
Database Buffers             318767104 bytes
Redo Buffers                   2379776 bytes
Starting backup at 08-MAR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_DB11G.f tag=TAG20170308T024549 RECID=4 STAMP=938054749
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-MAR-17
Starting restore at 08-MAR-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 08-MAR-17
database mounted
contents of Memory Script:
{
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   datafile  5 auxiliary format new
   ;
   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
executing command: SET NEWNAME
Starting backup at 08-MAR-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/db11g/datafile/system.256.934594061
output file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_system_04ruj535_.dbf tag=TAG20170308T024557
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/db11g/datafile/sysaux.257.934594061
output file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_sysaux_05ruj53u_.dbf tag=TAG20170308T024557
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/db11g/datafile/example.261.934594145
output file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_example_06ruj54n_.dbf tag=TAG20170308T024557
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/db11g/datafile/undotbs1.258.934594063
output file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_undotbs1_07ruj556_.dbf tag=TAG20170308T024557
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/db11g/datafile/users.259.934594063
output file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_users_08ruj559_.dbf tag=TAG20170308T024557
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-MAR-17
sql statement: alter system archive log current
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u01/app/oracle/product/11.2.0/db_1/dbs/archive1_8_934594127.arc" auxiliary format
 "/u01/app/oracle/product/11.2.0/db_1/dbs/archive1_8_934594127.arc"   ;
   catalog clone archivelog  "/u01/app/oracle/product/11.2.0/db_1/dbs/archive1_8_934594127.arc";
   switch clone datafile all;
}
executing Memory Script
Starting backup at 08-MAR-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=8 RECID=6 STAMP=938054828
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/archive1_8_934594127.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 08-MAR-17
cataloged archived log
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/archive1_8_934594127.arc RECID=6 STAMP=938074630
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=938074630 file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_system_04ruj535_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=938074630 file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_sysaux_05ruj53u_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=938074630 file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_undotbs1_07ruj556_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=938074630 file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_users_08ruj559_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=938074630 file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_example_06ruj54n_.dbf
contents of Memory Script:
{
   set until scn  1163114;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 08-MAR-17
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/archive1_8_934594127.arc
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/archive1_8_934594127.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-MAR-17
Oracle instance started
Total System Global Area     839282688 bytes
Fixed Size                     2233000 bytes
Variable Size                515902808 bytes
Database Buffers             318767104 bytes
Redo Buffers                   2379776 bytes
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DB11G'' 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 =  ''DB11G'' 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     839282688 bytes
Fixed Size                     2233000 bytes
Variable Size                515902808 bytes
Database Buffers             318767104 bytes
Redo Buffers                   2379776 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DB11G" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1  SIZE 50 M ,
  GROUP   2  SIZE 50 M ,
  GROUP   3  SIZE 50 M
 DATAFILE
  '/u01/app/oracle/oradata/DB11G/datafile/o1_mf_system_04ruj535_.dbf'
 CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/DB11G/datafile/o1_mf_sysaux_05ruj53u_.dbf",
 "/u01/app/oracle/oradata/DB11G/datafile/o1_mf_undotbs1_07ruj556_.dbf",
 "/u01/app/oracle/oradata/DB11G/datafile/o1_mf_users_08ruj559_.dbf",
 "/u01/app/oracle/oradata/DB11G/datafile/o1_mf_example_06ruj54n_.dbf";
   switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/DB11G/datafile/o1_mf_temp_%u_.tmp in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_sysaux_05ruj53u_.dbf RECID=1 STAMP=938074644
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_undotbs1_07ruj556_.dbf RECID=2 STAMP=938074644
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_users_08ruj559_.dbf RECID=3 STAMP=938074644
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_example_06ruj54n_.dbf RECID=4 STAMP=938074644
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=938074644 file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_sysaux_05ruj53u_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=938074644 file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_undotbs1_07ruj556_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=938074644 file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_users_08ruj559_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=938074644 file name=/u01/app/oracle/oradata/DB11G/datafile/o1_mf_example_06ruj54n_.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 08-MAR-17
[oracle@db11g dbs]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 8 08:35:17 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
DB11G


at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

work on autovacuum postgreSQL parameter

 In This blog, we are discussing the auto vacuum parameter on a small scale. we will understand the below parameters and will see how to mod...