statistics lock in oracle

To lock statistics for a tables:

exec dbms_stats.lock_table_stats('<schema_name>', '<tablename>');

Ex:

exec dbms_stats.lock_table_stats('scott', 'test');

when stats is locked the value of stattype_locked is ALL in dba_tab_statistics;

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null; 

To unlock table stats:

exec dbms_stats.unlock_table_stats('table_owner','table_name');

ex:

exec dbms_stats.unlock_table_stats('scott', 'test')

To lock statistics for a schema:

exec dbms_stats.lock_schema_stats('schema_owner'); 
exec dbms_stats.unlock_schema_stats('schema_owner'); 

goldengate 12.3 installation in linux

Download software from the link

unzip the file 123010_fbo_ggs_Linux_x64_shiphome

root@localhost opt]# unzip 123010_fbo_ggs_Linux_x64_shiphome

root@localhost opt]# su - oracle

[oracle@localhost Disk1]$ pwd

/opt/fbo_ggs_Linux_x64_shiphome/Disk1

[oracle@localhost Disk1]$ ls

install  response  runInstaller  stage

[oracle@localhost Disk1]$ ./runInstaller

[oracle@localhost ogg]$ ./ggsci

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

[oracle@localhost oracle]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/lib

[oracle@localhost lib]$ ls libnnz12.so

libnnz12.so

[oracle@localhost lib]$ cd /u01/app/oracle/ogg/

[oracle@localhost ogg]$ ln -s /u01/app/oracle/product/12.2.0/dbhome_1/lib/libnnz12.so .

[oracle@localhost ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13

Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (localhost.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

example for goldengate unidirectional replication through Datapump process

Steps for performing an Online change Synchronization through Datapump process

====================================================

Make sure to create a same table structure on SRC and TRGT and manager process must be running .

Sample Table structure:

===============

Create table sender.empl (empid number(10),empname varchar2(10),constraint emp_key primary key(empid));

Create table receiver.empl (empid number(10),empname varchar2(10),constraint emp_key primary key(empid));

Prerequisite:

========

 In SRC:

Create a directories  “ mkdir /u01/app/oracle/GG/dirdat/etrail/“

In TRGT:

                              “ mkdir /u01/app/oracle/GG/dirdat/ptrail/“  for trail file’s location.

 On SRC

1. GGSCI> add extract extdml, tranlog, Begin now

2. GGSCI> add exttrail /u01/app/oracle/GG/dirdat/etrail/et,extract extdml

     3.  GGSCI> add extract dpump, EXTTRAILSOURCE /u01/app/oracle/GG/dirdat/etrail/et

    4.   GGSCI> edit params extdml

                 extract extdml

                 userid gguser@SRC,password oracle

                 exttrail /u01/app/oracle/GG/dirdat/etrail/et

                 table sender.empl;

5.  GGSCI> add rmttrail /u01/app/oracle/GG/dirdat/ptrail/rt, extract dpump

6. GGSCI> edit params dpump

                      Extract dpump

                      userid gguser@SRC,password oracle

                      rmthost 192.168.162.13,mgrport 7810

                      rmttrail /u01/app/oracle/GG/dirdat/ptrail/rt

                      passthru

                      table sender.empl;

On TRGT

1.    GGSCI> add replicat repldml, exttrail /u01/app/oracle/ GG/dirdat/dptrail/rt,checkpointtable gguser.chkptab

2.    GGSCI> edit params repldml

                        replicat repldml

                        userid gguser@TRGT,password oracle

                        ASSUMETARGETDEFS

                        MAP sender.empl, TARGET receiver.empl;

    

    On SRC

1. GGSCI > start extract extdml

 2. GGSCI> start extract dpump

    

    On TRGT

     1.  GGSCI> start replicat repldml    

Do some insert and update on SRC table which should be reflected on TRGT automatically.

====================================================

Schema Replication through Datapump

Sample Table structure:

===============

Create table sender.empl (empid number(10),empname varchar2(10),constraint emp_key unique(empid));

Create table receiver.empl (empid number(10),empname varchar2(10),constraint emp_key unique(empid));

Create table sender.company (city varchar2(10),pincode number(10),constraint pin_key unique(pincode));

Create table receiver.company (city varchar2(10),pincode number(10),constraint pin_key unique(pincode));

Prerequisite:

========

Create a directories  “ mkdir /u01/app/oracle/GG/dirdat/schtrail/“  &

                          “mkdir /u01/app/oracle/GG/dirdat/schtrail/“ for trail file’s location.

On SRC

3. GGSCI> add extract extp_sch, tranlog, Begin now

4. GGSCI> add exttrail /u01/app/oracle/GG/dirdat/schtrail/lt,extract extp_sch

5.  GGSCI> add extract dp_sch, EXTTRAILSOURCE /u01/app/oracle/GG/dirdat/schtrail/lt

6. GGSCI> edit params extp_sch

                 extract extp_dml

                 userid gguser@SRC,password gguser

                 exttrail /u01/app/oracle/GG/dirdat/schtrail/lt

                 Table sender.*;

7. GGSCI> add rmttrail /u01/app/oracle/GG/dirdat/schtrail/rt,extract dp_sch

8. GGSCI> edit params dp_sch

                      extract dpump

                      userid gguser@SRC,password gguser

                      rmthost 192.168.162.13,mgrport 7810

                      rmttrail /u01/app/oracle/GG/dirdat/schtrail/rt

                      passthru

                      table sender.*;

On TRGT

9. GGSCI> add replicat repl_sch, exttrail /u01/app/oracle/GG/dirdat/schtrail/rt,checkpointtable gguser.chkptab

10. GGSCI> edit params repl_sch

                        replicat repl_dml

                        userid gguser@TRGT,password gguser

                        ASSUMETARGETDEFS

                        MAP sender.*, TARGET receiver.*;

On SRC

11. GGSCI > start extract extp_sch

12. GGSCI> start extract dp_sch

    

On TRGT

13. GGSCI> start replicat repl_sch    

Do some insert and update on both the SRC table which should be reflected on TRGT tables automatically.

oracle Exadata interview questions

1.       what is exachk, why it’s used for?

2.       Diff b/w dcli and cellcli commands.

3.       Diff b/w grid disk and cell disk.

4.       What are cell software processes?

5.       What are the software components of exadata?

6.       What is HCC, smart scan, flash cache, storage index and IORM?

7.       What are the hardware components?

8.       What is the function of CELLSRV,MS and RS?

       

      what is cell offloading
what is directpath load
how can we know whether the query is taking smart scan or not
how to replace the hard disk and flask disk in cell server
how can we know the exadata peforming is fine or not
what will happen if cellip.ora and cellinit.ora files gone
how to find the version of cell server and the patching history of the cell
what is IORM and what are objectives in IORM.
How to prioritise the databases using IORM
what is wriththrough and writeback modes in exadata
which process will maintain the metrics of exadata
smart scan limitations and required parameter's in exadata
if the data comes form cell server to database buffer cache. what will happen to that data afterwords will it stay there or go to storage cell
how to resize the disk groups in exadata
HCC types and explanation 

oracle GoldenGate interview questions

1. What is different b/w 11g and 12c GG?
-11g supports only classic extract.
-In 12c integrated extract is introduced.
-12c support internal parallelism for replicat to increase apply performance.
-automatic CDR.
2. What is diff b/w integrated method and classic method?
3. What is the advantage if we use pump process?
- by using pump we can avoid impact of network issues, if trail file currupted in target host we can resend it by using pump since we maintain one copy of trail file in source host.
- Recovery will be easy if any trail file currupted in target.
4. What are the trail files?
-trail files are binary files that are maitained by goldengate to store replicate data.
5. What are the different checkpoints in goldengate.
-capture check poitn and replicate check point.
6. What is difference b/w lag at chkpt and time since chkpt.
7. What is logdump utility?
-logdump is goldengate utility used to handle trailfiles.
8. What is RBA in trailfile?
9. How to find number of transactions in a trailfile?
- using COUNT command in logdump utility.
10. From which database version supports integrated extract/replicate.
- RDBMS 11.2.0.3
11. Which process supports from database end for integrated extract.
-logmining server process
12. Which process supports from database end for integrated replicat.
-inbound server process
13. Checkpoint table is required for integrated replicat?
-No
14. What is the diff b/w coordinated and integrated replicat.
15. what is tranlog in goldengate level.
16. what is PASSTHROU parameter
17. what is ASSUMETARGETDEFS parameter
18. what is credential store
19. what is discard file. what data it will stores.
20. what si BATCHSQL mode.
21. difference between Lag at checkpoit and Time since checkpoint.
22. what is CDR.
23. what all manager process can do.

oracle ASM interview questions

1. What is striping in ASM.

ASM striping is the process of dividing a file into 1 MB extents and spreading the extents evenly across all disks in the disk group

Oracle ASM striping has two primary purposes:

· To balance loads across all of the disks in a disk group
· To reduce I/O latency

2. What mirroring in ASM.

ASM provides automatic mirroring of ASM files and allows the mirroring level to be specified by group. This mirroring occurs at the extent level. If a disk group is mirrored, each extent has one or more mirrored copies, and mirrored copies are always kept on different disks in the disk group.
There are three ASM mirroring options:
Normal redundancy (Two-way mirroring) - Each extent has one mirrored copy in this option
High redundancy (Three-way mirroring) - Each extent has two mirrored copies in this option.
External redundancy(Unprotected mirroring) - ASM provides no mirroring in this option, which is used when mirroring is provided by the disk subsystem.
3. Where ASM pfile will be stored?

ASM pfile normally will be located in GRID_HOME/dbs and it contains location of spfile which in ASM disk groups.

5. What are the files we can store in ASM?

we can store CRD files, archivelog files, spfile, rman backup files, flashback logs in ASM

6. What is rebalancing and when it happens.

re-balancing is process of striping, when ever disk is added or removed to ASM diskgroup asm will start rebalancing data accross all disks. if you add disk, rebalancing will take some data from all the disks and store into new disk so that all disks in ASM diskgroup will fill equally.

7. What is power limit and its range from version 11.2.0.4

Beginning with Oracle Database 11g Release 2(11.2.0.2), if the COMPATIBLE.ASM disk group attribute is set to 11.2.0.2 or higher, then the range of values is 0 to 1024.

8. How can we know ASM re-balance is going on in ASM instance.

We can use V$ASM_OPERATION view to check re-balance is going on or not. if below query displays any rows, we can say re-balance is in progress. 


select * from V$ASM_OPERATION where STATE='RUN';

9. Can we copy files from ASM to OS file system.

 Yes, we can copy files from ASM using cp commands in ASMCMD command prompt.

12. What is Req_mir_free_MB in lsdg output?
13. How to take ASM metadata backup?

oracle RAC interview questions

Q.if patching in rac one node is succefull on enode fail what you will do
Q.what rootupgrade.sh script will do
Q.why we need to have 3 voting disks
Q.what is gpgnp profile
Q.what is GRD
Q.if OLR is curepted what hapens
Q.How to check OCR and OLR
Q.Where we need to rigister the scan ip
Q.why we need to relocate  SCAN listners
Q.How to check the OCR backup locations
Q.what is cahe fussion
Q.what is split brain syndrom
Q.How configure standby in stand alone for RAC database
Q.Upgradation steps and patching steps
Q.sql profile, sql plane mangement, sql tunning adviser
Q.what is view difference between view and table and meterialised view

oracle interview questions

1. what is the RU and RUR from oracle 18c.

From 18c onwards, instead of PSU patches, oracle introduces RU and RUR.
version format changed as   [MAJOR].[RU].[RUR].0.0 ex: 19.3.0.0.0

RU will be released quarterly similer to PSU, which changes the second digit in the version number like 19.2.0 to 19.3.0.

RUR also released same quarterly basis to augment the Updates with fixes for known regressions, it will change the third digit in the version number like 19.2.1 to 19.2.2

Lets say, your database is in version 19.4.0, now jan patch oracle will released as 19.5.0 and 19.4.1
 you can apply either 19.5.0 or 19.4.1

But RUR will be released maximum two times, so 19.4.0 can go upto 19.4.2, after that if you want to patch you should go to 19.6 or higher.(note: you can not go to 19.5 as its lower release than 19.4.2)

In my view choose to apply only RU to database quarterly, but finally DBA can decide what they want to follow based on their requirement.

2. what are oracle 19c new features.
3. PSU patches are going to be discontinue after moving to 19c?

Now PSU patches will be released only for 12.1 and 11gr2. to support old releases.
From 12.2 on words 18c and 19c oracle releases patches only in the form of RU and RUR.
For 12.2, for continuation oracle release RU and RUR with the same version number.
you can observer that in master patch release note (Doc ID 756671.1)

4. what is block change tracking and what is the use of it.
5. what is restore point and what are the situations we will use it.
6. can we export data using expdp in standby database when its in read only mode?.
7. how to import dumpfile without generating redo in database?.
8. what root.sh script will do while installing rdbms software.
9. what is the default location of listener and tnsnames.ora, can we change it?
10. How to find latest patch releases in oracle?

oracle will update latest patch releases in every quarter in the Master Note for Database Proactive Patch Program (Doc ID 756671.1)

11. How to find the OS version, bit version, kernal version... etc.

Below command list the details of the server.

hostnamectl status

output:
Static hostname: Ora_testmachine
         Icon name: computer-vm
           Chassis: vm
        Machine ID: XXXXXXXXXXXXXXXX
           Boot ID: XXXXXXXXXXXXXXXXXXXXX
    Virtualization: XXXXXXXXXXXXXXXX
  Operating System: Red Hat Enterprise Linux Server 7.5 (Maipo)
       CPE OS Name: cpe:/o:redhat:enterprise_linux:7.5:GA:server
            Kernel: Linux 3.10.0-862.9.1.el7.x86_64
      Architecture: x86-64

Alternatively we can use uname -a, but it will not give OS version.

12. Can we install oracle database in CentOS?

 Yes, CentOS also similer  to linux, we can install. advantage is, its Open source no licence cost.

13. what is the ASM power limit value.

Beginning with Oracle Database 11g Release 2(11.2.0.2), if the COMPATIBLE.ASM disk group attribute is set to 11.2.0.2 or higher, then the range of values is 0 to 1024.

14. How can we know ASM re-balance is going on in ASM instance.

We can use V$ASM_OPERATION view to check re-balance is going on or not. if below query displays any rows, we can say re-balance is in progress.


select * from V$ASM_OPERATION where STATE='RUN';