CREATE DATABASE SCRIPT - Sample

 

CREATE DATABASE mydb

   USER SYS IDENTIFIED BY password

   USER SYSTEM IDENTIFIED BY password

   LOGFILE

GROUP 1 ('/u01/oracle/oradata/mydb/redo01.log') SIZE 100M,

GROUP 2 ('/u01/oracle/oradata/mydb/redo02.log') SIZE 100M,

GROUP 3 ('/u01/oracle/oradata/mydb/redo03.log') SIZE 100M

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXLOGHISTORY 1

   MAXDATAFILES 100

   MAXINSTANCES 1

   CHARACTER SET US7ASCII

   NATIONAL CHARACTER SET AL16UTF16

   DATAFILE '/u01/oracle/oradata/mydb/system01.dbf' SIZE 325M

                                                                                                REUSE

   EXTENT MANAGEMENT LOCAL

   SYSAUX DATAFILE '/u01/oracle/oradata/mydb/sysaux01.dbf' SIZE

                                                                                       325M REUSE

   DEFAULT TABLESPACE users

   DATAFILE '/u01/app/oracle/oradata/mydb/users.dbf' SIZE 500M

   DEFAULT TEMPORARY TABLESPACE tempts1

      TEMPFILE '/u01/oracle/oradata/mydb/temp01.dbf'

      SIZE 20M REUSE

   UNDO TABLESPACE undotbs

      DATAFILE '/u01/oracle/oradata/mydb/undotbs01.dbf'

      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 

 

Configure RMAN using Catalog

 

Configure RMAN using Catalog

1.  Modify tnsnames.ora file

 

Target DB	Catalog DB
TARGET =   (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = TARGET)(PORT = 1521))       (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = TARGETDB)     )   )   CATALOG =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = CATALOG)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = CATALOGDB)     )   )	TARGET =   (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = TARGET)(PORT = 1521))       (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = TARGETDB)     )   )   CATALOG =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = CATALOG)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = CATALOGDB)     )   )

 

 

2. Startup DB on archivelog mode with Target DB

               -    set parameter

                  LOG_ARCHIVE_DEST_1=’LOCATION=/ARC/’

                           LOG_ARCHIVE_STATE_1=enable

                           LOG_ARCHIVE_START=TRUE 

                           LOG_ARCHIVE_FORMAT= %t_%s_%r.dbf

-       startup database on archivelog mode

$sqlplus /nolog

Sql> connect / as sysdba

Sql> startup mount;

Sql> alter database archivelog;

Sql> alter database open

 

3. Create user for catalog on Catalog DB

Sql> create tablespace RMAN datafile ‘/oradata/catalog/rman01.dbf’ size 50M auto extend on next 5M;

Sql> create user rman identified by rman default tablespace rman temporary tablespace temp quota unlimited on RMAN;

Sql> grant recovery_catalog_owner, connect, resource to rman;

  

4. Start rman and connect to the Target DB (do on target DB)

               $ export ORACLE_SID=TARGET

               $ rman target / catalog rman/rman@catalog

               Rman> create catalog tablespace “RMAN”;

               Rman> register database;

5. Configure parameters on RMAN

               $ export ORACLE_SID=TARGET

               $ rman target / catalog rman/rman@catalog

               Rman> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/dbfile2/backup-cf/%F';

               Rman> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oradata/dbfile2/backup-db2/%U';

               Rman> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/10.1.0/db/dbs/snapcf_cmddb1.f';

               Rman> CONFIGURE CONTROLFILE AUTOBACKUP ON;

 

6. Create resync script (resync every hours)

               $ vi /oracle/resync.sh

               #!/bin/sh

export ORACLE_SID=TARGET

export ORACLE_BASE=/oracle

export ORACLE_HOME=/oracle/product/10.1.0/db

 

/oracle/product/10.1.0/db/bin/rman target / catalog rman/rman@catalog <<EOF

resync catalog;

EOF                

               --------------

               $ chmod 750 /oracle/resync.sh

   And add script to crontab

               0 0 * * * /oracle/rtlstenlog.sh > /dev/null 2>&1

 

STEPS TO CREATE RAW AND UPDATE PARTITIONS

 

Raw device

In computing, specifically Unix-like operating systems, a raw device is a special kind of block device file that allows accessing a storage device such as a hard drive directly, bypassing the operating system's caches and buffers (although the hardware caches might still be used).

a raw device offers a more "direct" route to the physical device and allows an application more control over the timing of IO to that physical device. This makes raw devices suitable for complex applications like Database Management Systems that typically do their own caching.

Raw devices are character devices (major number 162). The first minor number (i.e. 0) is reserved as a control interface and is usually found at /dev/rawctl. A utility called raw (see man raw) can be used to bind a raw device to an existing block device.

STEPS TO CREATE RAW AND UPDATE PARTITIONS:

PART -1

1. To check partitions:  df -Th
2. To check raw partitions: fdisk -l
3. To create Partitions: # fdisk    /dev/sda (enter)           command (m for help): n

First Cylinder: (enter)

Last Cylinder: +2048M

We need to repeat the above to create various partitions. And at last : w (to save)

4. Issue the following command to update:  # partprobe

 

PART – 2

1. To rename the partition: # raw            /dev/raw/raw1 /dev/sda6

                                                    # raw           /dev/raw/raw2 /dev/sda7 ..... so on

PART – 3

Update /etc/sysconfig/rawdevices file with raw information:

Insert: /dev/raw/raw1  /dev/sda6

           /dev/raw/raw2  /dev/sda7 ..... so on

 

PART – 4

Change the ownership:

chown -R oracle:oinstall         /dev/raw/raw*

chmod -R 777 /dev/raw

 

PART – 5 : Open dbca and create ASM instance.

ASM (AUTOMATIC STORAGE MANAGEMENT)

 

Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extension of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.

The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight.

The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.

         Disks

Disk groups are created using the CREATE DISKGROUP statement. This statement allows you to specify the level of redundancy:

• NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
• HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
• EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID.

HOW TO CREATE DISK GROUPS:

CREATE DISKGROUP NEW_GROUP NORMAL REDUNDANCY

    '/dev/raw/raw6' NAME d6,

    '/dev/raw/raw7' NAME d7,

HOW TO DROP DISK GROUP:

DROP DISKGROUP NEW_GROUP  INCLUDING CONTENTS;

HOW TO ADD DISK TO A GROUP

ALTER DISKGROUP NEW_GROUP ADD DISK

  '/dev/raw/raw8';

HOW TO DROP A DISK

ALTER DISKGROUP NEW_GROUP DROP DISK d7;

        ASM Views

The ASM configuration can be viewed using the V$ASM_% views, which often contain different information depending on whether they are queried from the ASM instance, or a dependent database instance.

V$ASM_ALIAS, V$ASM_CLIENT, V$ASM_DISK, V$ASM_DISKGROUP, V$ASM_FILE, V$ASM_OPERATION

V$ASM_TEMPLATE

Output file was found but is zero sized - Deleted [UNEXPECTED] [552457:RT37910837] java.lang.reflect.InvocationTargetException

 Error - 

Output type:   RTF

[6/16/21 6:35:58 AM] [552457:RT37904376] Output file was found but is zero sized - Deleted

[6/16/21 6:35:58 AM] [UNEXPECTED] [552457:RT37904376] java.lang.reflect.InvocationTargetException

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:597)

[6/17/21 1:23:04 PM] [552457:RT37910806] Completed post-processing actions for request 37910806.

[6/17/21 1:31:28 PM] [OPPServiceThread0] Post-processing request 37910837.

[6/17/21 1:31:28 PM] [552457:RT37910837] Executing post-processing actions for request 37910837.

[6/17/21 1:31:28 PM] [552457:RT37910837] Starting XML Publisher post-processing action.

[6/17/21 1:31:28 PM] [552457:RT37910837]

Template code: XLAACCPB02

Template app:  XLA

Language:      en

Territory:     US

Output type:   RTF

[6/17/21 1:31:28 PM] [552457:RT37910837] Output file was found but is zero sized - Deleted

[6/17/21 1:31:28 PM] [UNEXPECTED] [552457:RT37910837] java.lang.reflect.InvocationTargetException

        at sun.reflect.GeneratedMethodAccessor31.invoke(Unknown Source)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

        at java.lang.reflect.Method.invoke(Method.java:597)

        at oracle.apps.xdo.common.xml.XSLT10gR1.invokeGetDocument(XSLT10gR1.java:746)

        at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:449)

        at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:258)

        at oracle.apps.xdo.common.xml.XSLTWrapper.transform(XSLTWrapper.java:182)

        at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:1044)

        at oracle.apps.xdo.template.FOProcessor.generate(FOProcessor.java:941)

        at oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:5978)

        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3470)

        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3559)

        at oracle.apps.fnd.cp.opp.XMLPublisherProcessor.process(XMLPublisherProcessor.java:311)

        at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:176)

Caused by: java.lang.NullPointerException

        at oracle.xdo.parser.v2.FilePageManager.pageLength(FilePageManager.java:242)

        at oracle.xdo.comp.CXMLStream.seek(CXMLStream.java:199)

        at oracle.xdo.comp.CXMLStream.reset(CXMLStream.java:709)

        at oracle.xdo.parser.v2.DOMParser.getDocument(DOMParser.java:143)

        ... 17 more

[6/17/21 1:31:28 PM] [552457:RT37910837] Completed post-processing actions for request 37910837.

Solution - 

Temporary Directory pointing to /usr/tmp and no space available.

Changed the directory to different where ample of space available.

ERROR com.tableausoftware.gateway.GatewayControl - Exception while installing process

Issue - Gateway Service Does not Start and Tableau Server is Degraded

Error - 

ERROR com.tableausoftware.gateway.GatewayControl - Exception while installing process.

com.tableausoftware.tabadmin.configuration.ports.UnableToFindAvailablePortException: Port 80 was already registered for port type gateway_0:primary but isn't available. Unable to remap port

at com.tableausoftware.tabadmin.configuration.ports.PortSelector.selectPort(PortSelector.java:121)

at com.tableausoftware.tabadmin.configuration.ports.ControlAppPortsHelper.selectPorts(ControlAppPortsHelper.java:110)

at com.tableausoftware.service.control.ControlServiceApp$Commands.install(ControlServiceApp.java:341)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at com.tableausoftware.commandline.SwitchCommand$1.run(SwitchCommand.java:193)

at com.tableausoftware.commandline.SimpleCommand.execute(SimpleCommand.java:47)

at com.tableausoftware.commandline.SwitchCommand.execute(SwitchCommand.java:148)

at com.tableausoftware.service.control.ControlServiceApp.execute(ControlServiceApp.java:144)

at com.tableausoftware.service.control.ControlServiceApp.executeCommandWithApp(ControlServiceApp.java:130)

at com.tableausoftware.gateway.GatewayControl.main(GatewayControl.java:79)

2021-06-10 13:18:10.629 -0700 13432 main : INFO  com.tableausoftware.gateway.GatewayControl - Executing command with args [[install]]

2021-06-10 13:18:10.743 -0700 13432 main : INFO  com.tableausoftware.gateway.GatewayControl - Installing gateway

Solution - 

Identified the gateway port 80 is being using Sophos anti virus, changed the default port to 8000

Stopped the Tableau services 

tsm topology set-ports -n node1 -pn gateway -pv 8000

tsm pending-changes apply​​​​​​

Started the Tableau services

Reference - https://kb.tableau.com/articles/Issue/tableau-server-ports-changed-to-8000-and-higher?_gl=1*1jthnyk*_ga*MTQwMTU3ODEyNi4xNjIzMzU2NjM2*_ga_8YLN0SNXVS*MTYyMzM2OTM2OS40LjEuMTYyMzM3MTQxOS4w&_ga=2.139797235.1526428993.1623356641-1401578126.1623356636

Issue - An unexpected RuntimeException or other Throwable occurred -> java.lang.RuntimeException: Connection refused

 

Issue - An unexpected RuntimeException or other Throwable occurred -> java.lang.RuntimeException: Connection refused

Error messages on workflow log file - 

[May 9, 2021 5:17:51 AM MST]:1620562671861:-1:-1:prod1:127.0.0.1:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1515289823:94543:1620562656860:13:ERROR:[SVC-GSM-WFMLRSVC-1504463 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(BusinessEvent)]:java.lang.RuntimeException: Connection refused

at oracle.apps.fnd.wf.mailer.MailerUtils.isSocketConnectableNew(MailerUtils.java:300)

at oracle.apps.fnd.wf.mailer.SMTPUtils.isValidOutbound(SMTPUtils.java:508)

at oracle.apps.fnd.wf.mailer.Mailer.validateParameterValues(Mailer.java:1301)

at oracle.apps.fnd.cp.gsc.SvcComponent.performValidateParameterValues(SvcComponent.java:233)

at oracle.apps.fnd.cp.gsc.SvcComponent.start(SvcComponent.java:314)

at oracle.apps.fnd.cp.gsc.SvcComponentContainer.handleComponentEvent(SvcComponentContainer.java:2212)

at oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(SvcComponentContainer.java:301)

at oracle.apps.fnd.wf.bes.DispatchThread.run(DispatchThread.java:57)

[May 9, 2021 5:17:51 AM MST]:1620562671861:-1:-1:prod1:127.0.0.1:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1515289823:94543:1620562656860:13:UNEXPECTED:[SVC-GSM-WFMLRSVC-1504463 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(BusinessEvent)]:An unexpected RuntimeException or other Throwable occurred -> java.lang.RuntimeException: Connection refused

[May 9, 2021 5:17:51 AM MST]:1620562671862:-1:-1:prod1:127.0.0.1:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1515289823:94543:1620562656860:13:PROCEDURE:[fnd.wf.apps.WorkflowContext]:oracle.apps.fnd.wf.apps.WorkflowContext.getJDBCConnection(): BEGIN/END

[May 9, 2021 5:17:51 AM MST]:1620562671862:-1:-1:prod1:127.0.0.1:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1515289823:94543:1620562656860:13:PROCEDURE:[fnd.wf.apps.WorkflowContext]:oracle.apps.fnd.wf.apps.WorkflowContext.getJDBCConnection(null,0x17d6203,false,false,false): : BEGIN

[May 9, 2021 5:17:51 AM MST]:1620562671862:-1:-1:prod1:127.0.0.1:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1515289823:94543:1620562656860:13:PROCEDURE:[fnd.wf.apps.WorkflowContext]:oracle.apps.fnd.wf.apps.WorkflowContext.getJDBCConnection: END: returning oracle.jdbc.driver.T4CConnection@1e4e6c6

[May 9, 2021 5:17:51 AM MST]:1620562671862:-1:-1:prod1:127.0.0.1:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1515289823:94543:1620562656860:13:EXCEPTION:[SVC-GSM-WFMLRSVC-1504463 : oracle.apps.fnd.cp.gsc.SvcComponentStateMachine.updateComponentStatus(Logger, Connection, int, String, String)]:BEGIN (Logger{mLog=[$Header: AppsLog.java 120.4.12010000.11 2019/10/10 06:06:06 ssambidi ship $ @4297498 {oracle.apps.fnd.profiles.ExtendedProfileStore@1209745}],mUniqueId=SVC-GSM-WFMLRSVC-1504463,mLevel=4}, oracle.jdbc.driver.T4CConnection@1e4e6c6, 10006, STOPPED_ERROR, Could not handle the control event -> java.lang.RuntimeException: Connection refused)

[May 9, 2021 5:17:51 AM MST]:1620562671866:-1:-1:prod1:127.0.0.1:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1515289823:94543:1620562656860:13:PROCEDURE:[fnd.wf.apps.WorkflowContext]:oracle.apps.fnd.wf.apps.WorkflowContext.getJDBCConnection(): BEGIN/END

[May 9, 2021 5:17:51 AM MST]:1620562671866:-1:-1:prod1:127.0.0.1:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1515289823:94543:1620562656860:13:PROCEDURE:[fnd.wf.apps.WorkflowContext]:oracle.apps.fnd.wf.apps.WorkflowContext.getJDBCConnection(null,0x17d6203,false,false,false): : BEGIN

[May 9, 2021 5:17:51 AM MST]:1620562671867:-1:-1:prod1:127.0.0.1:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1515289823:94543:1620562656860:13:PROCEDURE:[fnd.wf.apps.WorkflowContext]:oracle.apps.fnd.wf.apps.WorkflowContext.getJDBCConnection: END: returning oracle.jdbc.driver.T4CConnection@1e4e6c6

Solution - 

1. Go into Oracle Application Manager as the SYSADMIN user.

2. In the upper right corner of the screen next to "Navigate to", please select "Workflow Manager" from the drop down and select "Go".

3. Then in the lower left corner of the screen under Related Links, select "Service Components".

4. Select "Workflow Notification Mailer" and click on "Edit".

5. Enter the correct Outbound(SMTP) Server name and Apply the changes.

6. Bounce the mailer and retest. 

Reference - Workflow Notification Mailer Not Starting After Running AutoConfig With Errors: Unable to make a network connection OR java.lang.RuntimeException: Connection refused (Doc ID 1565672.1)