Tuesday, December 20, 2016

ORA-12801: error signaled in parallel query server , ORA-27090: Unable to reserve kernel resources

ORA-12801: error signaled in parallel query server , ORA-27090: Unable to reserve kernel resources


ORA-12801: error signaled in parallel query server , ORA-27090: Unable to reserve kernel resources

Problem:
-------------------
 
ORA-12801: error signaled in parallel query server P006, instance
mdvreddy.markmotels.com:mdvreddy (1)
ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O and hence change

Solution:
------------------

Run as root:

modify  fs.aio-max-nr value to  3145728 in /etc/sysctl.conf


 

###fs.aio-max-nr = 1048576
fs.aio-max-nr= 3145728

Save the file and Run to make the changes permanent

 sysctl -p



Read the actual parameters with sysctl.

 
sysctl -a

Sunday, December 18, 2016

OHS not starting after giving opmnctl startall with error make_sock: could not bind to address xx.x.x:80

OHS not starting after giving opmnctl startall  with error make_sock: could not bind to address xx.x.x:80


[2016-12-19T05:51:07.1888+00:00] [OHS] [INCIDENT_ERROR:32] [OHS-9999] [core.c] [host_id: mdvreddy.markmotels.com] [host_addr: xx.100.100.100] [pid: 8256] [tid: 140065001035584] [user: mdvreddy] [VirtualHost: main] (13)Permission denied:  make_sock: could not bind to address xx.100.100.100:80

[2016-12-19T05:51:07.1888+00:00] [OHS] [INCIDENT_ERROR:20] [OHS-9999] [core.c] [host_id: mdvreddy.markmotels.com] [host_addr: xx.100.100.100] [pid: 8256] [tid: 140065001035584] [user: mdvreddy] [VirtualHost: main]  no listening sockets available, shutting down

[2016-12-19T05:51:07.1888+00:00] [OHS] [ERROR:32] [OHS-9999] [core.c] [host_id: mdvreddy.markmotels.com] [host_addr: xx.100.100.100] [pid: 8256] [tid: 140065001035584] [user: mdvreddy] [VirtualHost: main]  Unable to open logs

[2016-12-19T05:51:09.0676+00:00] [OHS] [INCIDENT_ERROR:32] [OHS-9999] [core.c] [host_id: mdvreddy.markmotels.com] [host_addr: xx.100.100.100] [pid: 8263] [tid: 140187892651840] [user: mdvreddy] [VirtualHost: main] (13)Permission denied:  make_sock: could not bind to address xx.100.100.100:80

[2016-12-19T05:51:09.0676+00:00] [OHS] [INCIDENT_ERROR:20] [OHS-9999] [core.c] [host_id: mdvreddy.markmotels.com] [host_addr: xx.100.100.100] [pid: 8263] [tid: 140187892651840] [user: mdvreddy] [VirtualHost: main]  no listening sockets available, shutting down

[2016-12-19T05:51:09.0676+00:00] [OHS] [ERROR:32] [OHS-9999] [core.c] [host_id: mdvreddy.markmotels.com] [host_addr: xx.100.100.100] [pid: 8263] [tid: 140187892651840] [user: mdvreddy] [VirtualHost: main]  Unable to open logs


cd  /u01//Oracle_WT1/ohs/bin 

Change permissions of .apachectl from


-rwxr-x---. 1 mdvrd mdvrd  12315 Feb  8  2013 .apachectl
[mdvrd@mdvreddy.markmotels.com bin]$

execute the following command to the required changes

su root 
chown root .apachectl
chmod 6750 .apachectl
su mdvreddy
to

-rwsr-s---.  1 root      mdvreddy  13278 Feb  8  2013 .apachectl

cd /u01//Oracle_WT1/instance/instance1/bin  ./opmnctl stopall
 ./opmnctl startall
 ./opmnctl status

 I see it is working..


 for other problems, there is a very good blog on similar issues
 https://sreejithsna.wordpress.com/2015/09/04/ohs-not-starting-after-giving-opmnctl-startall/

Thursday, October 13, 2016

Create password verification function in oracle 12c databases

Create password verification function in oracle 12c databases:

Password verification function can be created both in CDB and in PDB level. They are independent from one another.

Here it is the demo.


@?/rdbms/admin/utlpwdmg.sql
Function created.
Grant succeeded.
Function created.
Grant succeeded.
Function created.
Grant succeeded.

Profile altered.

select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';

LIMIT
--------------------------------------------------------------------------------
ORA12C_VERIFY_FUNCTION

alter session set container=PRDBORCL1;

Session altered.

select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';

LIMIT
--------------------------------------------------------------------------------
NULL


@?/rdbms/admin/utlpwdmg.sql

Function created.

Grant succeeded.
Function created. 
Grant succeeded. 
Function created. 
Grant succeeded. 
Profile altered. 


select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';
LIMIT
--------------------------------------------------------------------------------
ORA12C_VERIFY_FUNCTION

-- CDB level
alter profile default limit password_verify_function null;

Profile altered.

select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';
LIMIT
--------------------------------------------------------------------------------

NULL

--- PDB level

alter session set container=PRDBORCL1;

Session altered.

select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';

LIMIT

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

ORA12C_VERIFY_FUNCTION

Disable Password Policy in Oracle Database 12c and solution to ORA-28003: password verification for the specified password failed

Disable Password Policy in Oracle Database  12c and solution to ORA-28003: password verification for the specified password failed


-- Problem:

ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8

--Solution:


---Check if PASSWORD_VERIFY_FUNCTION is used:

SQL> select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';

LIMIT
--------------------------------------------------------------------------------
ORA12C_VERIFY_FUNCTION


--It is enabled .


--Turn off verification:

alter profile default limit password_verify_function null;
Profile altered.

SQL>  select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';


LIMIT
--------------------------------------------------------------------------------
NULL




--Turn on verification:


SQL> alter profile default limit password_verify_function ORA12C_VERIFY_FUNCTION;

Profile altered.

SQL>  select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';

LIMIT
--------------------------------------------------------------------------------
ORA12C_VERIFY_FUNCTION

Monday, October 10, 2016

What is patchset , patch set update and one off patch:

What is patchset , patch set update and one off patch:

Patch set:
Patch set consist of bug fixes for known issues and plus some new futures, we use OUI to install patch set, it is known as upgrade to the next level. It changes the db release number i.e. 4th digit number.

PSU Patch set update: Quarterly releases, contains fixes for known critical issues for the patch set. It is installed with using Opatch. PSUs are cumulative.

One of patch:

One of patch is a ptch which addresses a particular single bug. It is usually applied with opatch. We install one off patch only when we hit a oracle bug.

DBPITR recovery issues with RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed start until time

-- Please note correct set of backup is must for recovery.. no backup means no recovery..


 list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1452601228       PARENT  1          07-07-2014 05:38:47
2       2       ORCL     1452601228       PARENT  1594143    09-10-2016 22:34:55
3       3       ORCL     1452601228       CURRENT 1755910    10-10-2016 00:01:34

RMAN> reset database to incarnation 2;

database reset to incarnation 2


list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 ORCL 1452601228 PARENT 1 07-07-2014 05:38:47 2 2 ORCL 1452601228 CURRENT 1594143 09-10-2016 22:34:55 3 3 ORCL 1452601228 ORPHAN 1755910 10-10-2016 00:01:34 RUN { SHUTDOWN IMMEDIATE; startup mount; RESTORE DATABASE; RECOVER DATABASE UNTIL TIME "TO_DATE('09-10-2016 23:40:00','DD-MM-YYYY HH24:MI:SS')" ; alter database open resetlogs; alter pluggable database all open; } ----Problem:
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 10/10/2016 09:13:27 ORA-00283: recovery session canceled due to errors RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed  start until time '2016/10/09 23:40:00' using backup controlfile ORA-00283: recovery session canceled due to errors ORA-19912: cannot recover to target incarnation 2 RMAN> RMAN> -----Solution: list backup of controlfile;  ls -ltr /u01/app/oracle/fast_recovery_area/ORCL/autobackup_bkp/2016_10_09/o1_mf_s_924824392_czpfxrrv_.bkp -rw-r----- 1 oracle dba 18055168 Oct  9 23:39 /u01/app/oracle/fast_recovery_area/ORCL/autobackup_bkp/2016_10_09/o1_mf_s_924824392_czpfxrrv_.bkp ---Check the nearest control file  that we need to restore it first and restore db. restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/autobackup_bkp/2016_10_09/o1_mf_s_924824392_czpfxrrv_.bkp'; alter database mount; RESTORE DATABASE; RECOVER DATABASE UNTIL TIME "TO_DATE('09-10-2016 23:40:00','DD-MM-YYYY HH24:MI:SS')" ; alter database open resetlogs; alter pluggable database all open;

Friday, October 7, 2016

Backup and restore of pluggable databses, CDB, no cdb and DBPITR in oracle 12c

Backup and restore of pluggable databses, CDB in oracle 12c

Note: Please ensure required backup is available , it is mandatory when working on backup and recovery... No backup mean no recovery, as simple as that.


run

{

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

backup current controlfile spfile;

BACKUP   AS COMPRESSED BACKUPSET   DATABASE PLUS ARCHIVELOG  DELETE ALL INPUT format '/tmp/madhav/%d_inc0_%T_%U.bak';

}









select to_char(sysdate,'hh24:mi dd-mm-yyyy'), current_scn from v$database;







TO_CHAR(SYSDATE, CURRENT_SCN

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

08:44 07-10-2016    72629675



--- Point In Time Recovery (PITR) of a Pluggable Database (PDB) in oracle 12c



run

 {

 alter pluggable database pdborcl close immediate;

 restore pluggable database pdborcl;

 recover pluggable database pdborcl until time "to_date('08:44 07-10-2016','hh24:mi dd-mm-yyyy')"  auxiliary destination '/tmp';

 alter pluggable database pdborcl open resetlogs;

 }



---  Normal recovery of Pluggable Database (PDB) in oracle 12c



run

 {

 alter pluggable database pdborcl close immediate;

 restore pluggable database pdborcl;

 recover pluggable database pdborcl;

 alter pluggable database pdborcl open;

 }









--- Normal or Full restore, recovery of  of oracle 12c CDB and all of its PDBs

-- open cdb in mount status



run

 {

 restore database;

 recover database;

 alter database open;

 alter pluggable database all open ;

 }

--- Restore adn Recover of CDB in oracle 12c -- cdb levle

RUN 
{
alter pluggable database all close immediate;
SHUTDOWN IMMEDIATE; 
startup mount;
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME "TO_DATE('09-10-2016 23:40:00','DD-MM-YYYY HH24:MI:SS')" ;
alter database open resetlogs;
alter pluggable database all open;
}
---- Restore and Recover of NOCDB in oracle 12c
Note: Tested with multiple database open resetlogs; hence reset incarnation had to be done.

bash-4.2$ rman target sys/manager

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Oct 9 20:49:45 2016

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


connected to target database: BANUDB (DBID=881160953, not open)

RMAN>
RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BANUDB   881160953        PARENT  1          07-07-2014 05:38:47
2       2       BANUDB   881160953        PARENT  1594143    19-11-2015 03:18:19
3       3       BANUDB   881160953        PARENT  22187971   09-10-2016 03:20:09
4       4       BANUDB   881160953        CURRENT 22189494   09-10-2016 04:11:10

RMAN> reset database to incarnation 3;

database reset to incarnation 3

RMAN>


RMAN> RUN
{
SHUTDOWN IMMEDIATE;
startup mount;
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME "TO_DATE('09-10-2016 03:30:00','DD-MM-YYYY HH24:MI:SS')" ;
ALTER DATABASE OPEN read only;
}



List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BANUDB   881160953        PARENT  1          07-07-2014 05:38:47
2       2       BANUDB   881160953        PARENT  1594143    19-11-2015 03:18:19
3       3       BANUDB   881160953        CURRENT 22187971   09-10-2016 03:20:09
4       4       BANUDB   881160953        ORPHAN  22189494   09-10-2016 04:11:10



Tablespace Point-in-Time Recovery (TSPITR) with RMAN in oracle 12c database with mininum tablespaces backup

 Tablespace Point-in-Time Recovery (TSPITR) with RMAN in oracle 12c database with mininum tablespaces backup:




run
{
allocate channel c1 type disk format '/tmp/madhav/madhav_%U';
configure controlfile autobackup on;
CONFIGURE BACKUP OPTIMIZATION off;
backup current controlfile spfile;
BACKUP TABLESPACE SYSTEM, SYSAUX, UNDOTBS1, MADHAV;
}



SQL> select * From madhav.test1;

         A          B
---------- ----------
         1          2
         2          3


select to_char(sysdate,'hh24:mi dd-mm-yyyy'), current_scn from v$database;


TO_CHAR(SYSDATE, CURRENT_SCN
---------------- -----------
04:10 07-10-2016    22052194




drop table madhav.test1;


select * from madhav.test1;
ORA-00942: table or view does not exist



run
{
SQL 'ALTER TABLESPACE madhav OFFLINE IMMEDIATE';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT  '/tmp/madhav/madhav_%U';
recover tablespace madhav until time "to_date('04:10 07-10-2016 ','hh24:mi dd-mm-yyyy')" auxiliary destination '/tmp/';
SQL "ALTER TABLESPACE madhav ONLINE";
}



RMAN> select * From madhav.test1;

         A          B
---------- ----------
         1          2
         2          3

TABLE RECOVERY AND TABLE PARTITIONS RECOVERY IN DATABASE 12C USING RMAN

TABLE RECOVERY AND TABLE PARTITIONS  RECOVERY IN DATABASE 12C USING RMAN



rman target sys/manager





run

{

allocate channel c1 type disk format '/tmp/madhav/madhav_%U';

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

BACKUP AS BACKUPSET DATABASE ;

}



 select current_scn from v$database;



CURRENT_SCN

-----------

   22045558



 create table madhav.test1 (a number, b number);

 insert into  madhav.test1 values (1,2);

 insert into  madhav.test1 values (2,3);

 commit;

select *from madhav.test1;





         A          B

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

         1          2

         2          3




  

 select current_scn from v$database;



using target database control file instead of recovery catalog

CURRENT_SCN

-----------

   22045726





  

 list backup;



 sqlplus madhav/madhav

 drop table madhav.test1;



select * From test1

              *

ERROR at line 1:

ORA-00942: table or view does not exist







 

  rman target sys/manager





 recover table madhav.test1 until scn 22045726    auxiliary destination '/tmp';





 EXIT





   sqlplus madhav/madhav





SQL> select *from madhav.test1;



         A          B

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

         1          2

         2          3

 
Recover table partitions :

RECOVER TABLE MADHAV.TBL:TBL_PART98, MADHAV.TBL:TBL_PART99
    UNTIL SEQUENCE 22045726    AUXILIARY DESTINATION '/tmp'
    REMAP TABLE 'MADHAV'.'TBL':'TBL_PART98':'BKP_TBL_PART98',
                'MADHAV'.'TBL':'TBL_PART99':'BKP_TBL_PART99' 
    REMAP TABLESPACE 'TBL_TS':'TBL_BKP_TS';

Tablespace Point-in-Time Recovery(TSPIR) in Oracle 12c non-cdb

Tablespace Point-in-Time Recovery(TSPIR) in Oracle 12c non-cdb

 drop tablespace madhav including contents and datafiles;

 drop user madhav cascade;

 create tablespace madhav datafile '/oracle/app/oracle/oradata/madhav/madhav.dbf' size 10M autoextend on maxsize unlimited ;

 create user madhav identified by madhav default tablespace madhav;



 alter user madhav quota unlimited on madhav;

 alter user madhav temporary tablespace temp;

 grant connect, resource to madhav;

 grant connect, resource to madhav;

 conn madhav/madhav

sho user;

 create table test1 (a number, b number);

 insert into test1 values (1,2);

 insert into test1 values (2,3);

 commit;

select *from test1;



         A          B

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

         1          2

         2          3





select to_char(sysdate,'hh24:mi dd-mm-yyyy') from dual;



TO_CHAR(SYSDATE,

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

01:33 07-10-2016




rman target sys/manager



run

{

allocate channel c1 type disk format '/tmp/madhav/madhav_%U';

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

}





select to_char(sysdate,'hh24:mi dd-mm-yyyy') from dual;



RMAN>

TO_CHAR(SYSDATE,

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

01:35 07-10-2016







 truncate table madhav.test1;

 select *From madhav.test1;


no rows selected










run

{

SQL 'ALTER TABLESPACE madhav OFFLINE IMMEDIATE';

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT  '/tmp/madhav/madhav_%U';

recover tablespace madhav until time "to_date('01:34 07-10-2016','hh24:mi dd-mm-yyyy')" auxiliary destination '/tmp/';

SQL "ALTER TABLESPACE madhav ONLINE";

}





RMAN> select *From madhav.test1;



         A          B

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

         1          2

         2          3




Thursday, September 1, 2016

How to Create a RAM disk in Linux

How to Create a RAM disk in Linux



as root user
   
mkdir /mnt/ramdisk
mount -t tmpfs -o size=1024m tmpfs /mnt/ramdisk

Please note the 1024M is the memory from physical memory, ensure the memory we assign for creating the ramdisk must be free. from the physical memory i.e. RAM, we are taking and creating the ramdisks.


Enter the following entry into the fstab file
vi /etc/fstab

tmpfs       /mnt/ramdisk tmpfs   nodev,nosuid,noexec,nodiratime,size=1024M   0 0

df -h

It will show the rman disks..

start using rman disks..

It is not recommended storage for any type of files and must not be used unless it is test environment .  A big no for important db. The data that get stored in ramdisks get erased when systems get reboot or for any other issues.

It is purely for testing not for real use. Take your own call..

Wednesday, March 30, 2016

Unable to start Oracle Goldengate JAGENT process in Windows Server 2012 R2 Standard

Unable to start Oracle Goldengate JAGENT process in Windows Server 2012 R2 Standard


Unable to start JAGENT in Windows Server 2012 R2 Standard

Problem:

2016-0x-x0 04:57:34  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (rimmer): start jagent.
2016-0x-x0 04:57:34  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, MGR.prm:  Command received from GGSCI on host [FE80::XXXX:YYYYY:AEAA:14CFD3]:58034 (START GGCMD JAGENT).
2016-0x-x0 04:57:34  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, MGR.prm:  GGCMD JAGENT starting.
2016-0x-x0 04:57:34  INFO    OGG-00978  Oracle GoldenGate Command Tool for Oracle, JAGENT.prm:  GGCMD JAGENT is running.
2016-0x-x0 04:57:34  INFO    OGG-01927  Oracle GoldenGate Command Tool for Oracle, JAGENT.prm:  Child process started, process ID 9044, command line 'java -Dlog4j.configuration=file:./cfg/log4j.properties -jar -Xms64m -Xmx512m dirjar/jagent.jar'.
2016-03-30 04:57:35  INFO    OGG-00979  Oracle GoldenGate Command Tool for Oracle, JAGENT.prm:  GGCMD JAGENT is down (gracefully).

Solution:

cd C:\goldengate_121210\dirprm
Take a backup of jagent.prm

Open jagent.prm

replace java  with with complete JAVA_HOME path and java command something like

java --> C:\jdk1.7.0_75\bin\java

then Start the jagent

All gone well in my case






Thursday, March 24, 2016

Database Admin Interview Questions and Answers

The following Database Admin Interview Questions and Answers are just glimpse  of  what me may get face, however we have to understand what these are actually do internally, go in depth and learn more and not just these..

The most important thing is that interviewer at times can easily find out whether the answers given are the answers one can find in google search or answers from experience. As everyone know we may not experience each and concept in execution however we must add our experience wherever we can and learn concept in depth not just one or few lines answers, able to explain it in detail.


--- Oracle GoldenGate  --------

1. what is tranlog? trandata?

TRANLOG specifies the transaction log as the data source.
trandata command adds database schema level supplemental logging. This is necessary for all the transactional changes to be captured and written to the redo logs.
 The command must follow a successful DBLOGIN.

2. classic mode and integrated mode?


3. RBA
 Relative Byte Address (RBA).
 the RBA is mostly a marker within the trail file to identify the location of the transaction.
  Oracle is using the RBA to help calculate the amount of data being extracted
  RBA to “estimate” the amount of data being applied by a replicat.

4. How do I see ports used by OGG ex,rp processes
 send manager childstatus debug

5. what is cleanup  replicat, cleanup extract
cleanup replicat * , save 100
cleanup extract * , save 100

6. how can I see info of trail files (size, path, RBA)
info exttrail *

7. what is dblogin?

8. how can we encrypt passwords?

9. how to cleanup check point table?.. can we have checkpoint table in integrated mode?

10. How can I see ggserr.log from GGSCI command prompt?
 view ggsevt

 ============================== Database Admin =========================

 1.  How many scan listener in 8 node rac
 By default it is 3.

GNS based dynamic IP scheme still has no way to change number of SCAN listeners in rac environment.
 We can add extra scan listeners in DNS based static IP configuration.

 We can use nslookup to find IP addresses and DNS details.


2.  how do you solve 4031 errors

3.  how do you solve 600 errors

4.  How to check if node is alive or not from anohter node
 crsctl stat res -t
 crsctl status resource -t
 srvctl status nodeapps -n <>
 or
 ping
 or telnet

5. How to fix node eviction issues and problems you faced in the last six months ?

6. Tell me the top 5 RAC events you fixed in last six months, how did you fix and the RAC events you usually see more often

7. How do you reduce the size of 1 T bye LOG segment storage?

8. How do you fix a query problem that is running t0o slow when nothing changed in the db.

9.
Q: Number of table spaces created when creating db with dbca with default configuration?
A: Five

SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP

10.
Q:What is High Water Mark (HWM)?
A: Oracle uses the high water mark to identify the highest amount of space used by a particular segment. It acts as the boundary between used and unused space.

11.
Q: Oracle 11gR2 RAC architecture and Back ground processes?
The GCS, GES and GRD processes collaborate to enable Cache Fusion,
In addition to the normal database background processes , we also see the following bg processes

ACMS - per instance
GTX0
LMON
LMD
LMS
LCK0
RMSn
RSMN


12. How to find committed rows and uncommited rows?

13. Instance recovery and crash recovery, explain what happens in the database while these are happening?

Crash recovery is used to recover from a failures of either a single-instance database crashes or all instances of an RAC database crashes
Subsequent startup will conduct crash recovery

power outage, shutdown abort results instance recovery

Crash recovery is the term used when we have a cluster crashed where we have multi instance configuration.

Both recoveries are done by oracle automatically.


14. You are working on a Node , connecting to a specific rac db instance, when someone switched off that particular node, what happens to the current ongoing DMLs, what happens next?

15. Which protocol used for private inter connect i.e private network ?

user datagram protocol (UDP)
UDP is the default interface protocol for Oracle RAC and Oracle Clusterware.


16. How UDP is different from TCP/IP?

With UDP, packet transfer is considered complete, as soon as packet is sent and error handling is done by Oracle code itself.

With TCP/IP, packet transfer has some extent of overhead, connection setup, packet sent,  must wait for TCP Acknowledgement before considering the packet send as complete.


17. what is Multipath Disks in oracle cluster or oracle RAC?

18.  What are the mandatory back ground processes?
A: I see there are 7 mandatory oracle background processes.

1. Database writer-DBWn
2. Log writer -LGWR
3. Check point -CKPT
4. System monitor -SMON
5. Procesess Monitor- PMON
6. Recover process -RECO
7. Manageability Monitor Processes (MMON and MMNL)

19. What is HAIP? explain about it.

20. what are nodeapps in oracle RAC?
Nodeapps are set of Oracle application services used for RAC environments, such as the Net Listener,Virtual IP's ,Global Services Deamon and Oracle Notification Services.
Those services are started automagically on each RAC node as a node application and can be relocated from one node to another using the VIP's during the failover.

21. How NFS is different from SAN. which one you prefer?

SAN (Storage Area Networks) - generally using fibre to connect to the SAN
NAS (Network Attached Storage) - generally using a network to connect to the NAS using either NFS, ISCSI

add some others.. answer is not sufficient.

22. Explain the top five events you see in the AWR in both RAC and single instance?

the more explanation is good here.. 
pay attention to the RAC wait events..  example: GC CR request, GB buffer busy.. add few more 



23.  What is library cache , how do you check library contention?

It stores parsed or compiled  form of recently referenced SQL and PL/SQL code, java classes.
Its size get increases, decreases automaticallly

Shared pool should be size properly, as library cache is part of shared pool.

read database performance guide , chapter 14 for more info.

24. How do you fix user sessions slowness in oracle database?

25. Explain two RAC complex issues you solved in your career?

Though we do not like this Q, the Q give us an opportunity to explain our level of expertise, 

keep a note of your RAC db problems and fixed you do on regular base, this helps you when you face this Q.

26. 

learn more about other processes, optional , RAC specific, ASM specific, many more..

Sunday, February 7, 2016

REDO LOG SIZES FOR ORACLE ENTERPRISE MANAGER CLOUD CONTROL 13C




REDO LOG SIZES FOR ORACLE ENTERPRISE MANAGER CLOUD CONTROL 13C


There are two types of installations

1.     Simple Installation
2.     Advanced Installation


When we select Advanced Installation, we have an option to choose whether our installation can be small or medium  or large size  installation types

we can check the size of redo log files using


select min(bytes)/(1024*1024) size_in_mb from v$log;

the oracle recommended sizes for the redo logs is as follows

For simple installation, the redo log files size should be 300 MB or greater

 300 MB size for each log file member  for small
 600 MB size or greater for Medium
 1000 MB size or greater for Large 

Wednesday, February 3, 2016

How to create soft link with ln command in Linux or UNIX


How to create soft link with ln command in Linux or UNIX



Syntax:
ln -s Actual-filename-completepath  soft link-filename

Create:
cd desired-directory
ln -s /u01/oracle/product/12.1.0.2.0/dbhome_1/lib/libnnz12.so libnnz12.so
 

View soft link files:
cd desired-directory
ls -lrt | grep "^l"
 

Delete soft link:
cd desired-directory
unlink
or
rm

GoldenGate : ggsci: error while loading shared libraries in 12.1.2.1.1

GoldenGate : ggsci: error while loading shared libraries

Problem:
---------

./ggsci
./ggsci: error while loading shared libraries: libnnz12.so: cannot open shared object file: No such file or directory


Solutions:

cd OGG_HOME

 ln -s /u01/oracle/product/12.1.0.2.0/dbhome_1/lib/libnnz12.so libnnz12.so
 ln -s /u01/oracle/product/12.1.0.2.0/dbhome_1/lib/libclntsh.so libclntsh.so
 ln -s /u01/oracle/product/12.1.0.2.0/dbhome_1/lib/libons.so libons.so
 ln -s /u01/oracle/product/12.1.0.2.0/dbhome_1/lib/libclntshcore.so libclntshcore.so
 ln -s /u01/oracle/product/12.1.0.2.0/dbhome_1/lib/libclntshcore.a libclntshcore.a
      

Tuesday, February 2, 2016

ORACLE ENTERPRISE MANAGER CLOUD CONTROL 13C INITIALIZATION PARAMETERS SETTINGS


ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES=FALSE SCOPE=BOTH;

ALTER SYSTEM SET PROCESSES=1000 SCOPE=SPFILE;

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=4G SCOPE=BOTH;

ALTER SYSTEM SET SESSION_CACHED_CURSORS=500 SCOPE=SPFILE;

ALTER SYSTEM SET SGA_MAX_SIZE=10G SCOPE=SPFILE;

ALTER SYSTEM SET SGA_TARGET=10G SCOPE=SPFILE;

ALTER SYSTEM SET SHARED_POOL_SIZE=600M SCOPE=BOTH;

ALTER DATABASE ADD LOGFILE GROUP 16  '../../../../orcl/redo16.log' SIZE  600M

ALTER DATABASE ADD LOGFILE GROUP 17  '../../../../orcl/redo17.log' SIZE  600M

ALTER DATABASE ADD LOGFILE GROUP 18  '../../../../orcl/redo18.log' SIZE  600M

ALTER DATABASE ADD LOGFILE GROUP 19  '../../../../orcl/redo19.log' SIZE  600M

ALTER DATABASE ADD LOGFILE GROUP 20  '../../../../orcl/redo20.log' SIZE  600M

ALTER DATABASE ADD LOGFILE GROUP 21  '../../../../orcl/redo21.log' SIZE  600M

ALTER DATABASE ADD LOGFILE GROUP 22  '../../../../orcl/redo22.log' SIZE  600M

ALTER DATABASE ADD LOGFILE GROUP 23  '../../../../orcl/redo23.log' SIZE  600M

ALTER DATABASE ADD LOGFILE GROUP 24  '../../../../orcl/redo24.log' SIZE  600M

ALTER DATABASE ADD LOGFILE GROUP 25  '../../../../orcl/redo25.log' SIZE  600M

ALTER DATABASE DROP LOGFILE GROUP 1

ALTER DATABASE DROP LOGFILE GROUP 2

ALTER DATABASE DROP LOGFILE GROUP 3
 

alter system register;

alter system checkpoint;

alter system switch logfile;

alter pluggable database all close immediate;

shut immediate ;

startup;

alter pluggable database all open;

alter system register;

alter system switch logfile; 

Friday, January 29, 2016

Error Initializing JAgent. JAgent will not be initialized and about to return in AIX

Error Initializing JAgent. JAgent will not be initialized and about to return in AIX 


Problem:

2016-01-xx 03:02:59 [main] INFO  JAgentWSMain - About to call initialize on the WebService

2016-01-xx 03:02:59 [main] INFO  JAgentWSMain - Loading agent-spring-ws.xml ###############

2016-01-xx 03:03:00 [main] INFO  AgentInfoImpl - Using IP address 00.00.00.00 to connect to the Manager Web Service

2016-01-xx 03:03:00 [main] INFO  AgentInfoImpl - Reading Manager port 8830 from mgr.prm file.

2016-01-xx 03:03:01 [main] INFO  AgentInfoImpl - OEM Enabled ######

2016-01-xx 03:03:01 [main] INFO  JAgentWSMain - Error Initializing JAgent. JAgent will not be initialized and about to return.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'agentBootstrapper': Injection of resource dependencies failed; nested exception is java.lang.NoClassDefFoundError: javax.xml.bind.JAXBException

        at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessPropertyValues(CommonAnnotationBeanPostProcessor.java:297)


Solution:

cd OGG_HOME

echo $JAVA_HOME

export JAVA_HOME=/usr/java/java71_64

export PATH=$JAVA_HOME/bin:$PATH

./ggsci



info all

stop extract *

stop replicat *

stop jagent

stop mgr

start mgr

start jagent

start extract *

start replicat *
 
info all



Program     Status      Group       Lag at Chkpt  Time Since Chkpt



MANAGER     RUNNING

JAGENT      RUNNING

 

Thursday, January 28, 2016

oracle 13c oem is availbable for download

ORACLE 13c oem is availbable for download:


Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0) 


http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html



Saturday, January 23, 2016

Installing Java8 on AIX

Installing Java8 on AIX


Download Java8 from IBM site

 Java8_64.jre.8.0.0.10.tar.gz
 Java8_64.sdk.8.0.0.10.tar.gz

 Move these folders to /u01/java8/


 gunzip < Java8_64.jre.8.0.0.10.tar.gz | tar xvf -
 gunzip < Java8_64.sdk.8.0.0.10.tar.gz | tar xvf -

login as root user

 run
 smitty installpha
  or
 smitty 



Enter the directory path where software is extract
/u01/java8/
Enter
In the next screen
Select
Accept new license agreements
Press Enter
To make from No To Yes , Press TAB on keyboard

Enter to start the Java8 installation

Check the installation Summary on the screen.
Press F10 if all is success and to exit from the installation screen.

Thursday, January 21, 2016

OGG-01091 Unable to open file MGR.rpt in AIX



OGG-01091 Unable to open file MGR.rpt in AIX - Oracle Golden gate Installation discrepancies between major operating systems

By default, manager get started when you select start manager check box during the installation process, however we see this is not the case in AIX operating system

In our case we see manager process did not come up started when installation is done successful


$ ./ggsci



Oracle GoldenGate Command Interpreter for Oracle

Version 12.1.2.1.1 .............

AIX .........., ...........

Operating system character set identified as ..............



Copyright (C) ............... All rights reserved.







GGSCI (hostname) 1> info all



Program     Status      Group       Lag at Chkpt  Time Since Chkpt



MANAGER     STOPPED





GGSCI (hostname) 2> start mgr

Manager started.



GGSCI (hostname) 3>

Source Context :

  SourceModule            : [ggstd.util.file]

  SourceID                : [/../../../../../../gglib/ggstd/fileutl.c]

  SourceFunction          : [ggOpenFile(const char *, const char *)]

  SourceLine              : [772]



201*-**-** 11:01:54  ERROR   OGG-01091  Unable to open file "/../../../../../../../dirrpt/MGR.rpt" (error 2, No such file or directory).



201*-**-** 11:01:54  ERROR   OGG-01668  PROCESS ABENDING.



GGSCI (hostname) 3>



GGSCI (hostname) 4> create subdirs



Creating subdirectories under current directory /../../../../../../



Parameter files                /../../../../../../: already exists

Report files                   /../../../../../../: created

Checkpoint files               /../../../../../../: created

Process status files           /../../../../../../: created

SQL script files               /../../../../../../: created

Database definitions files     /../../../../../../: created

Extract data files             /../../../../../../: created

Temporary files                /../../../../../../: created

Credential store files         /../../../../../../: created

Masterkey wallet files         /../../../../../../: created

Dump files                     /../../../../../../: created





GGSCI (hostname) 6> start mgr

Manager started.

all is well thereafter...


Wednesday, January 20, 2016

Error in invoking target 'ntcontab.o' of makefile in OBIEE 11.1.1.9.0 Installation

Error in invoking target 'ntcontab.o' of makefile  in OBIEE 11.1.1.9.0 Installation

Error:
Exception Name: MakefileException
Exception String: Error in invoking target 'ntcontab.o' of makefile '/../../../../../network/lib/ins_net_client.mk'.




Solution:

df -g /tmp/

df -h /tmp/

check the directories that are taking huge disk space  using

cd /tmp/

du -ag  $PWD |sort -n -r|head



check if we have atleast 2GB free space, I made 4 GB space available.. then installation was successfull..


Thursday, January 14, 2016

How to check if database is exadata


select count(*) from (select distinct cell_name from gv$cell_state);
returns a number > 0, then the database runs on Exadata storage.


Non exadata:
-----------------

SQL> select count(*) from (select distinct cell_name from gv$cell_state);

  COUNT(*)
----------
         0

SQL> select distinct cell_name from gv$cell_state;

no rows selected

Wednesday, January 13, 2016

RA-31633: unable to create master table '' ORA-30511: invalid DDL operation in system triggers


PROBLEM:
-----------


expdp schemas=USERNAME directory=DUMP_DIR content=metadata_only dumpfile=USERNAME.dmp logfile=USERNAME.log userid=USERNAME/USERNAME



Export: Release 12.1.0.1.0 - Production on ..........



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



Connected to: Oracle Database 12c ......................

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

ORA-31626: job does not exist

ORA-31633: unable to create master table "USERNAME.SYS_EXPORT_SCHEMA_XX"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT", line 1031

ORA-30511: invalid DDL operation in system triggers

ORA-06512: at line 1288

ORA-30511: invalid DDL operation in system triggers







Solution:
--------------



SQL@USERNAME>Purge DBA_RECYCLEBIN;



DBA Recyclebin purged.



SQL@USERNAME>purge recyclebin;



Recyclebin purged.

If does not works Restart database and do the above steps again... All should be fine....



ORA-00060: deadlock detected while waiting for resource


Problem:
-------------
ORA-00060: deadlock detected while waiting for resource


Solution:
----------
Go to database trace folder /../../../trace/

cat alert_DBNAME.log |grep -i deadlock

ORA-00060: Deadlock detected. See Note at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /../../../../trace/SID_ora_XXX.trc

open one of the trace file and see what causing issues

see the "[Transaction Deadlock]" section to see if it is really an ORACLE errors

see the "Deadlock graph: " for any deadlocks

check for the Information , DML statements for the current (blocker) session and OTHER waiting sessions..

DBA do not have to do anything unless they are oracle errors, most of the cases i see , these are application issues. Design application and/or tune DMLs are the solutions.

Saturday, January 9, 2016

ORA-00039: error during periodic action ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Caused By: java.sql.SQLException: ORA-00039: error during periodic action ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

We see these errors frequently in oracle 12c databases,  PGA_AGGREGATE_LIMIT can be set manually or by default and its a limiting value on the aggregate PGA memory that instance can use max.
the sessions (running untunable DMLs) using the most memory will be terminated and the above said errors get reported.

An incident is a single occurrence of a problem, an incident is created for each problem occurrence

The sessions that experience ORA-04036:  errors will get terminated and these sessions current sql statements can be seen in trace files.


What is the fastest solution to this?

We either run one of the below alter to solve

alter system set pga_aggregate_limit=0 scope=both;

or

alter system set pga_aggregate_limit=10G scope=both;

Does it solves the problem permanently?  I dont think it will.. so what should we do to solve this problem . there are two ways either DML producers (Developers) tune their queries, if they say something is wrong with db  .. then as  DBA has to dig deep down to find the DML statements that causing issues...

here it is one of the method i use to find the queries that cause these issues..

We have a good tool known as ADRCI

login into adrci

adrci> show incidents

Find incidents that are causing 04036 issues

show incident -mode detail -p "incident_id=XXXX"

it shows the incident files with KEY INCIDENT_FILE, these are actually trace files..

Get the trace file names and open trace files to see causes.... the trace files shows more than required info.. session id, time, everything .. more importantly it shows  like

----- Current SQL Statement for this session (sql_id=xxxxxxxxxxxxxxxxx) -----
 we get not just SQL_ID but whole SQL statements that caused these issues... what else we want,.. let  developers know DML statements     and or  get these statements tuned..

 Hope it helps u all..

 Open for suggestions and comments.

Thursday, January 7, 2016

How to create service name in oracle database or Add service name to single instance database


Service name can be different from database name or instance name (also know as SID). Default service name is created with same as database name. Service name is like an alias that allow us to connect the db, it can be recorded in tnsnames.ora file or may not be recorded in tnsnames.ora file, it depends what connection method we use to connect.
we can create more than one service name for single database. The way we create service name in single instance db is different from RAC. the method below shown is for single instance db.

Let me explain why did we required to do the below...  Most people may wonder why we do have more than 8 character size service name or instance name. Oracle may not recommends but does not stop from creating instance_name with more than 8 character length string. We have a db with more than 8 character length and users want to connect db with 8 character length string with no db downtime, hence we did the below.. and it works......

In real production systems, we use service names as it allows to manage resources, workloads more effectively.
*real strings are changed*

We can also use dbms_service package .

connect sys as sysdba

show parameter service_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      MYPRODUCTIONDB

show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      MYPRODUC


SQL> alter system set service_names='MYPRODUCTIONDB,MYPRODUC' scope=both;

System altered.

SQL> alter system register;

System altered.

SQL> sho parameter service_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      MYPRODUCTIONDB,MYPRODUC


SQL> !tnsping MYPRODUCTIONDB

It works as it has entry in tnsnames.ora file

SQL> !tnsping MYPRODUC

TNS-03505: Failed to resolve name

--- It did not work as we have no entry for this in our tnsnames.ora file.

We still conntect db with new service name

SQL> CONNECT system/passwordscret@myhost.com:1521/MYPRODUC
Connected.
SQL> CONNECT system/passwordscret@//myhost.com/MYPRODUC
Connected.
SQL> CONNECT system/passwordscret@//myhost.com/MYPRODUC
Connected.
SQL>

The method we used to connect db is known as EZCONNECT or JDBC connection . EZCONNECT is very easy connect naming method and does not require any edits in tnsnames.ora file.

Monday, January 4, 2016

ORA-12518: TNS:listener could not hand off client connection



ORA-12518: TNS:listener could not hand off client connection



Listener stop, start .. it resolved the isseus.. if nothing helps ..try adding the below to listner. ora file.
DIRECT_HANDOFF_TTC_LISTENER=OFF

Listener stop, start .

connect to the db
alter system register;
sqlplus user/pwd@db


Ensure you have sufficent value is set for processes init parameter, change it to max lets say 1000 or 2000

Always ensure db is configured with right AMM  or properly configured SGA





Its been awhile posting a blog entry, I am back to blogging.. hopefully you all like my blogs and post your comments as always.