Archive

Posts Tagged ‘Oracle’

Oracle RAC on Extended Cluster

January 4, 2019 1 comment

I have recently worked with Oracle RAC on Extended Cluster, and I would like to share here some knowledge I acquired regarding this technology.

This is an alternative to Oracle Data Guard, which provides an almost immediate replication method between two sites. However, it requires the availability of a fast network between the two sites, for the interconnect.

Let’s imagine the following scenario: We have a production database hosted in our main building. Our enterprise has also a data center in another building, which is located 10 km away. How can we install a solution to replicate the data from our primary site to our standby site?

Here we have different options: we can install and configure DataGuard, creating a standby database in our second building. We can even use Active Dataguard to allow the standby database to the open in order to perform queries or reports.

But we can also have RAC on Extended Cluster. In this case, the database will be integrated in a RAC, where node1 will be our main site, and node2 will be our secondary site.

The application will then access only to the primary site, as it was done normally. The secondary site will have a separate storage, and the data will be replicated through the interconnect. This requires that the bandwidth for the interconnect is high enough, but the recovery would be almost immediate in case of failure, as the data will be always be available after commit in both sites. This is not the case on Dataguard, as the data is replicated only after the generation of archivelogs.

But then we face a new problem, how do we solve brain splitting? If we have two nodes, and the interconnect has a failure but both nodes are up, which node will know that he is the node that needs to be up, and which node needs to be shutdown?

To solve this problem, Voting Disk has a major importance in this scenario. We need a third server allocating a Voting Disk, and visible from both Oracle servers. This voting disk will act as a third vote to decide which server will remain up in case of failure, and which server is unavailable.

This third server can be an Oracle server, and we would allocate the voting disk in an ASM instance. But it is also possible to use an inexpensive NFS device for this purpose.

To do this, you can use the following command:

[root@nodo1]# crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE xxxxxxxxxxxx (/nfs/cluster1/vote1) []
2. ONLINE xxxxxxxxxxxxx (/nfs/cluster2/vote2) []

Then, add the third voting disk on cluster node 3 (the mount point must be created previously):

[root@nodo1 /]# crsctl add css votedisk /nfs/cluster3/vote3
Now formatting voting disk: /nfs/cluster3/vote3.
CRS-4603: Successful addition of voting disk /nfs/cluster3/vote3.

Using this method, we will be able to provide service in the standby database immediately in case the primary database fails, as the standby database will always be available. After all, it’s a RAC.

Additionally, we can use the standby node as primary node for another database, using the primary site as a standby site for this second database. By doing this, we will use more effectively the resources of the server.

Incremental restore from primary database to standby database

January 21, 2016 Leave a comment

If we have Dataguard, either with physical or logical standby, our databases will always be synchronized and we will be able to do switchover whenever we want.

Or not… maybe when we want to do a switchover, we notice that the standby database hasn’t been synchronized in months, and we need to restore the synchronization as soon as possible.

If the gap resolution is not an option, we will have to use RMAN in order to perform this operation. The steps will be the following ones:

1. On standby site, identify the minimum SCN used by all the datafiles:

SQL> select min(CHECKPOINT_CHANGE#) from v$datafile_header;

MIN(CHECKPOINT_CHANGE#)
———————–
262044906

2. On primary site, take an incremental backup of the database using a number a bit smaller than the smallest SCN obtained.

backup as compressed backupset incremental from scn 262044600 database format ‘/opt/oracle/install/restore_syncr/full_backup_mario_%U’;

3. Take also a backup of the controlfile:

backup as copy current controlfile format ‘/opt/oracle/install/restore_syncr/backup_control_file_mario’;

4. Copy the files to standby site (scp, ftp, diskettes)

5. Execute the following commands on RMAN in order to apply the incremental backup:

shutdown immediate;
startup force nomount;
restore standby controlfile from ‘/opt/oracle/install/restore_syncr/backup_control_file_mario’ ;
alter database mount;
catalog start with ‘+DATA/mario/datafile’ ;
catalog start with ‘/opt/oracle/install/restore_syncr/’ ;
switch database to copy ;

run {
allocate channel a0 device type disk ;
allocate channel a1 device type disk ;
allocate channel a2 device type disk ;
allocate channel a3 device type disk ;
allocate channel a4 device type disk ;
allocate channel a5 device type disk ;
allocate channel a6 device type disk ;
allocate channel a7 device type disk ;
allocate channel a8 device type disk ;
allocate channel a9 device type disk ;

recover database ;
}

6. Start the MRP process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT ;

7. Enjoy your synchronized database and make sure that you don’t have to restore it again

Changing sys password in RAC databases

May 24, 2011 5 comments

If you ever have worked with a RAC database, and you have changed a normal user password, you will notice that it has no difference between a single instance database. You just have to perform “ALTER USER xxx IDENTIFIED BY yyyy” and your password will be changed.

However, today I had a problem with a RAC database. I had to change the SYS password, and I did it the same way I would do in a single-instance database. But when I tried to connect with sys user, the error “ORA-01017: invalid username/password; logon denied” appeared. What I was doing wrong?

After a few thoughts, I found the solution. SYS password is instance specific in RAC databases, so you have to change it in every single instance. That’s all.

Removing an Oracle Instance in silent mode

February 14, 2011 3 comments

This post will be simple but really efective. If you want to delete an instance in silent mode, without using any GUI, you just have to write the following as oracle user:

 

dbca -silent -deleteDatabase -sourceDB YOUR_DATABASE

 

I hope you will find this command as useful as it is for me ūüôā

Categories: linux, oracle Tags: ,

3+Years Oracle DBA Interview Questions

February 9, 2011 105 comments

In response for a reader of this blog who asked me for some interview questions for a 3+Year experienced DBA, I have written this series of questions that you can use in your interviews. Either if you are the interviewer, or the interviewer. Enjoy!

1. Basic (Every DBA should answer correctly ALL these questions. This knowledge is just basic for a 3+ year experienced DBA)
1.1 Q- Which are the default passwords of SYSTEM/SYS?
A-  MANAGER / CHANGE_ON_INSTALL
1.2 Q- How can you execute a script file in SQLPLUS?
A- To execute a script file in SQLPlus, type @ and then the file name.
1.3 Q- Where can you find official Oracle documentation?
A- tahiti.oracle.com
1.4 Q- What is the address of the Official Oracle Support?
A- metalink.oracle.com or support.oracle.com
1.5 Q- What file will you use to establish Oracle connections from a remote client?
A- tnsnames.ora
1.6 Q- How can you check if the database is accepting connections?
A- lsnrctl status or lsnrctl services
1.7 Q- Which log would you check if a database has a problem?
A- Alert log
1.8 Q- Name three clients to connect with Oracle, for example, SQL Developer:
A- SQL Developer, SQL-Plus, TOAD, dbvisualizer, PL/SQL Developer… There are several, but an experienced dba should know at least three clients.
1.9 Q- How can you check the structure of a table from sqlplus?
A- DESCRIBE or DESC
1.10 Q- What command will you start to run the installation of Oracle software on Linux?
A- runInstaller

2. Moderate (Standard knoledge for a daily-work of every DBA. He could fail one or two questions, but not more)
2.1 Q- What should you do if you encounter an ORA-600?
A- Contact Oracle Support
2.2 Q- Explain the differences between PFILE and SPFILE
A- A PFILE is a Static, text file that initialices the database parameter in the moment that it’s started. If you want to modify parameters in PFILE, you have to restart the database.
A SPFILE is a dynamic, binary file that allows you to overwrite parameters while the database is already started (with some exceptions)
2.3 Q- In which Oracle version was Data Pump introduced?
A- Oracle 10g
2.4 Q- Say two examples of DML, two of DCL and two of DDL
A- DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
DDL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
DCL: GRANT, REVOKE
2.5 Q- You want to save the output of an Oracle script from sqlplus. How would you do it?
A- spool script_name.txt
select * from your_oracle_operations;
spool off;
2.6 Q- What is the most important requirement in order to use RMAN to make consistent hot backups?
A- Your database has to be in ARCHIVELOG mode.
2.7 Q- Can you connect to a local database without a listener?
A- Yes, you can.
2.8 Q- In which view can you find information about every view and table of oracle dictionary?
A- DICT or DICTIONARY
2.9 Q- How can you view all the users account in the database?
A- SELECT USERNAME FROM DBA_USERS;
2.10 Q- In linux, how can we change which databases are started during a reboot?
A- Edit /etc/oratab

3. Advanced (A 3+ year experienced DBA should have enough knowledge to answer these questions. However, depending on the work he has done, he could still fail up to 4 questions)
3.1 Q- When a user process fails, what Oracle background process will clean after it?
A- PMON
3.2 Q- How can you reduce the space of TEMP datafile?
A- Prior to Oracle 11g, you had to recreate the datafile. In Oracle 11g a new feature was introduced, and you can shrink the TEMP tablespace.
3.3 Q- How can you view all the current users connected in your database in this moment?
A- SELECT COUNT(*),USERNAME FROM V$SESSION GROUP BY USERNAME;
3.4 Q- Explain the differences between SHUTDOWN, SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE AND SHUTDOWN ABORT
A- SHUTOWN NORMAL = SHUTDOWN : It waits for all sessions to end, without allowing new connections.
SHUTDOWN IMMEDIATE : Rollback current transactions and terminates every session.
SHUTDOWN ABORT : Aborts all the sessions, leaving the database in an inconsistent state. It’s the fastest method, but can lead to database corruption.
3.5 Q- Is it possible to backup your database without the use of an RMAN database to store the catalog?
A- Yes, but the catalog would be stored in the controlfile.
3.6 Q- Which are the main components of Oracle Grid Control?
A- OMR (Oracle Management Repository), OMS (Oracle Management Server) and OMA (Oracle Management Agent).
3.7 Q- What command will you use to navigate through ASM files?
A- asmcmd
3.8 Q- What is the difference between a view and a materialized view?
A- A view is a select that is executed each time an user accesses to it. A materialized view stores the result of this query in memory for faster access purposes.
3.9 Q- Which one is faster: DELETE or TRUNCATE?
A- TRUNCATE
3.10 Q- Are passwords in oracle case sensitive?
A- Only since Oracle 11g.

4. RAC (Only intended for RAC-specific DBAs, with varied difficultied questions)
4.1 Q- What is the recommended method to make backups of a RAC environment?
A- RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file.
4.2 Q- What command would you use to check the availability of the RAC system?
A- crs_stat -t -v (-t -v are optional)
4.3 Q- What is the minimum number of instances you need to have in order to create a RAC?
A- 1. You can create a RAC with just one server.
4.4 Q- Name two specific RAC background processes
A- RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG.
4.5 Q- Can you have many database versions in the same RAC?
A- Yes, but Clusterware version must be greater than the greater database version.
4.6 Q- What was RAC previous name before it was called RAC?
A- OPS: Oracle Parallel Server
4.7 Q- What RAC component is used for communication between instances?
A- Private Interconnect.
4.8 Q- What is the difference between normal views and RAC views?
A- RAC views has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION
4.9 Q- Which command will we use to manage (stop, start…) RAC services in command-line mode?
A- srvctl
4.10 Q- How many alert logs exist in a RAC environment?
A- One for each instance.

5. Master (A 3+ year experienced DBA would probably fail these questions, they are very specifid and specially difficult. Be glad if he’s able to answer some of them)
5.1 Q- How can you difference a usual parameter and an undocumented parameter?
A- Undocumented parameters have the prefix ‘_’. For example, _allow_resetlogs_corruption
5.2 Q- What is BBED?
A- An undocumented Oracle tool used for foresnic purposes. Stans for Block Browser and EDitor.
5.3 Q- The result of the logical comparison (NULL = NULL) will be… And in the case of (NULL != NULL)
A- False in both cases.
5.4 Q- Explain Oracle memory structure
The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer s RAM is called memory structure. Oracle has two memory structures in the computer s RAM. The two structures are the Program Global Area (PGA) and the System Global Area (SGA).

The PGA contains data and control information for a single user process. The SGA is the memory segment that stores data that the user has retrieved from the database or data that the user wants to place into the database.
5.5 Q- Will RMAN take backups of read-only tablespaces?
A- No
5.6 Q- Will a user be able to modify a table with SELECT only privilege?
A- He won’t be able to UPDATE/INSERT into that table, but for some reason, he will still be able to lock a certain table.
5.7 Q- What Oracle tool will you use to transform datafiles into text files?
A- Trick question: you can’t do that, at least with any Oracle tool. A very experienced DBA should perfectly know this.
5.8 Q- SQL> SELECT * FROM MY_SCHEMA.MY_TABLE;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
Why I’m getting this error?
A- The table has a BLOB column.
5.9 Q- What parameter will you use to force the starting of your database with a corrupted resetlog?
A- _ALLOW_RESETLOGS_CORRUPTION
5.10 Q- Name the seven types of Oracle tables
A- Heap Organized Tables, Index Organized Tables, Index Clustered Tables, Hash Clustered Tables, Nested Tables, Global Temporary Tables, Object Tables.

DBA Tasks

December 3, 2010 8 comments

We can separate the dba tasks between daily, weekly and monthly procedures:

Daily
– Verify instance status
– Check alerts
– Check configured metrics
– Check RMAN backups
– Check storage
– Check CPU contention
– Check waiting times
– Check memory usage
– Check network load
– Check iostat

Weekly

– Invalid objects
– Tunning: indexes and execution plans
– Top SQL
– Environments consistence
– Review of ressource policy
– Trends and peaks
– Cleaning of alert logs
– Review of RMAN

Monthly

– Recovery tests
– Analyze the data increment trend
– Tunning
– Review I/O
– Fragmentation
– Row chaining
– High Availability Analysis
– Scalability
– Schedule monthly downtime

Grid Control: EMD upload error

December 2, 2010 Leave a comment

Today I was installing the grid control agent in a new server, and I probably mistyped the password during the installation. I didn’t notice at the start, but the client was succesfully installed. However, it didn’t appear on my grid control maintenance window.

I connected to the brand new server and issued the following command:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl upload agent

And it produced this error:

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

This error was caused because I typed a bad password, and in order to change it, I had to remove first the password:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl unsecure agent

And then, securing the agent again:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl secure agent

It asked me for a new password, which I typed well this time, and everything started working fine.

ora-600

November 26, 2010 Leave a comment

If you don’t know what an ORA-600 is, you probably are new in the bussiness, or really lucky.

ORA-600 is the worst error you could face in an Oracle Database, and unfortunately, it’s more

common than people would want.

The description of the ORA-600 error is the following:

Error: ORA 600
Text: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s]
——————————————————————————-
Cause: This is the generic internal error number for Oracle program
exceptions. This indicates that a process has encountered an
exceptional condition.
Action: Report as a bug – the first argument is the internal error number

When you get an ORA-600 in your database, a record will always be written into the alert log,

and you will probably have the reference to a trace file for debugging purposes. If you use

grid control, you will want to configure it in order to receive an e-mail whenever an ORA-600

is produced.

Basically, if you receive an ORA-600, you are recommended to open a SR in metalink (My Oracle

Support), but maybe you are facing a known error that can be solved without the intervention

of Oracle Support. Notice that you have to know what are you doing, if you lack the required

knowledge then you should open an SR and follow the help provided by the Oracle staff.

And a last piece of information: there is an undocumented oracle utility called ORADEBUG that

you could use under your responsability to troubleshoot ORA-600 errors. For more info, check

the following paper:

Click to access oradebug.pdf

I hope this information helped you in case you have found an ORA-600 in your database. And

remember, ORA-600 are almost always serious bussiness, so if you see this kind of failure, you

should assign the maximum priority to solve it!

What is the data dictionary?

The scope of this article is to clarify what exactly is the data dictionary. Probably all of you have heard of it, and you know it’s a bunch of metadata that Oracle uses to work, but if you want to know more about this Data Dictionary, then I hope this article teaches you something new.

The data dictionary is a repository of metadata (Information about information), about all the information inside the database. This repository is owned by SYS, and is stored principally in the SYSTEM tablespace, though some components are stored in the SYSAUX tablespace (in Oracle 10+).

The data dictionary is composed of tables and views. Some of these tables are inside of the Oracle kernel, so you would never work directly with them unless you are working for Oracle support or performing a disaster recovery scenario. But instead you can access to the views in order to know the “information about the information”.

For example, a possible usage of this data dictionary would be to know all the tables owned by a single user, or the list or relationships between all the tables of the database.

The main view of the data dictionary is the view DICT (or DICTIONARY):

SQL> DESC DICT
Nombre                                    ?Nulo?   Tipo
—————————————– ——– —————————-
TABLE_NAME                                         VARCHAR2(30)
COMMENTS                                           VARCHAR2(4000)

Through the DICT view, you can access to all the data dictionary views that could provide you the information that you need.

For example, if you are looking for information related to db_links, but you don’t know where to look for, then query the DICT view:

SQL>¬† SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE ‘%LINK%’;

TABLE_NAME
——————————
USER_DB_LINKS
ALL_DB_LINKS
DBA_DB_LINKS
V$DBLINK
GV$DBLINK

Now, you have just to query one of these views to find the data you are looking for. GV$ views are very useful when you are working with RAC, and V$ views are instance related.

Remember that the data dictionary provides critical information of the database, and it should be restricted to users. However, if a user really needs to query the data dictionary, you can use the following sentence:

GRANT SELECT_CATALOG_ROLE TO user_name;

Undocumented parameter _allow_read_only_corruption

July 7, 2010 4 comments

Some time ago we talked about the undocumented parameter _allow_resetlogs_corruption, which could help us to open a database in case we have corruption in our redo logs. In this post we will discuss another undocumented parameter that could be helpful in case of corruption: _allow_read_only_corruption.

This parameter should not be used without the support of Oracle itself. But if you want to do some tests to retrieve your data more quickly, you can make a copy of your corrupted database and try this hidden parameter. Just make sure that you have a copy, because playing with this kind of parameters you can corrupt your database much more.

The procedure to use this hidden parameter would be to shutdown abort your database to make sure it’s completly closed.

Then, startup the database in nomount mode, and set this parameter to on:

ALTER DATABASE SET _allow_read_only_corruption SCOPE=SPFILE;

Shutdown immediate the database, and then try to startup your database “normally”. It will open your database even if you have some corruption, or at least, it will try to do it.

The first thing you should do after opening the database in this mode is to export all the data, and recover as much as you can. You shouldn’t use the database in this instance as this is an inconsistent mode, and _allow_resetlogs_corruption.

Just be careful, and remember that if you use this kind of knowledge without knowing what you are doing, you are claiming for disaster.