EBS production RAC to UAT single instance database refresh, ebs clone

EBS和DB 在没有版本变化的情况下可以直接复制数据库备份到测试场. 但是还是很多工作要做.
这里是只是做DB层面的clone,或者说叫refreshing.
如果是有版本变化就要做app tier的事情

production db name= prod
uat db name=test

RAC to single instance db refreshing.

copy ALL DATABASE rman BACKUP AND recent necessary ARCHIVE LOG BACKUP FROM production TO ebisdbuat 


ALL PROD_df0_20160225*
c-287928742-20160226-01
ALL ARCHIVE LOG BACKUP AFTER DATABASE BACKUP .

remove TEST data files AND ARCHIVELOG



SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size             469764296 bytes
Database Buffers         1644167168 bytes
Redo Buffers               21725184 bytes





[oracle@ebisdbuat dbs]$ rman auxiliary /

RMAN>
RUN {
set newname for database to '/oradata/oracle/test/dbf/%b';
duplicate database to "TEST" backup location '/tmpData/rman' nofilenamecheck 
LOGFILE
      GROUP 1 ('/oradata/oracle/test/dbf/redo01a.log', '/oradata/oracle/test/dbf/redo01b.log') SIZE 100M REUSE, 
      GROUP 2 ('/oradata/oracle/test/dbf/redo02a.log', '/oradata/oracle/test/dbf/redo02b.log') SIZE 100M REUSE;
}



--if rman restoration abort, shutdown instance, startup nomount , and continue to duplicate. 
archived log file name=/oradata/oracle/test/dbf/archive/2_145024_876090406.dbf RECID=10 STAMP=904837715
archived log file name=/oradata/oracle/test/dbf/archive/2_145025_876090406.dbf thread=2 sequence=145025
channel clone_default: deleting archived log(s)
archived log file name=/oradata/oracle/test/dbf/archive/1_148911_876090406.dbf RECID=6 STAMP=904837715
archived log file name=/oradata/oracle/test/dbf/archive/1_148912_876090406.dbf thread=1 sequence=148912
channel clone_default: deleting archived log(s)
archived log file name=/oradata/oracle/test/dbf/archive/2_145025_876090406.dbf RECID=9 STAMP=904837715
archived log file name=/oradata/oracle/test/dbf/archive/2_145026_876090406.dbf thread=2 sequence=145026
channel clone_default: deleting archived log(s)
archived log file name=/oradata/oracle/test/dbf/archive/1_148912_876090406.dbf RECID=3 STAMP=904837715
archived log file name=/oradata/oracle/test/dbf/archive/1_148913_876090406.dbf thread=1 sequence=148913
channel clone_default: deleting archived log(s)
archived log file name=/oradata/oracle/test/dbf/archive/2_145026_876090406.dbf RECID=11 STAMP=904837715
archived log file name=/oradata/oracle/test/dbf/archive/2_145027_876090406.dbf thread=2 sequence=145027
channel clone_default: deleting archived log(s)
archived log file name=/oradata/oracle/test/dbf/archive/1_148913_876090406.dbf RECID=7 STAMP=904837715
archived log file name=/oradata/oracle/test/dbf/archive/1_148914_876090406.dbf thread=1 sequence=148914
channel clone_default: deleting archived log(s)
archived log file name=/oradata/oracle/test/dbf/archive/2_145027_876090406.dbf RECID=17 STAMP=904837715
archived log file name=/oradata/oracle/test/dbf/archive/2_145028_876090406.dbf thread=2 sequence=145028
channel clone_default: deleting archived log(s)
archived log file name=/oradata/oracle/test/dbf/archive/1_148914_876090406.dbf RECID=18 STAMP=904837715
archived log file name=/oradata/oracle/test/dbf/archive/1_148915_876090406.dbf thread=1 sequence=148915
channel clone_default: deleting archived log(s)
archived log file name=/oradata/oracle/test/dbf/archive/1_148915_876090406.dbf RECID=14 STAMP=904837715
channel clone_default: deleting archived log(s)
archived log file name=/oradata/oracle/test/dbf/archive/2_145028_876090406.dbf RECID=16 STAMP=904837715
media recovery complete, elapsed time: 00:01:02
Finished recover at 26-FEB-16
Oracle instance started

Total System Global Area    2137886720 bytes

Fixed Size                     2230072 bytes
Variable Size                486541512 bytes
Database Buffers            1627389952 bytes
Redo Buffers                  21725184 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TEST'' 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 =  ''TEST'' 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    2137886720 bytes

Fixed Size                     2230072 bytes
Variable Size                486541512 bytes
Database Buffers            1627389952 bytes
Redo Buffers                  21725184 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
  MAXLOGFILES    315
  MAXLOGMEMBERS      2
  MAXDATAFILES     1024
  MAXINSTANCES    63
  MAXLOGHISTORY    11972
 LOGFILE
  GROUP   1 ( '+DATA/prod/dbf/redo01a.log', '+DATA/prod/dbf/redo01b.log' ) SIZE 100 M  REUSE,
  GROUP   2 ( '+DATA/prod/dbf/redo02a.log', '+DATA/prod/dbf/redo02b.log' ) SIZE 100 M  REUSE,
  GROUP   3 ( '+DATA/prod/dbf/redo03b.log', '+DATA/prod/dbf/redo03a.log' ) SIZE 100 M  REUSE,
  GROUP   4 ( '+DATA/prod/dbf/redo04a.log', '+DATA/prod/dbf/redo04b.log' ) SIZE 100 M  REUSE,
  GROUP   5 ( '+DATA/prod/dbf/redo05b.log', '+DATA/prod/dbf/redo05a.log' ) SIZE 100 M  REUSE,
  GROUP   6 ( '+DATA/prod/dbf/redo06b.log', '+DATA/prod/dbf/redo06a.log' ) SIZE 100 M  REUSE,
  GROUP   7 ( '+DATA/prod/dbf/redo07a.log', '+DATA/prod/dbf/redo07b.log' ) SIZE 100 M  REUSE,
  GROUP   8 ( '+DATA/prod/dbf/redo08a.log', '+DATA/prod/dbf/redo08b.log' ) SIZE 100 M  REUSE
 DATAFILE
  '/oradata/oracle/test/dbf/system01.dbf'
 CHARACTER SET UTF8

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/26/2016 15:50:06
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-00349: failure obtaining block size for '+DATA/prod/dbf/redo01a.lo




---restore completed, but cannot create logfile on ASM, there is no ASM on target server.





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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/26/2016 18:02:07
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled



---add thread 2 logfile to open database.

ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 3 '/oradata/oracle/test/dbf/TEST/onlinelog/redo03a.log' SIZE 100M,
GROUP 4 '/oradata/oracle/test/dbf/TEST/onlinelog/redo04a.log' SIZE 100M;

SQL> alter database open resetlogs;

Database altered.





SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
TEST


[oracle@ebisdbuat dbf]$ sqlplus apps/toosimple

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 29 09:05:14 2016

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


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> EXEC FND_CONC_CLONE.SETUP_CLEAN;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

[oracle@ebisdbuat dbf]$ sqlplus / as sysdba


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size             469764296 bytes
Database Buffers         1644167168 bytes
Redo Buffers               21725184 bytes
Database mounted.
Database opened.




[oracle@ebisdbuat dbf]$ ps -ef|grep lsnr
orauat    4312     1  0  2015 ?        00:17:03 /oracle/uat/app/oracle/product/11.2.0/db/bin/tnslsnr UAT -inherit
oracle   32581 63781  0 09:08 pts/2    00:00:00 grep lsnr
oracle   35095     1  0  2015 ?        00:10:14 /oracle/test/app/oracle/product/11.2.0/db/bin/tnslsnr TEST -inherit
[oracle@ebisdbuat dbf]$ lsnrctl stop test

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-FEB-2016 09:08:10

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ebisdbuat.hthk.com)(PORT=1526)))
The command completed successfully
[oracle@ebisdbuat dbf]$ ps -ef|grep lsnr
orauat    4312     1  0  2015 ?        00:17:03 /oracle/uat/app/oracle/product/11.2.0/db/bin/tnslsnr UAT -inherit
oracle   32584 63781  0 09:08 pts/2    00:00:00 grep lsnr



[oracle@ebisdbuat dbf]$ cd /oracle/test/app/oracle/product/11.2.0/db/appsutil/clone/bin/
[oracle@ebisdbuat bin]$ perl adcfgclone.pl dbTier

                     Copyright (c) 2011 Oracle Corporation
                        Redwood Shores, California, USA

                        Oracle E-Business Suite Rapid Clone

                                 Version 12.2

                      adcfgclone Version 120.63.12020000.7

Enter the APPS password :

Running:
/oracle/test/app/oracle/product/11.2.0/db/appsutil/clone/bin/../jre/bin/java -Xmx600M -cp /oracle/test/app/oracle/product/11.2.0/db/appsutil/clone/jlib/java:/oracle/test/app/oracle/product/11.2.0/db/appsutil/clone/jlib/xmlparserv2.jar:/oracle/test/app/oracle/product/11.2.0/db/appsutil/clone/jlib/ojdbc5.jar oracle.apps.ad.context.CloneContext -e /oracle/test/app/oracle/product/11.2.0/db/appsutil/clone/bin/../context/db/CTXORIG.xml -validate -pairsfile /tmp/adpairsfile_32589.lst -stage /oracle/test/app/oracle/product/11.2.0/db/appsutil/clone  2> /tmp/adcfgclone_32589.err; echo $? > /tmp/adcfgclone_32589.res

Log file located at /oracle/test/app/oracle/product/11.2.0/db/appsutil/clone/bin/CloneContext_0229090927.log

Provide the values required for creation of the new Database Context file.

Target System Hostname (virtual or normal) [ebisdbuat] :

Target Instance is RAC (y/n) [y] : n

Target System Database SID : TEST

Target System Base Directory : /oracle/test/app/oracle

Target System utl_file_dir Directory List : /usr/tmp

Number of DATA_TOP's on the Target System [3] : 1

Target System DATA_TOP Directory 1 : /oradata/oracle/test/dbf

Target System RDBMS ORACLE_HOME Directory [/oracle/test/app/oracle/11.2.0] : /oracle/test/app/oracle/product/11.2.0/db

Do you want to preserve the Display [localhost:10.0] (y/n)  : n

Target System Display [ebisdbuat:0.0] :

Do you want the target system to have the same port values as the source system (y/n) [y] ? : n

Target System Port Pool [0-99] : 5

Checking the port pool 5
done: Port Pool 5 is free
Report file located at /oracle/test/app/oracle/product/11.2.0/db/appsutil/temp/portpool.lst
Complete port information available at /oracle/test/app/oracle/product/11.2.0/db/appsutil/temp/portpool.lst


.....

Completed Apply...
Mon Feb 29 09:15:42 2016

Starting database listener for TEST:
Running:
/oracle/test/app/oracle/product/11.2.0/db/appsutil/scripts/TEST_ebisdbuat/addlnctl.sh start TEST
Logfile: /oracle/test/app/oracle/product/11.2.0/db/appsutil/log/TEST_ebisdbuat/addlnctl.txt

You are running addlnctl.sh version 120.4


Starting listener process TEST ...


Listener TEST has already been started.


addlnctl.sh: exiting with status 0

addlnctl.sh: check the logfile /oracle/test/app/oracle/product/11.2.0/db/appsutil/log/TEST_ebisdbuat/addlnctl.txt for more information ...



[oracle@ebisdbuat bin]$ sqlplus apps/toosimple

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 29 09:22:38 2016

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


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> UPDATE fnd_concurrent_requests
            SET phase_code = 'C', status_code = 'X'
            WHERE status_code ='T' OR phase_code = 'R';
  2    3
2 rows updated.

SQL>
SQL>
SQL> commit;

Commit complete.

SQL> UPDATE fnd_concurrent_requests
           SET hold_flag = 'Y'
           WHERE phase_code = 'P'
           AND status_code in ('Q','I');
  2    3    4
263 rows updated.

SQL>
SQL>
SQL> commit
  2  ;

Commit complete.

SQL> UPDATE wf_notifications
           SET mail_status ='SENT',
                   status ='CLOSED'
           WHERE NVL(mail_status, '~')  NOT IN ('SENT');
  2    3    4


43896 rows updated.

SQL> SQL> SQL>
SQL>
SQL>
SQL> commit
  2  ;

Commit complete.

SQL> UPDATE fnd_conc_req_outputs
SET file_name = REPLACE(file_name, '/app/oracle/product/fs_ne/inst/PROD_ebisapp2n/','/app/test/oracle/product/fs_ne/inst/TEST_ebisappuat/') ,
file_node_name = 'EBISAPPUAT'
WHERE file_node_name ='EBISAPP2N';
  2    3    4
3212 rows updated.

SQL>
SQL>
SQL> UPDATE fnd_conc_req_outputs
SET file_name = REPLACE(file_name, '/app/oracle/product/fs_ne/inst/PROD_ebisapp1n/','/app/test/oracle/product/fs_ne/inst/TEST_ebisappuat/') ,
file_node_name = 'EBISAPPUAT'
WHERE file_node_name ='EBISAPP1N';
  2    3    4
0 rows updated.

SQL>
SQL> UPDATE fnd_concurrent_requests
   SET    logfile_name = REPLACE(logfile_name, '/app/oracle/product/fs_ne/inst/PROD_ebisapp2n/','/app/test/oracle/product/fs_ne/inst/TEST_ebisappuat/'),
    outfile_name = REPLACE(outfile_name, '/app/oracle/product/fs_ne/inst/PROD_ebisapp2n/','/app/test/oracle/product/fs_ne/inst/TEST_ebisappuat/'),
    logfile_node_name ='EBISAPPUAT',
    outfile_node_name ='EBISAPPUAT'
    where logfile_node_name ='EBISAPP2N';

207861 rows updated.

SQL>
SQL>
SQL>
SQL> UPDATE fnd_concurrent_requests
  2  SET    logfile_name = REPLACE(logfile_name, '/app/oracle/product/fs_ne/inst/PROD_ebisapp1n/','/app/test/oracle/product/fs_ne/inst/TEST_ebisappuat/'),
  3  outfile_name = REPLACE(outfile_name, '/app/oracle/product/fs_ne/inst/PROD_ebisapp1n/','/app/test/oracle/product/fs_ne/inst/TEST_ebisappuat/'),
  4  logfile_node_name ='EBISAPPUAT',
  5  outfile_node_name ='EBISAPPUAT'
  6  where logfile_node_name ='EBISAPP1N';

0 rows updated.



restart INSTANCE.


SQL> alter system set utl_file_dir='/usr/tmp','/oracle/test/app/oracle/product/11.2.0/db/appsutil/outbound/TEST_ebisdbuat', '/oracle/prod/hthk/csms/hgc/out/data','/oracle/prod/hthk/csms/4g/out/data','/oracle/prod/hthk/wms/hk/in/data','/oracle/prod/hthk/wms/hk/in/error','/oracle/prod/hthk/wms/hk/out/data','/oracle/prod/hthk/wms/mc/in/data','/oracle/prod/hthk/wms/mc/in/error','/oracle/prod/hthk/wms/mc/out/data','/oracle/prod/hthk/sois/hk/in/data','/oracle/prod/hthk/sois/hk/in/error','/oracle/prod/hthk/sois/mc/in/data','/oracle/prod/hthk/sois/mc/in/error','/oracle/prod/hthk/gl/hk/in/data','/oracle/prod/hthk/ap/hk/in/data','/oracle/prod/hthk/ce/hk/in/data','/oracle/prod/hthk/gl/hk/in/error','/oracle/prod/hthk/ap/hk/in/error','/oracle/prod/hthk/ce/hk/in/error','/oracle/prod/hthk/gl/hk/out/data','/oracle/prod/hthk/ap/hk/out/data','/oracle/prod/hthk/ce/hk/out/data','/oracle/prod/hthk/gl/mc/in/data','/oracle/prod/hthk/ap/mc/in/data','/oracle/prod/hthk/ce/mc/in/data','/oracle/prod/hthk/gl/mc/in/error','/oracle/prod/hthk/ap/mc/in/error','/oracle/prod/hthk/ce/mc/in/error','/oracle/prod/hthk/gl/mc/out/data','/oracle/prod/hthk/ap/mc/out/data','/oracle/prod/hthk/ce/mc/out/data','/oracle/prod/hthk/csms/hk/in/data','/oracle/prod/hthk/csms/hk/in/error','/oracle/prod/hthk/csms/hk/out/data','/oracle/prod/hthk/csms/mc/in/data','/oracle/prod/hthk/csms/mc/in/error','/oracle/prod/hthk/csms/mc/out/data','/oracle/prod/hthk/wms/3g/in/data','/oracle/prod/hthk/wms/3g/in/error','/oracle/prod/hthk/wms/3g/out/data','/oracle/prod/hthk/vms/hk/in/resp','/oracle/prod/hthk/vms/hk/out/data' scope = spfile;

System altered.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ebisdbuat bin]$ ps -ef|grep lsnr
orauat    4312     1  0  2015 ?        00:17:03 /oracle/uat/app/oracle/product/11.2.0/db/bin/tnslsnr UAT -inherit
oracle   36820     1  0 09:14 ?        00:00:00 /oracle/test/app/oracle/product/11.2.0/db/bin/tnslsnr TEST -inherit
oracle   39337 63781  0 09:36 pts/2    00:00:00 grep lsnr



--remove thread 2 logfile
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE
         4 UNUSED

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.


SQL> select THREAD#, STATUS, ENABLED from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC