Important Queries for cloning

 Let us see the important queries we might need during the clone.

1. Query to check the database status

set lines 333
col HOST_NAME for a33
SELECT instance_name,
       host_name,
       version,
       startup_time,
       status,
       archiver,
       database_status
FROM   v$instance; 

2. Query to get the dba_directories

set linesize 150
set pagesize 999
column directory_name format a25
column directory_path format a99
select directory_name, directory_path from dba_directories;

3. Query to get the db_link details

set lines 333
col OWNER for a11
col DB_LINK for a33
col USERNAME for a11
col HOST for a11
select * from dba_db_links;

4. Query to get the dml statement of db links

SELECT 'create '
       ||Decode(U.name, 'PUBLIC', 'public ')
       ||'database link '
       ||Decode(U.name, 'PUBLIC', NULL,
                        U.name
                        ||'.')
       || L.name
       ||' connect to '
       || L.userid
       || ' identified by ****** using '''
       || L.host
       || ''''
       ||';' TEXT
FROM   sys.link$ L,
       sys.user$ U
WHERE  L.owner# = U.user#; 

5. Query to get the important worflow details

col parameter_value format a48
set lines 111
set pagesize 1000
column parameter_name format a30
column parameter_value format a60
select parameter_name, PARAMETER_VALUE
from apps.FND_SVC_COMP_PARAM_VALS V,
apps.FND_SVC_COMP_PARAMS_VL p
where v.parameter_id   = p.PARAMETER_ID
and P.COMPONENT_TYPE = 'WF_MAILER'
and p.parameter_name in ('NODENAME',
'INBOUND_PROTOCOL',
'INBOUND_SERVER',
'ACCOUNT',
'OUTBOUND_PROTOCOL',
'OUTBOUND_SERVER',
'TEST_ADDRESS',
'FROM',
'REPLYTO',
'HTMLAGENT',
'PROCESSOR_OUT_THREAD_COUNT',
'PROCESSOR_IN_THREAD_COUNT');

6. Query to get the workflow status

set lines 150 
set pages 10 
col component_id format 9999999 
col component_name format a30 
col "STATUS" format a21
col component_type format a25 
col startup_mode format a15 
SELECT component_id, 
       component_name, 
       component_status "STATUS", 
       component_type, 
       startup_mode 
FROM   apps.fnd_svc_components 
WHERE  component_name LIKE '%Mailer%';

7.Query to get the Node Configuration

set lines 150 
set pages 10 
col node_name for a20
select
NODE_NAME,
decode(STATUS,'Y','ACTIVE','INACTIVE') Status,
decode(SUPPORT_CP,'Y', 'ConcMgr','No') ConcMgr,
decode(SUPPORT_FORMS,'Y','Forms', 'No') Forms,
decode(SUPPORT_WEB,'Y','Web', 'No') WebServer,
decode(SUPPORT_ADMIN, 'Y','Admin', 'No') Admin,
decode(SUPPORT_DB, 'Y','Rdbms', 'No') Database
from apps.fnd_nodes
where node_name != 'AUTHENTICATION';