Friday, July 31, 2020

How to connect to and start a specific database using dgmgrl

The data guard broker allows you to connect to any of the participating members of the configuration:
oracle@myserver:[hdal]# dgmgrl / as sysdba
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Jul 31 10:44:47 2020

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

Welcome to DGMGRL, type "help" for information.
Connected to "hdal"
Connected as SYSDBA.
DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  hdal      - Primary database
    hdal_stb  - Physical standby database
      hdal_tstb - Physical standby database (receiving current redo)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 51 seconds ago)

DGMGRL> connect sys@hdal_stb as sysdba
Password:
Connected to "hdal_stb"
Connected as SYSDBA.
Note that the password files must be identical for all members of the configuration.
You can startup and shutdown individual members of your configuration without leaving the data guard broker interface, for example:
DGMGRL> startup mount
ORACLE instance started.
Database mounted.
Documentation on how to use Oracle 12.2 version of dgmgrl is found here

Wednesday, July 29, 2020

How to rebuild an index partition

To limit a rebuild to a specific index partition only, use the following syntax:
ALTER INDEX SH.SALES_IDX4 
REBUILD PARTITION SYS_P51490 
TABLESPACE DATA04 ONLINE;
Notice the use of the ONLINE keyward, which will allow DML against the table (and thus update the index). To generate rebuild-statements, one index at a time, I have used the script below. It takes as arguments
  1. The index owner 
  2. The index name 
  3. The new tablespace where you want to place your partitions 
  4. The old tablespace from which you want to move out
set termout off
select 'alter session set nls_language=''american'';' from dual;
set termout on
accept index_owner prompt 'Index owner: '
accept index_name prompt 'Index name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_move_part_&&index_owner..&&index_name..sql
select 'alter session set nls_language=''american'';' from dual;

select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_rebuild_part_&&index_owner..&&index_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''Rebuild_part_&&index_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''Rebuild_part_t_&&index_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''Rebuild_part_&&index_name''); ' from dual;

select 'prompt moving the index partitions from &&old_tabspc to &&new_tabspc,' from dual;
select 'prompt and setting default attributes for the table ' from dual;
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE &&new_tabspc ONLINE;'
FROM    dba_indexes idx, dba_ind_partitions idxpart
WHERE   idx.table_owner = '&&index_owner'
and     idxpart.TABLESPACE_NAME = '&&old_tabspc'
AND     idx.index_name = idxpart.index_name
AND     idx.index_name = '&&index_name'
AND     idxpart.subpartition_count=0
ORDER BY idx.table_owner, idx.index_name;

select distinct 'alter index ' ||   IDXPART.INDEX_OWNER || '.' || IDXPART.INDEX_NAME || ' modify default attributes tablespace &&new_tabspc;'
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON     (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXPART.INDEX_NAME='&&index_name'
AND    IDXPART.TABLESPACE_NAME='&&old_tabspc';

select 'exit' from dual;
exit

Run it as a user with access to the data dictionary: 

sqlplus / as sysdba @gen_move_idx_part.sql 

It will generate a new script called exec_move_part_<username>.<index_name>.sql which will perform the actual rebuild: 
sqlplus / as sysdba @exec_move_part_<username>.<index_name>.sql

I also added som calls to dbms_application_info so that the session can be easily identfied in v$session. Remember, sometimes it's desirable to set the PARALLEL degree and the NOLOGGING options during rebuild, to complete it as fast as possible. See this post for more info.

How to list index partitions and their sizes

To list index partitions for index SALES_IDX1:
SELECT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXPART.INDEX_NAME='SALES_IDX1'
AND    S.SEGMENT_TYPE='INDEX PARTITION'
-- To only list partitions in tablespace DATA1, uncomment the following line:
-- AND    IDXPART.TABLESPACE_NAME='DATA1'
GROUP BY IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.PARTITION_NAME,IDXPART.TABLESPACE_NAME
ORDER BY 5 DESC;

INDEX_OWNER INDEX_NAME PARTITION_NAME TABLESPACE_NAME GB
SH SALES_IDX1 SYS_P177667 DATA1
8
SH SALES_IDX1 SYS_P177518 DATA1
5
SH SALES_IDX1 SYS_P44844 DATA1
3
SH SALES_IDX1 SYS_P44663 DATA1
2
SH SALES_IDX1 SYS_P177677 DATA1
2

To list all index partitions for tablespace DATA1:
SELECT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
AND    S.SEGMENT_TYPE='INDEX PARTITION'
AND    IDXPART.TABLESPACE_NAME='DATA1'
GROUP BY IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.PARTITION_NAME,IDXPART.TABLESPACE_NAME
ORDER BY 5 DESC;

INDEX_OWNER INDEX_NAME PARTITION_NAME GB
SH SALES_IDX1 SYS_P177667
8
SH SALES_IDX1 SYS_P177518
5
SH SALES_IDX1 SYS_P44844
3
SH SALES_IDX1 SYS_P177677
2
SH SALES_IDX1 SYS_P44663
2
SH SALES_IDX2 SYS_P179608
1
SH SALES_IDX2 SYS_P178334
1
SH SALEX_IDX2 SYS_P178459
1
SH SALES_IDX3 SYS_P28534
0
SH SALES_IDX3 SYS_P50905
0

To list the names of all partitioned indexes and their total sizes, in a particular tablespace:
SELECT DISTINCT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.TABLESPACE_NAME, 
    (SELECT ROUND(SUM(S.BYTES)/1024/1024/1024) 
     FROM DBA_SEGMENTS S
     WHERE S.SEGMENT_NAME = IDXPART.INDEX_NAME
     ) GB
FROM   DBA_IND_PARTITIONS IDXPART 
WHERE  IDXPART.TABLESPACE_NAME='DATA1'
ORDER BY 4 DESC;
INDEX_OWNER INDEX_NAME TABLESPACE_NAME GB
SH SALES_IDX1 DATA1
567
SH SALES_IDX2 DATA1
511
SH SALES_IDX3 DATA1
331

To check which tablespaces the different partitions in an index reside in:
SELECT TABLESPACE_NAME,COUNT(*)
FROM DBA_IND_PARTITIONS
WHERE INDEX_NAME='SALES_IDX4'
GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME COUNT(*)
DATA1
13
DATA2
832

Tuesday, July 21, 2020

How to fix RMAN-04006 when cloning from active database




Background:
During an attempt to clone database using active duplication, I received an error when testing my connectivity to the target and auxiliary server. I normally do a testrun first, to see if I can connect without problems, before I start my cloning script.

My script to test the connection is simple and called "testconnection.cmd".
The connections are made through a wallet, so that you do not have to expose your passwords in any scripts or any shell prompts. See my previous post for details about setting up a wallet.
connect target /@proddb01.oric.no
connect auxiliary /@testdb01.oric.no
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
}
exit

Execute it as follows:
rman cmdfile=testconnection.cmd

connected to target database: prodb01 (DBID=2078894010, not open)
connected to auxiliary database (not started)

RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Cause:
Incorrect password in the auxiliary database

Solution:
Copy the password file from your target database server to your auxiliary database server and retry the operation.

When all is good, the output from the auxiliary database should be
connected to auxiliary database: testdb01 (not mounted)

Tuesday, July 14, 2020

Identity columns in Oracle 12c



The identity column introduced in Oracle 12c can be used in the different manners, according to your needs. Here are some basic examples:

create table songs (
    song_id number generated always as identity,
    title varchar2(128)
);

The "generated always" clause is default and may be omitted.

In this scenario, no inserts may contain a value for the column song_id, even if the datatype is correct. Neither null values are accepted. Oracle will generate its own value and will not allow any manipulation of the value recorded in the identity column.


Allow for the possibility to override the auto generated numbers by specifying "by default" instead of "always":
create table songs (
    song_id number generated by default as identity,
    title varchar2(128)
);
In other words, the statements below would both be permissible:
insert into songs (song_id, title)values(10,'Born to run');
update songs set song_id=20 where song_id=10;
However, null cannot be specified as a legitimate value for the identity column:
insert into songs values(null, 'White wedding');
would return the error
ORA-01400: cannot insert NULL into ("MUSIC"."SONGS"."SONG_ID")

If you need to allow for NULL values in the identity column, you create the table as follows:
create table songs (
    song_id number generated by default on null as identity,
    title varchar2(128)
);

With the above definition, Oracle

* permits overriding the autogenerated numbers
* allows update of the identity column
* allows null values to be specified during inserts

All of the three statements below are thus valid:
insert into songs (song_id, title)values(10,'Born to run');
update songs set song_id=20 where song_id=10;
insert into songs values(null, 'White wedding');

Tim Hall has published a more in-depth article about the same topic, as usual of the highest quality

Friday, June 19, 2020

How to run RMAN with debug information


Tested with Oracle 12.2

For example, to trace a duplicate database session, I have put the following into a file called "run_duplication.cmd":

spool trace to run_duplication.trc
spool log to run_duplication.log
set echo on;
debug on;


connect target sys/password@sourcedb
connect auxiliary sys/password@auxdb

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
DUPLICATE DATABASE "sourcedb.mydomain.com" TO auxdb
FROM ACTIVE DATABASE
TABLESPACE TOOLS
USING COMPRESSED BACKUPSET;
}
exit

When you execute your rman script:

rman cmdfile='run_duplication.cmd'

your log and trace files will be generated in your working directory.

Wednesday, May 13, 2020

How to use the TIMESTAMP_TO_SCN function


select timestamp_to_scn(to_timestamp('2020-05-11 14:36:22', 'YYYY-MM-DD HH24:MI:SS')) "scn" from dual;

scn
----------
20325895

Reversed:
select scn_to_timestamp(20325895) "timestamp" from dual;

timestamp
----------------------------------
11-MAY-20 02.36.20.000000000 PM