ORACLE ASM DEMYSTIFIED FOR PERSONAL USE AND INTERVIEWS

1. How to stop ASM?

Ans:-  I have issued the following command.

srvctl stop ASM


It shows the following errors:-
PRCR-1065 : Failed to stop resource ora.asm
CRS-2673: Attempting to stop 'ora.asm' on 'vienna'
CRS-5017: The resource action "ora.asm stop" encountered the following error:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "DATA" precludes its dismount
. For details refer to "(:CLSN00108:)" in "/u01/app/grid/diag/crs/vienna/crs/trace/ohasd_oraagent_grid.trc".

CRS-2675: Stop of 'ora.asm' on 'vienna' failed

CRS-2675: Stop of 'ora.asm' on 'vienna' failed

Same is the result when I have tried to stop it with the SQL plus utilities. So what could be the possible reasons?

1. The disk groups are mounted. If disk groups are remain mounted then we can not stop the ASm instance. Here I have a standalone ASM. I have installed the GRID only without any database in it.

crs_stat -t
The above command shows that lots of disk groups are in online . 
 
Name           Type           Target    State     Host
------------------------------------------------------------
ora.DATA.dg    ora....up.type OFFLINE   OFFLINE
ora....ER.lsnr ora....er.type ONLINE    ONLINE    vienna
ora....N1.lsnr ora....er.type ONLINE    ONLINE    vienna
ora.MGMTLSNR   ora....nr.type OFFLINE   OFFLINE
ora.asm        ora.asm.type   OFFLINE   ONLINE    vienna
ora.cvu        ora.cvu.type   ONLINE    ONLINE    vienna
ora....network ora....rk.type ONLINE    ONLINE    vienna
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    vienna
ora.ons        ora.ons.type   ONLINE    ONLINE    vienna
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    vienna
ora....SM1.asm application    OFFLINE   ONLINE    vienna
ora....NA.lsnr application    ONLINE    ONLINE    vienna
ora.vienna.ons application    ONLINE    ONLINE    vienna
ora.vienna.vip ora....t1.type ONLINE    ONLINE    vienna


select name,state from v$asm_diskgroup;

The above command shows that the 

NAME                           STATE
------------------------------ -----------
DATA                           QUIESCING


Here the state is QUIESCING. Normal case it should be mounted.

Now why in my case it is showing QUISCING?

I copied the below few lines from the oracle docs:-
QUIESCING - CRSCTL utility attempted to dismount a disk group that contains the Oracle Cluster Registry (OCR). The disk group cannot be dismounted until Cluster Ready Services (CRS) exits, because the disk group contains the OCR.

So this disk groups contains the OCR informations. 

So at first in my case we have to stop crsctl.

So to stop CRS in the current node, we ahave to logged in as a root user.

Go to the /u01/app/12.1.0/grid/bin path

and run the command

./crsctl stop crs

So now check the status of the disk groups.

select name,state from v$asm_diskgroup;

The result is showing :-

select name,state from v$asm_diskgroup
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 28832
Session ID: 176 Serial number: 1945


That means The ORACLE ASM has been stopped directly from the QUIESCING state as I stopped the CRS.

N.B:-  Here I am showing all these for example purpose. My current state is Partially installed GRID
only in node1. Do not stop CRSCTL or ASM without taking proper precautions.

From the above example we have found that we have to start or stop crs service first. then we can startup or shutdown the ASM.




2. How to start ASM?

Ans:- Now to start ASM in this above case we first need to start up crs as

./crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

 It will start the ASMalso.

Oracle 1z0-062 questions and answers with explanations (SET 1)

Hi guys,

All the explanations are copied from different sites and I have also tried my best to attach all the corresponding links. This is made only to help those guys who are planning to take 062 exam.

1. An application accesses a small lookup table frequently. You notice that the required data blocks
   are getting aged out of the default buffer cache.
   How would you guarantee that the blocks for the table never age out?

    options:
A. Configure the KEEP buffer pool and alter the table with the corresponding storage clause.
B. Increase the database buffer cache size.
C. Configure the RECYCLE buffer pool and alter the table with the corresponding storage clause.
D. Configure Automatic Shared Memory Management.
E. Configure Automatic Memory Management-


Ans: A

Explanation:

Keep Buffer Pool:

The purpose of the KEEP buffer pool is to retain objects in memory, thus avoiding I/O operations. Each object kept in memory results in a trade-off. It is more beneficial to keep frequently-accessed blocks in the cache.
If there are certain segments in your application that are referenced frequently, then consider storing the blocks from those segments in the KEEP buffer pool. Typical segments that are kept in the KEEP pool are small, frequently-used reference tables. To determine which tables are candidates, check the number of blocks from candidate tables by querying the V$BH view,

 Recycle Buffer Pool:

You can configure a RECYCLE buffer pool for blocks belonging to those segments that you do not want to keep in memory. The purpose of the RECYCLE pool is to retain segments that are scanned rarely or are not referenced frequently. If an application randomly accesses the blocks of a very large object, then it is unlikely for a block stored in the buffer pool to be reused before it is aged out. This is true regardless of the size of the buffer pool (given the constraint of the amount of available physical memory). Consequently, the object's blocks do not need to be cached; the cache buffers can be allocated to other objects.
Do not discard blocks from memory too quickly. If the buffer pool is too small, then blocks can age out of the cache before the transaction or SQL statement completes its execution. For example, an application might select a value from a table, use the value to process some data, and then update the record. If the block is removed from the cache after the SELECT statement, then it must be read from disk again to perform the update. The block should be retained for the duration of the user transaction.

Automatic Memory Management

Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.

and for AMM and ASMM:

Both are related to total memory components:

Difference in ASMM and AMM 

Diff. between ASMM and AMM 

 B is not correct because only increasing  the buffer cache does not guarantee that the table block will remain in the memory.

       

• You conned using SQL Plus to the root container of a multitenant container database (CDB) with
  SYSDBA privilege.
  The CDB has several pluggable databases (PDBs) open in the read/write mode.
  There are ongoing transactions in both the CDB and PDBs.
  What happens alter issuing the SHUTDOWN TRANSACTIONAL statement?

•  options:
•  

A. The shutdown proceeds immediately.
The shutdown proceeds as soon as all transactions in the PDBs are either committed or rolled back.
B. The shutdown proceeds as soon as all transactions in the CDB are either committed or rolled
back.
C. The shutdown proceeds as soon as all transactions in both the CDB and PDBs are either
committed or rolled back.
D. The statement results in an error because there are open PDBs.

 Ans: B

Explanations:

In the above questions it actually wants to know will it wait for all the PDB transactions also, if the "shutdown transaction command issued in root container.

It will not wait :

Shutdown root 

 shutdown startupmultitient

Shutdown commands that wait for current calls to complete or users to disconnect such as
SHUTDOWN NORMAL and SHUTDOWN TRANSACTIONAL have a time limit that the
SHUTDOWN command will wait. If all events blocking the shutdown have not occurred within the
time limit, the shutdown command cancels with the following message:
ORA-01013: user requested cancel of current operation

• Question:
• You are planning the creation of a new multitenant container database (CDB) and want to store
  the ROOT and SEED container data files in separate directories.
  You plan to create the database using SQL statements.
  Which three techniques can you use to achieve this?
• Options:
  A. Use Oracle Managed Files (OMF).
  B. Specify the SEED FILE_NAME_CONVERT clause.
  C. Specify the PDB_FILE_NAME_CONVERT initialization parameter.
  D. Specify the DB_FILE_NAMECONVERT initialization parameter.
  E. Specify all files in the CREATE DATABASE statement without using Oracle managed Files 

Answer: A,B,C

Explanation: why not D?

DB_FILE_NAME_CONVERT 'string_pattern'
Specifies a rule for creating the filenames for duplicate datafiles and tempfiles. Note that DB_FILE_NAME_CONVERT specified on the DUPLICATE command overrides the initialization parameter DB_FILE_NAME_CONVERT if it is set in the initialization parameter file.

 Example:
DUPLICATE TARGET DATABASE TO dup1
  FROM ACTIVE DATABASE
  DB_FILE_NAME_CONVERT '/disk1','/disk2'
  SPFILE
    PARAMETER_VALUE_CONVERT '/disk1', '/disk2'
    SET LOG_FILE_NAME_CONVERT '/disk1','/disk2'
    SET SGA_MAX_SIZE '200M'
    SET SGA_TARGET '125M';

The PARAMETER_VALUE_CONVERT option substitutes /disk2 for /disk1 in all initialization parameters that specify filenames (with the exception of DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT). The SET LOG_FILE_NAME_CONVERT clause substitutes /disk2 for /disk1 in the filenames of the online redo logs of the duplicate database. The DB_FILE_NAME_CONVERT option replaces /disk1 with /disk2 in the names of the duplicate datafiles and tempfiles.

DB_FILE_name_convert 

Also E is not correct as this is not allowed for seed or root.

question:
You use a recovery catalog for maintaining your database backups.
You execute the following command:
$rman TARGET / CATALOG rman / cat@catdb
RMAN > BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
Which two statements are true?
options:
Corrupted blocks, if any, are repaired.
Checks are performed for physical corruptions.
Checks are performed for logical corruptions.
Checks are performed to confirm whether all database files exist in correct locations
Backup sets containing both data files and archive logs are created.

Answer: here the command used
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

And the question is what this command will do or rather why we will use this command. After reading few documents I came to know that:

This BACKUP VALIDATE command in common is used to validate the physical and logical corruptions and the location and existence of the data files. When we fire the command, it will populate the corrupted files name if exist to a view called v$database_block_corruption.

Now as in the above command logical block check is not included, so it willonly check the physical block corruptions and the check the locations of the data files.

Now if we use the following command,then it will check both the logical and physical corruptions.

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVE ALL

so answers are

D.
Checks are performed to confirm whether all database files exist in correct locations

Checks are performed for physical corruptions. onlyin this case.

Questions:

Which three statements are true concerning the multitenant architecture?

Each pluggable database (PDB) has its own set of background processes.
A PDB can have a private temp tablespace.
PDBs can share the sysaux tablespace.
Log switches occur only at the multitenant container database (CDB) level.
Different PDBs can have different default block sizes.
PDBs share a common system tablespace.
Instance recovery is always performed at the CDB level.


ANSWERS WITH EXPLANATION:
 To answer this question we need the architecture level concept: So I am attaching two pictures:

source:https://www.toadworld.com/cfs-file/__key/communityserver-wikis-components-files/00-00-00-00-03/JC052714_2D00_1.png



 source:https://oracle-base.com/articles/12c/images/multitenant-overview/multitenant-overview.png




In the above two sites it is mentioned briefly.


 so correct answers are

B.
A PDB can have a private temp tablespace.
D.
Log switches occur only at the multitenant container database (CDB) level.
G.
Instance recovery is always performed at the CDB level.

 AS both undo and redo belongs to CDB only. SO this will happen only at CDB level.



  

Basic sql interview questions

1.What are the main five types of sql Statements present?
Ans.

• Data Manipulation Statement (DML).
• Data Definition Language Statement (DDL).
• Transaction Control Statement (TC).
• Data Control Language Statement (DCL).
• Query Statement.

2. What do you mean by Data Manipulation Language (DML) statements?
Ans. It is mainly used to modify the content of the tables.The three DML statements are

• Insert
• Update
• Delete

3. What do you mean by Data Definition Language (DDL) statements?

Ans. It mainly defines the data structure of a tables. That means whether to alter a table or create a table.

There are five basic types DDL.

• Create
• Alter
• Truncate
• Drop
• Rename

4. What do you mean by  TC statements?

Ans.It mainly records permanently any data changes or undo those changes.

There are three main types of TC presents.

• commit
• rollback
• savepoint

5.What do you mean by  DCL statements?

Ans. It is related to the permission of my own database structure.There are mainly two types of DCL statements present.

• Revoke: Prevents another user to access your data base.
• Grant: Gives another user's access to my database structure

6.Write down the statement to create a "customers" table .

Ans.CREATE TABLE customers (
customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
dob DATE,
phone VARCHAR2(12)
);

7.what is constraints used above?

Ans.

The CONSTRAINT clause indicates that the customer_id column is the primary key.You should always name your primary key constraints, so that when a constraint error occurs it is easy to spot where it happened.So in the above case if you enter the same value for the customer id in two times,then the error massage will be like..unique constraint (SYSTEM.CUSTOMERS_PK) violated... here any name can be attached instead of customers_pk.

8.Use of foreign key?
Ans.Actually foreign key points the primary key of another table.
person's table...

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	2
4	24562	1

Here in the person's table, the P_ID  column is the primary key.And in the orders table the P_ID column is the foreign key.That means,we can't enter any values in this P-ID column.These two tables are linked.We can't enter values in orders table in P-ID column other than 1,2,3.

Foreign key written in the following way.
IN MYSql..
create table Orders(
O-ID int,
OrderNo int,

FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

);