9/28/2017

SAP Installation of SMD Agent With SWPM SP04

In the Diagnostics Agents page we have seen Diagnostic Agent installation-related Information and all key SAP Notes having all required detail. To summarize that info I would like to introduce some steps on how to install the latest Diagnostic Agent (includes SAP Host Agent). This Diagnostic Agent is compatible with any SP level of SAP Solution Manager 7.1. Also, you don't have to do a separate SAP Host Agent installation, if you have followed these steps. You can also activate the Agents On-the-fly feature with this Diagnostic Agent.


Note:  
When carrying out the below steps, make sure you have read and understood the Diagnostics Agent Installation Strategy PDF file attached with SAP Notes: 1365123  
            and sap note 1833501 for diagnostic agent installer version that is mentioned in the General Information section here: Diagnostics Agents. They explain the strategy, download procedure, and installation details.
Note: These steps have been performed on MS Windows server 2003 Enterprise Edition X64 bit encoding, so in your case, the file name could be different.

Download and extract the Installer media file.
To start the installation you need to first download the  Software Provisioning Manager 1.0 and their relevant kernel file. Once the correct files are downloaded have you need to unpack those.





Following these steps below you can download the Kernal media file.







Once the download is finished then you have to unpack these 2 files



Once you unpack those files then you have something like below,  Once you see these files available then you are ready to launch the installation.



After launching SAPINST.EXE  you will have to select this option below to go ahead.



Here you need to select the path of Kernal file been extracted in the same folder D:\download_basket\SWPM_SP04\Krnl_7.20_WIN__IA32__IA64__x64__SP04_








Note: here on this step 2 user gets created on OS level, you need to make sure on this server these users have admin-level or rights
 also they should have full Read + Write + Execute access to any folder and subfolder available on this server.







Note : This part below registering to the SLD is not advisable with the newer Solution Manager system so this step could be ignored and direct do to the next step Connection of diagnostic agent with Solution Manager. 

On this step if the user credential of target SLD system  is correct then this agent get register to the SLD. Here target SLD Could be any in your landscape usually you should connect this agent to the same SLD where your Managed system is reporting to using RZ70 or JAVA data supplier. This is optional step but it is strongly advise to connect agent with some SLD.





















If you have SAP MMC Installed on the same server then you can see a new Instance of SMD Agent is available, should appear something like this below, from here you can start or stop agent.
 

To make sure if the SMD Agent is connected to the SLD detail you have provided before, just logon to SLD and navigate to this below screen here you will see an agent instance is available under Diagnostic Agent Class.


SAP Uninstallation of SMD Agent Using SWPM SP04

Переход к началу метаданных
In Installation of SMD Agent With SWPM SP04  we have seen how to carry out Installation of SMD Agent . Here we will see how to uninstall that using SWPM. You must delete a Diagnostics Agent instance only using SAPINST comes with SWPM  otherwise you might have problems with further installations on this host.  Before you go ahead with the removal of SMD Agent please make sure you have refre to the Section 4 of this SAP Note : 1833501  and Rule no # 6 of the attached document Agent Installation Strategy from SAP Note : 1365123

Rule No: 6 of SAP Note attched document 1365123

To remove a Diagnostics Agent instance installed for a specific hostname you must remove the JVM parameters described in the Introscope Setup Guide, which is available at service.sap.com/instguides.
Finally restart the Java Managed system to no longer run the Introscope Byte Code Agent
– The Managed System JVM parameters (to be removed) show that the Introscope Byte Code Adapter
executed inside the Managed System uses files from Diagnostics Agent directory structure: 
Xbootclasspath/p:D:/usr/sap/DAA/SMDA77/SMDAgent/applications.config/com.sap.smd.agent.application.wily/BytecodeAgent/ISAGENT.8.2.3.5-2011-01-
14/wily/connectors/AutoProbeConnector.jar;D:/usr/sap/DAA/SMDA77/SMDAgent/applications.config/com.sap.smd.agent.application.wily/BytecodeAgent/ISAGENT.8.2.
3.5-2011-01-14/wily/Agent.jar
Section 4 of SAP Note : 1833501
Make sure to respect the section(s) dealing with "Uninstalling Introscope Agents" here [http://wiki.sdn.sap.com/wiki/x/kgELE].
SAP Note 1259982 documents "Deleting a Diagnostics Agent Manually" for UNIX Operating Systems.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
following above instruction  here below is the test case with steps , how would uninstall SMD Agent.

SWPM

SAP SMDA diagnostic agent

SAP inst  SWMP diagnostics gent uninstall

SAP inst  SWMP diagnostics gent uninstall

SAP inst  SWMP diagnostics gent uninstall

SAP inst  SWMP diagnostics gent uninstall

SAP inst  SWMP diagnostics gent uninstall

Note : If you have register this agent to any SLD then it is also advisable to remove the agent detail from SLD using remove button on this below SLD screen.

SAP diagnostics agents

Diagnostics Agents

Skip to end of metadata

Installation

General Information

Following documents are most relevant for installing and working with the Diagnostics Agent:
  • Refer to SAP Note 1365123 for the Diagnostics Agent installation strategy. 
  • Refer to SAP Note 1833501 for information on Diagnostics Agent installer versions, and for an overview of SAP Notes related to Mass Deployment / Unattended Installation.
  • Refer to SAP Note 1858920 and the latest Installation and Setup Guide for:
    • Information on how to access the Product Availability Matrix (PAM) for the Diagnostics Agent
    • Instructions on how to download the Installation Media
    • Instructions on how to patch the Installation Media
    • Details on supported kernels
    • Description of the unattended/silent installation mechanism
    • SMD Setup Script (smdsetup.bat/smdsetup.sh) documentation
    • More detailed installation instructions

Download Paths

Artifacts required for Diagnostics Agent installations can be downloaded as follows (for detailed instructions and alternative paths refer to latest Diagnostics Agent Installation and Setup Guides):
  • Go to http://support.sap.com/sltoolset
    • Product Availability Matrix/PAM:  search for "Product Availability Matrix"
    • Documentation:  System Provisioning  →  System Provisioning Scenarios →  Install a System using System Provisioning Manager →  Installation Option of Software Provisioning Manager 1.0 → Installation Guides - Standalone Engines and Clients → Diagnostics Agent  → download the guide for your  "Operating System Platform"
    • "installer":  System Provisioning → Download Software Provisioning Manager → SOFTWARE PROVISIONING MGR 1.0 → DOWNLOADS → → SWPM10SP*.SAR
  • For download of individual archives go to SAP Note 2253383
  • Check SAP Note 1858920 and the Product Availability Matrix for supported kernel versions

Recommended Reading

Which agent version should be used?

Please refer to the above mentioned Diagnostics Agent Installation Strategy SAP Note.

Where to install an agent?

Please refer to the above mentioned Diagnostics Agent Installation Strategy SAP Note.

Where to download the agent installation files?

Please refer to the General Information section above.

Connection Configuration

Refer to the installation procedures contained inside the Diagnostics Setup Guide.

Connection options

Before starting the installation make sure that you have identified which installation strategy you want to use. This means that you need to choose between the two following scenarios:
  • Direct Solution Manager Registration: In this scenario, the Diagnostics Agent establishes a direct connection to the Solution Manager system. Using this type of connection no SLD attachment is necessary especially since Solution Manager 7.1 SP05. Operational issues concerning Diagnostics Agents to Solution Manager connectivity can now be easily resolved via the “Non-authenticated Agents” list. (See Non Authenticated Diagnostics Agents).
    Prerequisite: Diagnostics Agent was connected to a Solution Manager 7.1 SP05 system at least once.
  • SLD Registration: In this scenario, the Diagnostics Agent registers itself into the production SLD assigned to the managed system on which the Diagnostics Agent will be installed. This scenario should be used especially if Solution Manager is not yet installed.

SLD Registration - Remotely Connect DIagnostics Agents  - Solution Manager 7.1

If you choose the "SLD Registration" strategy, the Diagnostics Agent should be visible using SOLMAN_SETUP transaction -> System preparation -> Step "Connect Agents". You must connect the relevant Diagnostics Agents to the current Solution Manager system in order to later be able to use them.
Agent reconnection may require waiting several minutes. It depends on settings which are set on the SMD Agent side. As soon as it detects that the association has been changed the SLD state is updated and the registration should proceed.

Direct Solution Manager Registration

If you want to perform the "Direct Solution Manager Registration" after the installation of the Diagnostics Agent, you will need to perform the smdsetup script action managingconf.
You can perform the direct connection to the solution manager in two ways, using the J2EE Message Server HTTP port (recommended). For more information about the smdsetup script refer to the Diagnostics Agent setup guide attached to the SAP Note for the most recent installer release (see SAP Note 1833501).
Example:
  • Connection by Message Server:
smdsetup managingconf hostname:"sapms://" port:"" [optional user:"<...>" pwd:"<...>"]
To run these script we will need to following information:
  1. Full qualified solution manager hostname.
  2. J2EE Message Server HTTP Port, like 81.
  3. Diagnostics Agent system user: For more information about these user check the User Administration Guide.
  4. Diagnostics Agent system user password.
Where to find the J2EE Message Server HTTP port.
  • J2EE Message Server HTTP: This port is usually 81XX (where xx is the SCS instance number: /usr/sap//SCS).
Make sure that you address the Solution Manager Java SCS Message Server with the above mentioned port number. URL.  Enter in the following URL: http://:/msgserver/text/logon
This shall display a P4 and/or P4S line, like here:


Having all necessary information, you have to perform the smdsetup script to connect the Diagnostics Agent to the Solution Manager system:
   1. Open a prompt command and navigate to the script folder inside of the agent path: usr/sap//SMDAXX/script
   2. Run the smdsetup script as the following examples:
  • Connection by Message Server:
     - smdsetup managingconf hostname:"sapms://solman.full.qual.host.name" port:"81XX" user:"SMD_ADMIN" pwd:"XXXXXXXX"
  •  Afterwards, the command should finish successfully:
Then check at the “Agent Administration” if the agent is connected to the Solution manager. If the agent does not connect to the Solman system check the SMDSystem log for error. This log file can be found into the log folder at the agent path.
IMPORTANT: Note that SAP recommends to use the connection via the Solution Manager Java SCS Message Server. Trying out a direct connection via the P4 or P4S port, is only relevant in some very specific situations.

Connect the Diagnostics Agent to Solution Manager Using SSL 

How to Configure the Different Ciphers on the Diagnostic Agent

Please refer to SAP Note 2849162.

Frequently Asked Questions and Trouble Shooting

Please refer to the FAQ Diagnostics Agent.

9/09/2017

Oracle how to move and rename datafiles

First of all, you don't have to restart the database. Renaming data files is very common due to the following reasons. 

Maybe you don't like the name of data file and want to keep a naming method, or the most important, you want to move the datafile to a new, faster filesystem with more empty space. Moving datafiles is something that you need when you will start searching how I/O is spread on you filesystems! 

How to 
For our example. Let's say that the name of the tablespace is TBS1 and the datafile is '/filesystem01/tbs01.dbf' and you want to move it to '/filesystem02/tbs01.dbf' 

1. First take the tablespace offline

ALTER TABLESPACE TBS1 OFFLINE;

If someone is using the tablespace, then you can't take it offline! You must kill all sessions using it. The time needed to take it offline, depends on the size of tablespace, a 10Mbytes tablespace may take 1 sec, a 1TByte tablespace may take some minutes… so be patient. 

2. Move the datafile to the new filesystem with the os command.

$>mv /filesystem01/tbs01.dbf /filesystem02/tbs01.dbf

3. Do the renaming to update the dictionary and controlfile

ALTER TABLESPACE TBS1 RENAME DATAFILE '/filesystem01/tbs01.dbf' 
TO '/filesystem02/tbs01.dbf';
4. Take back tablespace online.

ALTER TABLESPACE TBS1 ONLINE;

Oracle How to find user object grants

Here  with this query you can find the objects grants for a specific user.

SELECT tpm.NAME PRIVILEGE,

       DECODE (MOD (oa.OPTION$, 2), 1, 'YES', 'NO') grantable,
       ue.NAME Grantee, ur.NAME grantor, u.NAME owner,
       DECODE (o.TYPE#,
               0, 'NEXT OBJECT',
               1, 'INDEX',
               2, 'TABLE',
               3, 'CLUSTER',
               4, 'VIEW',
               5, 'SYNONYM',
               6, 'SEQUENCE',
               7, 'PROCEDURE',
               8, 'FUNCTION',
               9, 'PACKAGE',
               11, 'PACKAGE BODY',
               12, 'TRIGGER',
               13, 'TYPE',
               14, 'TYPE BODY',
               19, 'TABLE PARTITION',
               20, 'INDEX PARTITION',
               21, 'LOB',
               22, 'LIBRARY',
               23, 'DIRECTORY',
               24, 'QUEUE',
               28, 'JAVA SOURCE',
               29, 'JAVA CLASS',
               30, 'JAVA RESOURCE',
               32, 'INDEXTYPE',
               33, 'OPERATOR',
               34, 'TABLE SUBPARTITION',
               35, 'INDEX SUBPARTITION',
               40, 'LOB PARTITION',
               41, 'LOB SUBPARTITION',
               42, 'MATERIALIZED VIEW',
               43, 'DIMENSION',
               44, 'CONTEXT',
               46, 'RULE SET',
               47, 'RESOURCE PLAN',
               48, 'CONSUMER GROUP',
               51, 'SUBSCRIPTION',
               52, 'LOCATION',
               55, 'XML SCHEMA',
               56, 'JAVA DATA',
               57, 'SECURITY PROFILE',
               59, 'RULE',
               62, 'EVALUATION CONTEXT',
               'UNDEFINED'
              ) object_type,
       o.NAME object_name, '' column_name
  FROM SYS.objauth$ oa, SYS.obj$ o, SYS.USER$ u, SYS.USER$ ur, SYS.USER$ ue,
 table_privilege_map tpm
 WHERE oa.obj# = o.obj#
   AND oa.grantor# = ur.USER#
   AND oa.Grantee# = ue.USER#
   AND oa.col# IS NULL
   AND oa.PRIVILEGE# = tpm.PRIVILEGE
   AND u.USER# = o.owner#
   AND o.TYPE# IN (2, 4, 6, 9, 7, 8, 42, 23, 22, 13, 33, 32)
   AND ue.NAME = 'NISA_DL'
UNION ALL                                               -- column level grants
SELECT tpm.NAME PRIVILEGE,
       DECODE (MOD (oa.OPTION$, 2), 1, 'YES', 'NO') grantable,
       ue.NAME Grantee, ur.NAME grantor, u.NAME owner,
       DECODE (o.TYPE#,
               0, 'NEXT OBJECT',
               1, 'INDEX',
               2, 'TABLE',
               3, 'CLUSTER',
               4, 'VIEW',
               5, 'SYNONYM',
               6, 'SEQUENCE',
               7, 'PROCEDURE',
               8, 'FUNCTION',
               9, 'PACKAGE',
               11, 'PACKAGE BODY',
               12, 'TRIGGER',
               13, 'TYPE',
               14, 'TYPE BODY',
               19, 'TABLE PARTITION',
               20, 'INDEX PARTITION',
               21, 'LOB',
               22, 'LIBRARY',
               23, 'DIRECTORY',
               24, 'QUEUE',
               28, 'JAVA SOURCE',
               29, 'JAVA CLASS',
               30, 'JAVA RESOURCE',
               32, 'INDEXTYPE',
               33, 'OPERATOR',
               34, 'TABLE SUBPARTITION',
               35, 'INDEX SUBPARTITION',
               40, 'LOB PARTITION',
               41, 'LOB SUBPARTITION',
               42, 'MATERIALIZED VIEW',
               43, 'DIMENSION',
               44, 'CONTEXT',
               46, 'RULE SET',
               47, 'RESOURCE PLAN',
               48, 'CONSUMER GROUP',
               51, 'SUBSCRIPTION',
               52, 'LOCATION',
               55, 'XML SCHEMA',
               56, 'JAVA DATA',
               57, 'SECURITY PROFILE',
               59, 'RULE',
               62, 'EVALUATION CONTEXT',
               'UNDEFINED'
              ) object_type,
       o.NAME object_name, c.NAME column_name
  FROM SYS.objauth$ oa, SYS.obj$ o, SYS.USER$ u, SYS.USER$ ur, SYS.USER$ ue, SYS.col$ c,
 table_privilege_map
 tpm
 WHERE oa.obj# = o.obj#
   AND oa.grantor# = ur.USER#
   AND oa.Grantee# = ue.USER#
   AND oa.obj# = c.obj#
   AND oa.col# = c.col#
   AND BITAND (c.property, 32) = 0                     /* not hidden column */
   AND oa.col# IS NOT NULL
   AND oa.PRIVILEGE# = tpm.PRIVILEGE
   AND u.USER# = o.owner#
   AND o.TYPE# IN (2, 4)
   AND ue.NAME = 'NISA_DL';

check rman backup completion time oracle

You can find your database backup time with the following query



SELECT * FROM (SELECT END.dt, 'Incremental' BACKUP, STAR.TIME started, END.TIME END,  
DECODE(TRUNC(END.TIME - star.TIME), 0, NULL, TRUNC(END.TIME - star.TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(END.TIME - star.TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') TIME
FROM 
(SELECT TRUNC(start_time) dt , MAX(COMPLETION_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='I'
GROUP BY TRUNC(start_time)) END, 
(SELECT TRUNC(start_time) dt, MIN(START_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='I'
GROUP BY TRUNC(start_time)) STAR
WHERE END.dt = STAR.dt 
UNION ALL
SELECT END.dt, 'Full Database' BACKUP, STAR.TIME started, END.TIME END,  
DECODE(TRUNC(END.TIME - star.TIME), 0, NULL, TRUNC(END.TIME - star.TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(END.TIME - star.TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') TIME
FROM 
(SELECT TRUNC(start_time) dt , MAX(COMPLETION_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='D'
GROUP BY TRUNC(start_time)) END, 
(SELECT TRUNC(start_time) dt, MIN(START_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='D'
GROUP BY TRUNC(start_time)) STAR
WHERE END.dt = STAR.dt) 
ORDER BY dt ASC;

DT         BACKUP        STARTED    END        TIME
---------- ------------- ---------- ---------- --------
13/10/2010 Incremental   13/10/2010 13/10/2010 01:16:39
14/10/2010 Incremental   14/10/2010 14/10/2010 02:03:00
16/10/2010 Full Database 16/10/2010 16/10/2010 05:40:09
18/10/2010 Incremental   18/10/2010 18/10/2010 01:55:08
19/10/2010 Incremental   19/10/2010 19/10/2010 01:55:57
20/10/2010 Incremental   20/10/2010 20/10/2010 02:29:29
21/10/2010 Incremental   21/10/2010 21/10/2010 02:10:50
23/10/2010 Full Database 23/10/2010 23/10/2010 06:08:06
25/10/2010 Incremental   25/10/2010 25/10/2010 02:27:07
26/10/2010 Incremental   26/10/2010 26/10/2010 01:34:12
27/10/2010 Incremental   27/10/2010 27/10/2010 01:50:09
28/10/2010 Incremental   28/10/2010 28/10/2010 01:35:10
30/10/2010 Full Database 30/10/2010 30/10/2010 05:24:12
01/11/2010 Incremental   01/11/2010 01/11/2010 02:03:43
02/11/2010 Incremental   02/11/2010 02/11/2010 00:47:35

Oracle How to calculate export dump size

Sometimes you have to export all the database or many schemas but you don't have all the necessary space needed, or you don't know exactly how much space the dmp file it will take. 

Oracle with exports (or expdp) stores at the dmp file the table data. Indexes are just "Create index statements" which will be created after loading the table data. So the key factor of how big will be your dmp file, depends on how much table data you have. 

The following query calculates how much table data each schema takes on your database



SELECT owner, ROUND(SUM(size_mb)) MBytes FROM 
(
SELECT owner, segment_name, segment_type, partition_name,
ROUND(bytes/(1024*1024),2) SIZE_MB,
tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION',
 'TABLE SUBPARTITION') 
--AND TABLESPACE_NAME LIKE 'COSTE%' 
--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%' 
--AND partition_name LIKE 'USAGE_FCT_NEW%'
--AND OWNER = 'TARGET_DW' 
--AND ROUND(bytes/(1024*1024),2) > 1000)
) 
GROUP BY owner 
ORDER BY MBytes DESC;

OWNER                              MBYTES
------------------------------ ----------
TARGET_DW                         3774208
TARGET_POC                         673192
STAGE_DW                           469263
PRESTAGE_DW                        389326
SHADOW_DW                          257233
PRESENT_PERIF                      148063
SNAP                               141565
PKIOUSIS                           117535
DM_SPSS                             44760
MONITOR_DW                          35336
CUSTOMER_VIEW                       29807

for example if you export the SHADOW_DW schema, it will create a dmp file approximately 257233MB->250GBytes. 

Tip: 
Bear in mind that the actual dump file will have smaller size than 250GB!!!, because in most cases the TABLE DATA are fragmented!. Export-Datapump, removes fragmentation!. 

Indexes, packages, procedures, views, etc, are not taking too much space in the dmp file, are just DDL statements.



SELECT 'expdp SYSTEM/oracle DUMPFILE=' || owner || '_%U.dmp
 DIRECTORY=exp_dir PARALLEL=10 LOGFILE='
 || owner ||'.log schemas= ' || owner SQL
FROM (
SELECT owner, ROUND(SUM(size_mb)) MBytes FROM 
(
SELECT owner, segment_name, segment_type, partition_name,
 ROUND(bytes/(1024*1024),2) SIZE_MB,
 tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION',
 'TABLE SUBPARTITION') 
--AND TABLESPACE_NAME LIKE 'COSTE%' 
--AND SEGMENT_NAME LIKE 'OE_ORDER_LINES_ALL%' 
--AND partition_name LIKE 'USAGE_FCT_NEW%'
AND OWNER <> 'SYSTEM' 
--AND ROUND(bytes/(1024*1024),2) > 1000)
) 
GROUP BY owner 
ORDER BY MBytes DESC);

How to create oracle database with sqlplus


In case you want to create an Oracle Database, without the GUI (dbca), use the following simple steps: 

1. First set the enviroment for the oracle user.


export ORACLE_HOME=/oradb/CUSTDB/orabin/11.2.0
export PATH=/oradb/CUSTDB/orabin/11.2.0/bin:$PATH
export ORACLE_SID=CUSTDB
export TNS_ADMIN=/oradb/CUSTDB/orabin/11.2.0/network/admin

2. Create the initSID.ora (for example SID=CUSTDB) 

The file usually is located in the $ORACLE_HOME/dbs/initSID.ora

db_name='CUSTDB'
memory_target=1G
processes = 500
audit_file_dest='/oradb/CUSTDB/orabin/11.2.0/rdbms/audit'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/oradb/CUSTDB/oraarch'
db_recovery_file_dest_size=4G
diagnostic_dest='/oradb/CUSTDB/orabin/oradiag_oracust'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS'
control_files = (/oradb/CUSTDB/oradata1/controlfiles/control1.ctl,
/oradb/CUSTDB/oradata2/controlfiles/control2.ctl,
/oradb/CUSTDB/oradata2/controlfiles/control2.ctl)
compatible ='11.2.0'

3. Login as oracle user and startup nomount the database 

sqlplus "/ as sysdba" 
SQL>startup nomount pfile=$ORACLE_HOME/dbs/initSID.ora 

4. Run the script to create the database


CREATE DATABASE CUSTDB
   USER SYS IDENTIFIED BY password
   USER SYSTEM IDENTIFIED BY password
   LOGFILE GROUP 1 ('/oradb/CUSTDB/oradata1/redologs/redo01_a.rdlog',
 '/oradb/CUSTDB/oradata2/redologs/redo01_b.rdlog') SIZE 1024M,
           GROUP 2 ('/oradb/CUSTDB/oradata2/redologs/redo02_a.rdlog',
 '/oradb/CUSTDB/oradata3/redologs/redo02_b.rdlog') SIZE 1024M,
           GROUP 3 ('/oradb/CUSTDB/oradata3/redologs/redo03_a.rdlog',
 '/oradb/CUSTDB/oradata1/redologs/redo03_b.rdlog') SIZE 1024M,
           GROUP 4 ('/oradb/CUSTDB/oradata1/redologs/redo04_a.rdlog',
 '/oradb/CUSTDB/oradata2/redologs/redo04_b.rdlog') SIZE 1024M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 1000
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/oradb/CUSTDB/oradata1/datafiles/system01.dbf'
 SIZE 3000M REUSE
   SYSAUX DATAFILE '/oradb/CUSTDB/oradata1/datafiles/sysaux01.dbf'
 SIZE 1000M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/oradb/CUSTDB/oradata1/datafiles/users01.dbf'
      SIZE 10M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '/oradb/CUSTDB/oradata1/datafiles/temp01.dbf'
      SIZE 1000M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/oradb/CUSTDB/oradata1/datafiles/undotbs01.dbf'
      SIZE 2000M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Of course, you have to modify appropriately for your case. 

5. Run the following scripts to create dictionary and basic
 database functionality.

sqlplus "/ as sysdba"
SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

login as SYSTEM user and run the script

SQL>@$ORACLE_HOME/sqlplus/admin/pupbld.sql

Oracle Table space vs index space

It is very common as time passes the database to constantly grow. You can calculate the tables and indexes space occupied with the following script.
SELECT SUBSTR(segment_type, 0, 18) SEGMENT, SUM(size_mb) size_mb, 
ROUND((RATIO_TO_REPORT(SUM(size_mb)) OVER ())*100, 2)|| '%' PERC
FROM (
SELECT segment_type, ROUND(SUM(size_mb)) size_mb 
FROM (SELECT owner, segment_name, segment_type, partition_name,
ROUND(bytes/(1024*1024),2)
SIZE_MB, tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('LOBINDEX', 'INDEX PARTITION',
'INDEX SUBPARTITION', 'INDEX', 
'TABLE SUBPARTITION', 'TABLE PARTITION', 'LOB PARTITION',
'LOBSEGMENT', 'TABLE', 'LOB SUBPARTITION') 
ORDER BY bytes DESC) 
GROUP BY segment_type 
ORDER BY size_mb DESC) 
GROUP BY SUBSTR(segment_type, 0, 18);

SEGMENT               SIZE_MB PERC
------------------ ---------- --------
TABLE SUBPARTITION    1980567 17,18%
INDEX PARTITION       1333269 11,56%
LOBINDEX                  298 0%
TABLE PARTITION       1806063 15,67%
LOB PARTITION           12080 ,1%
INDEX SUBPARTITION    2240245 19,43%
LOBSEGMENT             157713 1,37%
TABLE                 3275990 28,42%
INDEX                  722279 6,27%

9 rows selected.

If someone wants to have a better grouping like tables against indexes and lob space can use the following.

SELECT SUBSTR(segment_type, 0, 3) SEGMENT, SUM(size_mb) size_mb,
ROUND((RATIO_TO_REPORT(SUM(size_mb)) OVER ())*100, 2)|| '%' PERCFROM (SELECT segment_type, ROUND(SUM(size_mb)) size_mb
FROM (SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('LOBINDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'INDEX',
'TABLE SUBPARTITION', 'TABLE PARTITION', 'LOB PARTITION', 'LOBSEGMENT', 'TABLE', 'LOB SUBPARTITION')
ORDER BY bytes DESC)
GROUP BY segment_type
ORDER BY size_mb DESC)
GROUP BY SUBSTR(segment_type, 0, 3); SEG SIZE_MB PERC--- ---------- ----------TAB 7062620 61,26%LOB 170091 1,48%IND 4295793 37,26%

Popular Posts