Monday, April 15, 2019

Canada PR


Canada immigration official https://www.canada.ca/home.html

English and French are official languages in Canada.

CRS (Comprehensive Ranking System) Tool http://www.cic.gc.ca/english/immigrate/skilled/crs-tool.asp

The Primary applicant should have good IELTS score to get good CRS score, Spouse factor is around 25-30 points.

PR     : Permanent Residency
SIN    : Social Insurance Number
IRCC : Immigration, Refugees and Citizenship Canada. It maintains list of ineligible employers to offer job  to Skilled worker.
LMIA : Labor market impact assessment how to find your exemption code and who to contact if you need help.
NOC  : National Occupational Qualification

EIP            : Economic Immigration Program
PSPC        : Public service and Procurement Canada
ROE Web : Records of employment on the web

Hire a skilled worker and support their PR process


Employers who wish to hire foreign workers and support their PR can make a job offer under IRCC express entry system and the job offer must be any one of the Economic Immigration Programs.

FSWP: Federal Skilled worker program
FSTP: Federal Skilled Trades Program
CEC: Canadian Experience Class

FSWP: 

- Management, professional, scientific, technical or trade occupations (NOC, Skill type 0 and Skill Levels A and B)
- minimum 30 hours full time work, atleast one year and non-seasonal position.

FSTP:

- An eligible skill trade or technical occupation (NOC Skill level B)
- minimum 30 hours full time work, atleast one year

CEC:

- Management, professional, scientific, technical or trade occupations (NOC, Skill type 0 and Skill Levels A and B)
- minimum 30 hours full time work, atleast one year and non-seasonal position.

Note: Under the CEC, the foreign worker must have at least 12 months of full-time (or an equivalent amount in part-time) skilled work experience in Canada within the 36 months prior to applying for permanent residence.

The following employers cannot offer a job under skilled worker.

- An embassy
- A high commission or consulate in Canada
- Ineligible employers maintains by IRCC
- New and have not been in business for 1 year.
- intended to reside in Quebec Province.

Employers who wish to hire skilled foreign workers through one of these immigration programs may also want to hire these workers temporarily while their application for permanent residence is being processed by IRCC. As a result, employers can apply for a dual intent Labour Market Impact Assessment (LMIA) which requires paying the processing fee. These dual intent LMIAs can be used to support the foreign nationals’ application to IRCC for a:
  • permanent resident visa; and
  • temporary work permit.


Procurement : The action of obtaining something.
Diversity        : A range of different things. (వైవిధ్యం)



Thursday, February 22, 2018

Interview Questions


What are the different kernel parameters?

Why do we set kernel parameters?

What happens to the database instance If database is completely using configured shmmax memory?

What is meant by fixed size?

What are the different types of buffers are allocated in memory at the instance startup time?

If a user or server reaches the maximum number of configured processes, What would be the impact to the user or server?

How do you decide which block size to be used in the database or in a particular tablespace?

What is a hard parsing and soft parsing?

How do you control hard parsing?

What is a literal and bind variables?

When do use literals and when do you use bind variables?

How do you check block corruption in the database?

What are different type of protection modes in data guard environment?

I have a 3 standby databases with one primary database, Now how many protection modes can be configured?

Can different standby databases receives redo data synchronously and asynchronously? If Yes, How can we have a single protection mode throughout data guard setup having more than one standby database?

Which process will give the commit acknowledgement to the primary database?

How data is shipped from primary database to standby database?

What is the functionality of LNS and RFS server?

What is the functionality of ARCH background process?

Which process will apply the redo data in standby database?

What are the parameters do we need to configured to set up physical standby database?

What is FAL_SERVER parameter?

If we unset FAL_SERVER parameter, What would be the impact?

What are standby redo log files?

What happens if archive logs are deleted or corrupted in primary before they applied to standby?

When we issue switch over command in physical standby database, The new primary database will be recovered/opened to which point of the database?

What are issues you have faced while installing Oracle RAC?

What is OCR and Voting disks?

What is a client side load balancing and server side load balancing?

How do you achieve client side load balancing and server side load balancing?

I have 2 queries executing in the RAC database instance on ORCL1,  one is insert and another is select statement and suddenly instance got crashed then what happens to my sessions?

how do you configure fail over and what are different types of fail over methods?

What are the steps you will take before applying patch?

Why do we unlock and lock the grid binaries before and after applying patches?

How do you recover the global inventory corruption in oracle RAC?

What are new features in Oracle upgrade utility in Oracle 12c?

What are the new features in Oracle 12c?

What is your backup strategy?

How backups were configured in your environment?

What is an obsolete backup?

My database backup retention is 5 days and 

day 0 - Full backup
day 1 - Incremental backup
day 2 - Incremental backup
day 3 - Incremental backup
day 4 - Incremental backup
day 5 - Incremental backup
day 6 - Incremental backup
day 0 - Full backup
day 1 - Incremental backup
day 2 - Incremental backup
day 3 - Database is crashed.

Since database's backup retention is 5 days, How many backups are available in the backup destination?

I have yesterday's database full backup and today datafile has been added and accidentally dropped. Can we recover the datafile without any data loss?

I have yesterday's database full backup and database is crashed today, How database knows the recovery length/time?

What is a db file sequential read and db file scatter read?

How do you troubleshoot slow performance of a query which was doing perfectly good some time back?

What is a index, Why do we create indexes and what are different types of indexes?

What is a partition and what are different types of partitions?





Saturday, February 17, 2018

Result: PRVF-4007 : User equivalence check failed for user "grid"

[grid@oel01 grid]$ ./runcluvfy.sh stage -post hwos -n oel01,oel02 -verbose

Performing post-checks for hardware and operating system setup

Checking node reachability...

Check: Node reachability from node "oel01"
  Destination Node                      Reachable?           
  ------------------------------------  ------------------------
  oel02                                 yes                   
  oel01                                 yes                   
Result: Node reachability check passed from node "oel01"


Checking user equivalence...

Check: User equivalence for user "grid"
  Node Name                             Comment               
  ------------------------------------  ------------------------
  oel02                                 failed               
  oel01                                 failed               
Result: PRVF-4007 : User equivalence check failed for user "grid"

ERROR:
User equivalence unavailable on all the specified nodes
Verification cannot proceed


Post-check for hardware and operating system setup was unsuccessful on all the nodes.
[grid@oel01 grid]$


[grid@oel01 sshsetup]$ ./sshUserSetup.sh -user grid -hosts "oel01 oel02" -noPromptPassphrase
The output of this script is also logged into /tmp/sshUserSetup_2018-02-18-00-41-56.log
Hosts are oel01 oel02
user is grid
Platform:- Linux 
Checking if the remote hosts are reachable
PING oel01.oracle.com (192.168.52.1) 56(84) bytes of data.
64 bytes from oel01.oracle.com (192.168.52.1): icmp_seq=1 ttl=64 time=0.065 ms
64 bytes from oel01.oracle.com (192.168.52.1): icmp_seq=2 ttl=64 time=0.075 ms
64 bytes from oel01.oracle.com (192.168.52.1): icmp_seq=3 ttl=64 time=0.065 ms
64 bytes from oel01.oracle.com (192.168.52.1): icmp_seq=4 ttl=64 time=0.061 ms
64 bytes from oel01.oracle.com (192.168.52.1): icmp_seq=5 ttl=64 time=0.062 ms

--- oel01.oracle.com ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4000ms
rtt min/avg/max/mdev = 0.061/0.065/0.075/0.010 ms
PING oel02.oracle.com (192.168.52.2) 56(84) bytes of data.
64 bytes from oel02.oracle.com (192.168.52.2): icmp_seq=1 ttl=64 time=1.43 ms
64 bytes from oel02.oracle.com (192.168.52.2): icmp_seq=2 ttl=64 time=0.796 ms
64 bytes from oel02.oracle.com (192.168.52.2): icmp_seq=3 ttl=64 time=0.644 ms
64 bytes from oel02.oracle.com (192.168.52.2): icmp_seq=4 ttl=64 time=0.668 ms
64 bytes from oel02.oracle.com (192.168.52.2): icmp_seq=5 ttl=64 time=0.417 ms

--- oel02.oracle.com ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4006ms
rtt min/avg/max/mdev = 0.417/0.791/1.432/0.343 ms
Remote host reachability check succeeded.
The following hosts are reachable: oel01 oel02.
The following hosts are not reachable: .
All hosts are reachable. Proceeding further...
The script will setup SSH connectivity from the host oel01.oracle.com to all
the remote hosts. After the script is executed, the user can use SSH to run
commands on the remote hosts or copy files between this host oel01.oracle.com
and the remote hosts without being prompted for passwords or confirmations.

NOTE 1:
As part of the setup procedure, this script will use ssh and scp to copy
files between the local host and the remote hosts. Since the script does not
store passwords, you may be prompted for the passwords during the execution of
the script whenever ssh or scp is invoked.

NOTE 2:
AS PER SSH REQUIREMENTS, THIS SCRIPT WILL SECURE THE USER HOME DIRECTORY
AND THE .ssh DIRECTORY BY REVOKING GROUP AND WORLD WRITE PRIVILEDGES TO THESE
directories.

Do you want to continue and let the script make the above mentioned changes (yes/no)?
yes

The user chose yes
User chose to skip passphrase related questions.
Creating .ssh directory on local host, if not present already
Creating authorized_keys file on local host
Changing permissions on authorized_keys to 644 on local host
Creating known_hosts file on local host
Changing permissions on known_hosts to 644 on local host
Creating config file on local host
If a config file exists already at /home/grid/.ssh/config, it would be backed up to /home/grid/.ssh/config.backup.
Removing old private/public keys on local host
Running SSH keygen on local host with empty passphrase
Generating public/private rsa key pair.
Your identification has been saved in /home/grid/.ssh/id_rsa.
Your public key has been saved in /home/grid/.ssh/id_rsa.pub.
The key fingerprint is:
da:96:e7:db:d8:74:4b:74:de:07:c1:db:89:9b:82:9a grid@oel01.oracle.com
The key's randomart image is:
+--[ RSA 1024]----+
|                 |
|             .   |
|              o  |
|              .+.|
|        S    .+.o|
|       o ..  .o+.|
|      . +....oo +|
|       .oo =.o ..|
|       E  +.o .  |
+-----------------+
Creating .ssh directory and setting permissions on remote host oel01
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR grid. THIS IS AN SSH REQUIREMENT.
The script would create ~grid/.ssh/config file on remote host oel01. If a config file exists already at ~grid/.ssh/config, it would be backed up to ~grid/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host oel01.
Warning: Permanently added 'oel01,192.168.52.1' (RSA) to the list of known hosts.
grid@oel01's password: 
Done with creating .ssh directory and setting permissions on remote host oel01.
Creating .ssh directory and setting permissions on remote host oel02
THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR group AND others ON THE HOME DIRECTORY FOR grid. THIS IS AN SSH REQUIREMENT.
The script would create ~grid/.ssh/config file on remote host oel02. If a config file exists already at ~grid/.ssh/config, it would be backed up to ~grid/.ssh/config.backup.
The user may be prompted for a password here since the script would be running SSH on host oel02.
Warning: Permanently added 'oel02,192.168.52.2' (RSA) to the list of known hosts.
grid@oel02's password: 
Done with creating .ssh directory and setting permissions on remote host oel02.
Copying local host public key to the remote host oel01
The user may be prompted for a password or passphrase here since the script would be using SCP for host oel01.
grid@oel01's password: 
Done copying local host public key to the remote host oel01
Copying local host public key to the remote host oel02
The user may be prompted for a password or passphrase here since the script would be using SCP for host oel02.
grid@oel02's password: 
Done copying local host public key to the remote host oel02
cat: /home/grid/.ssh/known_hosts.tmp: No such file or directory
cat: /home/grid/.ssh/authorized_keys.tmp: No such file or directory
SSH setup is complete.

------------------------------------------------------------------------
Verifying SSH setup
===================
The script will now run the date command on the remote nodes using ssh
to verify if ssh is setup correctly. IF THE SETUP IS CORRECTLY SETUP,
THERE SHOULD BE NO OUTPUT OTHER THAN THE DATE AND SSH SHOULD NOT ASK FOR
PASSWORDS. If you see any output other than date or are prompted for the
password, ssh is not setup correctly and you will need to resolve the
issue and set up ssh again.
The possible causes for failure could be:
1. The server settings in /etc/ssh/sshd_config file do not allow ssh
for user grid.
2. The server may have disabled public key based authentication.
3. The client public key on the server may be outdated.
4. ~grid or ~grid/.ssh on the remote host may not be owned by grid.
5. User may not have passed -shared option for shared remote users or
may be passing the -shared option for non-shared remote users.
6. If there is output in addition to the date, but no password is asked,
it may be a security alert shown as part of company policy. Append the
additional text to the <OMS HOME>/sysman/prov/resources/ignoreMessages.txt file.
------------------------------------------------------------------------
--oel01:--
Running /usr/bin/ssh -x -l grid oel01 date to verify SSH connectivity has been setup from local host to oel01.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Sun Feb 18 00:42:50 IST 2018
------------------------------------------------------------------------
--oel02:--
Running /usr/bin/ssh -x -l grid oel02 date to verify SSH connectivity has been setup from local host to oel02.
IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR.
Sun Feb 18 00:42:50 IST 2018
------------------------------------------------------------------------
SSH verification complete.
[grid@oel01 sshsetup]$ 

[grid@oel01 grid]$ ./runcluvfy.sh stage -post hwos -n oel01,oel02 -verbose

Performing post-checks for hardware and operating system setup

Checking node reachability...

Check: Node reachability from node "oel01"
  Destination Node                      Reachable?           
  ------------------------------------  ------------------------
  oel02                                 yes                   
  oel01                                 yes                   
Result: Node reachability check passed from node "oel01"


Checking user equivalence...

Check: User equivalence for user "grid"
  Node Name                             Comment               
  ------------------------------------  ------------------------
  oel02                                 passed               
  oel01                                 passed               
Result: User equivalence check passed for user "grid"

Checking node connectivity...

Checking hosts config file...
  Node Name     Status                    Comment               
  ------------  ------------------------  ------------------------
  oel02         passed                                         
  oel01         passed                                         

Verification of the hosts config file successful


Interface information for node "oel02"
 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU 
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   192.168.22.2    192.168.22.0    0.0.0.0         UNKNOWN         08:00:27:31:C4:F2 1500
 eth1   192.168.52.2    192.168.52.0    0.0.0.0         UNKNOWN         08:00:27:1C:9C:4C 1500
 virbr0 192.168.122.1   192.168.122.0   0.0.0.0         UNKNOWN         52:54:00:DC:15:6A 1500


Interface information for node "oel01"
 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU 
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   192.168.22.1    192.168.22.0    0.0.0.0         UNKNOWN         08:00:27:51:84:D3 1500
 eth1   192.168.52.1    192.168.52.0    0.0.0.0         UNKNOWN         08:00:27:F0:D7:99 1500
 virbr0 192.168.122.1   192.168.122.0   0.0.0.0         UNKNOWN         52:54:00:DC:15:6A 1500


Check: Node connectivity of subnet "192.168.22.0"
  Source                          Destination                     Connected?   
  ------------------------------  ------------------------------  ----------------
  oel02:eth0                      oel01:eth0                      yes           
Result: Node connectivity passed for subnet "192.168.22.0" with node(s) oel02,oel01


Check: TCP connectivity of subnet "192.168.22.0"
  Source                          Destination                     Connected?   
  ------------------------------  ------------------------------  ----------------
  oel01:192.168.22.1              oel02:192.168.22.2              passed       
Result: TCP connectivity check passed for subnet "192.168.22.0"


Check: Node connectivity of subnet "192.168.52.0"
  Source                          Destination                     Connected?   
  ------------------------------  ------------------------------  ----------------
  oel02:eth1                      oel01:eth1                      yes           
Result: Node connectivity passed for subnet "192.168.52.0" with node(s) oel02,oel01


Check: TCP connectivity of subnet "192.168.52.0"
  Source                          Destination                     Connected?   
  ------------------------------  ------------------------------  ----------------
  oel01:192.168.52.1              oel02:192.168.52.2              passed       
Result: TCP connectivity check passed for subnet "192.168.52.0"


Check: Node connectivity of subnet "192.168.122.0"
  Source                          Destination                     Connected?   
  ------------------------------  ------------------------------  ----------------
  oel02:virbr0                    oel01:virbr0                    yes           
Result: Node connectivity passed for subnet "192.168.122.0" with node(s) oel02,oel01


Check: TCP connectivity of subnet "192.168.122.0"
Result: TCP connectivity check failed for subnet "192.168.122.0"


Interfaces found on subnet "192.168.22.0" that are likely candidates for a private interconnect are:
oel02 eth0:192.168.22.2
oel01 eth0:192.168.22.1

Interfaces found on subnet "192.168.52.0" that are likely candidates for a private interconnect are:
oel02 eth1:192.168.52.2
oel01 eth1:192.168.52.1

Interfaces found on subnet "192.168.122.0" that are likely candidates for a private interconnect are:
oel02 virbr0:192.168.122.1
oel01 virbr0:192.168.122.1

WARNING:
Could not find a suitable set of interfaces for VIPs

Result: Node connectivity check passed


Checking for multiple users with UID value 0
Result: Check for multiple users with UID value 0 passed

Post-check for hardware and operating system setup was successful.
[grid@oel01 grid]$
[grid@oel01 grid]$
[grid@oel01 grid]$
[grid@oel01 grid]$   


[grid@oel01 grid]$ ./runcluvfy.sh stage -pre crsinst -n oel01,oel02 -r 11gR2 \
> -osdba dba \
> -orainv oinstall \
> -fixup -fixupdir /u01/app/grid -verbose

Please run the following script on each node as "root" user to execute the fixups:
'/tmp/CVU_11.2.0.1.0_grid/runfixup.sh'

Pre-check for cluster services setup was unsuccessful on all the nodes.
[grid@oel01 grid]$  

Monday, February 12, 2018

Deleting a node from the cluster

Deleting a node from cluster

Stop the database and ASM in the node.
Remove the database and ASM.
Remove the database software.
Remove the cluster software.
Update the Oracle inventory.

olsnodes command list the nodes and other information for all the nodes in the cluster.

olsnodes
olsnodes -n lists the nodes that are participating in the cluster with node numbers.
olsnodes -i lists the nodes that are participating in the cluster with VIPs.
olsnodes -s lists node status
olsnodes -a lists node mode
olsnodes -t lists the nodes that are pinned/unpinned
olsnodes -l -p lists the IP of private interconnect on the node where command is executed.

Current state

As Oracle user, olsnodes -s -t
srvctl status database -d ORCL -verbose
select inst_id,instance_name,status,to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') "STARTUP_TIME" from gv$instance order by inst_id;

1. Stop the instance

srvctl stop instance -d ORCL -i ORCL3
srvctl status database -d ORCL -verbose

2. Remove the instance

srvctl remove instance -d ORCL -i ORCL3

3. Disable Oracle clusterware on Node 3

As root,

cd $GRID_HOME/crs/install
./rootcrs.pl -deconfig -deinstall -force

4. Disable and stop the listener on node 3

As root,

srvctl stop vip -i ORCL3-VIP -f
srvctl remove vip -i ORCL3-VIP -f

5. Delete the node, Run the below command as root user from surviving node node 1.

As root,

cd $GRID_HOME/bin
./crsctl delete node -n node3

6. Update the inventory for grid home.

cd $GRID_HOME/oui/bin
./runInstaller -updateNodeList ORACLE_HOME=/u01/app/grid/product/11.2.0/grid "CLUSTER_NODE={node1,node2}" CRS=TRUE

7. Remove the database home

cd $ORACLE_HOME/deinstall
./deinstall -local

8. Update the inventory for oracle home

cd $ORACLE_HOME/oui/bin
./runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/product/11.2.0 "CLUSTER_NODE={node1,node2}"

9. Post deletion steps



rm -rf /etc/oraInst.loc
rm -rf /etc/oracle
rm -rf /opt/ORCLfmap
rm -rf /etc/oratab
rm -rf /u01
groupdel dba oinstall
rm -rf /u01/app/oracle

Adding a node to the cluster

Before adding a node,

Install the operating system software.
create/modify required users and groups.
Configure kernel parameters.
Configure network.
Install the grid and oracle binaries.etc..

check the nodes in the cluster

olsnodes -s -i -t Here we see only 2 nodes in the cluster.

Once the node is ready to add to the cluster,

As grid user,

1. Run the cluster verification utility to confirm the node can be added to the node.

cd $GRID_HOME/bin
./cluvfy stage -pre nodeadd -n RAC3 -fixup -fixupnoexec

2. Run addnode.sh to add the node

cd $GRID_HOME/bin
./addNode.sh -silent "CLUSTER_NEW_NODES={RAC3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={rac3-vip}"


3. As a root user, execute orainstRoot.sh and root.sh scripts.

4. olsnodes -s -i -t Here the new node is part of the cluster.

Real Application Clusters

Master Node in RAC can be identified by

select * from gv$gcs_resource;

The node which takes the auto backup of OCR is called master node in RAC.

By reviewing the ocssd and crsd logs.

cat $ORACLE_HOME/log/host01/cssd/ocssd.log |grep ‘master node’ |tail -1

Cluster name can found from below command

How to Query the Cluster Name [ID 577300.1]

GRID_HOME/bin/cemulto -n

OCR

OCR maintains information about clusterware resources like ASM, Database instances, scan listeners, disk groups, VIPs, Nodeapps etc.
OCR can be managed by ocrconfig, ocrcheck, ocrdump utilities as root user.
We can have up to 4 mirror copies of OCR.
Oracle automatically takes the backup of OCR for every 4, 8, 12 hours, At the end of every day, At the end of every week and frequency or number of OCR backups to retain can't be changed.
Oracle clusterware retains last 3 backups of OCR plus last daily and last weekly backup.
When a node gets added or deleted the information will be updated in OCR. It should be shared by all nodes in the cluster.

ocrconfig -showbackup Automatic backup location
ocrconfig -local -manualbackup Manually backing up to the local node.
ocrconfig -local -showbackup    lists backups available in the local node.
ls -ltr /u01/app/grid/cdata/nodename/*.olr lists backups available in OS.

Restore OLR from current backup

Stop the clusterware.
Verify ohasd.bin is not running
Restore the backup
ocrconfig -local -restore /u01/app/grid/cadata/nodename/olrbackup.olr
Start the clusterware.

Relocate OCR to different ASM disk group

Check the current location of OCR.
$GRID_HOME/bin/ocrcheck
Add the new location
$GRID_HOME/bin/ocrconfig -add +NEW_OCR
Check the current location of OCR
$GRID_HOME/bin/ocrcheck
Delete the old OCR location
$GRID_HOME/bin/ocrconfig -delete +DATA
Check the current location of OCR
$GRID_HOME/bin/ocrcheck

Voting Disks

Voting disks maintains the node membership.
Each node participating in the cluster has to send it's heart beat to voting disks for every 5 seconds.
If any of the node fails to vote it's availability for 30 seconds the node gets rebooted.
voting disk backups are taken by dd command and backing up voting disk should be part of your backup routine/policy and operations on voting disk can be performed as root user.
Oracle recommends to take the backup of voting disk after node addition or deletion.

Relocate VOTING disks to different disk groups.

$GRID_HOME/bin/crsctl query css votedisk
$GRID_HOME/bin/crsctl replace votedisk +OCR
$GRID_HOME/bin/crsctl query css votedisk

OCR and voting files are vital for clusterware operation, During installation of GI we have the option to choose only one disk group for OCR and voting files. If the disk group goes down we will be loosing both OCR and voting files and recovery of both will have different approaches, Hence down time would be more. Here i'm outlining the procedure to separate OCR and voting to different disk groups.

As a root user,

$GRID_HOME/bin/ocrcheck
$GRID_HOME/bin/crsctl query css votedisk
Create disk group after new disks are available.
$GRID_HOME/bin/crsctl query css votedisk
$GRID_HOME/bin/crsctl replace votedisk +VD
$GRID_HOME/bin/crsctl query css votedisk

Thursday, February 8, 2018

Sequential Read and Scatter Read

A db file sequential read is a single-block read and db file scatter read is a multi block read.

db file sequential read - A single-block read (i.e., index fetch by ROWID) 
db file scatter read       - A multi block read (a full-table scan, OPQ, sorting)

The db file sequential read event signifies that the user process is reading the buffers into SGA(Database buffer cache) and is waiting for physical I/O to return or complete. It reads the blocks in to contiguous memory space and these single block reads or I/Os uses indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache.

Starting with Oracle 10g R2, Oracle recommends to not to set db_file_multiblock_read_count parameters that allowing oracle to empirically determine the optimal setting.


The db file sequential read has 3 parameters.


  1. file#,
  2. first block#,
  3. block count.
In 10g, Wait event falls under User I/O wait class.
Physical disk speed is an important factor in weighing these costs. Faster disk access speeds can reduce the costs of a full-table scan vs. single block reads to a negligible level. solid state disks provide up to 100,000 I/Os per second, six times faster than traditional disk devices. In a solid-state disk environment, disk I/O is much faster and multiblock reads become far cheaper than with traditional disks.

e.g.

Top 5 Timed Events
                                                           % Total
Event                        Waits         Time (s)       Ela Time
--------------------------- ------------ -----------      --------
db file sequential read       2,598          7,146          48.54
db file scattered read       25,519          3,246          22.04
library cache load lock         673          1,363           9.26
CPU time                                     1,154           7.83
log file parallel write      19,157            837           5.68


From the above e.g. reads and a write constitute the majority of the total database time. In this case, We need to increase database buffer cache (db_cache_size) or tune the SQL or Invest amount in having faster disk (SSD) I/O sub system.



Script to measure disk I/O cost of db file sequential read.

col c1 heading 'Average Waits|forFull| Scan Read I/O'        format 9999.999
col c2 heading 'Average Waits|for Index|Read I/O'            format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans'        format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans'       format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
 
 
select
   a.average_wait                                  c1,
   b.average_wait                                  c2,
   a.total_waits /(a.total_waits + b.total_waits)  c3,
   b.total_waits /(a.total_waits + b.total_waits)  c4,
   (b.average_wait / a.average_wait)*100           c5
from
  v$system_event  a,
  v$system_event  b
where
   a.event = 'db file scattered read'
and
   b.event = 'db file sequential read';

Scattered reads and full-table scans

Full table scans are not necessarily a detriment to performance and is a fastest way to access the table rows. The CBO choices to perform FTS depends on OPQ, db_block_size and clustering_factor, estimated % of rows determined by the query and other factors. Once of CBO chooses to perform FTS, The speed of performing FTS (SOFTS) depends on internal and external factors.
  1. The number of CPUs on the system
  2. The setting for Oracle Parallel Query (parallel hints, alter table)
  3. Table partitioning
  4. The speed of the disk I/O subsystem
With all the above factors it may be impossible to find best setting for optimizer_index_cost_adj parameter. In the real world the decision to perform FTS depends on below factors.
  1. Free blocks available in database buffer cache.
  2. Free space in temp as if query having order by clause
  3. Current demands on CPU.
Hence, it follows that the optimizer_index_cost_adj should change frequently, as the load changes on the server.

Tuesday, November 28, 2017

Runlevel Change


$ runlevel 
$ vi /etc/inittab
$ reboot

Installation scripts

If Oracle Installation is first time on the server then it creates /etc/oraInst.loc file which will be having information about oracle central inventory and it's group by default oinstall.

Do not put the oraInventory directory under the Oracle base directory for a new installation, because that can result in user permission errors for other installations.

If oraInventory group i.e. oinstall doesn't exists then installer uses the primary group of the software owner who is installing software.

Creating oinstall group if oraInvenotry doesn't exists

/usr/sbin/groupadd -g 54321 oinstall

grep oinstall /etc/groups

In Oracle documentation, a user created to own only Oracle Grid Infrastructure software installations is called the Grid user (grid). This user owns both the Oracle Clusterware and Oracle Automatic Storage Management binaries. A user created to own either all Oracle installations, or one or more Oracle database installations, is called the Oracle user (oracle). You can have only one Oracle Grid Infrastructure installation owner, but you can have different Oracle users to own different installations.

Can we have multiple owners for GI installations? No
Can we have multiple owners for DB installations? Yes, different versions of DB installations can be owned by different users.

Oracle software owners (grid and oracle) should have oinstall or Oracle Inventory group as their primary group so that each software installation  owner can able to write to the central inventory and OCR and Oracle clusterware resource permissions are set correctly.

The database software owner must also have osdba group and also osoper, osbackupdba, osdgdba, osracdba, oskmdba groups as secondary groups if they are created for role seperation duties.

oracle : 54321
grid : 54331

oinstall:54321
dba:54322
oper:54323
backupdba:54324
dgdba:54325
kmdba:54326
racdba:54330
asmdba:54327
asmoper:54328
asmadmin:54329

Oracle user can be in assigned with asmdba to manage asm instances.


$ grep "oinstall" /etc/group
oinstall:x:54321:grid,oracle

$ id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba), 
54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54327(asmdba),54330(racdba)
$ id grid
uid=54331(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),
54327(asmdba),54328(asmoper),54329(asmadmin),54330(racdba)

For Oracle Restart installations, to successfully install Oracle Database, ensure that the grid user is a member of the racdba group.


We need to run the below scripts as root user.

/u01/app/oraInstroot.sh


It creates the oraInventory.

Revokes read, write and execute permissions on the inventory from the world.
Grants read and write permission to the group oinstall.
creates /etc/oraInst.loc file which will be having information about location of the inventory as well as it's group.

/u01/app/oracle/product/12.1.0/dbhome_1/root.sh


Copies oraenv, coraenv to local bin directory i.e. /usr/local/bin

Creates oratab file and entries will be added to it when database is created DBCA.
If we manually create a database then the database entry will not be added to oratab, We need to add the new database to oratab manually.

If Central inventory/Global inventory is lost/corrupted then we can recreate it by attachhome method.


If Oracle installation is first time on the server then there wouldn't be any Oracle Inventory until we run /u01/app/oraInstroot.sh script as root user. We can register any number installations with one Oracle central inventory, Lets say if a server is running with 10g, 11g and 12c installations there would be only one Oracle Inventory for multiple installations.


oracle preinstall RPM


Oracle 12c R1 and R2 is certified to install on Oracle Linux 6 or 7 and use Oracle RPM, This RPM installs all required packages, sets kernel parameters for the installation of Oracle database and grid infrastructure. oracle-database-server-12cR2-preinstall is the RPM configures server ready for the installation of database and grid infrastructure.

Steps to install  oracle preinstall RPM

Download the Oracle linux 6 or 7 from edelivery.oracle.com
Once downloaded, start the installation with the appropriate selections for your environment.
During the installation process, Select customize now option in the software selection window.
On Oracle Linux, Select servers on the left pane of the screen and system administration on the right pane of the screen and then packages in system tools window will open.
Select Oracle Preinstallation RPM package box from the package list.
For Oracle Linux 7 select package similar to oracle-database-server-12cR2-preinstall-1.0-4.el7.x86_64.rpm
Close the optional package window and go with next steps.

Tuesday, September 26, 2017

Oracle Database Cloud Service Wizard

We will use create service wizard to create a service in oracle cloud.
The wizard takes you through below options.

Service Level

Oracle 12.2 is not available in oracle database cloud service virtual image service level.

Oracle Database Cloud Service - Virtual Image

It is a compute environment with pre-installed virtual machine that include all software needed to create and run an Oracle Database.
We connect to the virtual machine and run the DBCA to create database.
All maintenance operations like backup, patching and upgrades should be done manually, They are not automated.

Oracle Database Cloud Service

It is a Virtual machine plus database created according to specifications provided in the Create Service wizard.
It will have tools that provides automatic and on-demand backups, patching and upgrading, and point-in-time recovery for Oracle Databases.

Cloud Tools

bkup_api utility used to perform on-demand backups where as in RAC environments we use raccli. We can chnage the configuration of how backups are configured.

orec of dbaascli utility used to restore from backups where as in RAC environments we use raccli.

dbpatchm of dbaascli utility used for automatic patching where as in RAC environments we use raccli.

We use DBaaS Monitor web application to monitor Oracle databases and computing resources. It is not available on the Oracle RAC deployments on cloud.

Metering Frequency is of 2 types

Hourly  - Pay only for hours used during your billing period. We cannot switch the metering frequency from hourly to monthly once database has been deployed in cloud.

Monthly - Pay one price per month irrespective of number of hours used. We cannot switch the metering frequency from monthly to hourly once database has been deployed in cloud. Deployments that are created in middle of month the price is pro-rated. We only pay partial month amount from the start date.

Oracle Database Software Release

We can create Oracle database 11g R2, 12c R1 and 12c R2 deployments in cloud. Oracle 12.2 is not available in oracle database cloud service virtual image service level.

We can install following Oracle Database Software Editions.

Standard Edition
Enterprise Edition
Enterprise Edition - High Performance
Enterprise Edition - Extreme Performance

If we choose Enterprise Edition or Enterprise Edition - High Performance, all available database enterprise management packs and Enterprise Edition options are included in the database deployment.
The packs and options that are not part of the software edition you chose are available to you for use on a trial basis.

We can create following types of database deployments in Oracle cloud.

Single instance - Instance and database data store in a single compute node.
Database Clustering with RAC - A two node clustered database, Each compute node has one instance and two instances access the same shared database data store.
Single instance with dataguard standby - Two single-instance databases, one acting as the primary database and one acting as the standby database in an Oracle Data Guard configuration.
Database clustering with RAC and datagaurd standby - Two two-node Oracle RAC databases, one acting as the primary database and one acting as the standby database in an Oracle Data Guard configuration.
Dataguard standby fr hybrid DR - Single-instance database acting as the standby database in an Oracle Data Guard configuration. The primary database is on your own system.

Single instance is the only supported type for Oracle database cloud sevice - virtual image service level.
Single instance is the only supported type in standard edition.
The two RAC types are only supported in Enterprise Edition - Extreme Performance.


Oracle Database Cloud Service


Oracle database cloud service provides you the ability to deploy oracle databases in cloud.
Each deployment would have a single instance Oracle database.
We will be having full access to features and options in cloud with oracle provided computing power, storage and tools for database's routine and management operations.
It creates compute hosts to host the databases and provides access to these compute nodes using networking features provided by oracle database cloud services.

Two service levels are available with Oracle database cloud service.

Oracle Database Cloud Service - Virtual Image level

It includes Oracle database and supporting software.
We need to install the software and we are responsible for maintenance tasks.
We will be having root access/privilege and full administrative privilege to oracle database, So that we can load and run software in compute environment.

The Oracle Database Cloud Service level 

It includes Oracle Database and supporting software. 
The software is installed, Oracle database is created using values you provide when creating the database deployment and the database is started. We can set up automatic backups and it will be having tools for backup, recovery, patching and upgrade operations.
We will be having root privilege and full administrative privileges for the Oracle database, so you can load and run software in the compute environment.
We can make changes to the automated maintenance setup, and we are responsible for recovery operations in the event of a failure.

Tuesday, April 19, 2016

Notes

list archive log all shows all the archivelogs currently know to the controlfile.

list backup of archivelog all will show you the archives that have been backed up

there backup timestamp must be greater than the time when you started the recovery hence 5406,5407 and 5408  didn't recover as they were not backed up in the first place.

ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1)

e.g. ORA-00600: internal error code, arguments: [kkqjpdpvpd: No join pred found.], [], [], [], [], [], [], [], [], [], [], []

First Argument : kkqjpdpvpd: No join pred found.

Fast Start Failover, is avialable through dataguard broker which do automatic failover to the choosen primary database in case of the primary crash. It has a componenet called Observer which can be installed in a

seperate location other than primary and standby databases which takes very less amount of resources, Just we need oracle database s/w or Oracle Client s/w and tnsentry pointing to both primary and standby

databases. The observer will continuously monitor the primary database and due to any reason if primary is unavilable it starts failover to  choosen standby by waiting the ammount specified in FSFThreshold.

FSFLaglimit   - The maxminum ammount of time in Seconds for the permissible data loss. FSFO will runs in Max availability or Max Performance. In Max availibity no data loss where Max Permformance

            30 seconds of dataloss is permissible.

FSFThreshold - 30 Seconds, FSFO waits for 30 seconds before failing it over to the specified standby database

FSFPrmyshutdown - [TRUE|FALSE]. If the parameter is true then primary will be stalled for the FSFThreshold time and shutdowns.

FSPTarget - Using this parameter we will specify the db_unique_name of the standby database to where primary has to  failover.

FSFAutoReinitiate - [TRUE|FALSE]. If the parameter is true then former primary will start re-initiate after the fast start failover.

Fast-Start-Failover has a few restrictions:

We can not change the protection mode of the dataguard broker configuration and logshipping mode of the primary and standby databases.

We can not switchover/failover to the targets(standby databases) which are not part of the FSFTarget

The broker configuration cannot be removed, If FSFO is enabled

Standby database cannot be dropped/deleted.

FSFO is not possible if the observer is not running and.

If the database shutdown normally then FSFO will not perform failover, It will perform only if abort option is used.

Thursday, April 7, 2016

Generating Oracle Query output to a csv file

--Set the linesize large enough to accommodate the longest possible line.
SET LINESIZE 9999
--Turn off all page headings.
SET PAGESIZE 20000
--Turn off feedback
SET FEEDBACK OFF
/*
-- It will not return how many number of rows were returned after executing the query.
*/
--Eliminate trailing blanks at the end of a line.
SET TRIMSPOOL ON

SET TERMOUT OFF
/*
-- The output upon running the command/script will not not returned on the standard output screen.
*/

/*
-- set your column seperator to a comma for CSV file

*/

SET COLSEP ','

SPOOL excel_readable_file.csv

Query

spool off;

Monday, March 21, 2016

password_verify_function

The Password_Verify_function will verify the password has been used ever. If it is enabled then we can not reuse the same password for alerting a user. There may be some situations still we need to reset the password to same password i.e older password. Here we go as below.
Since Oracle 11g has introduced new Password policies like PASSWORD_LIFE_TIME=180 days,PASSWORD_VERIFY_FUNCTION= VERIFY_FUNCTION_11G which was not there in Oracle 10g.

Oracle 11g introduces Case-sensitive passwords for database authentication. Along with this if you wish to change the password (temporarily) and reset it back to old , you will find that password field in dba_users is empty.


1) Identify the PROFILE assigned to the user you want to change the password.

select username,password,account_status,profile from dba_users where USERNAME='&USERNAME';

2) select * from dba_profiles where PROFILE_NAME='Profile name retrieved from step 1'; Identify the values assigned to resource name PASSWORD_VERIFY_FUNCTION

3) alter the profile with Password_Verify_function as null;

alter profile PROFILE_NAME limit password_verify_function null;

4) Now change the password.

alter user username identified by password; //If we know the old password

alter user username identified by values '******'; //If we don't know the password please use the below query or use the values found from step 1

e.g.

select 'alter user "'||d.username||'" identified by values '''||u.password||''';' Query
from dba_users d, sys.user$ u
where d.username = upper('&&username')
and u.user# = d.user_id;
Enter value for username: remote_dba
old   3: where d.username = upper('&&username')
new   3: where d.username = upper('remote_dba')

Query
--------------------------------------------------------------------------------
alter user "REMOTE_DBA" identified by values 'F894844C34402B67';

SQL> alter user "REMOTE_DBA" identified by values 'F894844C34402B67'


5) change password_verify_function to older value.

ALTER PROFILE profile_name LIMIT PASSWORD_VERIFY_FUNCTION {function | NULL | DEFAULT} //Found in step 2.


Data migration using database link

1) Connect to the target database and Create a user in target database

2) Grant select any table privilege to the user who has been created in step 1.

3) Create a Private database link from target to source using the username created in step 1

4) AS the user in target database,

CREATE TABLE new_table AS (SELECT * FROM owner.table_name@DB_LINK);
  insert into tablename select * from owner.source table_name@DB_LINK;
Gather schema/table stats
Verify the data with source database

Sunday, March 20, 2016

Enabling X11 Forwarding

Login to where you can access the GUI
Open the terminal
Click on SSH
Click on X11
Select “Enable X11 forwarding”
6          Login with individual ID
Hidden file namely Xauthority will be created under /home/<your user id>
Echo $DISPLAY
Run /usr/openwin/bin/xauth list, Will display the x11 session cookies.
Sudo su – oracle
/usr/openwin/bin/xauth add <Entire line from the above step)
Echo $DISPLAY
Export DISPLAY=<output of Step 9>
Echo $DISPLAY
Type xclock or /usr/openwin/bin/xclock

Killing a datapump job


1) select * from dba_datapump_jobs;

Here we need to identify the the job_name with status as EXECUTING.

2) expdp attach=EXPFULL  #JOB_NAME

3) Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

Export>

4) select * from dba_datapump_jobs;

Query the above command and make sure there is no more the job found in step 1.

Truncating a table

Today we received a request to truncate the table having 0 records but truncate taking too long time.

Investigation went in the following way:

1) Identify if there are any locks on the table, and gather the session information holds the lock on the table or issue the truncate command and in another session check for the blocking sessions then we get the session information which is blocking truncate the table.

2) I found a session is active on the table and session is doing nothing by checking event column from v$session and session is two months older.

3) Since session was doing no productive work and got confirmation to kill the session. I have killed the session and got replied as session marked for kill and status of the session is killed but still couldn't truncate the table.

4) Waited for day, Still the same issue persists.

5) Identified SPID of the session and found still the os process exists in the server for the respective session.

6) Killed the os process and able to truncate the table.

Interview Questions

1) What are the precautions to be used before truncating a table?

A) We need to disable all the constraints associated to the table and need to drop the triggers on the table.

2) We have restored and recovered the database with 11.2.0.3 backup on 11.2.0.4 successfully, Can we open the database with reset logs?

A) No, We can not open the database with reset logs. We need to open the database as alter database open resetlogs upgrade and then upgrade the database to 11.2..4 by running running  catupgrd.sql. Then Startup the database and check the invalid objects in the database.

3) Are the password case sensitive or case in-sensitive in Oracle 11g?

A) The passwords from 11g are case sensitive which are controlled by sec_case_sensitive_logon initialization parameter, If the value is true then will enforce the case sensitivity. From 11g on wards the password column returns nothing.

4) Can we alter the user's password to same password?

A) Yes, We can do it from 11g by alerting the password_verify_function resource to null.

5) Do we take online backup using RMAN when database is in no archive log mode?

A) No, The database must and should running archivelog mode to take online backup using RMAN or hot backup.

6) Suppose I have a table with three columns say column A, Column B and Column C, among them Column A is primary key column and column B is not null column. Now I’m going to insert the data in to the table, Could you please let me know what are the columns must and should have some data in the insert statement?

A) Column A and Column B Since Constraints are imposed on the columns.

7) If I missed value for Column C, Will the insert statement will complete?

A) Yes

8) What value will be stored in Column C?

A) Nothing.

9) Are the database passwords are case sensitive from which version?

A) Oracle 11g