Oracle 12c – How to Install OLAPTRAIN Sample Schema

installationoracleoracle-12c

I need to install the sample schema available from the Oracle website:

http://download.oracle.com/otndocs/products/warehouse/olap/olaptrain_install.zip

I am trying to run the commands as given here:

http://www.oracle.com/technetwork/database/enterprise-edition/readme-098894.html

But I get this error:

ORA-01537: cannot add file 'dm.dbf' - file already part of database

Given below is the copy of the screen:

SQL> ALTER SESSION SET CONTAINER = pdborcl;

Session altered.

SQL> @install_olaptrain.sql

Specify file system directory containing this installation program.

Directory: C:\Users\Ashish\Desktop\olaptrain_install

Specify a password for the OLAPTRAIN user.
Password:

Begin installation
... creating tablespaces
  declare
*
ERROR at line 1:
ORA-01537: cannot add file 'dm.dbf' - file already part of database
ORA-06512: at line 31

How can I resolve this error?

Best Answer

The problem is solved after changing the location for datafile in 'install_olaptrain.sql', and

changing the 'ho impdp' statement to the given below string resolves any login error in Oracle 12c:

ho impdp olaptrain/manager@//localhost:1521/my_pdb dumpfile=OLAPTRAIN12232009.DMP directory=OLAPTRAIN_INSTALL

But then "ORA-06512: at "SYS.UTL_FILE", line 536" occurs.

Related Solutions

Linux – Oracle silent install on Redhat Linux (The Global database name was left blank.)

./runInstaller -silent responseFile=/home/oracle/db_install.rsp -showProgress DECLINE_SECURITY_UPDATES=TRUE

The above line is wronge. I changed it like the following and it works fine.

./runInstaller -silent -responseFile /home/oracle/db_install.rsp -showProgress

Troubleshooting / traceroute within Oracle Instant Client

There is no trcroute for the instant client, but even if you had the full administrator client, trcroute may not return anything useful.

By the way, from the command line I can ping the destination DB (defined in tnsnames.ora) just fine, so I believe I can completely rule out the networking (e.g. DNS/firewall/accessibility).

And that is the mistake people usually make, based on a false assumption. Just because you can ping, let's say, even tnsping the database server, does not mean at all you will be able to connect or name resolution works properly. Ping and tnsping checks work only up to the first entry point in a database connection, and they stop there, these tools alone are not enough for testing database connections.

Based on my experience, ORA-12545/TNS-12545 is a result of indirect name resolution problems. Even the official description suggests trying IP addresses instead of host names:

12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name.  Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.

Whenever you connect through a listener, the listener may forward your request to another address that a participant does not understand => ORA-12545.

Make sure your client and server can resolve all addresses specified in the connection string, especially the addresses that appear in the output of lsnrctl services on the database server.

And below is an example.

My client:

[oracle@o62 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.6.2     o62.balazs.vm o62
192.168.12.5    o6ca-scan.balazs.vm o6ca-scan

My TNS entry:

[oracle@o62 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
MINDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = o6ca-scan.balazs.vm)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mindb)
    )
  )

Run some checks:

[oracle@o62 ~]$ ping o6ca-scan.balazs.vm -c 3
PING o6ca-scan.balazs.vm (192.168.12.5) 56(84) bytes of data.
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=1 ttl=64 time=0.090 ms
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=2 ttl=64 time=0.111 ms
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=3 ttl=64 time=0.094 ms

--- o6ca-scan.balazs.vm ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 1998ms
rtt min/avg/max/mdev = 0.090/0.098/0.111/0.012 ms

[oracle@o62 ~]$ tnsping mindb

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:35:06

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o6ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mindb)))
OK (0 msec)

I can ping the host in my TNS entry, even tnsping works, so I will be able to connect, right? Well, no:

[oracle@o62 ~]$ sqlplus bp/bp@mindb

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 7 18:36:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist

So let's check what trcroute says:

[oracle@o62 ~]$ trcroute mindb

Trace Route Utility for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:36:59

Copyright (c) 1995, 2014, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
07-APR-2016 18:36:59 ADDRESS= PROTOCOL=TCP  HOST=o6ca-scan.balazs.vm  PORT=1521

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
07-APR-2016 18:36:59 ADDRESS= PROTOCOL=TCP  HOST=o6ca-scan.balazs.vm  PORT=1521

No errors, everything should be OK, still, I can't connect. So much for trcroute.

Now let's do some real troubleshooting, and check the server side. The address I am connecting to:

[oracle@o6ca1 ~]$ lsnrctl status listener_scan1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:40:13

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-APR-2016 18:15:18
Uptime                    0 days 0 hr. 24 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/grid12102/network/admin/listener.ora
Listener Log File         /opt/oracle/base/diag/tnslsnr/o6ca1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.5)(PORT=1521)))
Services Summary...
Service "mindb" has 2 instance(s).
  Instance "mindb1", status READY, has 1 handler(s) for this service...
  Instance "mindb2", status READY, has 1 handler(s) for this service...
The command completed successfully

And the services registered here:

[oracle@o6ca1 ~]$ lsnrctl services listener_scan1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:39:37

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "mindb" has 2 instance(s).
  Instance "mindb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=o6ca1-vip.balazs.vm)(PORT=1521))
  Instance "mindb2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=o6ca2-vip.balazs.vm)(PORT=1521))

Notice in the above output, the mindb service has 2 instances/handlers, with different addresses. Whenever I connect to o6ca-scan.balazs.vm, my requests will be forwarded to o6ca1-vip.balazs.vm or o6ca2-vip.balazs.vm. My client can't understand those addresses, but if I fix it:

[oracle@o62 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.6.2     o62.balazs.vm o62
192.168.12.3    o6ca1-vip.balazs.vm o6ca1-vip
192.168.12.4    o6ca2-vip.balazs.vm o6ca2-vip
192.168.12.5    o6ca-scan.balazs.vm o6ca-scan
[oracle@o62 ~]$ sqlplus bp/bp@mindb

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 7 18:45:13 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Apr 07 2016 18:45:07 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL>

The above is a very common mistake when using Oracle RAC. But ORA-12545 is not specific to RAC, that can happen in single instance environments as well. All you need is a shared server configuration, and name resolution problems on the database server. Same story short:

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) Services Summary... Service "exdb" has 1 instance(s).   Instance "exdb", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "D000" established:55 refused:0 current:15 max:1022 state:ready
         DISPATCHER <machine: o61.balazs.vm, pid: 592>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o61.balazs.vm)(PORT=42571))

I have a dispatcher listening on o61.balazs.vm. If I request a shared server connection, and the listener tries to forward the request to o61.balazs.vm, but can not resolve that address, that will result an ORA-12545 as well.

Best Answer

Related Solutions

Linux – Oracle silent install on Redhat Linux (The Global database name was left blank.)

Troubleshooting / traceroute within Oracle Instant Client

Related Question