DOYENSYS Knowledge Portal




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




Wednesday, February 28, 2018

Adding disks online to existing diskgroups

STEPS:

1) Check the current ASM diskgroup size:
select name,total_mb,free_mb from v$asm_diskgroup;
2) Check the HEADER_STATUS shows as CANDIDATE or PROVISIONED for the ASM disks:
select group_number,disk_number,header_status,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,LABEL,PATH,VOTING_FILE from v$asm_disk order by GROUP_NUMBER,DISK_NUMBER;
3) Check the raw disk path and diskgroup name and status
col PATH format a10;
col name format a10;
SELECT MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,TOTAL_MB,
FREE_MB,NAME,PATH,LABEL FROM V$ASM_DISK;
4) Adding the disks to existing diskgroup online
ALTER DISKGROUP DG08 ADD DISK '/dev/oracleasm/disks/DG09' NAME DG09_0000;
5) Post check status of diskgroup and HEADER STATUS as 'MEMBER'
col PATH format a10;
col name format a10;
SELECT MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,TOTAL_MB,
FREE_MB,NAME,PATH,LABEL FROM V$ASM_DISK;
6) Check the diskgroup status and size in both nodes
asmcmd

ASMCMD> lsdg

Monday, February 19, 2018

ORA-27300: OS system dependent operation:semget failed with status: 28

Getting below error while startup after adding process parameter to 20000
 
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates

Solution:

SEMMNI should be increased to accomodate more semaphores.

1. Query the current semaphore values in the kernel
     # /sbin/sysctl -a | grep sem

2. Modify SEMMNI value in the /etc/sysctl.conf.

From
kernel.sem = 250 32000 100 128

To
kernel.sem = 250 32000 100 200

3. # /sbin/sysctl -p

Reference:

Database Startup Fails with ORA-27300: OS system dependent operation:semget failed with status: 28 (Doc ID 949468.1)

Monday, February 5, 2018

Install and Configure Oracle Application Express Apex 5.0.x with PL/SQL Gateway


1. Download Software as Zip file from OTN
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

2.Unzip apex_5.0_en.zip Software to ORACLE_HOME directory,it will create a directory called Apex

3. Login to SQLPLUS as SYSDBA in database where you will create the Apex instance. Create a tablespace called “apex” for the Apex 5 installation

$ cd $ORACLE_HOME/apex

SELECT dbms_xdb.gethttpport FROM dual;

EXEC DBMS_XDB.SETHTTPPORT(0);

4. — Full development environment. Run apexins.sql
@apexins.sql tablespace_apex tablespace_files temp_tablespace images

tablespace_apex is the name of the tablespace for the Oracle Application Express application user.

tablespace_files is the name of the tablespace for the Oracle Application Express files user.

temp_tablespace the name of the temporary tablespace or tablespace group.

images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/.

@apexins.sql apex apex temp /i/


@apxldimg.sql APEX_HOME

APEX_HOME is the directory you specified when unzipping the file, in our case it is the ORACLE_HOME directory

SQL> @apxldimg.sql  /home/app/oracle/product/11.2.0/dbhome_1

5. Run apxchpwd.sql to create Admin account and Reset the
password —
@apxchpwd.sql

Alter/Unlock the anonymous account



ALTER USER ANONYMOUS ACCOUNT UNLOCK;
ALTER USER XDB ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
ALTER USER FLOWS_FILES ACCOUNT UNLOCK;


—————————————————-
Configuring the PL/SQL Embedded Gateway
—————————————————-

6. Running the apex_epg_config.sql Configuration Script to the base directory where Apex software was unzipped. In our case it is the DB home directory

@apex_epg_config.sql /home/app/oracle/product/11.2.0/dbhome_1

7. Conifuringthe Oracle XML DB Protocol Server Port

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

EXEC DBMS_XDB.SETHTTPPORT(7888);

[orabssd1@wwibssdb301~]$ netstat -nlp |grep 7888

8. Enabling Network Services in Oracle Database 11g or Later

By default, the ability to interact with network services is disabled in Oracle Database 11g Release 1 or 2 or later. 
Therefore, if you are running Oracle Application Express with Oracle Database 11g Release 1 or 2 or later,
you must use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_050000 database user.  

Grant connect privileges to any host for the APEX_050000
database user

— for Apex 5 —


DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_050000
-- the "connect" privilege if APEX_050000 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_050000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_050000', TRUE, 'connect');
END IF;

EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_050000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;


— For Apex 4 —

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200
-- does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040200', TRUE, 'connect');
END IF;

EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;