IPv6 Formatting for JDBC and SQLPlus

Hey all!
Seems new right? But it’s available since 11gR2.
Not needed to explain what is IPV6, right? Any questions, go here.

In summary the only thing you need is to enclose the IPv6 address in square brackets. Like this:

For Easy Connect on IPV4:

SQL> connect user/pass@172.23.10.40:1521/GREPORADB
Connected.

 

For Easy Connect on IPV6:

SQL> connect user/pass@[1:95e05a:g0d:da7a:2007]:1521/GREPORADB
Connected.

For JDBC (thin) IPV4:

url="jdbc:oracle:thin:@(DESCRIPTION=
(LOAD_BALANCE=on) (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=172.23.10.40) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=172.23.10.41)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=GREPORADB)))"

For JDBC (OCI) IPV4:

url="jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=
(PROTOCOL=TCP)(HOST=172.23.10.40)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=GREPORADB)))"

For JDBC (thin) IPV6:

url="jdbc:oracle:thin:@(DESCRIPTION=
(LOAD_BALANCE=on) (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=[1:95e05a:g0d:da7a:2007]) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=[1:95e05a:g0d:da7a:2006])(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=GREPORADB)))"

For JDBC (OCI) IPV6:

url="jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=
(PROTOCOL=TCP)(HOST=[1:95e05a:g0d:da7a:2007])(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=GREPORADB)))"

As you can imagine, the same applies to your TNSNAMES entries.

Also, according to this, it can be used even for your LISTENER:

LISTENER =
 (DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS =
    (PROTOCOL = TCP)
    (HOST = [1:95e05a:g0d:da7a:2007])(PORT =1521))
  )
 )

Cheers!

ORA-02158: invalid CREATE INDEX option while using redef_table on Oracle 12cR1

Hey all,
I’m working on a table redefinition project to migrate the existing tables and indexes to new compressed tablespaces. As the customer asked to have near 0 downtime to its data we decided to go with DBMS_REDEFINITION.

Simple right? Well… I sure hoped so.
I’m preparing a serie of posts about it, but before that I would like to share some hands on issues and that the magic of the new redef_table is not that great yet, at least on 12cR1/12.1.0.2.
Prior 12c, when would need to redefine a table you would use the DBMS_REDEFINITION and its 6 steps:

0 – Manually create a interim table to receive the data with the same structure as the original table
1 – DBMS_REDEFINITION.can_redef_table
2 – DBMS_REDEFINITION.start_redef_table
3 – DBMS_REDEFINITION.sync_interim_table
4 – DBMS_REDEFINITION.copy_table_dependents
5 – DBMS_REDEFINITION.finish_redef_table

And sometimes you would need to user the DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT to help on some issues but if everything was good you would only need to do the steps above.

There are a few issues with the approach by that I mean BUGS 🙂 so you need to watch your back do an a good prep work.

In 12c you have a new procedure called DBMS_REDEFINITION.redef_table that would bundle all the 6 steps into one single procedure call. With its up and down side.
For me, the down side is that we can’t monitor the procedure, once this is no loger anything being recorded on dba_redefinition_errors.
By working as a transaction, everything works or its rolled back (Or it should but I will leave it for another post).

So the only way to know what is being done is to trace the session that is doing the redefinition. And that what I needed to do to see what was going on with a strange situation.

This is what was happening: I 1st tried the DBMS_REDEFINITION.redef_table and it raised ORA-02158: invalid CREATE INDEX option but when I used the 6 steps mentioned above (can_redef_table,start_redef_table,etc) it worked without issues:

That bugged me so I traced the session.

SQL> exec dbms_monitor.session_trace_enable(binds=>true,waits=>true);

SQL> BEGIN DBMS_REDEFINITION.REDEF_TABLE(uname => 'USER1',
tname => 'TEST1',
table_compression_type => 'COMPRESS FOR OLTP', 
table_part_tablespace => 'DATA_COMP', 
index_tablespace => 'DATA_COMP', 
index_key_compression_type   => 'COMPRESS ADVANCED LOW', 
lob_compression_type => 'COMPRESS HIGH', 
lob_tablespace => 'DATA_COMP', 
lob_store_as => 'SECUREFILE');
END;
/
BEGIN*
ERROR at line 1:ORA-02158: invalid CREATE INDEX option
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3385
ORA-06512: at line 2

And this was the create index statement that was in the trace:

250438 PARSE ERROR #140737488293344:len=421 dep=1 uid=0 oct=9 lid=0 tim=3977494319094 err=2158250439 CREATE INDEX "USER1"."TMP$$_INDEX0" ON "USER1"."REDEF$_16752430_0" ("VISIT_NO")250440   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED LOWNOLOGGING250441   STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645250442   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1250443   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)250444   TABLESPACE "DATA_COMP"

Can you see the issue?
Well neither could I, a colleague read the trace again and found a silly bug.

Here it is The create index had this:

COMPRESS ADVANCED LOWNOLOGGING

Instead of this, it had this:

COMPRESS ADVANCED LOW NOLOGGING

A silly space was missing and was causing the entire redefinition to fail!

I could not find any reference in MOS but that was it a space prevented to use the redef_table and caused me to lose some hours on it.

Hope this save you some time on your troubleshooting and I will be posting other strange situations that I found using the redef_table on Oracle 12cR1.

See you next post!

Elisson Almeida

After Patch: MRP0: Background Media Recovery terminated with error 10485

Ok,
I had that some time ago after applying Patch 27475598 – Oracle JavaVM Component 11.2.0.4.180417 Database PSU.
Why? Well, this is Non RAC-Rolling Installable and also Not Data Guard Standby First Installable.

This means there downtime for this patch, no escape.

I had to (skipping all the standard opatch steps, you can see those on README):

  • Stop DG Replication:
dgmgrl /
show configuration
show database mydg
edit database 'mydg' set state='apply-off';
show database mydg
  • Run postinstall.sql in upgrade mode with only 1 instance on (disable RAC):
cd $ORACLE_HOME/sqlpatch/27475598
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> alter system set cluster_database=false scope=spfile;
SQL> SHUTDOWN
SQL> STARTUP UPGRADE
SQL> @postinstall.sql
SQL> alter system set cluster_database=true scope=spfile;
SQL> SHUTDOWN
SQL> STARTUP

Ok, all good, seems all fine.

But now when starting my DG replication:

dgmgrl /
show configuration
show database mydg
edit database 'mydg' set state='apply-on';
show database mydg

What I see is:

DGMGRL> show database mydg

Database - mydg

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       41 minutes 53 seconds (computed 1 second ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    myprod

  Database Error(s):
    ORA-16766: Redo Apply is stopped

Database Status:
ERROR

DGMGRL>

And on Database Alert Log:

MRP0: Background Media Recovery terminated with error 10485
Errors in file /u01/app/oracle/diag/rdbms/axwest/greporaprod/trace/greporaprod_pr00_42628.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (greporaprod)

Well, in my case it happens because I use an Active Dataguard, in open read only. The solution? Start you DG in Mount Mode to apply the patching replication!

This is well described as per MOS: MRP process getting terminated with error ORA-10485 (Doc ID 1618485.1).

After getting sync, you can simple promote it to read only mode again.

Hope it helps!

Oracle Open World SP 2018: Review and Downloads

Hello Everyone!

So, this is a quick post just sharing that (almost) all presentations from the Oracle Open World SP are available, for free, to download here.

Of course, the presentations don’t replace the experience to be there, discussing and seeing in first hand the thoughts and insights from such great specialists. So, if you can, please arrange yourself for next year!

About the event?
Well, as usual we had a lot of merchandising for Oracle, as it’s expected. But it was great to see some well known specialists talking themselves about their products. Not only sales, but people who really work with the Products. The big problem: So many parallel sessions I’d like to attend! lol

I know you like something for specific, so let me share my top10 sessions from this year. I really recommend you to download those presentations from the link above and search for the speaker profiles for more information about them.

# OOW18 Top 10 by Matheus:
– Making Graph Databases Fun Again With Java (Otavio Santana)
– Redução de custos com Backups utilizando Oracle Storage Services (Rapahel Campelo)
– Por dentro da cabeça de um hacker de banco de dados (Antonio Santos)
– Novidades do Oracle 18c que você não pode perder (Luciano Verissimo)
– Nova regulamentação GDPR: proteção de dados e as implicações para os negócios (Angelo Neto)
– Da aquisição ao consumo de dados: informação é poder para tomar decisões  (Felipe Rasche, João Barros Neto)
– Confie no seu pipeline: Teste automaticamente um aplicativo Java de ponta a ponta (Elisas Nogeira)
– Oracle Autonomous Cloud Platform: Inovações mais Rápidas, Baratas e Seguras Utilizando IA (Siddhartha Agarwal)
– SQL Tuning 101 (Alex Zaballa)
– Autonomous: O primeiro Banco de Dados Autônomo do mundo (Andrew Mendelsohn)

Hope you enjoy it, see you there next year!

Managing AWR Warehouse Repository Database

1. Change Retention Period Of AWR Warehouse Repository Database

This retention of the AWR on the Repository Database can be changed by the following:

<OMS_HOME>/bin>./emcli awrwh_reconfigure -retention=<New retention period (in years)>
For example: 
[oracle@oem13c oms]$ emcli awrwh_reconfigure -retention=5

2. Change Staging Location Of AWR Dump Files

For the AWR Warehouse, the target database by default creates dump file in home directory. So after adding the target to AWR warehosue, we need to reconfigure it from OEM CLI to change the dump files directory as following:

<OMS_HOME>/bin>./emcli awrwh_reconfigure_src -target_name=<target database name> -target_type=rac_database -src_dir="<directory path>"
For example: 
[oracle@oem13c ]$ ./emcli awrwh_reconfigure_src -target_name=greporadb -target_type=rac_database -src_dir="/arwdata/awrw"

3. Change Upload Interval Of SnapShots In AWR Warehouse Repository Database

This configuration can be changed by the following:

<OMS_HOME>/bin>./emcli awrwh_reconfigure -upload_interval <New upload interval>
For example: 
[oracle@oem13c oms]$ ./emcli awrwh_reconfigure -upload_interval=12

4. List Current Configuration

This can be accomplished by the following:

[oracle@oem13c oms]$ emcli awrwh_reconfigure -list
Upload Interval (hours) = 12
Retention (years) = 5
Dump Location = /awrdata/awrw/
AWR Warehouse reconfigured successfully
[oracle@oem13c oms]$

Reference
EM13c: How To Change Retention Period Of AWR Warehouse Repository Database In 13.2 OEM Cloud Control (Doc ID 2247437.1)
EM13c: How To Change Staging Location Of Dump Files On AWR Warehouse Repository Database In 13.2 OEM Cloud Control (Doc ID 2247439.1)
EM13c: How To Change Upload Interval Of SnapShots In AWR Warehouse Repository Database In 13.2 OEM Cloud Control (Doc ID 2247438.1)