Feed aggregator

Monitor audit_file_dest !

Laurent Schneider - Tue, 2017-03-28 09:25

Until 11.2, audit_file_dest used to remain small with default settings and reasonably sized and active database. Suddenly, in 12c, you will sooned or later get ORA-09925: Unable to create audit trail file.

At that point, no more connection is possible to the database, it is a complete loss of service.

Why suddenly in 12c ? This is because the default for audit_sys_operations changed to true. In 11g, you used to get an 1K file each time you connect as sysdba. So a few tousands sysdba connections a weeks, a few mega, no worries.


Mon Mar 27 14:08:01 2017 +02:00
LENGTH : '155'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'

Suddenly in 12c, you get plenty files that are many Mb. For instance for AUTOTASK jobs, every single select is dumped to the filesystem. A single week-end of an quiet database may generate 1Gb of *.aud files of DBMS_SCHEDULER.

Those DB001_j000_12345_20170327140802123456789.aud files are highly useless and annoying.


LENGTH : '641'
ACTION :[490] 'select /*+  no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad  */ substrb(dump("PERIOD_END_TIME",16,0
,64),1,240) val,
                      rowidtochar(rowid) rwid from "SYS"."WRP$_REPORTS_TIME_BANDS" t where rowid in (chartorowid('AABJ58AADAAAMsrAAA'),chartorowid('AABJ58AADAAAMsrAAB'),chartorowid('AABJ58AADAAAMsrAAC'),chartorowid('AABJ58A
ADAAAMssAAA')) order by "PERIOD_END_TIME"'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[0] ''
CLIENT TERMINAL:[7] 'UNKNOWN'
STATUS:[1] '0'

Once your audit_file_dest is getting full, your database stops, so better delete those *_j00*_* and *_m00*_* quickly enough!

Gartner Peer Insight: Please Write a Review!

WebCenter Team - Tue, 2017-03-28 08:55

Have you deployed an Oracle solution in the past 2 - 3 years? 

If so, why not share your experience and knowledge with others and write a review!

Did you know B2B technology purchase decisions are highly influenced by peers? 

  • In fact, peer input was ranked #1 when making a technology purchase decision by 85% of the respondents to an independent study.
  • By sharing your experience with Oracle products and services, not only can you provide valuable feedback to us, you can also help your peers in their technology decisions. 
So please go to the website below and write a review.  Thank you.

Gartner Peer Insight: Please Write a Review!

WebCenter Team - Tue, 2017-03-28 08:55

Have you deployed an Oracle solution in the past 2 - 3 years? 

If so, why not share your experience and knowledge with others and write a review!

Did you know B2B technology purchase decisions are highly influenced by peers? 

  • In fact, peer input was ranked #1 when making a technology purchase decision by 85% of the respondents to an independent study.
  • By sharing your experience with Oracle products and services, not only can you provide valuable feedback to us, you can also help your peers in their technology decisions. 
So please go to the website below and write a review.  Thank you.

Vertically scale your PostgreSQL infrastructure with pgpool – 3 – Adding another standby

Yann Neuhaus - Tue, 2017-03-28 08:52

In the first and second post in this series we did the basic pgpool setup including the watchdog configuration and then did a simple setup for automatically failover from a PostgreSQL master instance in case it goes down for any reason. In addition we told pgpool how an old master instance can be reconfigured as a new standby instance that follows the new master. In this post we’ll add another standby instance and then teach pgpool how a standby can be made aware of a new master when the master fails. Sound interesting? Lets go…

As reminder this is how the system looks like right now:

pgpool-architecture

What we want to have is:

pgpool-extended

The idea behind a third node is that we always will have at least one standby server up and running in case the master node goes down. What do we need to do to bring in another instance? Once the operating system is up and running, PostgreSQL is installed it is actually quite easy. As a first step lets create the new standby database on the third node using exactly the same layout as on the other nodes:

postgres@pgpool3:/home/postgres/ [pg962] mkdir -p /u02/pgdata/PG1
postgres@pgpool3:/home/postgres/ [pg962] cd /u02/pgdata/PG1
postgres@pgpool3:/u02/pgdata/PG1/ [pg962] pg_basebackup -h 192.168.22.34 -x -D /u02/pgdata/PG1/
postgres@pgpool3:/u02/pgdata/PG1/ [pg962] echo "standby_mode = 'on'
primary_conninfo = 'host=pgpool1 user=postgres'
primary_slot_name = 'standby2'
recovery_target_timeline = 'latest'
trigger_file='/u02/pgdata/PG1/failover_trigger'" > recovery.conf
postgres@pgpool3:/u02/pgdata/PG1/ [pg962] psql -h pgpool1 -c "select * from pg_create_physical_replication_slot('standby2')" postgres
postgres@pgpool3:/u02/pgdata/PG1/ [PG1] pg_ctl -D /u02/pgdata/PG1/ start

Now we have one master instance with two standby instances attached. Lets configure the third instance into pool (the configuration change needs to be done on both pgpool nodes, of course). The lines we need to add to pgpool.conf are:

backend_hostname2 = '192.168.22.40'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/u02/pgdata/PG1'
backend_flag2 = 'ALLOW_TO_FAILOVER'

Reload pgpool (a stop is not necessary) and check the current status:

[postgres@centos7_pgpool_m1 etc]$ pgpool reload
[postgres@centos7_pgpool_m1 etc]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | 192.168.22.34 | 5432 | up     | 0.333333  | primary | 0          | true              | 0
 1       | 192.168.22.35 | 5432 | up     | 0.333333  | standby | 0          | false             | 0
 2       | 192.168.22.40 | 5432 | up     | 0.333333  | standby | 0          | false             | 0
(3 rows)

We have a second standby database which is used to load balance read requests. In case the master fails now what we want is that one of the standby instances gets promoted and the remaining standby instance should be reconfigured to follow the new master. What do we need to do?

As we are using replication slots in this setup we need a way to make the failover scripts independent of the name of the replication slot. The first script that we need to change is “promote.sh” on all the PostgreSQL nodes because currently there is a hard coded request to create the replication slot:

#!/bin/bash
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
export PATH PGDATA
pg_ctl promote -D ${PGDATA} >> /var/tmp/failover.log
psql -c "select * from pg_create_physical_replication_slot('standby1')" postgres >> /var/tmp/failover.log

The easiest way to do this is to create as many replication slots as you plan to add standby instances, e.g.:

#!/bin/bash
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
export PATH PGDATA
pg_ctl promote -D ${PGDATA} >> /var/tmp/failover.log
psql -c "select * from pg_create_physical_replication_slot('standby1')" postgres >> /var/tmp/failover.log
psql -c "select * from pg_create_physical_replication_slot('standby2')" postgres >> /var/tmp/failover.log
psql -c "select * from pg_create_physical_replication_slot('standby3')" postgres >> /var/tmp/failover.log
psql -c "select * from pg_create_physical_replication_slot('standby4')" postgres >> /var/tmp/failover.log

Of course this is not a good way to do it as you would need to adjust the script every time the amount of standby instances changes. One better way to do it is to centrally manage the amount of standby instances and the relation of the standby instances to the replication slots in a configuration in the $PGDATA directory of each PostgreSQL node and on each pgpool node in the HOME directory of the postgres user:

postgres@pgpool1:/u02/pgdata/PG1/ [PG1] cat pgpool_local.conf 
# the total amount of instances that
# participate in this configuration
INSTANCE_COUNT=3
# the mapping of the hostname to
# to the replication slot it uses for
# the PostgreSQL instance it is running
# in recovery mode
192.168.22.34=standby1
192.168.22.35=standby2
192.168.22.40=standby3

Having this we can adjust the promote.sh script (sorry I have to use a screenshot as the source code destroys the formatting of this post. let me know if you want to have the script):
pgp-promote-sh

Now the script will create all the replication slots on a promoted instance and in addition drops the replication slot it used before being promoted. What else do we need? As we now have a third instance in the configuration there is another point we need to take care of: When the master fails a new standby is promoted, so far so good. But in addition we want the second standby to follow the new master automatically, don’t we? For this we need to tell pgpool to call another script which is executed on the active pgpool node after failover:

[postgres@centos7_pgpool_m1 ~]$ grep follow /u01/app/postgres/product/pgpool-II/etc/pgpool.conf
follow_master_command = '/home/postgres/follow_new_master.sh "%h" "%H"' 

This will be executed when there is failover (all pgpool nodes need to have this script)

#!/bin/sh
set -x
master_node_host_name=$2
detached_node_host_name=$1
tmp=/tmp/mytemp$$
trap "rm -f $tmp" 0 1 2 3 15
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
RECOVERY_NODE_REPLICATION_SLOT=`cat /home/postgres/pgpool_local.conf | grep ${detached_node_host_name} | awk -F '=' '{print $2}'`
export PATH PGDATA
# make sure the instance is down
ssh -T $detached_node_host_name /home/postgres/stop_instance.sh
cat > $tmp <<EOF
standby_mode = 'on'
primary_conninfo = 'host=$master_node_host_name user=postgres'
primary_slot_name = '${RECOVERY_NODE_REPLICATION_SLOT}'
recovery_target_timeline = 'latest'
trigger_file='/u02/pgdata/PG1/failover_trigger'
EOF
scp $tmp $detached_node_host_name:$PGDATA/recovery.conf
ssh ${detached_node_host_name} /home/postgres/start_instance.sh
psql -c "select 'done'" postgres

So, this is the status now:

[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_dela
y 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-----------------
 0       | 192.168.22.34 | 5432 | up     | 0.333333  | primary | 4          | true              | 0
 1       | 192.168.22.35 | 5432 | up     | 0.333333  | standby | 2          | false             | 0
 2       | 192.168.22.40 | 5432 | up     | 0.333333  | standby | 0          | false             | 0
(3 rows)

Lets shutdown the primary and see what happens:

postgres@pgpool1:/home/postgres/ [PG1] pg_ctl -D /u02/pgdata/PG1/ stop -m immediate
waiting for server to shut down.... done
server stopped

Pgpool is telling this:

[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_dela
y 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-----------------
--
 0       | 192.168.22.34 | 5432 | down   | 0.333333  | standby | 4          | false             | 0
 1       | 192.168.22.35 | 5432 | up     | 0.333333  | primary | 4          | true              | 0
 2       | 192.168.22.40 | 5432 | down   | 0.333333  | standby | 0          | false             | 0
(3 rows)

Re-attach:

[postgres@centos7_pgpool_m1 ~]$ pcp_attach_node -w -n 0
pcp_attach_node -- Command Successful
[postgres@centos7_pgpool_m1 ~]$ pcp_attach_node -w -n 2
pcp_attach_node -- Command Successful
[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_dela
y 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-----------------
--
 0       | 192.168.22.34 | 5432 | up     | 0.333333  | standby | 4          | false             | 0
 1       | 192.168.22.35 | 5432 | up     | 0.333333  | primary | 4          | true              | 0
 2       | 192.168.22.40 | 5432 | up     | 0.333333  | standby | 0          | false             | 0
(3 rows)

Perfect. The only pain point is that we need to manually re-attach the nodes, everything else is automated. But, luckily there is way to get around this: As we are on the pgpool nodes when the script is executed we can just use pcp_attach_node at the end of the follow_new_master.sh script (and pass the node id %d into the script):

[postgres@centos7_pgpool_m1 ~]$ grep follow /u01/app/postgres/product/pgpool-II/etc/pgpool.conf
follow_master_command = '/home/postgres/follow_new_master.sh "%h" "%H" %d' 
[postgres@centos7_pgpool_m1 ~]$ cat follow_new_master.sh 
#!/bin/sh
set -x
master_node_host_name=$2
detached_node_host_name=$1
detached_node_id=$3
tmp=/tmp/mytemp$$
trap "rm -f $tmp" 0 1 2 3 15
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
RECOVERY_NODE_REPLICATION_SLOT=`cat /home/postgres/pgpool_local.conf | grep ${detached_node_host_name} | awk -F '=' '{print $2}'`
export PATH PGDATA
# make sure the old master is down
ssh -T $detached_node_host_name /home/postgres/stop_instance.sh
cat > $tmp <<EOF
standby_mode = 'on'
primary_conninfo = 'host=$master_node_host_name user=postgres'
primary_slot_name = '${RECOVERY_NODE_REPLICATION_SLOT}'
recovery_target_timeline = 'latest'
trigger_file='/u02/pgdata/PG1/failover_trigger'
EOF
scp $tmp $detached_node_host_name:$PGDATA/recovery.conf
ssh ${detached_node_host_name} /home/postgres/start_instance.sh
psql -c "select 'done'" postgres
pcp_attach_node -w -n ${detached_node_id}

And now, when you shutdown the master everything is automatic. Hope this helps.

 

Cet article Vertically scale your PostgreSQL infrastructure with pgpool – 3 – Adding another standby est apparu en premier sur Blog dbi services.

RMAN-06820: WARNING: failed to archive current log at primary database

Michael Dinh - Tue, 2017-03-28 08:18

The best solution is a simple one.

Standard Edition Oracle with Manual Standby generating error – RMAN-06820.

From RMAN-06820 ORA-17629 During Backup at Standby Site (Doc ID 1616074.1):
As of 11.2.0.4, we now include the current standby redo log as part of an RMAN archivelog backup at the standby site.
This is achieved by forcing a log switch at the primary site.

Some options to remedy the issue which seems too complicated.
RMAN-06820 ORA-17629 ORA-12154 During Backup of a Standby Database (Doc ID 2025142.1)
OERR: RMAN-6613 “Connect identifier for DB_UNIQUE_NAME %s not configured” Reference Note (Doc ID 2050646.1)
RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured (Doc ID 1598653.1)

DEMO:
oracle@arrow2:HAWKB:/home/oracle
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 1 14:09:07 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HAWK (DBID=3187737370, not open)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWKB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_HAWKB.f'; # default

RMAN> CONFIGURE DB_UNIQUE_NAME 'HAWKB' CONNECT IDENTIFIER 'HAWKB';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 12/01/2016 14:09:26
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file

RMAN> backup archivelog all not backed up 2 times tag='ALL';

Starting backup at 2016-DEC-01 14:09:53
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=342 RECID=274 STAMP=929450567
input archived log thread=1 sequence=343 RECID=275 STAMP=929451468
input archived log thread=1 sequence=344 RECID=276 STAMP=929452365
input archived log thread=1 sequence=345 RECID=277 STAMP=929453265
input archived log thread=1 sequence=346 RECID=278 STAMP=929454166
input archived log thread=1 sequence=347 RECID=279 STAMP=929455067
channel ORA_DISK_1: starting piece 1 at 2016-DEC-01 14:09:54
channel ORA_DISK_1: finished piece 1 at 2016-DEC-01 14:09:55
piece handle=/fra/HAWKB/backupset/2016_12_01/o1_mf_annnn_ALL_d417xlg1_.bkp tag=ALL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-DEC-01 14:09:55

RMAN> backup archivelog until time 'sysdate' not backed up 2 times tag='UNTIL';

Starting backup at 2016-DEC-01 14:10:22
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=342 RECID=274 STAMP=929450567
input archived log thread=1 sequence=343 RECID=275 STAMP=929451468
input archived log thread=1 sequence=344 RECID=276 STAMP=929452365
input archived log thread=1 sequence=345 RECID=277 STAMP=929453265
input archived log thread=1 sequence=346 RECID=278 STAMP=929454166
input archived log thread=1 sequence=347 RECID=279 STAMP=929455067
channel ORA_DISK_1: starting piece 1 at 2016-DEC-01 14:10:22
channel ORA_DISK_1: finished piece 1 at 2016-DEC-01 14:10:23
piece handle=/fra/HAWKB/backupset/2016_12_01/o1_mf_annnn_UNTIL_d417ygxb_.bkp tag=UNTIL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-DEC-01 14:10:23

RMAN> backup archivelog all not backed up 2 times tag='ALL';

Starting backup at 2016-DEC-01 14:10:26
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

using channel ORA_DISK_1
skipping archived logs of thread 1 from sequence 342 to 347; already backed up
Finished backup at 2016-DEC-01 14:10:26

RMAN> backup archivelog until time 'sysdate' not backed up 2 times tag='UNTIL';

Starting backup at 2016-DEC-01 14:10:32
using channel ORA_DISK_1
skipping archived logs of thread 1 from sequence 342 to 347; already backed up
Finished backup at 2016-DEC-01 14:10:32

RMAN> exit


Recovery Manager complete.
oracle@arrow2:HAWKB:/home/oracle
$

Database Backup and Recovery User’s Guide (11.2)
Backing Up Archived Redo Logs with RMAN
https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmbckba.htm#BRADV81399

Online Redo Log Switch will occur with backup contains the following clause:
PLUS ARCHIVELOG
ARCHIVELOG ALL
ARCHIVELOG FROM ..

The simple solutions for not switching redo at primary.

Use backup archivelog until time ‘sysdate’ instead of backup archivelog all.
Use backup until sequence instead of from sequence.
Use backup database followed with backup archivelog until time ‘sysdate’.


Real World OBIEE: Demystification of Variables Pt. 3

Rittman Mead Consulting - Tue, 2017-03-28 08:00
 Demystification of Variables  Pt. 3

In part two of this blog series, I went over using Repository, System and Presentation Variables to make reports dynamic for any series of time. In part three, I am going to talk about making reports dynamic for periods of time using built in functions within Answers itself.

Real World

While it's a lot more efficient to create Repository Variables to use in filters and prompts for time dimensions, sometimes it is simply not possible. Perhaps you are a front end developer for OBIEE and have no access to the RPD or the database . Perhaps you have no communication with the person in your organization who handles all of the RPD development and therefore can not submit any change requests.

Don't worry. We've got you covered.

There are several functions and tricks you can use within Answers itself to make reports dynamic and eliminate having to hardcode dates.

The Scenario

I am going to use the same scenario I used for part two of this series for the example one. To recap, here are the requirements:

I have been asked to create a report that is going to reside on a products dashboard. It needs to have the same product grouping as the report I used in part one of this series, needs to contain 'Gross Rev $', 'Net Rev $' and '# of Orders' and have a prompt that can select between the first and current day of the month and every day in-between. The person who requested the report wants the prompt to change dynamically with each month and does not want users to be able to select future dates.

In part two, I used a custom SQL statment which used Repository Variables I created to populate all the date values from the first of every month to the current date for Variable Prompts. There is a gap in the data loads for # of Orders in which data does not update until the 2nd or 3rd of each new month. The person who requested the report wanted a summary of the previous months '# of Orders' to be shown until the data is updated for the current month. I used a Repository Variable that returned the value of the previous month with the current year and used a CASE statement with along with Filter Expressions to switch between the Filter Expression using the Repository Variable (Prev_Month) if the date was <=2 or if # of Orders is null and the Filter Expression which contained the Start Date and End_Date Presentation Variable placeholders which were defined in my Variable Prompts.

Example One

In this example, I have to figure out a way to make the report dynamic with only the functions available within Answers. There are two parts to this example. First I need to use a function that will return the previous month's value for the Calendar Year Month column to use with the '# of Orders' column. This will replace the Repository Variable Prev_Month I used in part two of this series. Second I need to write a new SQL statment for the Start Date and End_Date prompts I created in part two and also define a new SQL statment for the default values in those prompts.

Part 1

I am going to start by creating a new statement to return the previous month's value for the Calendar Year Month column. I can use the TIMESTAMPADD function in conjunction with the extraction syntax 'YEAR' and 'MONTH' to return the desired results. Let's take a look at the entire statment and then I will break it down.

CAST(YEAR(timestampadd(SQL_TSI_YEAR, -1,CURRENT_DATE))*100+MONTH(timestampadd(SQL_TSI_MONTH, -1,CURRENT_DATE)) AS VARCHAR(6))

1. TIMESTAMPADD - This is what defines that I am going to use addition to return a date by adding or one date to another.

2. SQL_TSI_YEAR and SQL_TSI_MONTH - The first argument in the function. It defines what interval of time the function will work with.

3. -1. This the interval of time that is compared to the third argument.

4. CURRENT_DATE - The third argument in the function. This is what the second argument is compared against.

5. YEAR and MONTH - This is the extraction syntax that will return only the year and the month respectively.

Also notice that I have used VARCHAR(6) for the CAST argument. If I use VARCHAR, I can specify the exact number of characters I want returned.

Now I need to copy my column formula and paste it into the column formula that I created for '# of Orders' in part two of this series.

I am going to replace the Prev_Month Repository Variable with my statement, which will look like this.

CASE WHEN DAY(CURRENT_DATE)<=2 or="" "sales="" -="" fact="" sales"."measures"."#="" of="" orders"="" is="" null="" then="" filter("sales="" using="" ("sales="" sales"."periods"."calendar="" year="" month"="CAST(YEAR(timestampadd(SQL_TSI_MONTH," -1,current_date))*100+month(timestampadd(sql_tsi_month,="" -1,current_date))="" as="" varchar(6))))="" else="" ("periods"."day="" date"="" between="" @{pv_start_dt}{date="" '2015-10-01'}="" and="" @{pv_end_dt}{date="" '2015-10-15'}))="" end<="" code="">

 Demystification of Variables  Pt. 3

If I run the report, my results return as expected.

 Demystification of Variables  Pt. 3

Part 2

Now I need to write a new SQL statement for my Start Date and End Date prompts. In order to do this, I am going to need to use two functions: TIMESTAMPS and CURRENT_DATE. First, lets take a look at the TIMESTAMP function.

I am going to use the TIMESTAMP function to filter the Day Date column for the first day of the month. To demonstrate, I am going to create a new analysis and use the TIMESTAMP function in a column formula. My column formula looks like the following:

TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) +1, CURRENT_DATE)

This formula can be broken down into four parts:

1. TIMESTAMPADD - This is what defines that I am going to use addition to return a date by adding or one date to another.

2. SQL_TSI_DAY - The first argument in the function. It defines what interval of time the function will work with (in this case days)

3. -DAYOFMONTH(CURRENT_DATE)+1 - This the interval of time that is compared to the third argument. In this case I am taking the negative value of the day of the month, adding 1 and then adding it with current date which always returns 1 or the first day of the month.

4. CURRENT_DATE - The third argument in the function. This is what the second argument is compared against.

This is only scratching the surface of what you can do with the TIMESTAMP function. If you would like more information, check out the blog on TIMESTAMPS written by Brian Hall.

I am going to add an additional column to the Criteria and use the CURRENT_DATE function in a column formula.

 Demystification of Variables  Pt. 3

 Demystification of Variables  Pt. 3

Now I am going to click on Results to show the results of the TIMESTAMP function and the CURRRENT_DATE function.

 Demystification of Variables  Pt. 3

From the results you can see that I have both the first day of the month and the current date. Now I need to convert this into a filter for the Day Date column so that I can get the logical SQL query for my Start Date and End Date prompts.

 Demystification of Variables  Pt. 3

In the New Filter window, I need to change the operator to is between and click on Add More Options to add a SQL Expression.

 Demystification of Variables  Pt. 3

In the SQL Expression box, I need to put the TIMESTAMP function for current date from the previous example. In addition I need to add another SQL Expression for the CURRENT_DATE function.

 Demystification of Variables  Pt. 3

 Demystification of Variables  Pt. 3

When I return to my Criteria, I can see the filter I created in the Filter window.

 Demystification of Variables  Pt. 3

I can click on Results to run the report. The results for the Day Date column return as expected.

 Demystification of Variables  Pt. 3

Now I can click on the Advanced tab and copy the logical SQL statement to use for my Start Date and End Date prompts.

 Demystification of Variables  Pt. 3

Now I am going to paste the following into my Start Date Variable Prompt

Choice List Values > SQL Results

SELECT "Sales - Fact Sales"."Periods"."Day Date" 
FROM "Sales - Fact Sales" WHERE ("Periods"."Day Date" BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE), CURRENT_DATE)  AND CURRENT_DATE) 
ORDER BY "Periods"."Day Date"

Default Selection > SQL Results

SELECT
TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) +1, CURRENT_DATE)
FROM "Sales - Fact Sales"
FETCH FIRST 65001 ROWS ONLY

 Demystification of Variables  Pt. 3

For the default selection, I am using a SQL statment that is selecting the first day of the month using the same TIMESTAMP function used in the above query from my subject area "Sales - Fact Sales".

Now I need to change the SQL query for both the Choice List Values and Default Selection for my End Date Variable Prompt.

I am going to use the same SQL query for the Choice List Values in my End Date prompt as I did in my Start Date prompt. I am going to change the default selection to the following:

SELECT
CURRENT_DATE
FROM "Sales - Fact Sales"
FETCH FIRST 65001 ROWS ONLY

 Demystification of Variables  Pt. 3

If I go to the Display window, I can view the results of my changes.

 Demystification of Variables  Pt. 3

 Demystification of Variables  Pt. 3

Notice that the results are exactly the same as the results in part two of this series.

I can save the dashboard prompt and go to my dashboard and test the prompt.

 Demystification of Variables  Pt. 3

 Demystification of Variables  Pt. 3

 Demystification of Variables  Pt. 3

In Conclusion

In part one of this series, we looked at using Bins, CASE statements to create custom grouping for values and switch between those groups and values using Presentation Variables.

In part two of this series, we looked at creating Repository Variables to make reports dynamic using those Repository Variables in Variable Prompts and passing them into column formulas using Presentation Variables.

In the third and final part of this series, we looked at making reports dynamic by using built in functions within Answers such as TIMESTAMPS and CURRENT_DATE.

My hope is that you can take these examples and apply them in your own OBIEE development. If you would like to know more about front end or RPD development, please check out the variety of training courses we offer at Rittman Mead. Until next time.

Categories: BI & Warehousing

Oracle’s HCM World and Modern Finance Experience 2017 Deliver Foundation for Modern Business Success

Oracle Press Releases - Tue, 2017-03-28 07:00
Press Release
Oracle’s HCM World and Modern Finance Experience 2017 Deliver Foundation for Modern Business Success Complementary events for HR and finance professionals showcase the building blocks, tools and strategies companies need to drive digital business transformation

Redwood Shores, Calif.—Mar 28, 2017

Helping businesses embrace modern best practices, Oracle today announced HCM World 2017 and Modern Finance Experience 2017, taking place in Boston, April 11-13. Organizations across every industry are examining how they can use technology to innovate and gain a competitive advantage, all while enhancing employee engagement and performance. HCM World and Modern Finance Experience will bring together leading visionaries and business professionals to share real-world examples and strategies for modernizing business with technology to fuel digital growth strategies, engage and retain top talent, and maximize organizational efficiency and effectiveness.

Taking place concurrently, HCM World is being held at the Boston Marriott Copley Place and Modern Finance Experience at The Westin Copley Place.

“The need for digital transformation is challenging today’s companies to look ahead and redefine how they operate. Forward-looking companies are exploring new and emerging technologies, such as machine learning, predictive analytics, big data, and the Internet of Things (IoT) to optimize business processes. They also are looking for new ways that the cloud and data analytics can help them stay ahead of their competitors,” said Steve Miranda, executive vice president of applications development, Oracle. “The HCM World and Modern Finance Experience events are designed to deliver the practical roadmap to help companies future proof their business systems, while showcasing examples of those who have successfully navigated the transition.”

HCM World

HCM World is geared to provide HR professionals with the multi-faceted strategies they need to build and grow a modern, digital-based HR practice. More than 60 sessions throughout the three-day event are tailored to help elevate HR as a strategic partner in advancing business goals by:

  • Engaging employees with modern, customized, and easy-to-use systems and processes that help them be productive and happy at work.
  • Informing through predictive analytics to allow organizations to take action pro-actively and give HR the foresight to plan ahead.
  • Adapting and expanding an organization’s capabilities, both as a business and as a digital leader, by enabling the flexibility to personalize, brand, and extend the experience at every layer of the cloud in a secure and safe way.

To register for Oracle HCM World 2017, visit here and find more information on the event on the Oracle Modern HR in the Cloud blog, Facebook or Twitter.

Modern Finance Experience

The premier event for CFOs and finance professionals, Modern Finance Experience is designed to provide attendees with guidance on how to build a modern operating model for finance that supports digital business processes and new ways of working. Over 25 visionary keynotes and thought-leadership sessions, including an exclusive CFO Summit, during the three-day event will inspire, educate, and empower the finance function to assume a larger role in driving business success. Attendees will be able to:

  • Learn from hands-on sessions with practitioners and industry experts who will give finance leaders a roadmap to finance modernization and offer advice on how new technologies can reshape finance to be more predictive and extend their value across other business functions (such as HR).
  • Share how emerging technologies, such as big data analytics, artificial intelligence (AI), and machine learning, powered by the cloud, can optimize both the transactions and analytical capabilities required by finance to become more agile.
  • Craft high-impact strategies for improving the non-traditional finance skill sets now required of finance professionals to provide proactive guidance to the business.

Register for Modern Finance Experience 2017 here. For additional information on Oracle Cloud solutions for Finance, visit Oracle Enterprise Resource Planning (ERP) Cloud’s Facebook, and Twitter and Oracle Enterprise Performance Management (EPM) Cloud’s Facebook and Twitter or the Modern Finance Leader blog.

Contact Info
Simon Jones
PR for Oracle
+1.415.856.5155
sjones@blancandotus.com
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Safe Harbor

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Simon Jones

  • +1.415.856.5155

connect / as sysoper generates ORA-1017

Laurent Schneider - Tue, 2017-03-28 06:42

Today I had the issue that I could not login as sysoper on one database, despite being in the dba and oper groups.

The problem is that the osoper group was -probably- not selected during installation

e.g. in 12c /AIX
it may be called differently on your system


$ sqlplus -L -s / as sysoper
ERROR:
ORA-01017: invalid username/password; logon denied
$ awk '/H.17.*, 3/,/End/{print}' $ORACLE_HOME/rdbms/lib/config.s
        .csect  H.17.NO_SYMBOL{RO}, 3
        .string ""
# End   csect   H.17.NO_SYMBOL{RO}

The string is empty. Let’s change this to oper or dba. Stopping any processes using that home is strongly recommended before relink.


$ vi $ORACLE_HOME/rdbms/lib/config.s
        .string "dba"
$ relink
$ sqlplus -s -L / as sysoper
sho user
USER is "PUBLIC"
quit
$

Cloning EBS 12.1.3 Environments Integrated with Oracle Access Manager

Steven Chan - Tue, 2017-03-28 02:05

We have documented procedures for cloning EBS 12.1.3 environments.  We also have documented procedures for integrating EBS 12.1.3 environments with Oracle Access Manager
(OAM) and Oracle Internet Directory (OID).  The next logical question
would be: do we have documented procedures for cloning EBS 12.1.3
environments that have been integrated with OAM and OID?

Yes, we have published this here:

EBS OAM architecture

This Note provides a certified process and detailed steps to:

  • Clone EBS using Rapid Clone
  • Deregister the cloned EBS instance from OAM and remove AccessGate
  • Remove OID from the cloned EBS instance
  • Integrate the cloned EBS instance with OID
  • Integrate the cloned EBS instance with OAM
  • Reconfigure SSL

Related Articles

Categories: APPS Blogs

Cloning EBS 12.1.3 Environments Integrated with Oracle Access Manager

Steven Chan - Tue, 2017-03-28 02:05

We have documented procedures for cloning EBS 12.1.3 environments.  We also have documented procedures for integrating EBS 12.1.3 environments with Oracle Access Manager (OAM) and Oracle Internet Directory (OID).  The next logical question would be: do we have documented procedures for cloning EBS 12.1.3 environments that have been integrated with OAM and OID?

Yes, we have published this here:

EBS OAM architecture

This Note provides a certified process and detailed steps to:

  • Clone EBS using Rapid Clone
  • Deregister the cloned EBS instance from OAM and remove AccessGate
  • Remove OID from the cloned EBS instance
  • Integrate the cloned EBS instance with OID
  • Integrate the cloned EBS instance with OAM
  • Reconfigure SSL

Related Articles

Categories: APPS Blogs

Links for 2017-03-27 [del.icio.us]

Categories: DBA Blogs

datafile, tablespace

Tom Kyte - Mon, 2017-03-27 16:46
Hi Tom, For lots of datafiles, tablespace there are different value of extend, and different values of max_size, how to find the max_size and how to make sure weather the value of extend is which one best??
Categories: DBA Blogs

XMLTYPE storage issue on oracle 11G

Tom Kyte - Mon, 2017-03-27 16:46
We recently upgraded from 10G to 11G and the existing query started running really slow....5 minutes to more than 3 hours. Check the attached live sql file for sample details about the issue. With SECUREFILE BINARY XML, EXTRACT method is runnin...
Categories: DBA Blogs

Implicit Materialized View Fast Refresh

Tom Kyte - Mon, 2017-03-27 16:46
Hi, Is there any way by which we can fast refresh a materialized view implicitly without using refresh explicitly. Thanks In Advance
Categories: DBA Blogs

Using Materialized views with Oracle Change Data Capture

Tom Kyte - Mon, 2017-03-27 16:46
Tom, I would value your opinion on my intended solution for replicating data aggregations to external applications. We have a Data Warehouse with a table containing 100 million rows with several tens of thousands of new rows added each day. Thi...
Categories: DBA Blogs

Google dashboard for lazy businessman

Nilesh Jethwa - Mon, 2017-03-27 15:20

Once you start your own business or startup, the very first thing that comes to mind is "How many visitors did my website receive?"

Google Analytics provides tons of metrics and it becomes quite a chore to keep up with all the dashboard pages and filter options. As a small biz owner myself, I went through a phase where I ended up spending significant amount of time checking out Google Analytics

To save time and mental energy on a daily routine task, I asked "What are the most basic metrics I need to measure from Google Analytics?"

The answer pretty much came down as a need to have "one page dashboard that displays various metrics".

Read more at http://www.infocaptor.com/dashboard/what-are-the-bare-minimum-traffic-metrics-that-i-can-track-easily

Webcast: "Oracle E-Business Suite Integration Best Practices"

Steven Chan - Mon, 2017-03-27 14:51

Integration Best PracticesOracle University has a wealth of free webcasts for Oracle E-Business Suite.  If you're looking for an overview of options for integrating EBS with other applications, see:

Oracle is investing across applications and technologies to make the application integration experience easier for customers. Oracle E-Business Suite provides tools and technologies to address various application integration challenges and styles. Vijay Shanmugam, Director Product Development, shares more about Oracle’s integration offering for cloud, data, event-driven, business-to-business, and process-centric integrations. In this session, you will get a better understanding of what Oracle integration technologies you can use and how, when, and where you can leverage them to connect end-to-end business processes across your enterprise, including the Oracle Applications portfolio in the cloud. This material was presented at Oracle OpenWorld 2016.

Categories: APPS Blogs

Webcast: "Oracle E-Business Suite Integration Best Practices"

Steven Chan - Mon, 2017-03-27 14:51

Integration Best PracticesOracle
University has a wealth of free webcasts for Oracle E-Business Suite. 
If you're looking for an overview of options for integrating EBS with other applications, see:

Oracle is investing across applications and technologies to make the application integration experience easier for customers. Oracle E-Business Suite provides tools and technologies to address various application integration challenges and styles. Vijay Shanmugam, Director Product Development, shares more about Oracle’s integration offering for cloud, data, event-driven, business-to-business, and process-centric integrations. In this session, you will get a better understanding of what Oracle integration technologies you can use and how, when, and where you can leverage them to connect end-to-end business processes across your enterprise, including the Oracle Applications portfolio in the cloud. This material was presented at Oracle OpenWorld 2016.

 

Categories: APPS Blogs

Live Twitter Chat on Headless CMS – Mark Your Calendar!

WebCenter Team - Mon, 2017-03-27 10:29

You are invited! Have you heard the term "headless CMS" going around? Decoupled CMS architecture (aka “headless”) is rising in popularity in the development world and we want to hear from you! If you have something to say about headless CMS or WCM, if you have questions on this topic, if you have experience working with coupled or decoupled CMS or WCM, if you've been following the discussions on this topic or if you are just interested in trying a Twitter Chat out for the first time, we invite you to participate in the live Twitter conversation on "Is headless CMS signaling the end of WCM?" @oraclewebcenter will be kicking off the live Twitter Chat on Thursday, April 20 at 10:00am PT / 1:00pm ET, and we hope to hear from you!

The live Twitter Chat would run about an hour long, and we look to have a meaningful conversation on the topic. So, if you have a Twitter handle, we encourage you to participate in the live discussion. Join or simply follow along!

And, if you have questions in mind you’d like to discuss, simply send those to us @oraclewebcenter and we will do our best to include or have those addressed by the Digital Experience community.

So, mark your calendar:

Live Twitter Chat
  • Topic: Is headless CMS signaling the end of WCM? 
  • Date: Thursday, April 20
  • Time: 10:00am PT / 1:00pm ET
  • Host: @oraclewebcenter
We will archive and post the discussion shortly after the live event. We hope you'll join us!

Live Twitter Chat on Headless CMS – Mark Your Calendar!

WebCenter Team - Mon, 2017-03-27 10:29

You are invited! Have you heard the term "headless CMS" going around? Decoupled CMS architecture (aka “headless”) is rising in popularity in the development world and we want to hear from you! If you have something to say about headless CMS or WCM, if you have questions on this topic, if you have experience working with coupled or decoupled CMS or WCM, if you've been following the discussions on this topic or if you are just interested in trying a Twitter Chat out for the first time, we invite you to participate in the live Twitter conversation on "Is headless CMS signaling the end of WCM?" @oraclewebcenter will be kicking off the live Twitter Chat on Thursday, April 20 at 10:00am PT / 1:00pm ET, and we hope to hear from you!

The live Twitter Chat would run about an hour long, and we look to have a meaningful conversation on the topic. So, if you have a Twitter handle, we encourage you to participate in the live discussion. Join or simply follow along!

And, if you have questions in mind you’d like to discuss, simply send those to us @oraclewebcenter and we will do our best to include or have those addressed by the Digital Experience community.

So, mark your calendar:

Live Twitter Chat
  • Topic: Is headless CMS signaling the end of WCM? 
  • Date: Thursday, April 20
  • Time: 10:00am PT / 1:00pm ET
  • Host: @oraclewebcenter
We will archive and post the discussion shortly after the live event. We hope you'll join us!

Pages

Subscribe to Oracle FAQ aggregator