Feed aggregator

how to trap unique id of record with error using <SQL%BULK_EXCEPTIONS/>

Tom Kyte - Tue, 2016-10-11 12:26
From <SQL%BULK_EXCEPTIONS/> we can find out the error_index. This does not allow us to identify the the particular record with error. We should be able to trap the unique id (primary key or whatever) of the record. Only this will allow us to pinpoint...
Categories: DBA Blogs

Capturing DDL changes on a Table

Tom Kyte - Tue, 2016-10-11 12:26
I am thinking of creating a utility Proc that will capture all Development DDL changes from the Database. This utility will baseline all DDl's for a given release say R1 and while we are developing for release R2 The utility will create the increme...
Categories: DBA Blogs

Identify the Users who had modified Database Objects

Tom Kyte - Tue, 2016-10-11 12:26
Hi Tom, Please can you help me to identify the easiest method to figure out the users who had modified a Database Object like tables,indexes etc. I am a new DBA and would be very helpful if you could advice. Thanks, Vish
Categories: DBA Blogs

appendChildXML with parent-namespace in the child

Tom Kyte - Tue, 2016-10-11 12:26
Hi Chris & Connor I want to append child nodes to an XML but have problems because of the (parent)namespace in the children: The child node in appendchildxml() is not a valid XML because of the namespace that comes from the parent. The XML shou...
Categories: DBA Blogs

PDF pages in Oracle Apex 5

Tom Kyte - Tue, 2016-10-11 12:26
Dear Tom how to open a specific page or range of pages of a PDF file in oracle APEX 5. Let us say that I want to display only pages 15-20 of a PDF file stored in a database table. I am able to open the PDF file in Apex 5, but I need to open a spec...
Categories: DBA Blogs

file not importing in apex 4.2

Tom Kyte - Tue, 2016-10-11 12:26
Guys, I am getting erro, that " File is not valid Application Express application export file " When importing exported SQL application file, I am selecting Unicode UTF - 8 when importing, I am using apex 4.2 version. My possibilitie...
Categories: DBA Blogs

Log file sync

Tom Kyte - Tue, 2016-10-11 12:26
Hi There, I found slow performing pl/sql functionality indicated to be slow only when a backup of rachivelog is in progress. Checking the events using AWR and ASH reports there is clear indication for "Log file sync" waiting event for a delete...
Categories: DBA Blogs

Mirantis OpenStack 9.0 installation using VirtualBox – part 1

Yann Neuhaus - Tue, 2016-10-11 10:47

In this series of  blogs, I am going to  give a quick overview of OpenStack and learn how to install it using Mirantis.

“Mirantis is the #1 pure play OpenStack company. More customers rely on Mirantis than any other company to scale out OpenStack without vendor lock-in” (source: https://www.openstack.org/marketplace/distros/distribution/mirantis/mirantis-openstack)

OpenStack is an open source Infrastructure as a Service (IaaS) platform and was born in July 2010 as a collaboration between NASA and Rackspace.

OpenStack is not monolithic but is composed of several projects. I am not going to  detail in all of them now but here are the components I am going to  install in the lab :

  • Horizon : OpenStack Dashboard
  • Keystone : handles all the authentification processes
  • Neutron : creates virtual networks
  • Nova :  heart of the OpenStack project, provides virtualization capabilities
  • Cinder: provides persistent storage to the instances
  • Glance:  provides ready operating systems to the virtual instances

OpenStackCloud

 

Of course, there are many ways to install OpenStack :

  • manually  –> not recommended because very difficult to maintain
  • using a deployment tool like : Ansible, Puppet, Chef, etc ..
  • using a distribution like :
    • Mirantis –> which uses Fuel as automation tool
    • Red Hat –> which is based on  TripleO
    • Rackspace –> which uses Ansible
    • Canonical –> which uses Juju and MaaS amoung other tools
    • […]

Distributions are here to handle:

  • OpenStack’s lifecycle
  • Patches & Upgrades
  • Documentation
  • Bug fixing and so on..

In this series of blogs I am going to  focus on Mirantis which is one of the best ways to get an OpenStack stable, up and running very quickly.
As said before, Mirantis uses Fuel (based on Puppet) as a deployment tool for OpenStack.

This is how the architecture of Fuel looks like:

 

Fuel_Architecture

  • Web UI : provides the Fuel User Interface based on Nginx
  • Keystone : for the authentification process
  • PosgreSQL Database: stores Fuel Master’s informations, about the deployment of the Fuel slave nodes
  • Nailgun : is the heart of the Fuel project which basically converts the choice of the user into commands for Astute workers
  • AMQP : is the message queue which Nailgun uses to give orders to Astute workers
  • Astute : gives node’s configuration to Cobbler and reboot the Fuel slaves node to let Cobller do its job
  • Cobbler :  installs the base Operating System on the Fuel slave nodes
  • MCollective : Orchestration tool for deploying Puppet via MCollective agents
  • MCollective agents: run on all Fuel slave node

 

Sotfware requirements:
– Virtual Box 4.2.12 – 5.0.x
– VirtualBox Extension Pack (to enable PXE boot)
can be downloaded at: https://www.virtualbox.org/wiki/Downloads
– Mirantis 9.0 ISO and Mirantis VirtualBox scripts
can be  download it from https://www.mirantis.com/how-to-install-openstack/

Hardware requirements:
– 64 bit  host operating system
– 8GB RAM at least
– 300GB+ Disk

 

1. Download the openstack/fuel-virtualbox project:

$ git clone https://github.com/openstack/fuel-virtualbox.git
Cloning into 'fuel-virtualbox'...
remote: Counting objects: 741, done.
remote: Total 741 (delta 0), reused 0 (delta 0), pack-reused 741
Receiving objects: 100% (741/741), 338.50 KiB | 0 bytes/s, done.
Resolving deltas: 100% (492/492), done.
Checking connectivity... done.


2. Go to the fuel-virtualbox directory and put the Mirantis OpenStack .ISO in the iso/ directory

$ cd fuel-virtualbox/
$ ls -l
total 104
drwx------ 1 sbe sbe 4096 Oct 4 11:14 actions
-rw------- 1 sbe sbe 1091 Jun 15 15:04 clean.sh
-rw------- 1 sbe sbe 7277 Oct 10 10:14 config.sh
drwx------ 1 sbe sbe 0 Oct 3 14:02 contrib
drwx------ 1 sbe sbe 0 Oct 3 14:02 drivers
-rw------- 1 sbe sbe 61122 Jun 15 15:04 dumpkeys.cache
drwx------ 1 sbe sbe 4096 Oct 4 10:44 functions
drwx------ 1 sbe sbe 0 Oct 10 10:11 iso
-rw------- 1 sbe sbe 653 Oct 4 10:40 launch_16GB.sh
-rw------- 1 sbe sbe 652 Jun 15 15:04 launch_8GB.sh
-rw------- 1 sbe sbe 1308 Jun 15 15:04 launch.sh
-rw------- 1 sbe sbe 1462 Jun 15 15:04 MAINTAINERS
-rw------- 1 sbe sbe 1939 Jun 15 15:04 README.md

You can see that there are two launch_X.sh file; one for 16GB and one for 8 GB. For testing purpose I will use the launch_8GB.sh script. One important file here is config.sh because it is where you set up the hardware configurations (RAM, Disk, CPU) for the Fuel master node and the Fuel slave nodes. You can have a look on it for more details. If you run a 16GB RAM machine, then you can use the “launch_16GB.sh” script.

By default, for 8 GB, the script will create 4 machines:

– one Fuel Master node with 2 GB RAM and 60 GB disk
– 3 Fuel slave nodes with 1.5 GB RAM and 3 disk of 65 GB

So the lab will looklike to this :

fuelarchicorrige

 

  • PXE network :  used by the Fuel Master node administrate the Fuel slave nodes and install OpenStack
  • Managament network :  for OpenStack services communication within the cloud
  • External network : to access the Internet
  • Private network : the inter-instances communication network within the OpenStack cloud
  • Storage network : used by instances to access the storage

3. Then launch the script :

$  ./launch_8GB.sh 
Prepare the host system...
Checking for 'dumpkeys.cache'... OK
Checking for 'expect'... OK
Checking for 'xxd'... OK
Checking for 'VBoxManage'... OK
Checking for VirtualBox Extension Pack... OK
Checking for VirtualBox iPXE firmware...SKIP
VirtualBox iPXE firmware is not found. Used standard firmware from the VirtualBox Extension Pack.
Checking for Mirantis OpenStack ISO image... OK
Going to use Mirantis OpenStack ISO file: iso/MirantisOpenStack-9.0.iso
Checking if SSH client installed... OK
Checking if ipconfig or ifconfig installed... OK
Done.


bootstrapfinished

Now the Fuel Master node is going to download a special Linux image.

Once the bootstrap image is downloaded the Fuel slave nodes boots up with this image :

bootstrap

This image will send to the Fuel Master all the hardware informations of the Fuel slave nodes which are called “facts”.

This is an important step because via this image the Fuel Master node will discover the slave nodes.

bootstrapfinished2

Slave nodes have been created. They will boot over PXE and get discovered by the master node.
To access master node, please point your browser to:

http://10.20.0.2:8000/

The default username and password is admin:admin

This concludes the first part of the blog. In the next blog, I will show you  the interface of Fuel and how to install OpenStack on the Fuel slave nodes.

 

Cet article Mirantis OpenStack 9.0 installation using VirtualBox – part 1 est apparu en premier sur Blog dbi services.

OTN Appreciation Day : OSWatcher Black Box Analyzer (OSWBBA)

Yann Neuhaus - Tue, 2016-10-11 10:23

Following my last blog post about OSWatcher, I will present in this one OSWatcher Black Box Analyzer (OSWBBA), which is the tool that you can use to display graphically the data collected by OSWBB.
This tool is a Java utility and exists since OSWatcher version 4.0.0. It permits to create graphs and complete HTML reports containing collected OS statistics.  Image1

OSWBBA require no installation. It is embedded in the OSWatcher home directory.
To start the Analyser, run oswbba.jar :
$ java -jar oswbba.jar -i ./archive
Starting OSW Analyzer V7.3.3
OSWatcher Analyzer Written by Oracle Center of Expertise
Copyright (c)  2014 by Oracle Corporation
Parsing Data. Please Wait...
Scanning file headers for version and platform info...
Parsing file srvtestoel7.it.dbi-services.com_iostat_16.07.25.2000.dat
...

The ”–i” parameter indicates the OSWatcher archive directory and is mandatory.
Once launched, the main menu is displayed :
Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs
Enter 6 to Generate All CPU Gif Files
Enter 7 to Generate All Memory Gif Files
Enter 8 to Generate All Disk Gif Files
Enter L to Specify Alternate Location of Gif Directory
Enter T to Alter Graph Time Scale Only (Does not change analysis dataset)
Enter D to Return to Default Graph Time Scale
Enter R to Remove Currently Displayed Graphs
Enter A to Analyze Data
Enter S to Analyze Subset of Data(Changes analysis dataset including graph time scale)
Enter P to Generate A Profile
Enter X to Export Parsed Data to File
Enter Q to Quit Program

You must enable a X-Windows environment to display graphs.

If you don’t want to go through this menu every time you want to display graph or generate report, you can pass all of the above options to OSWBBA from the command line, for example :
$ java -jar oswbba.jar -i ./archive -4 -P last_crash

  • -i  : Specify the archive directory
  • -4 : Create memory graphs
  • -P : Create a profile called “last_crash”

Other options :

  • -6..8 : Same options as in the menu
  • -L : User specified location to place gif files
  • -A : Create a report
  • -B : Specify the start time to analyze (format Mon DD HH:MM:SS YYYY)
  • -E : Specify the end time to analyze  (format Mon DD HH:MM:SS YYYY)
  • -F : Specify a filename of a text file containing a list of options
    (all others options are ignored if –F is used)

Example :
$ java -jar oswbba.jar -i ./archive -6 -B Sep 23 09:25:00 2016 -E Sep 23 09:30:00 2016
Will start OSWatcher Analyzer with the following parameters :

  • Archive directory : $OSWatcher_HOME/archive
  • Generate all CPU GIF files
  • Time slot : 23 of Septembre 2016 – 09:25:00 to 09:30:00

Generated file :
Untitled

It’s also possible to specify in a text file all options you want to use and then run OSWBBA with the “-f” parameter :
$ cat input.txt
-P today_crash -B Sep 23 09:00:00 2016 -E Sep 23 11:00:00 2016
$ java -jar oswbba.jar -i ./archive -F input.txt

This will generate a complete HTML report (called “today_crash”) with all available graphs based on the statistics stored in the archive directory.

 

Cet article OTN Appreciation Day : OSWatcher Black Box Analyzer (OSWBBA) est apparu en premier sur Blog dbi services.

OTN Appreciation Day : OSWatcher Black Box (OSWBB)

Yann Neuhaus - Tue, 2016-10-11 10:22

In this post, I will present a usefull and easy-to-use Oracle tool : OSWatcher.

Image1What is it ?

OSWatcher Black Box (OSWBB), for its full name, is a free Oracle Tool which will help you to diagnose performance issues on the OS side.
Of course, it will not solve the issue for you, but it gives a system health state at a given moment.
OSWBB is multi-platforms supported (AIX, Solaris, HP-UX, Linux and Windows) and is installed by default on Oracle Database Appliance (ODA).

How does it work ?

OSWatcher invoke OS utilities like vmstat, netstat, iostat, etc. by creating a “Data Collectors” for each of them available on the system. The “Data Collectors” works as background processes to collect periodically the data provided by these different OS utilities.
Once collected, all the statistics are stored inside a common destination (archive directory).

Image2

Below is the content of the archive directory. As you can see there is a dedicated folder for each type of OS statistics collected :
oracle@srvtestoel7:/u01/app/oracle/product/oswbb/archive/ [JOCDB1] ll
total 36
-rw-r--r-- 1 oracle oinstall 1835 28 sept. 16:55 heartbeat
drwxr-xr-x 2 oracle oinstall 4096 28 sept. 16:52 oswifconfig
drwxr-xr-x 2 oracle oinstall 4096 28 sept. 16:52 oswiostat
drwxr-xr-x 2 oracle oinstall 4096 28 sept. 16:52 oswmeminfo
drwxr-xr-x 2 oracle oinstall 4096 28 sept. 16:52 oswmpstat
drwxr-xr-x 2 oracle oinstall 4096 28 sept. 16:52 oswnetstat
drwxr-xr-x 2 oracle oinstall 6 23 sept. 09:18 oswprvtnet
drwxr-xr-x 2 oracle oinstall 4096 28 sept. 16:52 oswps
drwxr-xr-x 2 oracle oinstall 6 23 sept. 09:18 oswslabinfo
drwxr-xr-x 2 oracle oinstall 4096 28 sept. 16:52 oswtop
drwxr-xr-x 2 oracle oinstall 4096 28 sept. 16:52 oswvmstat

Downloading

You can download OSWatcher from My Oracle Support – Doc ID 301137.1 (.tar file – 6Mb)

Installing

To install OSWatcher, you simply have to untar the downloaded file :
$ tar -xvf oswbb733.tar
All necessary files are stored in the oswbb folder.

Uninstalling

To remove OSWatcher from your server, you only have to :
– Stop all OSWatcher running processes
– Delete the oswbb folder

Starting

$ nohup ./OSWatcher.sh P1 P2 P3 P4

Parameters

– P1 = snapshot interval in seconds (default : 30 seconds)
– P2 = number of hours of archive data to store (default : 48 hours)
– P3 = name of a compress utility to compress each file automatically (default : none)
– P4 = alternate location to store the archive directory (default : oswbb/archive)

You can also set the UNIX environment variable oswbb_ARCHIVE_DEST to specify a non-default location.

Startup steps

Starting OSWatcher involve 4 steps :

  1. Check parameters
    $ ./OSWatcher.sh 60 24 gzip /tmp/oswbb/archive
    Info...Zip option IS specified.
    Info...OSW will use gzip to compress files.
    ...
  2. Discover OS utilities
    Testing for discovery of OS Utilities...
    VMSTAT found on your system.
    IOSTAT found on your system.
    MPSTAT found on your system.
    ...
    ...
    Discovery completed.
  3. Discover CPU count
    Testing for discovery of OS CPU COUNT
    oswbb is looking for the CPU COUNT on your system
    CPU COUNT will be used by oswbba to automatically look for cpu problems
    CPU COUNT found on your system.
    CPU COUNT = 1
  4. Data collection
    Data is stored in directory: /tmp/oswbb/archive
    Starting Data Collection...
    oswbb heartbeat:mar. sept. 13 22:03:33 CEST 2016
    oswbb heartbeat:mar. sept. 13 22:04:33 CEST 2016
    oswbb heartbeat:mar. sept. 13 22:05:33 CEST 2016
Check if OSWBB is running

$ ps -ef | grep OSWatcher | grep -v grep
oracle    8130     1  0 13:47 pts/0    00:00:33 /bin/sh ./OSWatcher.sh 5 48
oracle    8188  8130  0 13:47 pts/0    00:00:00 /bin/sh ./OSWatcherFM.sh 48 /u01/app/oracle/product/oswbb/archive

The OSWatcherFM.sh process is the file manager who delete collected statitstics once they have reached their retention.

Stopping

Run the stopOSWbb.sh to stop all OSWatcher processes
$ ./stopOSWbb.sh

Configure automatic startup

Oracle provide a RPM package to configure auto-start of OSWatcher when the system starts.
You can download it here : My Oracle Support – Doc ID 580513.1
Once downloaded, install the package (as root) :
$ rpm -ihv oswbb-service-7.2.0-1.noarch.rpm
Preparing... ######################################### [100%] 1:oswbb-service    ######################################### [100%]

You can adapt the following values in /usr/libexec/oswbb-service/oswbb-helper to define the parameters with which OSWatcher will auto-starts :
OSW_HOME='/u01/app/oracle/product/oswbb/'
OSW_INTERVAL='10'
OSW_RETENTION='48'
OSW_USER='oracle'
OSW_COMPRESSION='gzip'
OSW_ARCHIVE='archive'

Start the service :
$ service oswbb start
Starting oswbb (via systemctl): [ OK ]

Check the service :
$ service oswbb status
OSWatcher is running.

Stop the service :
$ service oswbb stop
Stopping oswbb (via systemctl):  Warning: Unit file of oswbb.service changed on disk, 'systemctl daemon-reload' recommended.
[  OK  ]

Enable the service when the system start :
$/sbin/chkconfig oswbb on

Systemd commands (Linux 7) :
$ systemctl stop oswbb.service
$ systemctl start oswbb.service
$ systemctl status oswbb.service
$ systemctl enable oswbb.service

Inside the archive directory, one dedicated folder is created by type of collected statistics :
oracle@srvtestoel7:/u01/app/oracle/product/oswbb/archive/ [JOCDB1] ll
total 0
drwxr-xr-x 2 oracle oinstall 136 23 sept. 10:00 oswifconfig
drwxr-xr-x 2 oracle oinstall 132 23 sept. 10:00 oswiostat
drwxr-xr-x 2 oracle oinstall 134 23 sept. 10:00 oswmeminfo
drwxr-xr-x 2 oracle oinstall 132 23 sept. 10:00 oswmpstat
drwxr-xr-x 2 oracle oinstall 134 23 sept. 10:00 oswnetstat
drwxr-xr-x 2 oracle oinstall 6 23 sept. 09:18 oswprvtnet
drwxr-xr-x 2 oracle oinstall 124 23 sept. 10:00 oswps
drwxr-xr-x 2 oracle oinstall 6 23 sept. 09:18 oswslabinfo
drwxr-xr-x 2 oracle oinstall 126 23 sept. 10:00 oswtop
drwxr-xr-x 2 oracle oinstall 132 23 sept. 10:00 oswvmstat

In a following blog post, I’ll present OSWatcher Black Box Analyzer (oswbaa), which is a tool used to analyze graphically the collected data.

 

Cet article OTN Appreciation Day : OSWatcher Black Box (OSWBB) est apparu en premier sur Blog dbi services.

OTN Appreciation Day – tnsping

Yann Neuhaus - Tue, 2016-10-11 08:38

Tim Hall had the idea that as many people as possible would write a small blog post about their favorite Oracle feature and we all post them on the same day. I do have a lot of favorite Oracle tools, and the one I choose today is: tnsping

tnsping tells you, if your connect string can be resolved and if the listener where the connect string is pointing to, is available, and in the end, it displays an estimate of the round trip time (in milliseconds) it takes to reach the Oracle Net service.

All in all, tnsping is very easy to use and that’s why I love it, and not so overloaded like e.g. crsctl.  In fact, tnsping knows only 2 parameters. <address> and optionally <count>, like shown in the following example.

Usage: tnsping <address> [<count>]

Getting the option list of tnsping, a few lines are enough. I don’t need to scroll down several pages, like e.g. for emctl.  emctl is another one besides crsctl were you can spend a lifetime only reading the manual.  No, I picked tnsping this time because I like the option list.

Here we go … now I run one tnsping without and one with count.

oracle@oel001:/home/oracle/ [OCM121] tnsping RMAN
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 11-OCT-2016 14:28:14
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oel001)(PORT = 1521))) 
(CONNECT_DATA = (SERVICE_NAME = OCM121)))
OK (0 msec)

oracle@oel001:/home/oracle/ [OCM121] tnsping RMAN 5
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 11-OCT-2016 14:28:20
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oel001)(PORT = 1521))) 
(CONNECT_DATA = (SERVICE_NAME = OCM121)))
OK (0 msec)
OK (10 msec)
OK (0 msec)
OK (0 msec)
OK (0 msec)

But … wait a second … my RMAN connect string points to host oel001, but it should point to oel002. Let’s take a look in $ORACLE_HOME/network/admin/tnsnames.ora

oracle@oel001:/u00/app/oracle/ [OCM121] cat /u00/app/oracle/product/12.1.0.2/network/admin/tnsnames.ora
RMAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel002)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = OCM121)
    )
  )

It looks correct. So what is going on here. There are several explanations to this issue.

1.) You might have set the TNS_ADMIN environment variable, which points to a total different directory
2.) Or your sqlnet.ora might point to a LDAP server first, which resolves the name
3.) Or a total different tnsnames.ora file is taken into account, but which one?
4.) Or something totally different, e.g. corrupt nscd, symlinks …

For quite a long time, Oracle is not searching first in the $ORACLE_HOME/network/admin/tnsnames.ora
to get the name resolved. The tnsnames.ora search order is the following:

1.) $HOME/.tnsnames.ora    # yes, it looks up the a hidden file in your home directory first
2.) /etc/tnsnames.ora    # then, a global tnsnames.ora in the /etc directory
3.) $ORACLE_HOME/network/admin/tnsnames.ora    # and last but not least, it looks it up in the $ORACLE_HOME/network/admin

To prove it, simply run a strace on your tnsping command and take a look at the trace file.

$ strace -o /tmp/tnsping.trc -f tnsping RMAN
$ cat /tmp/tnsping.trc | grep tnsnames

21919 access("/home/oracle/.tnsnames.ora", F_OK) = 0
21919 access("/etc/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
21919 access("/u00/app/oracle/product/12.1.0.2/network/admin/tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
21919 stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=173, ...}) = 0
21919 open("/home/oracle/.tnsnames.ora", O_RDONLY) = 3

Here we go … in my case, the “/home/oracle/.tnsnames.ora” was taken into account. Let’s take a look.
Indeed, I have found an entry here.

oracle@oel001:/home/oracle/ [OCM121] cat /home/oracle/.tnsnames.ora
RMAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel001)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = OCM121)
    )
  )

Have fun with tnsping.

Cheers,
William

 

 

 

Cet article OTN Appreciation Day – tnsping est apparu en premier sur Blog dbi services.

OTN Appreciation Day: Oracle Text

Joel Kallman - Tue, 2016-10-11 08:13
For OTN Appreciation Day, I was told that it wouldn't be appropriate to write about my favorite Oracle feature (APEX, obviously).  So I'll gladly promote my second-favorite Oracle Database feature...Oracle Text!

I've used Oracle Text for many years - from when it was SQL*TextRetrieval to Oracle ConText Option to Oracle interMedia Text to finally Oracle Text.  This was one of those products that used to be a for-cost option and was merged into the Oracle Database as native, no-cost functionality (how cool is that?).  You can use Oracle Text to index BLOB columns containing Microsoft Word or PDF documents, you can score the query results for relevance, you can perform a proximity search within the contents (find "Oracle" and "APEX" within 10 words of each other), you can search within sections of a document, you can do a fuzzy search, you can create a thesaurus to assist in searching for similar terms, you can create a text result with the matching words highlighted, and on and on.

The beauty of Oracle Text is that it's all completely accessible in SQL.  Any tool that can "talk" SQL can easily take advantage of this rich functionality in the Oracle Database - Java, .NET, PHP, Node, and of course, APEX!  I authored the PL/SQL functions and text indexes (and text queries) for AskTom back in 2001 - and they're still running as fast as ever today.  One of the most popular applications inside of Oracle, an employee directory (1.5M page views every day from 55,000 distinct users), is an APEX application that we're responsible for - and we are in the process of expanding this to use the fuzzy search capabilities of Oracle Text - what is more commonly misspelled than someone's name?  And it's easy, because this is all running inside the Oracle Database.  Whether your content is a string or BLOB or XML or JSON, once this content is inside the Oracle Database, it's accessible to Oracle Text and SQL, and the application development opportunities on top of this are easy.  I'm a big  fan of Oracle Text, and you should take a look at it too!

OTN Appreciation Day : Create Database Using SQL | Thinking Out Loud Blog

Michael Dinh - Tue, 2016-10-11 07:30

Do you ever wonder how to get all parameters for CREATE DATABASE Statement ?

I will be sharing some of the reverse engineering done to create a duplicate copy of the database.

Some of you may be thinking, “Why not just duplicate database or backup and restore?”

For the project I was working on, this was not feasible since Extended Data Types (12c NF) was enabled and there is no going back.

Restoring database from backup would result in too much data loss.

This leaves the only option is to create new database with max_string_size=standard, and perform full export/import.

From backup controlfile to trace:
SYS@DB1> alter database backup controlfile to trace as '/tmp/cf_@.sql' reuse resetlogs;
Database altered.

SYS@DB1>
From /tmp/cf_DB1.sql:
$ ll /tmp/cf_DB1.sql
-rw-r--r--. 1 oracle oinstall 2955 Oct 11 04:45 /tmp/cf_DB1.sql

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DB1" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 4
    MAXDATAFILES 400
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/DB1A/onlinelog/o1_mf_1_czl4h9sg_.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/oradata/DB1A/onlinelog/o1_mf_2_czl4h9yr_.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/oradata/DB1A/onlinelog/o1_mf_3_czl4hbdb_.log'  SIZE 200M BLOCKSIZE 512
From DBA_REGISTRY:
DB1:(SYS@DB1):PRIMARY> select name,comp_id,comp_name,version,schema,status
  2  from v$database,dba_registry
  3  order by 2
  4  ;

NAME       COMP_ID      COMP_NAME                                VERSION    SCHEMA     STATUS
---------- ------------ ---------------------------------------- ---------- ---------- --------
DB1        CATALOG      Oracle Database Catalog Views            12.1.0.2.0 SYS        VALID
DB1        CATJAVA      Oracle Database Java Packages            12.1.0.2.0 SYS        VALID
DB1        CATPROC      Oracle Database Packages and Types       12.1.0.2.0 SYS        VALID
DB1        JAVAVM       JServer JAVA Virtual Machine             12.1.0.2.0 SYS        VALID
DB1        XDB          Oracle XML Database                      12.1.0.2.0 XDB        VALID
DB1        XML          Oracle XDK                               12.1.0.2.0 SYS        VALID

6 rows selected.

DB1:(SYS@DB1):PRIMARY>
From DATABASE_PROPERTIES:
DB1:(SYS@DB1):PRIMARY> select property_name,property_value from DATABASE_PROPERTIES;

PROPERTY_NAME                            PROPERTY_VALUE
---------------------------------------- ----------------------------------------
DICT.BASE                                2
DEFAULT_TEMP_TABLESPACE                  TEMP
DEFAULT_PERMANENT_TABLESPACE             USERS
DEFAULT_EDITION                          ORA$BASE
Flashback Timestamp TimeZone             GMT
TDE_MASTER_KEY_ID
EXPORT_VIEWS_VERSION                     8
DEFAULT_TBS_TYPE                         SMALLFILE
GLOBAL_DB_NAME                           DB1
NLS_RDBMS_VERSION                        12.1.0.2.0
NLS_NCHAR_CHARACTERSET                   AL16UTF16
NLS_NCHAR_CONV_EXCP                      FALSE
NLS_LENGTH_SEMANTICS                     BYTE
NLS_COMP                                 BINARY
NLS_DUAL_CURRENCY                        $
NLS_TIMESTAMP_TZ_FORMAT                  DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT                       HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT                     DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT                          HH.MI.SSXFF AM
NLS_SORT                                 BINARY
NLS_DATE_LANGUAGE                        AMERICAN
NLS_DATE_FORMAT                          DD-MON-RR
NLS_CALENDAR                             GREGORIAN
NLS_CHARACTERSET                         AL32UTF8
NLS_NUMERIC_CHARACTERS                   .,
NLS_ISO_CURRENCY                         AMERICA
NLS_CURRENCY                             $
NLS_TERRITORY                            AMERICA
NLS_LANGUAGE                             AMERICAN
DST_SECONDARY_TT_VERSION                 0
DST_PRIMARY_TT_VERSION                   18
DST_UPGRADE_STATE                        NONE
MAX_STRING_SIZE                          STANDARD
DBTIMEZONE                               US/Mountain
WORKLOAD_CAPTURE_MODE
WORKLOAD_REPLAY_MODE
NO_USERID_VERIFIER_SALT                  88C7FDB8D44CA60E05624A08A177722C

37 rows selected.

DB1:(SYS@DB1):PRIMARY>
From V$DATABASE:
DB1:(SYS@DB1):PRIMARY> r
  1  select log_mode,flashback_on,force_logging,created
  2  from V$DATABASE
  3*

LOG_MODE     FLASHBACK_ON       FORCE_LOGGING                           CREATED
------------ ------------------ --------------------------------------- -------------------
ARCHIVELOG   NO                 NO                                      2016-10-08 08:34:02

DB1:(SYS@DB1):PRIMARY>
From: V$BLOCK_CHANGE_TRACKING:
DB1:(SYS@DB1):PRIMARY> r
  1  select status, filename
  2  from V$BLOCK_CHANGE_TRACKING
  3*

STATUS     FILENAME
---------- --------------------
DISABLED

DB1:(SYS@DB1):PRIMARY>
From DBA_SCHEDULER_GLOBAL_ATTRIBUTE:
DB1:(SYS@DB1):PRIMARY> r
  1  select *
  2  from DBA_SCHEDULER_GLOBAL_ATTRIBUTE
  3  order by 1
  4*

ATTRIBUTE_NAME                           VALUE
---------------------------------------- ----------------------------------------
CURRENT_OPEN_WINDOW
DEFAULT_TIMEZONE                         US/Mountain
EMAIL_SENDER
EMAIL_SERVER
EMAIL_SERVER_CREDENTIAL
EMAIL_SERVER_ENCRYPTION                  NONE
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT                       0
LAST_OBSERVED_EVENT
LOG_HISTORY                              30
MAX_JOB_SLAVE_PROCESSES

11 rows selected.

DB1:(SYS@DB1):PRIMARY>
The finished SQL: crdb.sql
spool crdbp.log
set echo on timing on time on
host echo $ORACLE_SID
host sysresv
create spfile from pfile;
startup force nomount;
CREATE DATABASE
MAXINSTANCES 1
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 292
MAXDATAFILES 400
ARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
SET TIME_ZONE='US/Mountain'
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 513M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
SYSAUX DATAFILE SIZE 257M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
LOGFILE GROUP 1 SIZE 200M,GROUP 2 SIZE 200M,GROUP 3 SIZE 200M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 8192M
DEFAULT TABLESPACE users DATAFILE SIZE 129M AUTOEXTEND ON NEXT 128M MAXSIZE 8193M
UNDO TABLESPACE undotbs1 DATAFILE SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE 8192M;
spool off
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catxdbj.sql
@?/rdbms/admin/utlrp.sql
exec dbms_scheduler.set_scheduler_attribute(attribute=>'default_timezone',value=>'US/Mountain');

-- alter system set nls_length_semantics=CHAR scope=both sid='*';
-- alter database flashback on;
-- alter database FORCE LOGGING;
-- alter database enable block change tracking;

connect system/oracle @?/sqlplus/admin/pupbld.sql 
exit

OTN Appreciation Day – The Community

Mathias Magnusson - Tue, 2016-10-11 07:00

So this is my post aboout my favorite feature of my favorite product. I can hear a lot of you say “The community is not a feature of the database or any other product”?

That is your opinion, I think it is the greatest one. I’d say that the mostly friendly Oracle community is by far the most important driver for quality solutions. I know I have and still do learn more from the community than from any manual.

I began using Oracle AskTom back when I started with Oracle. Tom was nice enough to start it up just months before I joined the fun in the Oracle world. From there I started finding all the amazing blogs that let me dig deeper and deeper into the database. Part of finding that community was fidning great presenters at Training Days that RMOUG holds every year. Those two days used to be the professional highlight of the year while I was based in Denver, CO.

My manager at the time used to comment that where she and others just saw a technology I referred to the community over and over. That is how I see it, when people talk about Oracle I think about the company and the community first and about the specific technology after that.

That is still the case to the point where I today try to create a community where one is missing. That one is just a very small piece in the bigger world wide community of Oracle professionals. The user group scene is one of the greatest opportunities available to learn more and to get a chance to share the knowledge one happens to pick up.

Not only is taking part in the community one of the greatest opportunities available to learn critical skills in the technology you focus on, presenting in it on thing you think you know forces you to learn even more about it. It is also a great way to start building a network to others who enjoy sharing and debating technical aspects of Oracle technologies.

Another part of the community is OTN who sponsors a large part of the things that makes the community “one” community. Things like the ACE-program that awards some of the best in the community the ACE-title for their ability to share and educate. The ability for user groups to have ACE Directors to visit and hold a couple of presentations is a fantastic thing for every member of a user group.

Going to conferences and when I get a chance to present at them is one of the things I enjoy the most. That is when you really feel the power of the community. I feel we have too little of it in Sweden, so to see and feel how great it is in other places provides a lot of motivation to bring people together in one in Sweden.

If you are not feeling part of the user group community, sign up with your local user group. Start reading blogs, get on twitter and start following some of the greats. From there you’ll find more and more interesting sites, people and blogs to follow.

I’ll refrain from name dropping the guys and gals I follow. If you know me, you’ll know who anyway. If not, search for your favorite topics within Oracle on Twitter or google it followed by twitter or some other social network name and you’ll find lots twitterites or bloggers writing about the stuff that inspires you.

If you still want a list of where to start, hit me up and I’ll get you a good starting point from where to expand your horizons.

OTN Appreciation Day : ADVM

Yann Neuhaus - Tue, 2016-10-11 05:26

Tim Hall had the idea that as many people as possible would write a small blog post about their favorite Oracle feature and we all post them on the same day. Here is my favorite feature: ADVM – The Oracle ASM Dynamic Volume Manager.

So, what is it? The docs tell you this: “Oracle ASM Dynamic Volume Manager (Oracle ADVM) provides volume management services and a standard disk device driver interface to clients. File systems and other disk-based applications send I/O requests to Oracle ADVM volume devices as they would to other storage devices on a vendor operating system.”

The easy to understand version is this: It enables us to use regular file systems on top of ASM.

Does is make sense to use it? When you have ASM running on the host or all the hosts of a Grid Infrastructure cluster anyway then it definitely makes sense. ASM will do all the mirroring and striping for you so there is no need to use another technology to achieve that when you can create ADVM volumes and create file systems on top of these. Although the most common scenario is to create an ACFS file system on top of the volumes you are actually not limited to that. Lets do a short demo.

Lets say we have these devices available for use by the grid user:

[root@rac1 ~] ls -la /dev/sd[b-f]*
brw-rw----. 1 root disk     8, 16 Oct 10 17:54 /dev/sdb
brw-rw----. 1 grid asmadmin 8, 17 Oct 10 18:10 /dev/sdb1
brw-rw----. 1 root disk     8, 32 Oct 10 17:54 /dev/sdc
brw-rw----. 1 grid asmadmin 8, 33 Oct 10 18:10 /dev/sdc1
brw-rw----. 1 root disk     8, 48 Oct 10 17:54 /dev/sdd
brw-rw----. 1 grid asmadmin 8, 49 Oct 10 18:10 /dev/sdd1
brw-rw----. 1 root disk     8, 64 Oct 10 17:54 /dev/sde
brw-rw----. 1 grid asmadmin 8, 65 Oct 10 18:10 /dev/sde1
brw-rw----. 1 root disk     8, 80 Oct 10 17:54 /dev/sdf
brw-rw----. 1 grid asmadmin 8, 81 Oct 10 18:10 /dev/sdf1

We want to use “/dev/sde1″ for our new ADVM volume. What we need is an ASM diskgroup in a first step because for creating an ADVM volume you’ll need a ASM diskgroup where you can place your volume on:

grid@rac1:/home/grid/ [+ASM1] sqlplus / as sysasm
SQL> create diskgroup ADVM external redundancy disk '/dev/sde1';

Diskgroup created.

SQL> 

Ok, fine. How can we proceed with creating a volume? Quite easy:

grid@rac1:/home/grid/ [+ASM1] asmcmd volcreate -G ADMV -s 2g VOLADVM
ORA-15032: not all alterations performed
ORA-15221: ASM operation requires compatible.asm of 11.2.0.0.0 or higher (DBD ERROR: OCIStmtExecute)

Hm, quite clear when you search the documentation: ADVM is available since 11gR2:
advm_search

Easy to fix:

grid@rac1:/home/grid/ [+ASM1] sqlplus / as sysasm

SQL> alter diskgroup ADVM set attribute 'compatible.asm'='12.1';

Diskgroup altered.

SQL> 

Lets try again:

grid@rac1:/home/grid/ [+ASM1] asmcmd volcreate -G ADMV -s 2g VOLADVM
grid@rac1:/home/grid/ [+ASM1] 

Perfect. Now I have a volume visible to the operating system:

grid@rac1:/home/grid/ [+ASM1] ls -la /dev/asm/*advm*
brwxrwx---. 1 root asmadmin 252, 115201 Oct 10 18:20 /dev/asm/voladvm-225

On top of this volume we can now create file systems. The natural one would be ACFS:

[root@rac1 ~] mkfs.acfs /dev/asm/voladvm-225
mkfs.acfs: version                   = 12.1.0.2.0
mkfs.acfs: on-disk version           = 39.0
mkfs.acfs: volume                    = /dev/asm/voladvm-225
mkfs.acfs: volume size               = 2147483648  (   2.00 GB )
mkfs.acfs: Format complete.

But in fact every other file system the operating system supports is possible, too:

[root@rac1 ~] mkfs.xfs /dev/asm/voladvm-225
meta-data=/dev/asm/voladvm-225   isize=256    agcount=4, agsize=131072 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=0        finobt=0
data     =                       bsize=4096   blocks=524288, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=0
log      =internal log           bsize=4096   blocks=2560, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0

Quite cool, isn’t it? Whatever file system your operating system supports can be put on ASM disk groups …

 

Cet article OTN Appreciation Day : ADVM est apparu en premier sur Blog dbi services.

OTN Appreciation Day : Prebuilt Developer VMs

Bar Solutions - Tue, 2016-10-11 05:11

I learnt a lot from the entire Oracle Technology Network and I still do. One of the best features of OTN these days, IMHO, is the ability to download prebuilt Virtual Machines. Since I am a developer and not an administrator, I don’t like to be bothered with stuff like how much disk space do I need, how many cores should I use etc. I can just download a Virtual Box image, import it and start experimenting with the technology I am interested in. For instance, the multi tenant features of the Oracle 12c database. The best thing in using a virtual machine is that when you screw up really, really bad, you can just throw away the virtual machine, import the original version again and try again.
Thanks, OTN, for making it possible for me to learn new technologies without having to learn all the administrator stuff.
Oh, and if I need some extra information or find out what an error means and what I can do about it, there is almost always an answer to be found at OTN.

#ThanksOTN

OTN Appreciation Day: Automatic Storage Management (ASM)

Jeff Moss - Tue, 2016-10-11 04:48

Big shout out to Tim for kicking this off!

Automatic Storage Management (ASM) provides optimised volume management and filesystem capabilities for Oracle databases, whether they be single or multi instance (RAC) implementations.

Although introduced with Oracle 10g Release 1 in 2004, I first used it in a production scenario around 2008, when upgrading a hardware platform for a Data Warehouse. It seemed like a logical choice for myself and the DBAs at the time, although the storage team were less pleased at losing some control. Ultimately it proved a big success on that project and is still in stable, reliable use today.

Things I like about ASM include:

  • Simplifies storage management
  • Automatic rebalancing when capacity is added
  • Visibility within Enterprise Manager for monitoring
  • Availability of detailed metrics within the database
  • Reliable, balanced and consistent performance
  • Works with RAC
  • Rolling upgrades and patching
  • Provides a reliable cluster filesystem (ACFS)
  • Even more cool features coming in 12c such as Flex ASM

 

Some useful links:

ASM Administrators Guide 12cR1 (Oracle Docs)

The Mother Of All ASM Scripts (John Hallas)

Technical overview of new features for ASM in 12c (Whitepaper)

SGMB_URL = "http://www.oramoss.com/wp-content/plugins/social-media-builder/"; jQuery(".dropdownWrapper").hide();
jQuery(".socialMediaOnEveryPost").addClass("sgmb-left")

Oracle 12c – Managing RMAN persistent settings via SQL

Yann Neuhaus - Tue, 2016-10-11 04:29

RMAN persistent settings can be managed in two different ways.

  • Via the RMAN interface
    – e.g. RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
  • Via SQL
    – e.g. VARIABLE RECNO NUMBER;
    EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(‘BACKUP OPTIMIZATION’,’ON’);

There are several scenarios when it might be helpful to use the SQL way. I will show 3 of them:

  • Automation
  • Reset to default
  • Rebuilding the RMAN persistent settings after losing all controlfiles (no catalog)

Let’s take a look at the first scenario. For example, when you have an automated way to run SQL’s against all of your databases and you want to change the RMAN retention from 3 days to 4 days for all of your databases. Then you could run the following.

SQL> select conf#, name, value from v$rman_configuration where name = 'RETENTION POLICY';

CONF# NAME                             VALUE
----- -------------------------------- ----------------------------------------------------------------------------------------
    1 RETENTION POLICY                 TO RECOVERY WINDOW OF 3 DAYS


SQL> EXECUTE DBMS_BACKUP_RESTORE.DELETECONFIG(CONF# => 1);

PL/SQL procedure successfully completed.

SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 4 DAYS');

PL/SQL procedure successfully completed.


SQL> select conf#, name, value from v$rman_configuration where name = 'RETENTION POLICY';

CONF# NAME                             VALUE
----- -------------------------------- ----------------------------------------------------------------------------------------
    1 RETENTION POLICY                 TO RECOVERY WINDOW OF 4 DAYS

	
-- The new value is, of course, immediately reflected via the RMAN interface as well

RMAN> SHOW RETENTION POLICY;

RMAN configuration parameters for database with db_unique_name OCM121 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;

 

The second useful scenario might be, to reset the whole RMAN config with one shot, instead of running several clear commands like the following, “RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;” , simply run the RESETCONFIG.

SQL> EXECUTE DBMS_BACKUP_RESTORE.RESETCONFIG;

PL/SQL procedure successfully completed.

-- After executing this command, the v$rman_configuration view is empty, which means that all
-- RMAN persistent settings are default.

SQL> select conf#, name, value from v$rman_configuration;

no rows selected

 

And last but not least, to restore the RMAN persistent settings via SQL, in case you have lost all of your controlfiles and no RMAN catalog is in place.

One little side note, in case you have a RMAN catalog. The RMAN sync from the controlfile to the catalog is usually unidirectional, meaning that the controlfile is always the master and it syncs the information to the catalog. However, there are exceptions were it is bidirectional. One of it is, when you recreate the controlfile manually, then RMAN is able to get the last RMAN persistent settings from the catalog and applies it to the controlfile.

However, if you don’t have a catalog, dump out the RMAN persistent settings into SQL, simply by backing up the controlfile to trace.

SQL> alter database backup controlfile to trace as '/tmp/cntrl.trc';

Database altered.

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 4 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RMAN OUTPUT','TO KEEP FOR 14 DAYS');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK');
-- Configure RMAN configuration record 7
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE ''SBT_TAPE'' PARMS  ''SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=/nfs/OCM121)''');
-- Configure RMAN configuration record 8
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET');
-- Configure RMAN configuration record 9
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG DELETION POLICY','TO APPLIED ON ALL STANDBY');

And if you run into the severe situation of losing all controlfiles, you can restore the RMAN persistent settings quite quickly. Especially useful, when you have configured complex Media Manager settings.
Cheers,
William

P.S. Managing RMAN persistent settings via SQL is not a 12c feature. It exists for quite a long time.

 

Cet article Oracle 12c – Managing RMAN persistent settings via SQL est apparu en premier sur Blog dbi services.

OTN Appreciation Day: Breaking Barriers…In Memory

Marco Gralike - Tue, 2016-10-11 04:17
The stuff I liked most in the database releases of the last years is the…

OTN Appreciation Day: OBIEE's Export to Excel Functionality

Rittman Mead Consulting - Tue, 2016-10-11 04:11

Only kidding…. Do you know that almost any transformation doable in excel can be achieved in OBIEE, probably faster and with zero impact on your local workstation?

Cat Million Rows Image credit https://twitter.com/fomin_andrew/status/749305992198881281

Why bothering downloading data to Excel when you have pivot tables, conditional formatting and a huge variety of graphs with drilling/action capabilities all in OBIEE. A platform where analysis can be shared by passing a single URL instead of emailing huge XLS files?

Sometimes however there is a good reason to export to excel, like when preparing a presentation on top of OBIEE data/analysis. The following are the possible ways of achieving the OBIEE/Excel integration:

  • Dashboard and Analysis can be exported to excel with a single click
  • A BI Publisher version of a dashboard can be created and used by default when exporting
  • Excel can be linked via Smartview to a single Analysis: Data and Visualisations can be downloaded and refreshed upon request with configurable parameters.
  • Excel can directly query the BiServer Subject Areas via Smartview.
  • Excel version of Dashboard and Analysis can be delivered by email via Agents.

An important note, Oracle published "OBIEE 11.1.1.7 - New Features, Export Guidance And Recommendations For Working With Microsoft Office (Doc ID 1558070.1)". This Document contains recommendations on how to provide the export to Excel depending on the output data volume. The document was written for OBIEE 11.1.1.7 but the same suggestions apply to almost any OBIEE version available.

Categories: BI & Warehousing

Pages

Subscribe to Oracle FAQ aggregator