DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, April 26, 2017

To change the database name after database creation

Method 1:

1) Using NID utility

a) alter database close;
b) nid target=sys as sysdba dbname=clone(this will change the control files and headers of the datafiles with the correction of new name)

c)cd $ORACLE_HOME/dbs

d) cp initprod.ora initclone.ora

e)vi initclone.ora

find db_name parameter and change it to clone
f)vi /etc/oratab
change the name prod to clone

g) . oraenv
set the variable ORACLE_SID=clone

f) startup the database
SQL> startup (but the database open will error out since the database should open with resetlogs)

h) alter database open resetlogs;

i) select dbid, name from v$database;


Method 2:

By changing the control file

a)alter database backup control file to trace;

b)go to trace directory and edit the control file trace

c)In control file trace update database name prod to clone,

CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oracle/app/oracle/oradata/CLONE/redo1.log'  SIZE 10M BLOCKSIZE 512,
  GROUP 2 '/u01/oracle/app/oracle/oradata/CLONE/redo2.log'  SIZE 10M BLOCKSIZE 512,
  GROUP 3 '/u01/oracle/app/oracle/oradata/CLONE/redo3.log'  SIZE 10M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/oracle/app/oracle/oradata/CLONE/system.dbf',
  '/u01/oracle/app/oracle/oradata/CLONE/user04.dbf',
  '/u01/oracle/app/oracle/oradata/CLONE/sysaux.dbf',
  '/u01/oracle/app/oracle/oradata/CLONE/undo.dbf',
CHARACTER SET UTF8; 

d)shut down the database

e)Backup the existing control file.

f)vi initclone.ora file and change the dbname prod to clone and change the control file location also.

g)startup nomount

h)execute the control file trace which we modified earlier(this will create a new control file in the location with the  new dbname )
@control.sql

i) alter database open resetlogs;

j) select database_name  from v$database

Change ASM Diskgroup from normal redundancy to external redundancy

STEPS: 

Step 1 :Shutdown the running database
SQL> shut immediate;

Step 2:Mount the database
SQL> startup mount

Step 3:
Open RMAN  and  do a Backup of Whole Database

SQL> !rman target /

RMAN> backup device type disk format '/u01/asm1/database_format%u' database;

Starting backup at 22-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/myasm/datafile/system.302.907085491
input datafile file number=00002 name=+DATA/myasm/datafile/sysaux.303.907085493
input datafile file number=00003 name=+DATA/myasm/datafile/undotbs1.304.907085495
input datafile file number=00005 name=+DATA/myasm/datafile/users.320.907107373
input datafile file number=00006 name=+DATA/myasm/datafile/demo.319.907106321
input datafile file number=00004 name=+DATA/myasm/datafile/users.305.907085495
channel ORA_DISK_1: starting piece 1 at 22-MAR-17
channel ORA_DISK_1: finished piece 1 at 22-MAR-17
piece handle=/u01/asm1/database_format09r148in tag=TAG20160322T122141 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:37
Finished backup at 22-MAR-17

Starting Control File and SPFILE Autobackup at 22-MAR-17
piece handle=+DATA/myasm/autobackup/2016_03_22/s_907157976.272.907158211 comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAR-17

RMAN> backup device type disk format '/u01/asm1/arch_format%u' archivelog all;

Starting backup at 22-MAR-17
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=2 RECID=1 STAMP=907086880
input archived log thread=1 sequence=3 RECID=2 STAMP=907105575
input archived log thread=1 sequence=4 RECID=3 STAMP=907110892
input archived log thread=1 sequence=5 RECID=4 STAMP=907115995
input archived log thread=1 sequence=6 RECID=5 STAMP=907116001
input archived log thread=1 sequence=7 RECID=6 STAMP=907116020
input archived log thread=1 sequence=8 RECID=7 STAMP=907116024
input archived log thread=1 sequence=9 RECID=8 STAMP=907116037
input archived log thread=1 sequence=10 RECID=9 STAMP=907116045
input archived log thread=1 sequence=11 RECID=10 STAMP=907116050
input archived log thread=1 sequence=12 RECID=11 STAMP=907116066
input archived log thread=1 sequence=13 RECID=12 STAMP=907116177
input archived log thread=1 sequence=14 RECID=13 STAMP=907116218
input archived log thread=1 sequence=15 RECID=14 STAMP=907116267
input archived log thread=1 sequence=16 RECID=15 STAMP=907116290
input archived log thread=1 sequence=17 RECID=16 STAMP=907116297
input archived log thread=1 sequence=18 RECID=17 STAMP=907153331
input archived log thread=1 sequence=19 RECID=18 STAMP=907154322
input archived log thread=1 sequence=20 RECID=19 STAMP=907154422
input archived log thread=1 sequence=21 RECID=20 STAMP=907155366
input archived log thread=1 sequence=22 RECID=21 STAMP=907155677
channel ORA_DISK_1: starting piece 1 at 22-MAR-17
channel ORA_DISK_1: finished piece 1 at 22-MAR-17
piece handle=/u01/asm1/arch_format0br148ns tag=TAG20160322T122427 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 22-MAR-17

Starting Control File and SPFILE Autobackup at 22-MAR-17
piece handle=+DATA/myasm/autobackup/2016_03_22/s_907157976.289.907158289 comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAR-17


Step 4 :Create pfile for Database and backup controlfile

SQL> create pfile='/u01/asm1/initnew.ora' from spfile;
File created.

SQL> alter database backup controlfile to '/u01/asm1/control01.ctl';
Database altered.

SQL> shut immediate;


Step 6
Run ASMCA, 

Create new diskgroup with External Redundancy  and drop disk group existing Diskgroup
$asmca

[oracle@oracleasm1 Desktop]$ .  oraenv
ORACLE_SID = [myasm] ? myasm
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@oracleasm1 Desktop]$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 22 12:49:17 2016
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/u01/asm1/initnew.ora';
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size     1336232 bytes
Variable Size   197135448 bytes
Database Buffers   109051904 bytes
Redo Buffers     6336512 bytes

SQL> create spfile ='+DATA' from pfile='/u01/asm1/initnew.ora' ;
File created.

SQL> !rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 22 12:50:34 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MYASM (not mounted)

RMAN> restore controlfile from '/u01/asm1/control01.ctl';

Starting restore at 22-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/myasm/controlfile/current.257.907160017
output file name=+DATA/myasm/controlfile/current.258.907160019
Finished restore at 22-MAR-16

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 22-MAR-17
Starting implicit crosscheck backup at 22-MAR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 22-MAR-17

Starting implicit crosscheck copy at 22-MAR-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 22-MAR-17

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

datafile 4 not processed because file is offline
datafile 5 not processed because file is offline
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/myasm/datafile/system.302.907085491
channel ORA_DISK_1: restoring datafile 00002 to +DATA/myasm/datafile/sysaux.303.907085493
channel ORA_DISK_1: restoring datafile 00003 to +DATA/myasm/datafile/undotbs1.304.907085495
channel ORA_DISK_1: restoring datafile 00006 to +DATA/myasm/datafile/demo.319.907106321
channel ORA_DISK_1: reading from backup piece /u01/asm1/database_format09r148in
channel ORA_DISK_1: piece handle=/u01/asm1/database_format09r148in tag=TAG20160322T122141
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:16
Finished restore at 22-MAR-17

RMAN> recover database ;

Starting recover at 22-MAR-17
using channel ORA_DISK_1
datafile 4 not processed because file is offline
datafile 5 not processed because file is offline

starting media recovery

archived log for thread 1 with sequence 23 is already on disk as file +NEWDATA/myasm/onlinelog/group_3.258.907115921
archived log file name=+NEWDATA/myasm/onlinelog/group_3.258.907115921 thread=1 sequence=23
media recovery complete, elapsed time: 00:00:01
Finished recover at 22-MAR-17

 Open the database with resetlogs
RMAN> alter database open resetlogs;

High version count statements causes library cache

Due to high SQL version counts issue in Production database, the library cache issue has occurred.The PROD database has not able to accumulate the shared memory for the specific execution of the query.So the execution of the specific query can’t ran with that memory,the new child cursor has been created with a new plan of hash value.  

Impact

The PROD database has high version counts,so that the library cache issue has been occurred.
The reason for not sharing the cursor is bind mismatch.BIND_EQUIV_FAILURE: the bind value’s selectivity does not match that used to optimize the existing child cursor.

Workaround:

We need a bounce of database to change the cursor_obsolete_threshold parameter.

Change cursor_sharing parameter set  from EXACT to FORCE.The cursor_sharing parameter=FORCE has doesn’t affect the bind variable values.

SQL>  show parameter cursor;

NAME                                 TYPE                    VALUE

_cursor_obsolete_threshold           integer                  100

cursor_sharing                       string                  EXACT


alter system set cursor_sharing=FORCE;

If issue is still present after change the cursor_sharing parameter to FORCE, consider setting cursor_obsolete_threshold = 100,

alter system set cursor_obsolete_threshold=100;

1) Consider using cursor_sharing parameter set to FORCE, this will improve share ability on cursors. Current setting is EXACT.

2) Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.

3) Bind mismatch means that something about the bind variables was different.

Tuesday, April 25, 2017

Oracle database 12cR2 New Feature -Spool CSV

Oracle database 12CR2 New Feature -Spool CSV
****************************************

Syntax

CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]

Traditionally you wiill get formatted output (without CSV)  as below.

SQL> conn system/oracle@orclpdb
Connected.

SQL> select ename,job from emp where rownum < 10;

ENAME   JOB
---------- ---------
KING   PRESIDENT
BLAKE   MANAGER
CLARK   MANAGER
JONES   MANAGER
SCOTT   ANALYST
FORD   ANALYST
SMITH   CLERK
ALLEN   SALESMAN
WARD   SALESMAN

9 rows selected.

New CSV Format.

SQL> SET MARKUP CSV ON

SQL> select ename,job from emp where rownum < 10;

"ENAME","JOB"
"KING","PRESIDENT"
"BLAKE","MANAGER"
"CLARK","MANAGER"
"JONES","MANAGER"
"SCOTT","ANALYST"
"FORD","ANALYST"
"SMITH","CLERK"
"ALLEN","SALESMAN"
"WARD","SALESMAN"

9 rows selected.


So Now in 12.2 you can spool as csv and open in excel sheet

Oracle database 12cR2 New Feature - SQLPlus enhancement


History
********

SQL*Plus can keep the history of the commands executed. You can enable or disable the HISTORY command

SQL> show history
history is OFF

SQL> set history on

SQL> select name from v$database;

NAME
---------
ORCL

SQL> select host_name from v$instance;

HOST_NAME
----------------------------------------------------------------
oracle12r2

SQL> history
  1  select name from v$database;
  2  select host_name from v$instance;

SQL> SHOW HIST
history is ON and set to "100"

SQL> HIST CLEAR

SQL> show history
history is ON and set to "100"

SQL> history
  1  show history

SQL> set history off


*********************************************************************************

Feedback
********


SQL> show feedback
FEEDBACK ON for 6 or more rows

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/orclpdb/system01.dbf
/u01/app/oracle/oradata/orcl/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/orcl/orclpdb/undotbs01.dbf
/u01/app/oracle/oradata/orcl/orclpdb/users01.dbf

SQL> set feedback only

SQL> select name from v$datafile;

4 rows selected.

****************************************************************************
Arraysize
********

SQL> set feedback only
SQL> select *  from emp1;

28672 rows selected.

SQL> show arraysize
arraysize 15

SQL> set timing on

SQL> select *  from emp1;

28672 rows selected.

Elapsed: 00:00:00.77

After increase the arraysize from 15 to 200


SQL> set arraysize 200

SQL> select *  from emp1;

28672 rows selected.

Elapsed: 00:00:00.41



Oracle Database 12.2 New Features-Separate Undo Tablespace for each PDB

12.2 New Features-Separate Undo Tablespace for each PDB
*************************************************





SQL> select c.con_id, c.name  con_name, t.tablespace_name, t.contents, t.status
from v$containers c, cdb_tablespaces t
where c.con_id=t.con_id
and t.tablespace_name like '%UNDO%'
order by 1,2;  2    3    4    5

    CON_ID CON_NAME TABLESPACE_NAME       CONTENTS     STATUS
---------- -------------------- ------------------------------ --------------------- ---------
1 CDB$ROOT UNDOTBS1                      UNDO     ONLINE
3 ORCLPDB         UNDOTBS1                     UNDO     ONLINE



SQL> conn system/oracle@orclpdb
Connected.
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled       boolean  FALSE
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS1

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/orclpdb/system01.dbf
/u01/app/oracle/oradata/orcl/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/orcl/orclpdb/undotbs01.dbf
/u01/app/oracle/oradata/orcl/orclpdb/users01.dbf


SQL> create undo tablespace undopdb1 datafile '/u01/app/oracle/oradata/orcl/orclpdb/undopdb1.dbf' size 2m;

SQL> alter system set undo_tablespace='undopdb1';
System altered.

SQL> show parameter undo
NAME           TYPE                  VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled     boolean          FALSE
undo_management              string                  AUTO
undo_retention     integer          900
undo_tablespace              string                  undopdb1

SQL> alter system set undo_tablespace='UNDOTBS1';
System altered.

SQL> drop tablespace undopdb1 including contents and datafiles;

Tablespace dropped.




Monday, April 24, 2017

Unable To Connect From Oracle HTTP Server 12c To Oracle 12c Database. (Doc ID 2033730.1)
Error Receivedin browser:
"Service Temporarily Unavailable
The server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later.

Checked the log from $ORACLE_INSTANCE/diagnostics/logs/OHS/ohs1/ohs1.log
[2017-04-23T00:14:09.3209+05:30] [OHS] [ERROR:32] [OHS-9999] [core.c] [client_id: xx..xx.xx.x] [host_id: somehost] [host_addr: yy.yy.yy.yy] [tid: 140540902872832] [user: oracle] [ecid: 005JSN2R2USFw00Fzzx0g00005PV000001] [rid: 0] [VirtualHost: main]  MODPLSQL-000058: mod_plsql: HTTP-503 ORA-12505 ORA-12505: TNS:listener does not currently know of SID given in connect descriptor\n

CAUSE:
-- 12c database are selected using service_names rather than a SIDS (service id) .

Solution:
1. Go to $ORACLE_INSTANCE/config/OHS/ohs1/mod_plsql/ location and Backup the current dads.conf file.
2. Obtain the correct service_name from the Oracle Database Administrator. By default the service name and database name will be same.To confirm check the local listener.ora config file
3. Replace the PlsqlDabaseConnect string using the following syntax ensuring the correct service name is used from step 2. Also ensure the host and port number are correct.
“PlsqlDatabaseConnectString (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=<database_host>)(Port=<database_port>))(CONNECT_DATA=(SERVICE_NAME=<service_name>))) TNSFormat”

4. Restart Oracle HTTP server.

Saturday, April 22, 2017

Active Users Completed with Error status after applying GST Phase 2 patches in R12.1.3 (Reference : Doc ID 1157857.1)
Error from log:
REP-0004: Warning: Unable to open user preference file.
REP-0069: Internal error
REP-57054: In-process job terminated:Finished successfully but output is voided

Cause:
The cause of this problem has been identified and verified in published Bug 9826688: GENERAL LEDGER ACCOUNT BALANCES ACROSS LEDGERS ERRORS
as the cause. 

The issue occurs due to the logic in retrieving the DAS Id (Definition Access Set Id) when one DAS has duplicate ledgers assigned to it, either directly or through ledger set.

Solution:
To implement the solution, please execute the following steps:

-- Download and review the readme and pre-requisites for the relevant patch:
For R12.0 - Patch 9826688:R12.GL.A
For R12.1 - Patch 9826688:R12.GL.B
-- Ensure that you have taken a backup of your system before applying the recommended patch.
-- Apply the patch in a test environment.
-- Confirm the following file versions:
For R12.0 - GLRGCBGT.rdf 120.0.12000000.9
Note: If a password is required to download the patch, please open a service request to get the proper password.
For R12.1 - GLRGCBGT.rdf 120.1.12010000.8
You can use commands like the following:
        strings -a $GL_TOP/reports/US/GLRGCBGT.rdf |grep '$Header'   
-- Retest the issue.
-- Migrate the solution as appropriate to other environments.

Friday, April 21, 2017

How To Diagnose And Debug A HTTP 503 Error In A Portal Environment (Doc ID 751101.1)

Error Received in browser:
"Service Temporarily Unavailable
The server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later.
Oracle-Application-Server-10g/10.1.2.0.0 Oracle-HTTP-Server Server at my.host.com Port 7780"

Checked the log from $ORACLE_HOME/Apache/Apache/logs/error_log
[Thu Apr 20 19:14:17 2017] [error] [client xxx.xx.xxx.x] [ecid: 1492695857:xxx.xxx.x.xx:21647:0:1,0] mod_plsql: /pls/apex_forging/apex HTTP-503 ORA-28000

1. Verify the APEX_PUBLIC_USER (or HTMLDB_PUBLIC_USER) password. By default, it will be the same password defined for the APEX ADMIN user.
If you are unable to login as the APEX_PUBLIC_USER, modify the password as the SYS user using the following command:
alter user APEX_PUBLIC_USER  identified by <password>;

After changing APEX_PUBLIC_USER password received the below error in browser:
"Forbidden
You don't have permission to access /pls/apex on this server"

Checked the log from $ORACLE_HOME/Apache/Apache/logs/error_log
[Tue Sep 14 09:34:03 2004] [error] [client 111.111.111.10] [ecid: 1095168843:121.121.121.12:2764:0:431,0] mod_plsql: /pls/apex/apex HTTP-403 ORA-1017

2. Make a backup of the file containing the Database Access Descriptor (DAD) definitition for APEX- tyically marvel.conf or dads.conf.
3. Modify the value PlsqlDatabasePassword in to reflect the password identified in step 1. For this example, let's say the password is manager1:

PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDatabasePassword manager1

4. Restart the Oracle HTTP Server.
$ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=HTTP_Server
$ORACLE_HOME/opmn/bin/opmnctl startproc ias-component=HTTP_Server

5. Test to verify that you can login to APEX / HTML DB successfully.

Thursday, April 20, 2017

To cancel particular concurrent program

update apps.fnd_concurrent_requests set phase_code = 'X', status_code = 'C' where request_id in (select REQUEST_ID from apps.fnd_concurrent_worker_requests where phase_code='P' and hold_flag!='Y' and USER_CONCURRENT_PROGRAM_NAME='&a' and requested_start_date<=sysdate and concurrent_queue_name<> 'FNDCRM' and concurrent_queue_name<> 'GEMSPS');

Monday, April 17, 2017

ORAchk - Health Checks for the Oracle Stack

RUN ORACHK
1.     Download the ORAchk: The latest version of ORAchk is attached to Note 1268927.1 and can be downloaded there.
2.     Unzip orachk.zip kit $ unzip orachk.zip
3.     Give to permission $ chmod 755 orachk
4.     You can execute with following commands ...

$ ./orachk (for regular healtcheck as well as HA best practices)
$ ./orachk-c hacheck (Only HA best practices)
·         After finish, the following (or similar) will be displayed:

Detailed report (html) - /home/oracle/orachk/orachk_ratlnx01_120211_114104/orachk_ratlnx01_120211_114104.html
UPLOAD(if required) - /home/oracle/orachk/orachk_ratlnx01_120211_114104.zip
5.     At this point you may view the HTML output in the file shown in the output above. 

USAGE
  •         ORAchk is scanning the configuration looking for problems. It can provide a report showing health risks.
  •         It is recommended that the database administrator run a report before and after software configuration changes to understand if those changes can cause future problems.