# DCcdb.ctl:202:Collects Oracle Container or Pluggable Database Information # $Id: DCcdb.ctl,v 1.3 2013/12/18 14:06:58 RDA Exp $ # ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/DB/DCcdb.ctl,v 1.3 2013/12/18 14:06:58 RDA Exp $ # # Change History # 20131218 KRA Fix spell. =for stopwords Pluggable =head1 NAME DB:DCcdb - Collects Oracle Container or Pluggable Database Information =head1 DESCRIPTION This module collects the basic Oracle Container or Pluggable Database-related diagnostic information. (Only available for Oracle Database 12c and later) The following reports can be generated and are regrouped under C or C: =cut if !${I_DBC/E} return # Initialization var $TOC = '%TOC%' var $TOP = '[[#Top][Back to top]]' var @TXT # Determine the database type set $sql {SELECT '| ' || " c.con_id || '| ' || " c.dbid || '| ' || " c.con_uid || '|' || " c.guid || ' |' || " c.name || ' |' || " c.open_mode || ' |' || " c.restricted || ' |' || " TO_CHAR(c.open_time,'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " c.create_scn || '| ' || " TO_CHAR(c.total_size / 1048576, '99G999G990D900') || '|' " FROM v$containers c; } call loadSql($sql) =head1 CONTAINER DATABASE =cut if grepLastSql('^\|\s*1\|') {echo tput('bold'),'Processing DB.CDB module (ROOT) ...',tput('off') pretoc '^1:RDBMS' pretoc '1+:Container Database' # Load the common macros run DB:DBinfo() =head2 containers - Containers Gets containers information. =cut debug ' Inside CDB module, reporting containers' report containers write '---+!! Containers' write '| *ID*| *Database ID*| *UID*|*GUID*|*Name*|*Open Mode*|\ *Restricted*|*Open Time*| *Create SCN*| *Total Size (MiB)*|' call writeLastSql() toc '2:[[',getFile(),'][rda_report][Containers]]' =head2 tablespace - Tablespaces Gets tablespace information. =cut debug ' Inside CDB module, processing tablespaces' report tablespace title '---+!! Tablespaces' title $TOC var @TTL = ('',\ '---+ Permanent Tablespaces',\ '---+ Locally Managed Temporary Tablespaces',\ '---+ Tablespace Groups') var @HDR = ('',\ '|*Status*|*Name*| *Container*|*Type*|*Extent Management*| \ *Segment Space Management*| *Size (MiB)*| *Used (MiB)*| *Used (%)*| \ *Initial Extent*| *Next Extent*| *Largest Free Extent*| \ *Minimum Extents*| *Maximum Extents*| *Minimum Extent Length*| \ *Increase (%)*|',\ '|*Status*|*Name*| *Container*| *Size (MiB)*| *Minimum Extents*| \ *Maximum Extents*| *Minimum Extent Length*| *Increase (%)*|',\ '|*Group Name*|*Tablespace Name*| *Container*|') set $sql {SELECT '|' || " d.status || '|' || " d.tablespace_name || '| ' || " d.con_id || '|' || " d.contents || '|' || " d.extent_management || '|' || " d.segment_space_management || '| ' || " TO_CHAR(NVL(a.bytes / 1048576, 0), " '99G999G990D900') || '| ' || " TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1048576 , " '99G999G990D900') || '| ' || " TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), " '990D00') || '| ' || " d.initial_extent || '| ' || " NVL(d.next_extent, 0) || '| ' || " TO_CHAR(NVL(f.largest_free / 1048576, 0), " '99G999G990D900') || '| ' || " d.min_extents || '| ' || " NVL(TO_CHAR(d.max_extents, " '99G999G999G999'), '%NULL%') || '| ' || " NVL(TO_CHAR(d.min_extlen, " '99G999G999G999'), '%NULL%') || '| ' || " NVL(TO_CHAR(pct_increase, " '999'),'%NULL%') || '|' " FROM sys.cdb_tablespaces d, " (SELECT tablespace_name, SUM(bytes) bytes " FROM cdb_data_files " GROUP BY tablespace_name " ) a, " (SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest_free " FROM cdb_free_space " GROUP BY tablespace_name " ) f " WHERE d.tablespace_name = a.tablespace_name " AND d.tablespace_name = f.tablespace_name(+) " ORDER BY d.con_id,d.tablespace_name; "PROMPT ___Macro_separator(2)___ "SELECT '|' || " d.status || '|' || " d.tablespace_name || '|' || " d.con_id || '|' || " TO_CHAR(NVL(a.bytes / 1048576, 0), '99G999G990D900')|| '|' || " d.min_extents || '|' || " NVL(TO_CHAR(d.max_extents, '99G999G999G999'), '%NULL%') || '|' || " NVL(TO_CHAR(d.min_extlen, '99G999G999G999'), '%NULL%') || '|' || " NVL(TO_CHAR(pct_increase, '999'), '%NULL%') || '|' " FROM sys.cdb_tablespaces d, " (SELECT tablespace_name, SUM(bytes) bytes " FROM cdb_temp_files " GROUP BY tablespace_name " ) a " WHERE d.tablespace_name = a.tablespace_name " ORDER BY d.con_id,d.tablespace_name; "PROMPT ___Macro_separator(3)___ "SELECT '|' || " group_name || ' |' || " tablespace_name || ' | ' || " con_id || '|' " FROM sys.cdb_tablespace_groups; } call separator(1) call writeSql($sql) call separator(0,'Tablespaces') =head2 datafile - Database Files Gets data file information. =cut debug ' Inside CDB module, processing datafiles' report datafile title '---+!! Datafile Information' title $TOC write '---+ Database Files' write '|*Status*|*Name*|*Tablespace*| *Container*|*File Number*| \ *Relative File Number*| *Size (MiB)*| *Used (MiB)*| *Used (%)*|\ *Autoextensible*| *Max Blocks*|' var @sep1 = (\ $TOP,\ '---+ TempFiles',\ '|*Status*|*Name*|*Tablespace*| *Container*| *File Number*| \ *Relative File Number*| *Size (MiB)*| *Max Blocks*|'\ ) set $sql {SELECT '|' || " v.status || '|' || " d.file_name || '|' || " d.tablespace_name || '| ' || " d.con_id || '| ' || " d.file_id || '| ' || " d.relative_fno || '| ' || " TO_CHAR((d.bytes / 1048576), " '99999990D000') || '| ' || " TO_CHAR(NVL((d.bytes - NVL(s.bytes, 0)) / 1048576, 0), " '99999990D000') || '| ' || " TO_CHAR((NVL(d.bytes - s.bytes, d.bytes) / d.bytes * 100), " '990D00') || '|' || " NVL(d.autoextensible, 'NO') || '| ' || " d.maxblocks || '|' " FROM sys.cdb_data_files d, " v$datafile v, " (SELECT file_id, SUM(bytes) bytes " FROM sys.cdb_free_space " GROUP BY file_id " ) s " WHERE s.file_id(+) = d.file_id " AND d.file_name = v.name " ORDER BY d.con_id,d.file_name; "PROMPT ___Separator(sep1)___ "SELECT '|' || " v.status || '|' || " d.file_name || '|' || " d.tablespace_name || '| ' || " d.con_id || '| ' || " d.file_id || '| ' || " d.relative_fno || '| ' || " TO_CHAR((d.bytes / 1048576), '99999990D000') || '| ' || " d.maxblocks || '|' " FROM sys.cdb_temp_files d, " v$tempfile v " WHERE (d.file_name = v.name) " ORDER BY d.con_id,d.file_name; } call writeSql($sql,4) if getSqlMessage() write last,'%BR%' write $TOP if isCreated() toc '2:[[',getFile(),'][rda_report][Database Files]]' } =head1 PLUGGABLE DATABASE =cut elsif grepLastSql('^\|\s*\d+\|','f') {var (undef,@dsc) = split('\s*\|\s*',last) echo tput('bold'),'Processing DB.CDB module (PDB) ...',tput('off') pretoc '^1:RDBMS' pretoc '1+:',$CONTAINER = $dsc[4],' Pluggable Database' # Load the common macros #run DB:DBinfo() =head2 container - Container Description Gets the container description. =cut debug ' Inside CDB module, reporting container description' report container write '---+!! Container ',$CONTAINER write '| *ID*| *Database ID*| *UID*|*GUID*|*Name*|*Open Mode*|\ *Restricted*|*Open Time*| *Create SCN*| *Total Size (MiB)*|' call writeLastSql() toc '2:[[',getFile(),'][rda_report][Container Description]]' } # Disable the group title in next index if isTocCreated(true) toc '-:RDBMS' =head1 SEE ALSO L, L, =begin credits =over 10 =item RDA 8.01: Jaime Alcoreza =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved. =head1 TRADEMARK NOTICE Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. =cut