SHUTDOWN: waiting for active calls to complete

Solution 

1. Shutdown database with ABORT option

SQL> shutdown abort;

2. Open database with RESTRICT option

SQL> startup restrict

3. Shutdown database with NORMAL option

SQL> shutdown normal;

Implementation 

Hash Partitioning an Existing Table using DBMS_REDEFINITION in Oracle 11gR2 RAC Environment

Hash partitioning is a partitioning technique where a hash key is used to distribute rows evenly across the different partitions (sub-tables). This is typically used where ranges aren't appropriate, i.e. invoiceNumber, productID, OrderID etc.

Implementation of HASH partition in FXXX_ACCOUNT table. Below are details step:

Step-1: Check Redefinition possible in FXXX_ACCOUNT for Partition.

SQL> EXEC DBMS_REDEFINITION.can_redef_table('Nxxx24', 'Fxxx_ACCOUNT');

PL/SQL procedure successfully completed.

Step-2: Check Index, constraint & data in Fxxx_ACCOUNT

SQL> select count(1) from Fxxx_ACCOUNT;

  COUNT(1)

----------

   2049406

SQL> select table_name, index_name from user_indexes where table_name='Fxxx_ACCOUNT';

TABLE_NAME                     INDEX_NAME

------------------------------ ------------------------------

Fxxx_ACCOUNT                   SYS_IL0001003322C00003$$

Fxxx_ACCOUNT                   Fxxx_ACCOUNT_PK

SQL> select  constraint_name, constraint_type,table_name from user_constraints where table_name='Fxxx_ACCOUNT';

CONSTRAINT_NAME                C TABLE_NAME

------------------------------ - ------------------------------

SYS_C00404236                  C Fxxx_ACCOUNT

Fxxx_ACCOUNT_PK                P Fxxx_ACCOUNT

Step-3: Check Partition is already exists on FXXX_ACCOUNT table

SQL> SELECT partitioned FROM user_tables WHERE  table_name = 'Fxxx_ACCOUNT';

PAR

---

NO

Step-4: create a partition interim table Fxxx_ACCOUNT_PAR

SQL> CREATE TABLE Fxxx_ACCOUNT_PAR(

RECID VARCHAR2(255) NOT NULL,

XMLRECORD XMLTYPE

)

PARTITION BY HASH(RECID) (

  partition p1  tablespace NBxxxDATA,

  partition p2  tablespace NBxxxDATA,

  partition p3  tablespace NBxxxDATA,

  partition p4  tablespace NBxxxDATA,

  partition p5  tablespace NBxxxDATA, 

  partition p6  tablespace NBxxxDATA,

  partition p7  tablespace NBxxxDATA,

  partition p8  tablespace NBxxxDATA,

  partition p9  tablespace NBxxxDATA,

  partition p10 tablespace NBxxxDATA,

  partition p11  tablespace NBxxxDATA,

  partition p12  tablespace NBxxxDATA,

  partition p13  tablespace NBxxxDATA,

  partition p14  tablespace NBxxxDATA,

  partition p15  tablespace NBxxxDATA

 );

/

Step-5: Start the Redefinition Process

SQL> ALTER SESSION FORCE PARALLEL DML PARALLEL 8;

Session altered.

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;

Session altered.

SQL> BEGIN

  2    DBMS_REDEFINITION.start_redef_table(

  3      uname      => ' Nxxx24',

  4      orig_table => 'Fxxx_ACCOUNT',

  5      int_table  => 'Fxxx_ACCOUNT_PAR');

  6  END;

  7  /

PL/SQL procedure successfully completed.

Note: this process take quite some time.

Step-6: Synchronize new table with interim data before index creation

SQL> BEGIN

  2    DBMS_REDEFINITION.sync_interim_table(

  3      uname      => ' Nxxx24',

  4      orig_table => 'Fxxx_ACCOUNT',

  5      int_table  => 'Fxxx_ACCOUNT_PAR');

  6  END;

  7  /

PL/SQL procedure successfully completed.

Step-7: The dependent objects will need to be created against the new table. This is done using the COPY_TABLE_DEPENDENTS procedure. We can decide which dependencies should be copied.

SET SERVEROUTPUT ON

DECLARE

l_errors  NUMBER;

BEGIN

DBMS_REDEFINITION.copy_table_dependents(

uname            => 'NBxxx24',

orig_table       => 'Fxxx_ACCOUNT',

int_table        => 'Fxxx_ACCOUNT_PAR',

copy_indexes     => DBMS_REDEFINITION.cons_orig_params,

copy_triggers    => TRUE,

copy_constraints => TRUE,

copy_privileges  => TRUE,

ignore_errors    => FALSE,

num_errors       => l_errors,

copy_statistics  => FALSE,

copy_mvlog       => FALSE);

DBMS_OUTPUT.put_line('Errors=' || l_errors);

END;

/

Note: we can do that manually instead of COPY_TABLE_DEPENDENTS 

Step-8: Finish the Redefinition Process.  At this process interim table has become the "real" table and their names have been switched in the data dictionary.

BEGIN

  DBMS_REDEFINITION.finish_redef_table(

    uname      => 'Nxxx24',       

    orig_table => 'Fxxx_ACCOUNT',

    int_table  => 'Fxxx_ACCOUNT_PAR');

END;

Step-9: Check whether partition is create in Fxxx_ACCOUNT

> Partition create check IN Fxxx_ACCOUNT

SQL> SELECT partitioned FROM user_tables WHERE  table_name = 'FXXX_ACCOUNT';

PAR

---

YES

> Partition wise total row INTO in Fxxx_ACCOUNT

SQL> SELECT 'p1', count(1) FROM FBNK_ACCOUNT partition (p1) UNION ALL
  2  SELECT 'p2', count(1) FROM FBNK_ACCOUNT partition (p2) UNION ALL
  3  SELECT 'p3', count(1) FROM FBNK_ACCOUNT partition (p3) UNION ALL
  4  SELECT 'p4', count(1) FROM FBNK_ACCOUNT partition (p4) UNION ALL
  5  SELECT 'p5', count(1) FROM FBNK_ACCOUNT partition (p5) UNION ALL
  6  SELECT 'p6', count(1) FROM FBNK_ACCOUNT partition (p6) UNION ALL
  7  SELECT 'p7', count(1) FROM FBNK_ACCOUNT partition (p7) UNION ALL
  8  SELECT 'p8', count(1) FROM FBNK_ACCOUNT partition (p8) UNION ALL
  9  SELECT 'p9', count(1) FROM FBNK_ACCOUNT partition (p9) UNION ALL
 10  SELECT 'p10', count(1) FROM FBNK_ACCOUNT partition (p10) UNION ALL
 11  SELECT 'p11', count(1) FROM FBNK_ACCOUNT partition (p11) UNION ALL
 12  SELECT 'p12', count(1) FROM FBNK_ACCOUNT partition (p12) UNION ALL
 13  SELECT 'p13', count(1) FROM FBNK_ACCOUNT partition (p13) UNION ALL
 14  SELECT 'p14', count(1) FROM FBNK_ACCOUNT partition (p14) UNION ALL

 15  SELECT 'p15', count(1) FROM FBNK_ACCOUNT partition (p15);

> After Partition total row in FXXX_ACCOUNT

SQL> select count(1) from fbnk_account;

  COUNT(1)

----------

   2049406 

Oracle Fusion Middleware 11g R2 Forms and Reports Installation on Windows (64-bit)

This article describes default installation of Oracle Fusion Middleware 11g R2 Forms and Reports on Windows (64-bit). To install 11g R2 Forms & Report, At fist you must have to install Oracle WebLogic Server 11gR1 (10.3.6) on that machine. The article assumes there is already an Oracle WebLogic Server 11gR1 (10.3.6) installation present on the machine. If you don't install weblogic server please follow the below link to install weblogic server:

http://bishwanathdas.blogspot.com/2016/10/oracle-weblogic-server-11gr1-1036.html

Installation

Unzipping the "ofm_frmrpts_win_11.1.2.2.0_64_disk1_1of2" and "ofm_frmrpts_win_11.1.2.2.0_64_disk1_2of2" files will result in 5 directories (Disk1, Disk2, Disk3, Disk4, Disk5). Run the "setup.exe" executable in the "Disk1" directory.

Click the "Next" button on the Welcome screen.

Select the "Skip Software Updates" radio group, then click the "Next" button.

Accept the "Install Software-Do Not Configure" option by clicking the "Next" button.

If the prerequisites were successful, click the "Next" button.

Select a previously installed 10.3.6 middlware home, then click the "Next" button.

Uncheck the security updates checkbox, then click the "Next" button.

Installation Summary and Click install button.

Installation in Progress

Software Installation Complete.

Scheduling RMAN Disk Backup in hpux & Linux using "cron" Utility

The following procedure uses the "cron" utility to schedule daily database RMAN backups at 8:00 a.m EXCEPT Friday.

Task-1: create RMAN Backup Script

Start a text editor and create and save a file with the following contents. Save the file in a directory that is accessible to the Oracle Database software and on which the Oracle software owner has the read permission.

# vi rmandiskbkp.sh

export ORACLE_BASE=/u02/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1

export ORACLE_SID=dcpdb1

export PATH=$ORACLE_HOME/bin:$PATH

export TZ=GMT6

dt=`date '+%d%m%Y%H%M'`

rman target rmanbkp/rmanbkp <<eof

sql 'alter system archive log current';

run{

allocate channel c1  type disk;

allocate channel c2  type disk;

allocate channel c3  type disk;

allocate channel c4  type disk;

allocate channel c5  type disk;

allocate channel c6  type disk;

allocate channel c7  type disk;

backup AS COMPRESSED BACKUPSET full database tag T24_DB_$dt format '/db_rmanbackup/t24_db_%p_%s_$dt.bkp';

sql 'alter system archive log current';

backup current controlfile tag T24_CTL_$dt format '/db_rmanbackup/t24_ctl_%p_%s_$dt.bkp';

backup spfile tag T24_SPF_$dt format '/db_rmanbackup/t24_spf_%p_%s_$dt.bkp'  ;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

release channel c5;

release channel c6;

release channel c7;

}

exit

eof

#

Task-2: Test RMAN backup Script

if script is in the file /u02/app/oracle/rmandiskbkp.sh, then enter this command:

# sh /u02/app/oracle/rmandiskbkp.sh

Task-3: Scheduling RMAN Backup Script

Entry to “crontab”. To entry in “crontab”, then enter this command:

 # crontab –e   [Edit]

In “crontab” below following entry will  place:

# MI HH DD MM DAY CMD

30 08  *  *  0,1,2,3,4,6  sh /u02/app/oracle/rmandiskbkp.sh -b 172.xx.xx.xx

So, By this setting RMAN backup will RUN everyday at 8:30AM except Friday.

Appendix:
# crontab –l   [List]

  Crontab Fields and Allowed Ranges

Field	Description	Allowed Value
MI	Minute field	0 to 59
HH	Hour field	0 to 23
DD	Day of Month field	1 to 31
MM	Month field	1 to 12
DAY	Day of week field	0 to 6
CMD	Command	Any command to be executed

   DAY: 0 to 6: SUN,MON,TUE,WED,THU,FRI,SAT

Create or Configure New Report Server in Fussion Middleware 11g

In this article will show how to configure report server in fusion middleware 11g.

Create report server component

C:\Oracle\Middleware\asinst_9\bin>opmnctl createcomponent -adminUsername weblogic -adminHost itd-bishwanath.nblbd.com -adminPort 7001 -oracleHome C:\Oracle\Middleware\Oracle_FRHome1 -oracleInstance C:\Oracle\Middleware\asinst_9 -instanceName asinst_9 -componentName rep_emdi -componentType ReportsServerComponent

C:\Oracle\Middleware\asinst_9\bin>opmnctl status

Start Report Server

C:\Oracle\Middleware\asinst_9\bin>opmnctl startproc ias-component=rep_emdi

C:\Oracle\Middleware\asinst_9\bin>opmnctl status

Entry to cgicmd.dat file

C:\Oracle\Middleware\user_projects\domains\myClassicDomain\config\fmwconfig\servers\AdminServer\applications\reports_11.1.2\configuration\cgicmd.dat

emedi_rep:server=rep_emdi userid=emedicare/emedicare@emedicaredb destype=cache desformat=pdf %*

COPY Archivelog from ASM to File System (vice-versa)

In this article we will show COPY archivelog from ASM to File System (vice-versa)

COPY ARCHIVELOG FROM ASM TO FILE SYSTEM

Copy Archive log from ASM to File System (172.xx.xx.17)

ASMCMD> cp thread_2_seq_29276.2143.926850451 /u01/grid/seq_29276.bak

copying +fra/dcpdb/ARCHIVELOG/2016_11_02/thread_2_seq_29276.2143.926850451 -> /u01/grid/seq_29276.bak

ASMCMD>

ASMCMD>

ASMCMD> cp thread_2_seq_29277.909.926851881 /u01/grid/seq_29277.bak

copying +fra/dcpdb/ARCHIVELOG/2016_11_02/thread_2_seq_29277.909.926851881 -> /u01/grid/seq_29277.bak

ASMCMD>

ASMCMD>

ASMCMD> cp thread_2_seq_29278.1497.926853021 /u01/grid/seq_29278.bak

copying +fra/dcpdb/ARCHIVELOG/2016_11_02/thread_2_seq_29278.1497.926853021 -> /u01/grid/seq_29278.bak

ASMCMD>

Copy Archive log from ASM to File System (172.xx.xx.15)

ASMCMD>

ASMCMD> cp thread_1_seq_29530.2118.926850057 /u01/oraclesoft/seq_29530.bak

copying +fra/dcpdb/archivelog/2016_11_02/thread_1_seq_29530.2118.926850057 -> /u01/oraclesoft/seq_29530.bak

ASMCMD>

ASMCMD> cp thread_1_seq_29531.848.926851257 /u01/oraclesoft/seq_29531.bak

copying +fra/dcpdb/archivelog/2016_11_02/thread_1_seq_29531.848.926851257 -> /u01/oraclesoft/seq_29531.bak

ASMCMD>

ASMCMD> cp thread_1_seq_29532.2814.926852359 /u01/oraclesoft/seq_29532.bak

copying +fra/dcpdb/archivelog/2016_11_02/thread_1_seq_29532.2814.926852359 -> /u01/oraclesoft/seq_29532.bak

ASMCMD>

ASMCMD> cp thread_1_seq_29533.2639.926853019 /u01/oraclesoft/seq_29533.bak

copying +fra/dcpdb/archivelog/2016_11_02/thread_1_seq_29533.2639.926853019 -> /u01/oraclesoft/seq_29532.bak

ASMCMD>

TRANSFER ARCHIVELOG TO ANOTHER SERVER

grid@dcpdb2 [/u01/grid]$

grid@dcpdb2 [/u01/grid]$ scp seq* grid@172.xx.xxx.26:/u01/grid

The authenticity of host '172.31.200.26 (172.xx.xxx.26)' can't be established.

ECDSA key fingerprint is 09:14:29:aa:83:c6:df:68:28:94:56:e8:0e:b0:db:3d.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '172.xx.xxx.26' (ECDSA) to the list of known hosts.

Password:

seq_29276.bak                                                         100%  582MB  32.3MB/s  34.4MB/s   00:18

seq_29277.bak                                                         100%  582MB  30.6MB/s  34.4MB/s   00:19

seq_29278.bak                                                         100%  561MB  31.1MB/s  35.6MB/s   00:18

grid@dcpdb2 [/u01/grid]$

grid@dcpdb1 [/u01/oraclesoft]$ scp seq* grid@172.xx.xxx.26:/u01/grid

The authenticity of host '172.31.200.26 (172.xx.xxx.26)' can't be established.

ECDSA key fingerprint is 09:14:29:aa:83:c6:df:68:28:94:56:e8:0e:b0:db:3d.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '172.xx.xxx.26' (ECDSA) to the list of known hosts.

Password:

seq_29530.bak                                                         100%  582MB  34.2MB/s  34.0MB/s   00:17

seq_29531.bak                                                         100%  581MB  30.6MB/s  34.9MB/s   00:19

seq_29532.bak                                                         100%  594MB  31.3MB/s  36.1MB/s   00:19

seq_29533.bak                                                         100%  381MB  34.7MB/s  36.1MB/s   00:11

grid@dcpdb1 [/u01/oraclesoft]$

COPY ARCHIVELOG FROM FILE SYSTEM TO ASM

ASMCMD>

ASMCMD> cp /u01/grid/seq_29276.bak +fra/dcpdb/ARCHIVELOG/

copying /u01/grid/seq_29276.bak -> +fra/dcpdb/ARCHIVELOG/seq_29276.bak

ASMCMD> cp /u01/grid/seq_29277.bak +fra/dcpdb/ARCHIVELOG/

copying /u01/grid/seq_29277.bak -> +fra/dcpdb/ARCHIVELOG/seq_29277.bak

ASMCMD>

ASMCMD> cp /u01/grid/seq_29278.bak +fra/dcpdb/ARCHIVELOG/

copying /u01/grid/seq_29278.bak -> +fra/dcpdb/ARCHIVELOG/seq_29278.bak

ASMCMD>

ASMCMD> cp /u01/grid/seq_29530.bak +fra/dcpdb/ARCHIVELOG/

copying /u01/grid/seq_29530.bak -> +fra/dcpdb/ARCHIVELOG/seq_29530.bak

ASMCMD>

ASMCMD> cp /u01/grid/seq_29531.bak +fra/dcpdb/ARCHIVELOG/

copying /u01/grid/seq_29531.bak -> +fra/dcpdb/ARCHIVELOG/seq_29531.bak

ASMCMD>

ASMCMD> cp /u01/grid/seq_29532.bak +fra/dcpdb/ARCHIVELOG/

copying /u01/grid/seq_29532.bak -> +fra/dcpdb/ARCHIVELOG/seq_29532.bak

ASMCMD>

ASMCMD> cp /u01/grid/seq_29533.bak +fra/dcpdb/ARCHIVELOG/

copying /u01/grid/seq_29533.bak -> +fra/dcpdb/ARCHIVELOG/seq_29533.bak

ASMCMD>

ASMCMD>