Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Thursday, November 28, 2019

Python simple program:

Print multiplication table of a number:
===========================
num = int(input("Enter the number: "))
print("Multiplication Table of", num)
for i in range(1, 11):
   print(num,"X",i,"=",num * i)

Print table of a number:
=================
num = int(input("Enter the number: "))
print("Multiplication Table of", num)
for i in range(1, 11):
   print(num * i)

Print sum of n natural number:
=======================
num = int(input("Enter the value of n: "))
hold = num
sum = 0
if num <= 0: 
   print("Enter a whole positive number!") 
else: 
   while num > 0:
        sum = sum + num
        num = num - 1;
   print("Sum of first", hold, "natural numbers is: ", sum)

Factorial of Number:
================
def factorial(num):
    """This is a recursive function that calls
   itself to find the factorial of given number"""
    if num == 1:
        return num
    else:
        return num * factorial(num - 1)
num = int(input("Enter a Number: "))
if num < 0:
    print("Factorial cannot be found for negative numbers")
elif num == 0:
    print("Factorial of 0 is 1")
else:
    print("Factorial of", num, "is: ", factorial(num))

Convert decimal to binary:
====================
def decimalToBinary(num):
   #This function converts decimal number to binary and prints it
    if num > 1:
        decimalToBinary(num // 2)
    print(num % 2, end='')
number = int(input("Enter any decimal number: "))
decimalToBinary(number)




https://dbaclass.com/monitor-your-db/



Monday, November 4, 2019

STARTUP Database failed ORA-38760 to turn on Flashback Database:

Error:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

Solution:
There are 2 possible solutions when a flashback log file was deleted manually:
(1) Turn off Flashback:
SQL> alter database flashback off;

After this turn the flashback on again if required.
SQL> alter database flashback on;

(2) In cases where "Guaranteed Restore Point" is defined, RVWR will still try to write to flashback log even though you have disable it and it will fail again with same error:

Check if flashback database is disable or not. When checked the flashback status after disabling flashback, it shows 'RESTORE POINT ONLY' instead of 'NO'.

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

FLASHBACK_ON - possible values are as follows:

    YES                - Flashback is on
    NO                 - Flashback is off
    RESTORE POINT ONLY - Flashback is on but one can only flashback to guaranteed restore points

So the cause of the issue is Guaranteed Restore Point created on database.
The database would still try to write flashback data to the current flashback log because the database still has at least one Guaranteed Restore Point declared in the controlfile.

Find out the name of Guaranteed Restore Point and delete so that database would not try to write to flashback log on startup:

Now we have 3 options to know the restore point name:

2.1) Check the name from v$restore_point view but that would also fail with same error:

SQL> select * from v$restore_point;
SQL>select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;

if this also error out.

2.2) Search for restore point name in alert log. In this case customer was purging alert log every year starting so could not find name for the Restore Point.

2.3) Dump the controlfile to get the restore point name:

SQL> oradebug setmypid
SQL> alter session set events 'immediate trace name controlf level 9';
SQL> oradebug tracefile_name

From trace file of controlfile dump, we could see below information:

RESTORE POINT #1:
 restore point name: <restore point test> guarantee flag: 1 incarnation: 2next record 0                            <<<< Name of restore point

Now we have name of Guaranteed Restore Point:

SQL> Drop restore point <restore point test>;

SQL> alter database open;

***This error may also occur if the FRA is full and thus a flashback log cannot be created.  Check V$flash_recovery_area_usage and/or V$recovery_file_dest regarding space availability in FRA. 

Reference doc id:
STARTUP Database failed ORA-38760 to turn on Flashback Database (Doc ID 1554596.1)

Removing Managed Server Instance

Process is similar to adding the managed server.

Done using the adProvisionEBS.pl and txkSetAppsConf.pl scripts

Execute adProvisionEBS.pl with option ebs-delete-managedserver. This will delete the managed server, and also update the respective context variables.

Run txkSetAppsConf.pl, Using the option configoption=removeMS.This removes details of the deleted managed servers from the OHS configuration files mod_wl_ohs.conf and apps.conf.

Restart HTTP server using adapcctl.sh script

Managing Managed Server Configurations:

 By default,application tier will have only a single instance of the oacore, oafm, forms and forms-c4ws services.

To increase the Web logic performance, we need to add up managed servers.

Filesystem: to be used run

There should  not be any active ADOP cycle.

Managed server additionbe removal  should be done only through scripts: adProvisionEBS.pl and txkSetAppsConf.pl 

Never use WebLogic Administration Console.

1.Execute the following command to add a new managed server. This will create a managed server and add a new entry to the context file for starting and stopping the new managed server via the adstrtal and adstpall scripts

$ perl <AD_TOP>/patch/115/bin/adProvisionEBS.pl \

ebs-create-managedserver -contextfile=<CONTEXT_FILE> \

-managedsrvname=<MANAGED_SERVER_NAME> -servicetype=<SERVICE_TYPE> \

-managedsrvport=<MANAGED_SERVER_PORT> -logfile=<LOGFILE>

$ perl <AD_TOP>/patch/115/bin/adProvisionEBS.pl \

ebs-create-managedserver -contextfile=<CONTEXT_FILE> \

-managedsrvname=oacore_server2 -servicetype=oacore \

-managedsrvport=7203 -logfile=<APPLRGF>/TXK/addMS_oacoreserver2.log

After completed scripts it’s created new oacoreserver2

2.Perform the following steps on all application tier nodes participating in the same cluster where this managed server is added:

Source the run file system.

Execute the following command to add details of the newly added managed servers into the OHS configuration files mod_wl_ohs.conf and apps.conf on the current node:

$ perl <FND_TOP>/patch/115/bin/txkSetAppsConf.pl \

-contextfile=<CONTEXT_FILE> \

-configoption=addMS \

-oacore=<host>.<domain>:<port> \

-oafm=<host>.<domain>:<port> \

-forms=<host>.<domain>:<port> \

-formsc4ws=<host>.<domain>:<port>

where

The argument contextfile accepts the complete path to the context file.

The arguments oacore, oafm, forms, formsc4ws accept a comma-separated list of managed server details in the following format:

<host>.<domain>:<port>

host and domain are the hostname and domain name of the newly added node port is the port of the new managed server whose reference is to be added

For example, if the managed server oacore_server2 has been added on host 'testserver' and domain 'example.com' with port 7205, the following command should be executed: 

$ perl <FND_TOP>/patch/115/bin/txkSetAppsConf.pl -contextfile=<CONTEXT_FILE> \

-configoption=addMS -oacore=testserver.example.com:7205

3.perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE

This script is used to explicitly pull the values of the WebLogic Server and Oracle HTTP Server configuration parameters and synchronize the corresponding context variable values accordingly.

4.Startup the New  managed server

$ sh <ADMIN_SCRIPTS_HOME>/admanagedsrvctl.sh start <MANAGED SERVER NAME>

5.If Oracle HTTP Server is enabled on the node, restart it as follows:

On UNIX:

$ sh <ADMIN_SCRIPTS_HOME>/adapcctl.sh stop

$ sh <ADMIN_SCRIPTS_HOME>/adapcctl.sh start

6.Run fs_clone :

During the next adop prepare, the Configuration Change Detector identifies that the addition has been made and the managed servers are automatically synced up from the run file system to the patch file system. The synchronization also gets done when fs_clone is executed.

adop option cleanup hung forever:

Error:

ORA-04020: deadlock detected while trying to lock object SYS.DBMS_SYS_SQL SQL: begin sys.ad_grants.cleanup; end;


Solution:
Connect to SQL*PLUS as APPS
 SQL>select count(1)
  from dba_tab_privs
  where table_name='DBMS_SYS_SQL'
  and privilege='EXECUTE'
  and grantee='APPS'
If retun row.
Run the following command: "exec sys.ad_grants.cleanup;"

 SQL>select count(1)
  from dba_tab_privs
  where table_name='DBMS_SYS_SQL'
  and privilege='EXECUTE'
  and grantee='APPS'
 
verify 0 rows are returned

Doc id: Adop Cleanup Issue: "[ERROR] [CLEANUP] ORA-04020: deadlock detected " (Doc ID 2424333.1)

Oracle Database Interactive Quick Reference:

12c

11g

Sunday, November 3, 2019

database character set

A character set determines what languages can be represented in the database.
Oracle recommends Unicode AL32UTF8 as the database character set. Unicode is the universal character set that supports most of the currently spoken languages of the world.

As AL32UTF8 is a multibyte character set, database operations on character data may be slightly slower when compared to single-byte database character sets, such as WE8MSWIN1252.


AL16UTF16 is only available as a choice for the National Character Set. If you want the database itself to be Unicode you can choose from UTF8 and AL32UTF8. AL32UTF8 is generally preferred because of its ongoing support of the Unicode Standard.

In most cases about 99% of the time, if you are using AL32UTF8 for your database character set, you wont make use of your national character set at all, so it doesnt matter.

If your database character set were AL32UTF8, there would be no advantage of using UTF-8 as your national character set. Occasionally, there may be a benefit to using UTF-16 for your national character set because that would potentially allow you to store Asian characters in less space (most Asian characters require 3 bytes to encode in UTF-8 and 2 bytes to encode in UTF-16).

select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

SELECT * FROM NLS_DATABASE_PARAMETERS;

SELECT UNIQUE VALUE FROM V$NLS_VALID_VALUES WHERE PARAMETER ='CHARACTERSET' AND isdeprecated = 'FALSE';

SELECT NLS_CHARSET_ID('WE8MSWIN1252') FROM dual;

SELECT * FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET%';

select * from nls_database_parameters  where parameter='NLS_CHARACTERSET';

SELECT * FROM NLS_DATABASE_PARAMETERS;

Saturday, November 2, 2019

Oracle DBA Interview Questions

Question related to Oracle database:

What is an Oracle Instance?
What information is stored in Control File?

When you start an Oracle DB which file is accessed first?

What is RDBMS?


Question related to Memory:

What is the Job of SMON, PMON processes?
What is a Checkpoint?
Which Process reads data from Datafiles?
Which Process writes data in Datafiles?
What is a Shared Pool?
What is PGA_AGGREGRATE_TARGET parameter?
Large Pool is used for what?
What is DB Buffer Cache Advisor?
Can you change SHARED_POOL_SIZE online?
When creating a new database, how would you handle the memory allocation?
Name some memory areas Oracle uses,and state their purpose?
What is difference between Multithreaded/Shared Server and Dedicated Server?
How does 32-bit Oracle compare with 64-bit in terms of memory restrictions?
What are the efects of using bind variables?
How would you ensure that a table’s blocks stayed in the SGA?
Why would a DBA pin packages into memory?
Explain what the parameter session_cached_cursors is used for?
What is kept in the Database Buffer Cache?


Question related to Transactions:

Explain how Oracle achieves read consistency?
Explain an ORA-01555 / Snapshot too old error and how you might prevent them?
What happens when a commit is issued?

A commit is a “end transaction” marker and make permanent all changes performed in the transaction.  A transaction is a sequence of SQL statements that Oracle Database treats as a single unit.

What happens when a Rollback hapens?




Some Backup and Recovery Questions:


What is written in Redo Log Files?
How many maximum Redo Logfiles one can have in a Database?
Explain the differences between an export, a hot backup and a cold backup and the benefits associated with each?
What is COMPRESS and CONSISTENT setting in EXPORT utility?
What is the difference between Direct Path and Convention Path loading?
What files are required to be included in a complete hot/online backup?
Explain the differences between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages of each?
Can you take partial backups if the Database is running in NOARCHIVELOG mode?
Can you take Online Backups if the the database is running in NOARCHIVELOG mode?
How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?
You cannot shutdown the database for even some minutes, then in which mode you should run the database?
How can you backup a control file?
What are redo logs? Explain their importance?
What happens duiring a instance recover?
Explain what effect “NOLOGGING” commands have on the database? How would this affect standby databases and point in time recovery?
A large amount of data has been deleted by a user of an application. The data can’t be re-entered via the application because it’s not all available. What options would you investigate them?
A datafile has been deleted while the database was open and being used. What would you do?
Scenario: An RMAN backup is multiplexing all of the datafiles to one tape drive. (Filesperset>1 channels >1). What are the implications? How would you improve the situation?
Can you import objects from Oracle ver. 7.3 to 9i?
Scenario: A busy database only has a full backup to tape each day using RMAN. 250 GB of archivelogs are generated per day. What are the implications? How would you improve the situation?
Where should you place Archive logfiles, in the same disk where DB is or another disk?
Can you take online backup of a Control file if yes, how?
What is a Logical Backup?
Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?
Why do you take tablespaces in Backup mode?
What is the advantage of RMAN utility?
How RMAN improves backup time?
Can you take Offline backups using RMAN?
How do you see information about backups in RMAN?
What is a Recovery Catalog?
Should you place Recovery Catalog in the Same DB?
Can you use RMAN without Recovery catalog?
Can you take Image Backups using RMAN?
Can you use Backupsets created by RMAN with any other utility?
Where RMAN keeps information of backups if you are using RMAN without Catalog?
You have taken a manual backup of a datafile using o/s. How RMAN will know about it?
You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?
Which is more efficient Incremental Backups using RMAN or Incremental Export?
Can you start and shutdown DB using RMAN?
How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?
You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?
You lost some datafiles and you don’t have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?
How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?
You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?
You loss controlfile how do you recover from this?
The current logfile gets damaged. What you can do now?
What is a Complete Recovery?
What is Cancel Based, Time based and Change Based Recovery?
Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?
Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?
A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don’t have any backups?
How do you recover from the loss of a controlfile if you have backup of controlfile?
Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?
Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?
Have you faced any emergency situation? Tell us how you resolved it?
At one time you lost parameter file accidentally and you don’t have any backup. How you will recreate a new parameter file with the parameters set to previous values.


Some Lock and Latches Questions:

What are latches used for?

What happens when a session has to wait for a latch?

Explain what deadlocks are and how to avoid them?

When do indexes help to reduce lock contention?

Some Storage Questions:

What is Automatic Management of Segment Space setting?

How do you control number of Datafiles one can have in an Oracle database?

How many Maximum Datafiles can there be in an Oracle Database?

Can you make a Datafile auto extendible? If yes, how?

What is a Locally Managed Tablespace?

Can you redefine a table Online?

Explain the relationship between a data block, an extent and a segment?

.What is the difference between Range Partitioning and Hash Partitioning?

What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?

How do you move tables from one tablespace to another tablespace?

How do you add a datafile to a tablespace?

How do you resize a datafile?

What view would you use to look at the size of a datafile?

What view would you use to determine free space in a (non-tempfile) tablespace?

What is PCT Increase setting?

Describe what the PCT_FREE setting does.

What are chained rows and migrated rows? What could you do to limit their effect on performance?

Some Performance Questions:


When should you rebuild indexes?

Can you built indexes online?

Can you see Execution Plan of a statement?

How would you determine what session’s arer connected and what resources they are waiting for?

How can you gather statistics on a table?

How often you should collect statistics for a table?

How do you collect statistics for a table, schema and Database?

Can you make collection of Statistics for tables automatic?

How can you enable a trace for a session?

How would you go about generating an EXPLAIN plan?

How would you go about increasing the buffer cache hit ratio?

Name some Oracle performance tools or sources of statistics?

How would you begin investigating a performance problem, beginning from a user’s complaint?

What is Cost Based Optimization?

What is STATSPACK tool?

Can you assign Priority to users?

Some Data Warehouse Questions:

What characterises a data warehouse as opposed to an OLTP database?

Which Oracle features are commonly useful to data warehouse databases?

Briefly describe how each of the features from the previous question work?

What tools might be used for ETL?


 Some Development Support Questions:


List and describe typical environments used in the development life of an application? (Eg. Production, development, QA,etc)

How would you manage the promotion of changes through the environments to ensure success when released into production?

How can a DBA help developers to design an application?

Explain the differences between a FUNCTION, PROCEDURE, PACKAGES and anonymous PL/SQL blocks?

After an import of a schema has finished, what should you check before notifying the users that the database can be used?

Give two types of referential integrity constraints?

How would you create a copy of a database with new name on a different server?

You have just compiled a PL/SQL package but got some errors. How would you view the errors?

On which columns you should create Indexes?

What types of Indexes are available in Oracle?

What is B-Tree Index?

A table is having few rows, should you create indexes on this table?

A Column is having many repeated values which type of index you should create on this column, if you have to?

What is an Index Organized Table?

What is a Global Index and Local Index?


Some Questions about RAC:

Briefly describe what RAC is, and what the intended benefits are?

What are two essential RAC hardware components?

What are two methods of RAC load balancing?

What does cache fusion mean?

What is the voting disk for?

What is the hangcheck timer for in Linux?

What performance issues should be considered when migrating an application to RAC?

Some Replication Questions:

Describe some types of Oracle Replication?

What is the difference between a complete refresh and a fast refresh?


Some Unix/Linux Questions:

How do you see how many instances are running?

How do you automate starting and shutting down of databases in UNIX?

How do you list the files in an Unix directory while also showing hidden files?

You have written a script to take backups. How do you make it run automatically every week?

How do you execute a UNIX command in the background?

What is OERR utility?

How do you see Virtual Memory Statistics in Linux?

How do you see how much hard disk space is free in Linux?

What is SAR?

What is SHMMAX?

Swap partition must be how much the size of RAM?

What is DISM in Solaris?

How do you see how many memory segments are acquired by Oracle Instances?

How do you see which segment belongs to which database instances?

What is VMSTAT, IOSTAT and NETSTAT?

How would you change all ocurrences of a value using VI?

How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?

Give two kernel parameters that affect an Oracle install?

How do you remove Memory segments?

What is the difference between Soft Link and Hard Link?

What is stored in oratab file?

What unix command will control the default file permissions when files are created?

Give the command to display space usage on the UNIX file system?

Explain the read, write, and execute permissions on an UNIX directory?

How do you see how many processes are running in UNIX?

How do you kill a process in UNIX?

Can you change priority of a Process in UNIX?

Briefly, how do you install Oracle software on UNIX?

Miscellaneous Questions:


How do you delete duplicate rows in a table?

Which default Database roles are created when you create a Database?

Name three files that can be used to configure SQL*Net for server and client?

What problems are associated with the use of generic user ids?

Where would you look for errors from the database engine?

An application is generating an ORA-00600 error in the database. What would you do about it?

Give two examples of how you might determine the structure of the table “DEPT”?

What is the difference between an pfile and spfile?

Compare TRUNCATE and DELETE for a table?

Give the stages of instance startup to a usable state where normal users may access it?

When creating a user, what are the minimun permissions that must be granted to allow them to connect to the database?

How would you find out what a particular pfile does?

What methods can you use to compact an index?

A table has been identified as needing reorganization. How would you plan for it? How would you implement it?

A user can’t loggin to a remote Oracle database via an application using SQL*Net from their PC. What would you do to determine the cause?

A database is not shutting down 15 minutes after the shutdown immediate command was issued. What would you do?

An error appears about a table that can’t extend due to insufficient space in the tablespace. What would you do?

You have been asked to write the requeriments for an automatic “health check” system for a database. What things would you include?

You have been presented with a UNIX database server, and have been logged in as the root user. How would you find the databases and obtain access to them?

Can you audit SELECT statements?

What does DBMS_FGA package do?

You want users to change their passwords every 2 months. How do you enforce this?

Can you disable and enable Primary key?