We all know the difference between RMAN full backup and Incremental Level 0 backup. Only incremental Level 0 backup can be the base for other incremental level 1 (cumulative and differential) backups.
I have been wondering so much about this. Why not a full backup as the base? I finally came up with my own imaginary behind-story. This is purely my own guess. So ignore if you don't like my notion.
We know Oracle DBMS has been evolved a lot. I think there have been a lot of changes and add-on to the backup part of Oracle DBMS.
1) There might not have been "incremental backup" with the earlier version of Oracle(like Oracle 7 for example).
2) they introduced incremental type of backup with later version to allow more flexible backup strategies*: probably level 1 differential only at that time)
3) they found people's need for more, more flexible backup strategies, so Oracle further introduced 'Cumulative incremental' and 'Level 0 incremental'
But, things got a bit complicated in the technical side. The developers couldn't find a way to integrate all the new types of backup functions with their original full backup codes. Or they found it too expensive to change the codes(it might end up being 'spaghetti code'). So they probably decided to keep the original Full backup and Level 0 incremental backup as well.
Again. this is just an idea. Peace!!!
Feb 24, 2011
Oracle data file - the maximum size
An Oracle data file can be up to 128 TB in size. 128 TB is a very huge number.
Feb 18, 2011
"Enterprise Manager is not able to connect to the database instance..." and SYSMAN account
Just want to document this issue here so that I won't forget again...
Issue - EM is not able to connect to the db instance even if the db instance is up and running. (In this picture the listener is down just because I didn't started. It is not our concern here)
Impact - we can't manage the database and its instance using Enterprise Manager.
Resolutions - We need to check on SYSMAN account.
(1) SYSMAN: this accounts should be open, and the password is not expired.
> sysman : Used to perform Oracle Enterprise Manager admin tasks.
The EM super admin account with mgmt_user, resource &
select_catalog_role roles granted. When we log in the OEM using SYS or
SYSTEM, we are logging in as this super user.
** DBSNP : If we see this account locked and expired. We need to alter this account as
well because this account is used to monitor and manage the database.
==> SQL> alter user sysman account unlock identified by newpassword ;
(2) Having SYSMAN and DBSNP accounts available seems to be good enough. However, I would also just check on two more things.
> remote_login_passwordfile
I would make sure that this parameter is set to "exclusive"
==> SQL> show parameter password
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
Then, restart the database instance.
> password file exist?
I would also make sure that a password file exist or just recreate it.
==> $> orapwd file=file_name password=new_password entries=max_num_of_users
i.e) orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=10
Lastly, I have found that we need to be really really PATIENT until the EM starts working correctly. We would expect to see the problem corrected right away after resetting the accounts. But nope!!! It seems that it takes quite long time for the EM to recognize the reconfiguration. It would be a great idea to restart the EM control.
Issue - EM is not able to connect to the db instance even if the db instance is up and running. (In this picture the listener is down just because I didn't started. It is not our concern here)
Impact - we can't manage the database and its instance using Enterprise Manager.
Resolutions - We need to check on SYSMAN account.
(1) SYSMAN: this accounts should be open, and the password is not expired.
> sysman : Used to perform Oracle Enterprise Manager admin tasks.
The EM super admin account with mgmt_user, resource &
select_catalog_role roles granted. When we log in the OEM using SYS or
SYSTEM, we are logging in as this super user.
** DBSNP : If we see this account locked and expired. We need to alter this account as
well because this account is used to monitor and manage the database.
==> SQL> alter user sysman account unlock identified by newpassword ;
(2) Having SYSMAN and DBSNP accounts available seems to be good enough. However, I would also just check on two more things.
> remote_login_passwordfile
I would make sure that this parameter is set to "exclusive"
==> SQL> show parameter password
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
Then, restart the database instance.
> password file exist?
I would also make sure that a password file exist or just recreate it.
==> $> orapwd file=file_name password=new_password entries=max_num_of_users
i.e) orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=10
Lastly, I have found that we need to be really really PATIENT until the EM starts working correctly. We would expect to see the problem corrected right away after resetting the accounts. But nope!!! It seems that it takes quite long time for the EM to recognize the reconfiguration. It would be a great idea to restart the EM control.
SAME - Striping And Mirroring Everything
Just a note for myself in respect to ASM.
(2) Mirroring is what "Failure Groups" thing is about
ASM mirrors(duplicates/copies) all the contents(extents) of an ASM file. It stores the original contents(extents) and the copied contents in separate sub groups, so-called "Failure Groups" inside of the same ASM diskgroup(+DATA).
- Normal redundancy (2 way mirroring) --> ASM creates one duplicate copy
- High redundancy (3 way mirroring) --> ASM creates two duplicate copies.
- External --> no mirroring. ASM assumes that mirroring is done EXTERNALLY.
** Each failure group has one copy of any given ASM file content(extent).
A disadvantage of using a bind variable -- Adaptive Cursor Sharing
We all know ... Using bind variables --> reduce time for parsing (by doing soft-parsing instead of hard parsing) --> improve the query performance.
BUT there is a problem. Using bind variable is not always the best option. It could result in a sub-optimal query execution. When our query is supposed to return more than a certain % of the table rows, FTS(Full Table Scan) is more efficient than index scan.** When we use a bind variable, we remove the ability for the optimizer to choose the option of a full table scan because the optimizer doesn't get a chance to compare the value of the bind variable with the data distribution in the column(Histogram). In other words, using bind variable is very economic b/c we need one shared cursor in the library cache to execute more than on SQL statement. However, it could sometimes results in a poor query performance.
** more than 3%~10% of the table rows to be returned as per Oracle while some others argue differently.
Solutions:
(1) Oracle 9i - there was "Bind Variable Peeking" with 9i. When a query with bind variable is parsed first time, the optimizer takes a look at the value of the bind variables and then comes up with a best execution plan based(on the value of the bind variables). --> This is good. HOWEVER, there was another problem. The same execution plan(from the first parsing) will be REUSED for all subsequent executions of the query. The same plan will be used even if the query gets different value in its bind variable.
(2) Oracle 11g - To address the issue of Bind Variable Peeking, Oracle introduced something called, Adaptive Cursor Sharing with 11g --> a compromising solution between 'reducing time spent for hard parsing' and 'keeping the good level of query performance'. We first need to set a parameter as following.
SQL> alter session set optimizer_features_enable="11.1.0.6" ; (11.1.0.6 or later version #)
What happens internally is...
> at the 1st run of a query with bind variable : Bind peeking takes place. The cursor for the query execution starts its life with a hard parse. The cursor is marked as a bind-sensitive cursor. In addition, some more information are stored in the cursor: the predicate selectivity, the bind values, the execution statistics, and an initial selectivity cube(a range of selectivity that allows a soft parsing of a subsequent execution of the query).
> at the 2nd run of the query with a new set of bind values : the system finds the predicate selectivity for the new set of bind values and see if it falls within the initial selectivity cube. If it falls within the initial cube, the system uses the existing execution plan. If not, the system does a hard parsing and creates a new execution plan. And the system also creates a new selectivity cube based on the selectivity of the new set of bind values. (By now, the cursor has two different selectivity cubes.)
> at the next subsequent runs : the sql parsing and the execution plan are determined in the same fashion. However, when the hard parse generates the same execution plan as any of existing plans, the system merges the two selectivity cubes(the existing one + the new one), which will result in a new bigger selectivity cubes. The selectivity cubes are merges and get bigger as the query runs more and more.
------------------------------------------------------------
*** For more information about Adaptive Cursor Sharing, refer to following links
http://www.dba-oracle.com/t_11g_adaptive_cursor_sharing.htm
*** We probably don't want to use bind variables that often in OLAP systems(Data warehousing) where the parsing of the query is a very small percentage of the work done when submitting a query.
> reference : http://www.akadia.com/services/ora_bind_variables.html
BUT there is a problem. Using bind variable is not always the best option. It could result in a sub-optimal query execution. When our query is supposed to return more than a certain % of the table rows, FTS(Full Table Scan) is more efficient than index scan.** When we use a bind variable, we remove the ability for the optimizer to choose the option of a full table scan because the optimizer doesn't get a chance to compare the value of the bind variable with the data distribution in the column(Histogram). In other words, using bind variable is very economic b/c we need one shared cursor in the library cache to execute more than on SQL statement. However, it could sometimes results in a poor query performance.
** more than 3%~10% of the table rows to be returned as per Oracle while some others argue differently.
Solutions:
(1) Oracle 9i - there was "Bind Variable Peeking" with 9i. When a query with bind variable is parsed first time, the optimizer takes a look at the value of the bind variables and then comes up with a best execution plan based(on the value of the bind variables). --> This is good. HOWEVER, there was another problem. The same execution plan(from the first parsing) will be REUSED for all subsequent executions of the query. The same plan will be used even if the query gets different value in its bind variable.
(2) Oracle 11g - To address the issue of Bind Variable Peeking, Oracle introduced something called, Adaptive Cursor Sharing with 11g --> a compromising solution between 'reducing time spent for hard parsing' and 'keeping the good level of query performance'. We first need to set a parameter as following.
SQL> alter session set optimizer_features_enable="11.1.0.6" ; (11.1.0.6 or later version #)
What happens internally is...
> at the 1st run of a query with bind variable : Bind peeking takes place. The cursor for the query execution starts its life with a hard parse. The cursor is marked as a bind-sensitive cursor. In addition, some more information are stored in the cursor: the predicate selectivity, the bind values, the execution statistics, and an initial selectivity cube(a range of selectivity that allows a soft parsing of a subsequent execution of the query).
> at the 2nd run of the query with a new set of bind values : the system finds the predicate selectivity for the new set of bind values and see if it falls within the initial selectivity cube. If it falls within the initial cube, the system uses the existing execution plan. If not, the system does a hard parsing and creates a new execution plan. And the system also creates a new selectivity cube based on the selectivity of the new set of bind values. (By now, the cursor has two different selectivity cubes.)
> at the next subsequent runs : the sql parsing and the execution plan are determined in the same fashion. However, when the hard parse generates the same execution plan as any of existing plans, the system merges the two selectivity cubes(the existing one + the new one), which will result in a new bigger selectivity cubes. The selectivity cubes are merges and get bigger as the query runs more and more.
------------------------------------------------------------
*** For more information about Adaptive Cursor Sharing, refer to following links
http://www.dba-oracle.com/t_11g_adaptive_cursor_sharing.htm
*** We probably don't want to use bind variables that often in OLAP systems(Data warehousing) where the parsing of the query is a very small percentage of the work done when submitting a query.
> reference : http://www.akadia.com/services/ora_bind_variables.html
Subscribe to:
Posts (Atom)