<!-- $Header: emdb/sysman/admin/metadata/dyn_props.xmlp /st_emdbsa_11.2/9 2009/03/12 15:53:11 ajdsouza Exp $
   MODIFIED    (MM/DD/YY)
      ajdsouza  09/19/08 - add resourceName dyn property
      yma       09/10/08 - 
      prjaiswa  08/18/08 - bug 6733346
      yma       07/18/08 - bug 6522269
      shasingh  05/08/08 - Added VersionCategory for 11gR2
      ajdsouza  05/07/08 - moved dyn prop isHasManaged to oracle_database.xml
      dchakumk  03/10/08 - XbranchMerge dchakumk_6752_xbmsrc from
                           st_emdbsa_11.1
      ajdsouza  12/09/07 -  added dyn props resourcename, isHasManaged
      gallison  02/02/07 - Bug 5527729
      jsoule    08/29/06 - add 11gR1 version
      jsoule    03/13/06 - add 10.2.0.3 version 
      yma       01/12/06 - Fix bug 4924155 
      ysun      08/04/05 - eveluate MetricScope first
      kramarat  08/02/05 - Remove DGPrimaryDBName 
      ysun      07/22/05 - categorize dynamic props 
      gallison  06/16/05 - Fix dg status 
      ysun      05/16/05 - workaround for 817 db
      gallison  04/07/05 - Fix dg role for FSFO 
      kramarat  03/30/05 - Replace RACOption with MetricScope for DGPriamryDBName
      kramarat  02/23/05 - excluded rac instances 
      ysun      02/18/05 - ysun_metadata_file_split
      ysun      02/16/05 - creation
-->
<!-- This file contains the COMMON dynamic properties of oracle_database and rac_database targets. -->

    <!--
    ==========================================================
    == Dynamic Properties: MetricScope
    ==========================================================
    -->

		<DynamicProperties NAME="decideIncludeDB" FORMAT="ROW" IS_CRITICAL="TRUE" PROP_LIST="MetricScope">
			<QueryDescriptor FETCHLET_ID="SQL">
				<Property NAME="STATEMENT" SCOPE="GLOBAL"><![CDATA[
DECLARE
   ltype              VARCHAR2(100);
   lpar               VARCHAR2(100);
   lret               VARCHAR2(100);
   TYPE data_cursor_type IS REF CURSOR;
   data_cursor       data_cursor_type;
BEGIN
  ltype := :1;

  lret := 'INST';
  SELECT parallel INTO lpar FROM v$instance;
  IF ltype='rac_database' AND lpar='YES' THEN
     lret := 'DB';
  ELSIF ltype='oracle_database' AND lpar='NO' THEN
     lret := 'DB';
  ELSIF ltype='oracle_database' AND lpar='YES' THEN
     lret := 'RACINST';
  END IF;

  OPEN data_cursor FOR SELECT lret FROM dual;
  :2 := data_cursor;
END;
]]></Property>
  			<Property NAME="SQLINPARAM1" SCOPE="INSTANCE">TYPE</Property>
			<Property NAME="SQLOUTPARAMPOS" SCOPE="GLOBAL">2</Property>
		    <Property NAME="SQLOUTPARAMTYPE" SCOPE="GLOBAL">SQL_CURSOR</Property>
    		<Property NAME="MachineName" SCOPE="INSTANCE">MachineName</Property>
			<Property NAME="Port" SCOPE="INSTANCE">Port</Property>
			<Property NAME="SID" SCOPE="INSTANCE">SID</Property>
			<Property NAME="UserName" SCOPE="INSTANCE">UserName</Property>
			<Property NAME="password" SCOPE="INSTANCE">password</Property>
			<Property NAME="Role" SCOPE="INSTANCE" OPTIONAL="TRUE">Role</Property>
			</QueryDescriptor>
		</DynamicProperties>


  <!--
   ======================================================================
   == Dynamic Properties: SYSTEM tablespace numbers
   == (can be used to improve performance on any query requiring the 
   ==  SYSTEM tablespaces. currently used in db_recSegmentSettings_sysseg)
   ======================================================================
   -->
		<DynamicProperties NAME="SystemTablespaceNumber" FORMAT="ROW" PROP_LIST="SystemTs#">
			<ValidIf>
                <CategoryProp NAME="MetricScope" CHOICES="DB"/>
			</ValidIf>
			<QueryDescriptor FETCHLET_ID="SQL">
				<Property NAME="STATEMENT" SCOPE="GLOBAL"><![CDATA[
SELECT ts# "SystemTs#" FROM sys.ts$ WHERE name = 'SYSTEM'
]]></Property>
				<Property NAME="MachineName" SCOPE="INSTANCE">MachineName</Property>
				<Property NAME="Port" SCOPE="INSTANCE">Port</Property>
				<Property NAME="SID" SCOPE="INSTANCE">SID</Property>
				<Property NAME="UserName" SCOPE="INSTANCE">UserName</Property>
				<Property NAME="password" SCOPE="INSTANCE">password</Property>
				<Property NAME="Role" SCOPE="INSTANCE" OPTIONAL="TRUE">Role</Property>
			</QueryDescriptor>
		</DynamicProperties>  

   <!--
   ======================================================================
   == Dynamic Properties: SYSAUX tablespace number
   == (can be used to improve performance on any query requiring the 
   ==  SYSAUX tablespaces. currently used in db_recSegmentSettings_sysseg)
   == NOTE: If the SYSAUX tablespace does not exist -1 is returned
   ======================================================================
   -->
		<DynamicProperties NAME="SysauxTablespaceNumber" FORMAT="ROW" PROP_LIST="SysauxTs#">
			<ValidIf>
                <CategoryProp NAME="MetricScope" CHOICES="DB"/>
			</ValidIf>
			<QueryDescriptor FETCHLET_ID="SQL">
				<Property NAME="STATEMENT" SCOPE="GLOBAL"><![CDATA[
SELECT nvl(min(ts#), -1) "SysauxTs#" FROM sys.ts$ WHERE name = 'SYSAUX' 
]]></Property>
				<Property NAME="MachineName" SCOPE="INSTANCE">MachineName</Property>
				<Property NAME="Port" SCOPE="INSTANCE">Port</Property>
				<Property NAME="SID" SCOPE="INSTANCE">SID</Property>
				<Property NAME="UserName" SCOPE="INSTANCE">UserName</Property>
				<Property NAME="password" SCOPE="INSTANCE">password</Property>
				<Property NAME="Role" SCOPE="INSTANCE" OPTIONAL="TRUE">Role</Property>
			</QueryDescriptor>
		</DynamicProperties>  

		<!--
    ======================================================================
    == Dynamic Properties: GetD(ata)B(ase)Version
    ======================================================================
      -->
		<DynamicProperties NAME="GetDBVersion" FORMAT="ROW" IS_CRITICAL="TRUE" PROP_LIST="DBVersion;DBDomain">
			<QueryDescriptor FETCHLET_ID="SQL">
				<Property NAME="STATEMENT" SCOPE="GLOBAL"><![CDATA[
select i.version "DBVersion", p.value "DBDomain" from v$instance i, v$parameter p 
where p.name='db_domain'
]]></Property>
				<Property NAME="MachineName" SCOPE="INSTANCE">MachineName</Property>
				<Property NAME="Port" SCOPE="INSTANCE">Port</Property>
				<Property NAME="SID" SCOPE="INSTANCE">SID</Property>
				<Property NAME="UserName" SCOPE="INSTANCE">UserName</Property>
				<Property NAME="password" SCOPE="INSTANCE">password</Property>
				<Property NAME="Role" SCOPE="INSTANCE" OPTIONAL="TRUE">Role</Property>
       
			</QueryDescriptor>
		</DynamicProperties>

		<!--
    ======================================================================
    == Dynamic Properties: ComputeV(ersion)C(ategory)
    ======================================================================
      -->
		<DynamicProperties NAME="ComputeVC" FORMAT="ROW" IS_CRITICAL="TRUE" PROP_LIST="VersionCategory">
			<QueryDescriptor FETCHLET_ID="VersionRangeComputer">
				<Property NAME="Version" SCOPE="INSTANCE" OPTIONAL="TRUE">DBVersion</Property>
				<Property NAME="pre8" SCOPE="GLOBAL">;8.0.0</Property>
				<Property NAME="8i" SCOPE="GLOBAL">8.0.5;</Property>
				<Property NAME="8iR2" SCOPE="GLOBAL">8.1.5;9.0.0</Property>
				<Property NAME="9i" SCOPE="GLOBAL">9.0.1;</Property>
				<Property NAME="9iR2" SCOPE="GLOBAL">9.2.0;</Property>
				<Property NAME="10gR1" SCOPE="GLOBAL">10.1.0.2;</Property>
                                <Property NAME="10gR2" SCOPE="GLOBAL">10.2.0;</Property>
                                <Property NAME="10gR203" SCOPE="GLOBAL">10.2.0.3;</Property>
                                <Property NAME="11gR1" SCOPE="GLOBAL">11.1.0;</Property>
                                <Property NAME="11gR2" SCOPE="GLOBAL">11.2.0;</Property>
			</QueryDescriptor>
		</DynamicProperties>
	
		<!--
    ======================================================================
    == Dynamic Properties: RAC OPTION
    ======================================================================
      -->
		<DynamicProperties NAME="CheckRAC" FORMAT="ROW" PROP_LIST="RACOption">
			<QueryDescriptor FETCHLET_ID="SQL">
				<Property NAME="STATEMENT" SCOPE="GLOBAL"><![CDATA[
select parallel from v$instance
]]></Property>
				<Property NAME="MachineName" SCOPE="INSTANCE">MachineName</Property>
				<Property NAME="Port" SCOPE="INSTANCE">Port</Property>
				<Property NAME="SID" SCOPE="INSTANCE">SID</Property>
				<Property NAME="UserName" SCOPE="INSTANCE">UserName</Property>
				<Property NAME="password" SCOPE="INSTANCE">password</Property>
				<Property NAME="Role" SCOPE="INSTANCE" OPTIONAL="TRUE">Role</Property>
        
			</QueryDescriptor>
		</DynamicProperties>

 <!--
    ======================================================================
    == Dynamic Properties: DataGuardStatus
    ======================================================================
      -->
		<DynamicProperties NAME="GetDataGuardStatus" FORMAT="ROW" PROP_LIST="DataGuardStatus">
		<ValidIf>
			<CategoryProp NAME="VersionCategory" CHOICES="9i;9iR2;10gR1"/>
		</ValidIf>
			<QueryDescriptor FETCHLET_ID="SQL">
				<Property NAME="STATEMENT" SCOPE="GLOBAL"><![CDATA[
select case when (((select count(*) from v$archive_dest where target='STANDBY') > 0) OR
(database_role like '%STANDBY')) then initcap(database_role) else ' ' end from v$database
]]></Property>
				<Property NAME="MachineName" SCOPE="INSTANCE">MachineName</Property>
				<Property NAME="Port" SCOPE="INSTANCE">Port</Property>
				<Property NAME="SID" SCOPE="INSTANCE">SID</Property>
				<Property NAME="UserName" SCOPE="INSTANCE">UserName</Property>
				<Property NAME="password" SCOPE="INSTANCE">password</Property>
				<Property NAME="Role" SCOPE="INSTANCE" OPTIONAL="TRUE">Role</Property>
      
			</QueryDescriptor>
		</DynamicProperties>
		<DynamicProperties NAME="GetDataGuardStatus" FORMAT="ROW" PROP_LIST="DataGuardStatus">
		<ValidIf>
			<CategoryProp NAME="VersionCategory" CHOICES="10gR2;10gR203;11gR1;11gR2"/>
		</ValidIf>
			<QueryDescriptor FETCHLET_ID="SQL">
				<Property NAME="STATEMENT" SCOPE="GLOBAL"><![CDATA[
select case when (((select count(*) from v$archive_dest where target='STANDBY') > 0) OR
(database_role like '%STANDBY') OR (database_role='PRIMARY' AND FS_FAILOVER_STATUS != 'DISABLED')) then initcap(database_role) else ' ' end from v$database
]]></Property>
				<Property NAME="MachineName" SCOPE="INSTANCE">MachineName</Property>
				<Property NAME="Port" SCOPE="INSTANCE">Port</Property>
				<Property NAME="SID" SCOPE="INSTANCE">SID</Property>
				<Property NAME="UserName" SCOPE="INSTANCE">UserName</Property>
				<Property NAME="password" SCOPE="INSTANCE">password</Property>
				<Property NAME="Role" SCOPE="INSTANCE" OPTIONAL="TRUE">Role</Property>
      
			</QueryDescriptor>
		</DynamicProperties>

<!--
    ======================================================================
    == Dynamic Properties: GetO(racle)T(ext)SchemaExists
    ======================================================================
-->
		<DynamicProperties NAME="GetOTSchemaExists" FORMAT="ROW"  PROP_LIST="OTSchemaExists">
			<QueryDescriptor FETCHLET_ID="SQL">
				<Property NAME="STATEMENT" SCOPE="GLOBAL"><![CDATA[
DECLARE
   lpar               VARCHAR2(100);
   lret               NUMBER;
   sqlstr             VARCHAR2(256);
   TYPE data_cursor_type IS REF CURSOR;
   data_cursor       data_cursor_type;
BEGIN
  lret := 0;
  lpar := ' ';
  sqlstr := 'select count(*) as "OTSchemaExists" from ALL_USERS where USERNAME=''CTXSYS''';
  SELECT status INTO lpar FROM v$instance;
  IF (lpar = 'OPEN') THEN
    execute immediate sqlstr into lret;     
  END IF;
  OPEN data_cursor FOR SELECT TO_CHAR(lret) FROM dual;
  :1 := data_cursor;
END;
]]></Property>
        <Property NAME="SQLOUTPARAMPOS" SCOPE="GLOBAL">1</Property>
        <Property NAME="SQLOUTPARAMTYPE" SCOPE="GLOBAL">SQL_CURSOR</Property>
				<Property NAME="MachineName" SCOPE="INSTANCE">MachineName</Property>
				<Property NAME="Port" SCOPE="INSTANCE">Port</Property>
				<Property NAME="SID" SCOPE="INSTANCE">SID</Property>
				<Property NAME="UserName" SCOPE="INSTANCE">UserName</Property>
				<Property NAME="password" SCOPE="INSTANCE">password</Property>
				<Property NAME="Role" SCOPE="INSTANCE" OPTIONAL="TRUE">Role</Property>
       
			</QueryDescriptor>
		</DynamicProperties>
