It will enable me to get enthusiast with RAC live webinars, documents from now on.
You may subcribe from http://www.oracleracsig.org/pls/apex/f?p=105:51:0::NO:51
Enjoy it !
Tuesday, December 25, 2012
Oracle RACSIG subscription
While looking for something in Oracle discussion forum, I bumped into webinar link, which lead me to subscribe Oracle RAC SIG.
It will enable me to get enthusiast with RAC live webinars, documents from now on.
You may subcribe from http://www.oracleracsig.org/pls/apex/f?p=105:51:0::NO:51
Enjoy it !
It will enable me to get enthusiast with RAC live webinars, documents from now on.
You may subcribe from http://www.oracleracsig.org/pls/apex/f?p=105:51:0::NO:51
Enjoy it !
Thursday, November 22, 2012
Which rights must be granted for displaying TOAD session browser
Following rights must be given to specific user, here MYUSER, in order to display sessions in TOAD session browser.
GRANT SELECT ON GV_$SESSION TO MYUSER;
GRANT SELECT ON GV_$PROCESS TO MYUSER;
GRANT SELECT ON GV_$SESS_IO TO MYUSER;
GRANT SELECT ON GV_$SESSION_WAIT TO MYUSER;
GRANT SELECT ON GV_$SESSION_EVENT TO MYUSER;
GRANT SELECT ON GV_$ACCESS TO MYUSER;
GRANT SELECT ON GV_$SESSTAT TO MYUSER;
GRANT SELECT ON GV_$SQL_PLAN TO MYUSER;
GRANT SELECT ON GV_$SQLTEXT_WITH_NEWLINES TO MYUSER;
GRANT SELECT ON GV_$SESSION TO MYUSER;
GRANT SELECT ON GV_$PROCESS TO MYUSER;
GRANT SELECT ON GV_$SESS_IO TO MYUSER;
GRANT SELECT ON GV_$SESSION_WAIT TO MYUSER;
GRANT SELECT ON GV_$SESSION_EVENT TO MYUSER;
GRANT SELECT ON GV_$ACCESS TO MYUSER;
GRANT SELECT ON GV_$SESSTAT TO MYUSER;
GRANT SELECT ON GV_$SQL_PLAN TO MYUSER;
GRANT SELECT ON GV_$SQLTEXT_WITH_NEWLINES TO MYUSER;
Monday, November 12, 2012
How to Apply Multicast Patch before root.sh
Applying multicast patch is not trivial at all, and there is not clear instruction steps in readme of the patch, regarding with the order of patching, or when/where to apply.
Beware that this installation scenario needed because we need to install 11.2.0.2.2 version. Below you could find the steps for applying multicast patch:
1. Follow installation instructions for 11.2.0.2.0 grid infra, till Execute Configuration Scripts window (root.sh script execution window)
2. As it stated in Configuration Scripts window, run orainstRoot.sh script in both nodes. [You may get orainst.loc not found errors, if you try to start following multicast patch here!]
3. Execute root.sh script in first node, (check that ASM, grid processes become online after then)
4. Apply Multicast patch (9974223) on first node, without skipping any CRS related step in patch readme.
5. Multicast patch on second node(for configuration B), skipping below locking steps because root.sh is not executed in 2nd node:
<CRS_HOME>/crs/install/rootcrs.pl -unlock
<CRS_HOME>/crs/install/rootcrs.pl -patch
[We inspired from http://oracledba.flyingamigos.com/archives/380, thank you flying amigos.]
6. Execute root.sh script in 2nd node.
7. Install database software.
8. Stop HAS and cluster. And apply multicast patch to database home by:
<CRS_HOME>/bin/./crsctl stop crs
<CRS_HOME>/bin/./crsctl stop cluster
opatch lsinventory -detail -oh /u01/app/oracle/product/11.2.0/dbhome_1
opatch napply custom/server/ -local -oh /u01/app/oracle/product/11.2.0/dbhome_1 -id 9974223
Thank you Tamer Onem, from Oracle, this note could not be "complete" without his enrollment.
Beware that this installation scenario needed because we need to install 11.2.0.2.2 version. Below you could find the steps for applying multicast patch:
1. Follow installation instructions for 11.2.0.2.0 grid infra, till Execute Configuration Scripts window (root.sh script execution window)
2. As it stated in Configuration Scripts window, run orainstRoot.sh script in both nodes. [You may get orainst.loc not found errors, if you try to start following multicast patch here!]
3. Execute root.sh script in first node, (check that ASM, grid processes become online after then)
4. Apply Multicast patch (9974223) on first node, without skipping any CRS related step in patch readme.
5. Multicast patch on second node(for configuration B), skipping below locking steps because root.sh is not executed in 2nd node:
<CRS_HOME>/crs/install/rootcrs.pl -unlock
<CRS_HOME>/crs/install/rootcrs.pl -patch
[We inspired from http://oracledba.flyingamigos.com/archives/380, thank you flying amigos.]
6. Execute root.sh script in 2nd node.
7. Install database software.
8. Stop HAS and cluster. And apply multicast patch to database home by:
<CRS_HOME>/bin/./crsctl stop crs
<CRS_HOME>/bin/./crsctl stop cluster
opatch lsinventory -detail -oh /u01/app/oracle/product/11.2.0/dbhome_1
opatch napply custom/server/ -local -oh /u01/app/oracle/product/11.2.0/dbhome_1 -id 9974223
Thank you Tamer Onem, from Oracle, this note could not be "complete" without his enrollment.
Saturday, November 10, 2012
Infrastructure Consolidation Projects
What is consolidation? At first, it seems a process which enables making things with smaller number of resources.
Below picture that depicts the economy of using one resource, one jar, for many fish, which forms a real motivation to managers, and initial understanding of the concept.
I had a chance to coordinate and enroll in operational standardization, consolidation projects.
At first glance, you may wonder why do we need consolidation or normalization projects? We may find the answers with more questions like following... Why systems fall in a state that are not economically operable? Why whole service responsibility is given on one or two super-persons who never gets ill, or goes to holiday, having roles like database administration, application server administration, hardware&software procurement, application administration etc? Why not divide and conquer responsibility of service management? Why do we try to use one resource for all problems?
Why people tends to put one fish in one jar, instead of putting more fish in one jar. This maybe because of less knowledge/specialization/expertise in operation, fast, unplanned installations, badly managed projects and vendor driven configurations/installations for applications/services.
Let's continue on consolidation projects... They took place among different divisions, teams, outsource companies, and tens of people, having goal of responsibility transition to several technology specialized groups such as database, application server, disk, test, security, resource management, procurement from single responsible group.
After consolidating/normalizing our service/application infrastructure we found a pot of gold, here comes, what is in this pot:
- Opportunity to technology improvement like filesystem to ASM, legacy Sun Cluster to Oracle RAC. It is an opportunity, because you are not renovating, it is a process that designing the underlying infrastructure from scratch.
- Decreasing the number of hardware & software, licenses, operation personnel which lead great savings, which means decreasing OPEX (operational expenditure).
- Combining above two issues, recall that technology inevitably occurs in infrastructure every 3-5 years, due to organic growth of business, and maintenance cost increase, and manager's will to prefer CAPEX with compared to OPEX.
- Have a chance to harden the underlying infrastructure, because we deal with fewer system. Hardening matrix example
- Adopt "service" concept to infrastructure, for example an application will go to database infrastructure over application server with its database service connection. This lead flexibility in locating services to database instances.
- Applying operational standards (backup, maintenance, patch, etc.), database security standards, change management, not using production for everything, change through development to test and to production environments.
- Compliance with security standards. In our case, compliance with ISO and SOX security requirements. Enabling segregation of duties or simply separation of duties. Centrify, guardium like technologies makes things easy.
- Specialization in operations: Special tasks must be operated by specialized people, in order to make it faster with high quality.
- At last, which is simpler? Defending tens of castles or only one. Such as monitoring a large database, application server, is always a lot more easier than managing tens of them. For example, you will only get one RMAN backup, instead of ten; this is not only database task, but also backup, and first line monitoring task. You could increase the examples for application server administration, Oracle Gateway administration, Oracle EM grid agent administration etc.
A picture which may lead understanding the compliance to security/operational standards, shows the real outcome value of consolidation projects. This is like putting the fish in a bag, and changing the water again and again because it is impossible to use air pump due to its cost or technology. Why not put these fish to modern aquarium with air pump, light, plants, thick glass.
I want to add more words for specialization... In Turkish, there is a saying, "If you own only a hammer, everything seems nail to you." In real world, do you really have only hammer in your hand, and nails to be nailed... or specialized tools like screw driver, pliers, saw for screwing a screw, gripping/bending a copper cable, cutting woods etc? You must utilize from specialized tools, people, in your systems... You must divide your service into specialized responsibility areas...
Below picture that depicts the economy of using one resource, one jar, for many fish, which forms a real motivation to managers, and initial understanding of the concept.
I had a chance to coordinate and enroll in operational standardization, consolidation projects.
At first glance, you may wonder why do we need consolidation or normalization projects? We may find the answers with more questions like following... Why systems fall in a state that are not economically operable? Why whole service responsibility is given on one or two super-persons who never gets ill, or goes to holiday, having roles like database administration, application server administration, hardware&software procurement, application administration etc? Why not divide and conquer responsibility of service management? Why do we try to use one resource for all problems?
Why people tends to put one fish in one jar, instead of putting more fish in one jar. This maybe because of less knowledge/specialization/expertise in operation, fast, unplanned installations, badly managed projects and vendor driven configurations/installations for applications/services.
Let's continue on consolidation projects... They took place among different divisions, teams, outsource companies, and tens of people, having goal of responsibility transition to several technology specialized groups such as database, application server, disk, test, security, resource management, procurement from single responsible group.
After consolidating/normalizing our service/application infrastructure we found a pot of gold, here comes, what is in this pot:
- Opportunity to technology improvement like filesystem to ASM, legacy Sun Cluster to Oracle RAC. It is an opportunity, because you are not renovating, it is a process that designing the underlying infrastructure from scratch.
- Decreasing the number of hardware & software, licenses, operation personnel which lead great savings, which means decreasing OPEX (operational expenditure).
- Combining above two issues, recall that technology inevitably occurs in infrastructure every 3-5 years, due to organic growth of business, and maintenance cost increase, and manager's will to prefer CAPEX with compared to OPEX.
- Have a chance to harden the underlying infrastructure, because we deal with fewer system. Hardening matrix example
- Adopt "service" concept to infrastructure, for example an application will go to database infrastructure over application server with its database service connection. This lead flexibility in locating services to database instances.
- Applying operational standards (backup, maintenance, patch, etc.), database security standards, change management, not using production for everything, change through development to test and to production environments.
- Compliance with security standards. In our case, compliance with ISO and SOX security requirements. Enabling segregation of duties or simply separation of duties. Centrify, guardium like technologies makes things easy.
- Specialization in operations: Special tasks must be operated by specialized people, in order to make it faster with high quality.
- At last, which is simpler? Defending tens of castles or only one. Such as monitoring a large database, application server, is always a lot more easier than managing tens of them. For example, you will only get one RMAN backup, instead of ten; this is not only database task, but also backup, and first line monitoring task. You could increase the examples for application server administration, Oracle Gateway administration, Oracle EM grid agent administration etc.
A picture which may lead understanding the compliance to security/operational standards, shows the real outcome value of consolidation projects. This is like putting the fish in a bag, and changing the water again and again because it is impossible to use air pump due to its cost or technology. Why not put these fish to modern aquarium with air pump, light, plants, thick glass.
I want to add more words for specialization... In Turkish, there is a saying, "If you own only a hammer, everything seems nail to you." In real world, do you really have only hammer in your hand, and nails to be nailed... or specialized tools like screw driver, pliers, saw for screwing a screw, gripping/bending a copper cable, cutting woods etc? You must utilize from specialized tools, people, in your systems... You must divide your service into specialized responsibility areas...
Monday, October 15, 2012
Steps to migrate database machines by mounting existing ASM disks on new machines
Philosophy behind this machine migration is: ASM disk groups could be mounted to any machine, that see the underlying disk luns. And there is so much to do for OS admins then DBAs while migrating your database to new servers(machines).
Let's tell about our environment and requirements before dive into steps.
Both our database and grid infra versions are 11.2.0.2, running on IBM AIX 6.1 and migration/ upgrade from ibm p5 series machine to p6.
There are a single database and a RAC database in our RAC environment needed to migrate.
In our case, IP adress and VLAN are needed to be changed. That is why we need install grid and Oracle database software instead of only mounting u01 disk. Also we need to deal with scan name change side effects.
You could find the steps for changing database machines followingly:
1. Install grid infra, and Oracle database software and do not create data disk groups after then.
This step could be done at any time, even days before following steps.
2. Call application admins before starting.
[Steps 3,4,5 will be executed in old database node]
3. disable&stop listeners
--run below two lines in all nodes of cluster
srvctl stop listener -l listener
srvctl disable listener -l listener
--disable&stop scan_listeners in one node
srvctl disable scan_listener -i 1
srvctl disable scan_listener -i 2
srvctl disable scan_listener -i 3
srvctl stop scan_listener -i 1
srvctl stop scan_listener -i 2
srvctl stop scan_listener -i 3
4. Backup your archive logs to tape, in order to rollback the operation.
5. Stop database, asm, cluster.
crsctl stop database
crsctl stop cluster
crsctl stop crs
[Below steps will be executed in old database node]
6. Add database, instance to cluster
srvctl add database -d MYDB -c RAC -o /u01/app/oracle/product/11.2.0/dbhome_1
srvctl add database -d APEX -c SINGLE -x istanbul1 -o /u01/app/oracle/product/11.2.0/dbhome_1
srvctl add instance -d MYDB -i MYDB1 -n istanbul1
srvctl add instance -d MYDB -i MYDB2 -n istanbul2
srvctl add instance -d MYDB -i APEX -n istanbul1
8. Change asm search string as it was in older machines
alter system set asm_diskstring='/dev/ORACLE/*/*' scope=both;
9. Mount ASM diskgroups
alter diskgroup data1dg mount;
alter diskgroup data2dg mount;
10. Create init.ora files as in old servers.
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDBDB1.ora and
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDBDB2.ora
12. Create adump folder, give oracle user required OS rights
Check for audit_file_dest parameter in old system, create folder in all new nodes like below:
mkdir /u01/app/oracle/admin/MYDB/adump
chown oracle:oinstall mkdir /u01/app/oracle/admin/MYDB/adump
13. Start database [check alert.log while starting database]
srvctl start database -d MYDB
srvctl start database -d APEX
14. Add & start services to cluster [be sure you started MYDB database as in step 13]
srvctl add service -d MYDB -s MYSVC1 -r MYDB1 -a MYDB2
srvctl add service -d MYDB -s MYSVC2 -r MYDB1 -a MYDB2
srvctl start service -d MYDB -s MYSVC1
srvctl start service -d MYDB -s MYSVC2
15. Change remote_listener parameter in init.ora
ALTER SYSTEM SET remote_listener='mydb-scan.turkcell.tgc:1521' SCOPE=BOTH;
Let's tell about our environment and requirements before dive into steps.
Both our database and grid infra versions are 11.2.0.2, running on IBM AIX 6.1 and migration/ upgrade from ibm p5 series machine to p6.
There are a single database and a RAC database in our RAC environment needed to migrate.
In our case, IP adress and VLAN are needed to be changed. That is why we need install grid and Oracle database software instead of only mounting u01 disk. Also we need to deal with scan name change side effects.
You could find the steps for changing database machines followingly:
1. Install grid infra, and Oracle database software and do not create data disk groups after then.
This step could be done at any time, even days before following steps.
2. Call application admins before starting.
[Steps 3,4,5 will be executed in old database node]
3. disable&stop listeners
--run below two lines in all nodes of cluster
srvctl stop listener -l listener
srvctl disable listener -l listener
--disable&stop scan_listeners in one node
srvctl disable scan_listener -i 1
srvctl disable scan_listener -i 2
srvctl disable scan_listener -i 3
srvctl stop scan_listener -i 1
srvctl stop scan_listener -i 2
srvctl stop scan_listener -i 3
4. Backup your archive logs to tape, in order to rollback the operation.
5. Stop database, asm, cluster.
crsctl stop database
crsctl stop cluster
crsctl stop crs
[Below steps will be executed in old database node]
6. Add database, instance to cluster
srvctl add database -d MYDB -c RAC -o /u01/app/oracle/product/11.2.0/dbhome_1
srvctl add database -d APEX -c SINGLE -x istanbul1 -o /u01/app/oracle/product/11.2.0/dbhome_1
srvctl add instance -d MYDB -i MYDB1 -n istanbul1
srvctl add instance -d MYDB -i MYDB2 -n istanbul2
srvctl add instance -d MYDB -i APEX -n istanbul1
8. Change asm search string as it was in older machines
alter system set asm_diskstring='/dev/ORACLE/*/*' scope=both;
9. Mount ASM diskgroups
alter diskgroup data1dg mount;
alter diskgroup data2dg mount;
10. Create init.ora files as in old servers.
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDBDB1.ora and
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDBDB2.ora
12. Create adump folder, give oracle user required OS rights
Check for audit_file_dest parameter in old system, create folder in all new nodes like below:
mkdir /u01/app/oracle/admin/MYDB/adump
chown oracle:oinstall mkdir /u01/app/oracle/admin/MYDB/adump
13. Start database [check alert.log while starting database]
srvctl start database -d MYDB
srvctl start database -d APEX
14. Add & start services to cluster [be sure you started MYDB database as in step 13]
srvctl add service -d MYDB -s MYSVC1 -r MYDB1 -a MYDB2
srvctl add service -d MYDB -s MYSVC2 -r MYDB1 -a MYDB2
srvctl start service -d MYDB -s MYSVC1
srvctl start service -d MYDB -s MYSVC2
15. Change remote_listener parameter in init.ora
ALTER SYSTEM SET remote_listener='mydb-scan.turkcell.tgc:1521' SCOPE=BOTH;
Sunday, October 14, 2012
How to open a database while restoring and recovering without archivelogs
In other words, how can I open a database, in a case that database is successfully restored, but cannot be recovered due to absence of archivelogs?
While initiating recovery from RMAN, we got below errors:
After investigating the cause of above messages by following:
2- Whether archive log sequence 111430 exists in recovery catalog querying from CATALOG DB metadata by:
3- Whether the sequence number exists in RMAN log file in /nsr/rman/log.
Not surpsrisingly, with parallel to above checking results,
there is not any entry for relevant archivelog sequence in RMAN log file.
4- Whether the each archivelog was archived, backed up before deleted; by querying production database:
And here comes, incomplete Inconsistent Recovery steps, make recovery without archivelogs:
Restore controlfile and datafile as usual.
Change parameter file as below.
Total System Global Area 1,0689E+11 bytes
Fixed Size 2240984 bytes
Variable Size 3,4897E+10 bytes
Database Buffers 7,1941E+10 bytes
Redo Buffers 55267328 bytes
SQL> @bckcf.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
Database altered.
SQL> alter database open resetlogs upgrade;
Database altered.
Controlfile change content is like below:
- The heart of opening the "wounded" database is based on using below init.ora parameters:
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_ALLOW_READ_ONLY_CORRUPTION = TRUE
UNDO_MANAGEMENT = MANUAL
Beware of using above parameters that it will be last resort for opening the database.
Further, we asked for _corrupted_rollback_segments parameter to Oracle ACS, thanks Osman Abi:), he told that we could have been used that parameter in case of corruption on undo segments which may prevent opening database.
- Restore environment is 11.2.0.3, and source database is 11.2.0.2, therefore we opened the db as if making upgrade by:
alter database open resetlogs upgrade;
- Lesson learnt from the case:
Monitor archivelog backup health by following:
select sequence#,archived,deleted,backup_count, status from gv$archived_log where sequence#=111430 and inst_id=2;
DETAILS
While initiating recovery from RMAN, we got below errors:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/09/2012 12:31:11
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 2 with sequence 111430 and starting SCN of 11345139269565 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111428 and starting SCN of 11344754878547 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111426 and starting SCN of 11344754846270 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111420 and starting SCN of 11344754612059 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111418 and starting SCN of 11344754547555 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111416 and starting SCN of 11344754483883 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111415 and starting SCN of 11344754472813 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111414 and starting SCN of 11344754461578 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111413 and starting SCN of 11344754450683 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 111412 and starting SCN of 11344754438403 found to restore
...After investigating the cause of above messages by following:
1- Whether archive log sequence 111430 exists in controlfile from RMAN connected to target database controlfile by:
oracle@kiev:/zfssa/restore1>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 9 16:52:27 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (DBID=1032014565, not open)
RMAN> list backup of archivelog FROM SEQUENCE 111245 UNTIL SEQUENCE 111247;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN>
2- Whether archive log sequence 111430 exists in recovery catalog querying from CATALOG DB metadata by:
RMAN> list backup of archivelog all completed between '2012-09-23 00:00:00' AND '2012-09-24 00:00:00';
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
199745 11.25M SBT_TAPE 00:00:10 23.09.2012 00:00:47
BP Key: 205852 Status: AVAILABLE Compressed: NO Tag: TAG20120923T000036
Handle: a3nlsh95_1_1 Media: J30643
List of Archived Logs in backup set 199745
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 83839 11343439796011 22.09.2012 23:30:05 11343439805018 23.09.2012 00:00:03
...3- Whether the sequence number exists in RMAN log file in /nsr/rman/log.
Not surpsrisingly, with parallel to above checking results,
there is not any entry for relevant archivelog sequence in RMAN log file.
4- Whether the each archivelog was archived, backed up before deleted; by querying production database:
select sequence#,archived,deleted,backup_count, status
from gv$archived_log where sequence#=111430 and inst_id=2;| SEQUENCE# | ARCHIVED | DELETED | BACKUP_COUNT | STATUS |
| 111430 | YES | YES | 0 | D |
As a result, we achieve a conclusion that archivelog file was deleted but not backed up because backup_count is 0.
And here comes, incomplete Inconsistent Recovery steps, make recovery without archivelogs:
Restore controlfile and datafile as usual.
Change parameter file as below.
oracle@kiev:/tmp>more /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDB.ora *._ALLOW_RESETLOGS_CORRUPTION= TRUE
*._ALLOW_READ_ONLY_CORRUPTION=TRUE
*.cluster_database=false
*.audit_file_dest='/u01/app/oracle/admin/MYDB/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_file_record_keep_time=14
*.control_files='/tmp/ctl_mydb_01.dbf'
*.cursor_sharing='FORCE'
*.db_block_size=32768
*.db_cache_size=0
*.db_create_file_dest='/zfssa/restore1'
*.db_domain=''
*.db_name='MYDB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBXDB)'
*.log_archive_dest_1='LOCATION=/zfssa/restore1'
*.memory_target=0
*.open_cursors=300
*.parallel_force_local=FALSE
*.parallel_max_servers=256
*.pga_aggregate_target=1212254720
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.sga_max_size=107374182400
*.sga_target=107374182400
*.shared_pool_size=34359738368
*.undo_tablespace='UNDOTBS1'
*.UNDO_MANAGEMENT=MANUALoracle@kiev:/tmp>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 9 19:41:37 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMYDB.ora';
ORACLE instance started.Total System Global Area 1,0689E+11 bytes
Fixed Size 2240984 bytes
Variable Size 3,4897E+10 bytes
Database Buffers 7,1941E+10 bytes
Redo Buffers 55267328 bytes
SQL> @bckcf.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
Database altered.
SQL> alter database open resetlogs upgrade;
Database altered.
Controlfile change content is like below:
oracle@kiev:/tmp>cat bckcf.ctl
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MYDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 34824
LOGFILE
GROUP 5 '/zfssa/restore1/redolog5.dbf' SIZE 500M BLOCKSIZE 512,
GROUP 6 '/zfssa/restore1/redolog6.dbf' SIZE 500M BLOCKSIZE 512,
GROUP 7 '/zfssa/restore1/redolog7.dbf' SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/zfssa/restore1/SYSTEM1.dbf',
'/zfssa/restore1/SYSAUX2.dbf',
'/zfssa/restore1/UNDOTBS3.dbf',
'/zfssa/restore1/UNDOTBS9.dbf',
'/zfssa/restore1/UNDOTBS4.dbf',
'/zfssa/restore1/UNDOTBS7.dbf',
'/zfssa/restore1/UNDOTBS8.dbf',
'/zfssa/restore1/UNDOTBS6.dbf',
'/zfssa/restore1/SYSAUX1.dbf',
'/zfssa/restore1/UNDOTBS2.dbf',
'/zfssa/restore1/UNDOTBS1.dbf',
'/zfssa/restore1/TBS_DWH_NODI_B.dbf'CHARACTER SET WE8ISO8859P9;
-- Create log files for threads other than thread one.
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 8 '/zfssa/restore1/redolog8.dbf' SIZE 500M BLOCKSIZE 512 REUSE,
GROUP 9 '/zfssa/restore1/redolog9.dbf' SIZE 500M BLOCKSIZE 512 REUSE,
GROUP 10 '/zfssa/restore1/redolog10.dbf' SIZE 500M BLOCKSIZE 512 REUSE;
-- Database can now be opened zeroing the online logs.
--ALTER DATABASE OPEN RESETLOGS;
- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
Friday, September 14, 2012
Oracle Database Gateway Characterset Problem
Problem
Environment: 11.1 Oracle Database Gateway
We are replacing, and consolidating our Oracle Database Gateway environment.
And after some time passed, we had encountered characterset problem.
- I will call gateway and gw instead of "Oracle Database Gateway" in the rest of this post.
While issuing select from remote sybase db table via new gateway,
we are seeing strange characters instead of Turkish characters.
Cause
After investigating the problem, we found that NLS_LANG environment,
variable in former gateway is different from the new gateway environment.
Solution
There is two alternative way of solving this problem:
First alternative:
Gateway is a listener, and only process you can see associated with listener is:
oracle@gw:>;;ps -ef|grep inherit|grep _MYDB
oracle 21758314 1 0 Sep 13 pts/0 0:01 /u01/app/oracle/product/11.1.0/bin/tnslsnr GW_MYDB_1664 -inherit
So, changing NLS_LANG parameter before starting listener solves the problem.
But, this is not a clean solution, because you need to know and deal with the environment variable before starting the listener.
Putting this to oracle user profile file "oracle@gw:/home/oracle/.profile" does not work for our situation,
because this is a consolidated gateway environment
and there exists other gateway definitions for other oracle to non-oracle databases already configured and running.
Second alternative:
While connecting to Sybase side, there is a parameter file influencing the sybase driver used by the listener.
After adding the HS_LANGUAGE parameter as below it solved the character set problem.
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
Also, there is a lesson while making the trials for seeing the character problem.
It is, after changing the initMYDB.ora parameter file, and stop/starting the gateway listener,
you must close database link and issue the select statement after then.
If you do not close the database link by below SQL command,
it still uses the old initMYDB.ora based configuration.
select * from dual;
commit;
alter session close database link DG_NEMS_PRE;
SELECT * FROM "dbo"."my_table1"@DG_MYDB_LIVE;
Gateway parameter file for sybase is like below:
/u01/app/oracle/product/11.1.0/dg4sybs/admin/initMYDB.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for Sybase
HS_FDS_CONNECT_INFO=10.10.44.22:4100/mydb1
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_STRING="EnableSPColumnTypes=2"
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRACE_FILE_NAME = /home/oracle/derya/dg4sybs_mydb1.trc
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
#
# Environment variables required for Sybase
#
set SYBASE=/u01/app/sybase
set LIBPATH=/u01/app/sybase/OCS-12_5/lib:/u01/app/sybase:/u01/app/oracle/product/11.1.0/lib
HS_FDS_QUOTE_IDENTIFIER=FALSE
Listener.ora entry for the service, given for informational purpose, not really needed for this case:
GW_MYDB_1664 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.20) (PORT = 1664))
)
)
)
SID_LIST_GW_MYDB_1664 =
(SID_LIST =
(SID_DESC =
(SID_NAME = MYDB1)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0)
(PROGRAM = dg4sybs)
(ENVS=LIBPATH=/u01/app/oracle/product/11.1.0/dg4sybs/driver/lib:/u01/app/oracle/product/11.1.0/lib)
)
)
You may check for gateway configuration from a former post. This post covers configuration steps: from target sybase database gateway parameter file creation to source oracle database dblink creation.
http://deryaoktay.wordpress.com/2012/05/22/defining-database-link-by-using-oracle-gateway/
Environment: 11.1 Oracle Database Gateway
We are replacing, and consolidating our Oracle Database Gateway environment.
And after some time passed, we had encountered characterset problem.
- I will call gateway and gw instead of "Oracle Database Gateway" in the rest of this post.
While issuing select from remote sybase db table via new gateway,
we are seeing strange characters instead of Turkish characters.
Cause
After investigating the problem, we found that NLS_LANG environment,
variable in former gateway is different from the new gateway environment.
Solution
There is two alternative way of solving this problem:
First alternative:
Gateway is a listener, and only process you can see associated with listener is:
oracle@gw:>;;ps -ef|grep inherit|grep _MYDB
oracle 21758314 1 0 Sep 13 pts/0 0:01 /u01/app/oracle/product/11.1.0/bin/tnslsnr GW_MYDB_1664 -inherit
So, changing NLS_LANG parameter before starting listener solves the problem.
But, this is not a clean solution, because you need to know and deal with the environment variable before starting the listener.
Putting this to oracle user profile file "oracle@gw:/home/oracle/.profile" does not work for our situation,
because this is a consolidated gateway environment
and there exists other gateway definitions for other oracle to non-oracle databases already configured and running.
Second alternative:
While connecting to Sybase side, there is a parameter file influencing the sybase driver used by the listener.
After adding the HS_LANGUAGE parameter as below it solved the character set problem.
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
Also, there is a lesson while making the trials for seeing the character problem.
It is, after changing the initMYDB.ora parameter file, and stop/starting the gateway listener,
you must close database link and issue the select statement after then.
If you do not close the database link by below SQL command,
it still uses the old initMYDB.ora based configuration.
select * from dual;
commit;
alter session close database link DG_NEMS_PRE;
SELECT * FROM "dbo"."my_table1"@DG_MYDB_LIVE;
Gateway parameter file for sybase is like below:
/u01/app/oracle/product/11.1.0/dg4sybs/admin/initMYDB.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for Sybase
HS_FDS_CONNECT_INFO=10.10.44.22:4100/mydb1
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_STRING="EnableSPColumnTypes=2"
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRACE_FILE_NAME = /home/oracle/derya/dg4sybs_mydb1.trc
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
#
# Environment variables required for Sybase
#
set SYBASE=/u01/app/sybase
set LIBPATH=/u01/app/sybase/OCS-12_5/lib:/u01/app/sybase:/u01/app/oracle/product/11.1.0/lib
HS_FDS_QUOTE_IDENTIFIER=FALSE
Listener.ora entry for the service, given for informational purpose, not really needed for this case:
GW_MYDB_1664 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.20) (PORT = 1664))
)
)
)
SID_LIST_GW_MYDB_1664 =
(SID_LIST =
(SID_DESC =
(SID_NAME = MYDB1)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0)
(PROGRAM = dg4sybs)
(ENVS=LIBPATH=/u01/app/oracle/product/11.1.0/dg4sybs/driver/lib:/u01/app/oracle/product/11.1.0/lib)
)
)
You may check for gateway configuration from a former post. This post covers configuration steps: from target sybase database gateway parameter file creation to source oracle database dblink creation.
http://deryaoktay.wordpress.com/2012/05/22/defining-database-link-by-using-oracle-gateway/
Thursday, August 16, 2012
PLSQL Notes From 3 day course in Turkcell Academy
Last week, I had a chance to be author of PLSQL course in Turkcell Academy.
Following issues worth noting which were a product of focused working/reading, nearly for a month, on PL/SQL topic:
-If you handle the exception fired by error RAISE_APPLICATION_ERROR in a BEFORE INSERT trigger body, it is surprising that the record is inserted despite the error message which is shown by RAISE_APPLICATION_ERROR.
- It is also strange that, if you want to prevent truncate table statements with a trigger. Although it is not executed, it will issue COMMIT implicitly because truncate statement is DDL.
http://hemantoracledba.blogspot.com/2011/06/ddl-triggers.html
- In order to get use of 11G fine grained dependency feature, thus not to invalidate procedures, views when exactly not needed, in sql statements you must reference underlying table columns with fully qualified names.
http://htmldb.oracle.com/pls/otn/f?p=2853:4:830114251864401::NO::P4_QA_ID:15642
Following issues worth noting which were a product of focused working/reading, nearly for a month, on PL/SQL topic:
-If you handle the exception fired by error RAISE_APPLICATION_ERROR in a BEFORE INSERT trigger body, it is surprising that the record is inserted despite the error message which is shown by RAISE_APPLICATION_ERROR.
- It is also strange that, if you want to prevent truncate table statements with a trigger. Although it is not executed, it will issue COMMIT implicitly because truncate statement is DDL.
http://hemantoracledba.blogspot.com/2011/06/ddl-triggers.html
- In order to get use of 11G fine grained dependency feature, thus not to invalidate procedures, views when exactly not needed, in sql statements you must reference underlying table columns with fully qualified names.
http://htmldb.oracle.com/pls/otn/f?p=2853:4:830114251864401::NO::P4_QA_ID:15642
Thursday, August 2, 2012
HP returns the Oracle game
Despite the dbas murmuring about Oracle working on HPUX,it seems unfair a vendor is disqualified after years of contribution to Oracle technology.
And good news, HP wins the Itanium lawsuit against Oracle.
http://allthingsd.com/20120801/hp-wins-key-ruling-in-itanium-lawsuit-with-oracle/?mod=googlenews
And good news, HP wins the Itanium lawsuit against Oracle.
http://allthingsd.com/20120801/hp-wins-key-ruling-in-itanium-lawsuit-with-oracle/?mod=googlenews
Why PLSQL is efficient than any other PL on Oracle database?
12 years ago when I argued on the performance of Programming Languages with an experienced Java programmer, who was developing CORBA like communication infrastructure and preferring only Java, confessed that a dba might develop better performance code than him in PLSQL.
In Turkish, there is a saying "tools work, hands take pride on". So why not use PLSQL much more...
PLSQL is really on database and closer to database layer any other Programming Languae could manage to be .
After years passed, using SQL instead of PLSQL was my first goal while implementing any requirement.
I had first seen below statement nearly 1 year ago, and it really fit my thoughts which I have not been clearly stated before. I really appreciated it and compelled to get my knowledge world.
http://tkyte.blogspot.com/2006/10/slow-by-slow.html
In Turkish, there is a saying "tools work, hands take pride on". So why not use PLSQL much more...
PLSQL is really on database and closer to database layer any other Programming Languae could manage to be .
After years passed, using SQL instead of PLSQL was my first goal while implementing any requirement.
I had first seen below statement nearly 1 year ago, and it really fit my thoughts which I have not been clearly stated before. I really appreciated it and compelled to get my knowledge world.
My mantra, that I'll be sticking with thank you very much, is:
- You should do it in a single SQL statement if at all possible.
- If you cannot do it in a single SQL Statement, then do it in PL/SQL.
- If you cannot do it in PL/SQL, try a Java Stored Procedure.
- If you cannot do it in Java, do it in a C external procedure.
- If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…
http://tkyte.blogspot.com/2006/10/slow-by-slow.html
Friday, July 27, 2012
active use of diskgroup "DATA" precludes its dismount
Problem
Environment details:
I want to drop database by drop database command.
Because, my SYSTEM tablespace needs recovery and I am lack of archive logs, I haven't chance to issue "drop database".
Further I tried to delete files by RMAN, but it does not work either. Because my controlfile also could not be found. Last resort, I tried flushing ASM disk headers by dd, it does not work either, it still shows DATA diskgroup and ASM disks. And finally I tried to drop disk group...
From asmca and asm command line I get below error message while dropping DATA disk group.
SQL> DROP DISKGROUP data INCLUDING CONTENTS;
DROP DISKGROUP data INCLUDING CONTENTS *
ERROR at line 1:
ORA-15039: diskgroup not dropped ORA-15027: active use of diskgroup "DATA" precludes its dismount
Cause
I do not know the real cause, but, as I stated above, it may be because of following reasons:
Solution
I managed to drop disk group by:
Note that, below trials does not work, I got same error message, despite the fact that the database remains closed state.
Environment details:
Restore test environment.
HP-UX myhost1 B.11.31 U ia64
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
I want to drop database by drop database command.
Because, my SYSTEM tablespace needs recovery and I am lack of archive logs, I haven't chance to issue "drop database".
Further I tried to delete files by RMAN, but it does not work either. Because my controlfile also could not be found. Last resort, I tried flushing ASM disk headers by dd, it does not work either, it still shows DATA diskgroup and ASM disks. And finally I tried to drop disk group...
From asmca and asm command line I get below error message while dropping DATA disk group.
SQL> DROP DISKGROUP data INCLUDING CONTENTS;
DROP DISKGROUP data INCLUDING CONTENTS *
ERROR at line 1:
ORA-15039: diskgroup not dropped ORA-15027: active use of diskgroup "DATA" precludes its dismount
Cause
I do not know the real cause, but, as I stated above, it may be because of following reasons:
- lack of controlfile
-SYSTEM tablespace needs media recovery
Solution
I managed to drop disk group by:
alter diskgroup data dismount force;
drop diskgroup data force including contents;
Note that, below trials does not work, I got same error message, despite the fact that the database remains closed state.
srvctl stop diskgroup -g DATA
srvctl disable diskgroup -g DATA
srvctl stop asm
Tuesday, July 10, 2012
fast_start_parallel_rollback recovery effect
I had a chance to see the effect of recovery while changing the fast_start_parallel_rollback parameter values.
Our database environment:
OS version: 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
DB version: 11.2.0.2
CPU_COUNT = 64
I had used some internal script pari which uses gv$px_session view for finding number of parallel slave processes.
FAST_START_PARALLEL_ROLLBACK = LOW
From the Oracle documentation remember:
ADMIN@MYDB:MYDB2> @pari
After setting fast_start_parallel_rollback value from 'LOW' to 'FALSE'.
-------------
The recovery is not so fast, so I changed the value of the parameter again.
After setting fast_start_parallel_rollback value from 'FALSE' to 'HIGH'.
Despite the huge number of parallel processes overhead, the outcome is remarkable, that you can see it is faster.
Our database environment:
OS version: 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
DB version: 11.2.0.2
CPU_COUNT = 64
I had used some internal script pari which uses gv$px_session view for finding number of parallel slave processes.
FAST_START_PARALLEL_ROLLBACK = LOW
From the Oracle documentation remember:
FALSEParallel rollback is disabled
LOWLimits the maximum degree of parallelism to 2 *CPU_COUNT
HIGHLimits the maximum degree of parallelism to 4 *CPU_COUNT
(http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams083.htm)
ADMIN@MYDB:MYDB2> @pari
INSTANCE USERNAME OSUSER ACTIVE INACTIVE SES_COUNT PERCENT
-------------------- ------------------------- --------------- --------- --------- --------- -------
MYDB1-MYDB1 ODI MYUSER 13 %5,07
oracle 12 116 128 %50
MYDB2-MYDB2 ODI MYUSER 14 11 31 %12,1
INSTANCE Username QC/Slave SID SERIAL OSUSER HASH_VALUE AUDSID Re Ac MACHINE STATUS
-------------------- ------------------------- ---------- ------ ------ --------------- ------------ ------------ -- -- ------------ ----------
MYDB1-MYDB1 ODI QC 1059 18677 MYUSER 3974800174 10212110 TURKCELL\T ACTIVE
QC 721 1 oracle 0 0 MYDB1.bi ACTIVE
QC 721 1 0 0 MYDB2.bi ACTIVE
MYDB2-MYDB2 ADMIN QC 1061 1393 MYUSER2 3551939252 10221613 TURKCELL\T ACTIVE
INSTANCE PARALLEL PROCESS INFO
-------------------- ----------------------------------------
MYDB2-MYDB2 USED : 31 AVAILABLE : 225 TOTAL : 256
MYDB1-MYDB1 USED : 141 AVAILABLE : 115 TOTAL : 256
ADMIN@MYDB:MYDB2>
ADMIN@MYDB:MYDB2> select * from gV$FAST_START_TRANSACTIONS;
INST_ID USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
--------- --------- --------- --------- ------------------------------------------------ -------------- --------------- --------- --------- --------- --------- --------- ---------------- ---------------- ----------
1 71 112 9176 RECOVERING 5211 159526 120 137 167 27 4681 47007000D8230000 A7001B0049120000 126
1 150 21 11387 RECOVERING 4019 155416 85 137 167 27 4681 960015007B2C0000 A7001B0049120000 1
1 271 25 1662 RECOVERING 2397 148283 89 137 167 27 4681 0F0119007E060000 A7001B0049120000 1
1 42 41 37080 RECOVERED 28 28 10 2A002900D8900000 128
4 rows selected.
-------------
After setting fast_start_parallel_rollback value from 'LOW' to 'FALSE'.
ADMIN@MYDB:MYDB2> alter system set fast_start_parallel_rollback=FALSE scope=both sid=*;
ADMIN@MYDB:MYDB2> select * from GV$FAST_START_TRANSACTIONS;
INST_ID USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
--------- --------- --------- --------- ------------------------------------------------ -------------- --------------- --------- --------- --------- --------- --------- ---------------- ---------------- ----------
1 71 112 9176 RECOVERING 4 153858 1 47007000D8230000 0
1 271 25 1662 RECOVERING 4 145586 1 0F0119007E060000 0
1 150 21 11387 RECOVERING 5 151280 1 960015007B2C0000 0
1 42 41 37080 RECOVERED 28 28 10 2A002900D8900000 128
4 rows selected.
ADMIN@MYDB:MYDB2> @pari
INSTANCE USERNAME OSUSER ACTIVE INACTIVE SES_COUNT PERCENT
-------------------- ------------------------- --------------- --------- --------- --------- -------
MYDB1-MYDB1 ODI MYUSER 13 %5,07
MYDB2-MYDB2 ODI MYUSER 14 11 31 %12,1
INSTANCE Username QC/Slave SID SERIAL OSUSER HASH_VALUE AUDSID Re Ac MACHINE STATUS
-------------------- ------------------------- ---------- ------ ------ --------------- ------------ ------------ -- -- ------------ ----------
MYDB1-MYDB1 ODI QC 1059 18677 MYUSER 3974800174 10212110 TURKCELL\T ACTIVE
MYDB2-MYDB2 ADMIN QC 1061 1393 MYUSER2 3551939252 10221613 TURKCELL\T ACTIVE
INSTANCE PARALLEL PROCESS INFO
-------------------- ----------------------------------------
MYDB2-MYDB2 USED : 31 AVAILABLE : 225 TOTAL : 256
MYDB1-MYDB1 USED : 13 AVAILABLE : 243 TOTAL : 256
ADMIN@MYDB:MYDB2>
-------------
The recovery is not so fast, so I changed the value of the parameter again.
After setting fast_start_parallel_rollback value from 'FALSE' to 'HIGH'.
Despite the huge number of parallel processes overhead, the outcome is remarkable, that you can see it is faster.
ADMIN@MYDB:MYDB2> alter system set fast_start_parallel_rollback=HIGH scope=both sid=*;
ADMIN@MYDB:MYDB2> select * from GV$FAST_START_TRANSACTIONS;
INST_ID USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
--------- --------- --------- --------- ------------------------------------------------ -------------- --------------- --------- --------- --------- --------- --------- ---------------- ---------------- ----------
1 71 112 9176 RECOVERING 1272 16970 80 24 167 27 4681 47007000D8230000 A7001B0049120000 233
1 150 21 11387 RECOVERING 651 20408 81 24 167 27 4681 960015007B2C0000 A7001B0049120000 1
1 271 25 1662 RECOVERING 909 11312 82 24 167 27 4681 0F0119007E060000 A7001B0049120000 1
1 42 41 37080 RECOVERED 28 28 10 2A002900D8900000 128
ADMIN@MYDB:MYDB2> @pari
INSTANCE USERNAME OSUSER ACTIVE INACTIVE SES_COUNT PERCENT
-------------------- ------------------------- --------------- --------- --------- --------- -------
MYDB1-MYDB1 ODI MYUSER 13 %5,07
oracle 243 0 243 %94,92
MYDB2-MYDB2 ODI MYUSER 24 0 30 %11,71
INSTANCE Username QC/Slave SID SERIAL OSUSER HASH_VALUE AUDSID Re Ac MACHINE STATUS
-------------------- ------------------------- ---------- ------ ------ --------------- ------------ ------------ -- -- ------------ ----------
MYDB1-MYDB1 ODI QC 1059 18677 MYUSER 0 10212110 TURKCELL\T ACTIVE
QC 721 1 oracle 0 0 MYDB1.bi ACTIVE
QC 721 1 0 0 MYDB2.bi ACTIVE
MYDB2-MYDB2 ADMIN QC 1061 1393 MYUSER2 3551939252 10221613 TURKCELL\T ACTIVE
INSTANCE PARALLEL PROCESS INFO
-------------------- ----------------------------------------
MYDB1-MYDB1 USED : 256 AVAILABLE : 0 TOTAL : 256
MYDB2-MYDB2 USED : 30 AVAILABLE : 226 TOTAL : 256
ADMIN@MYDB:MYDB2>
Tuesday, July 3, 2012
Why not any sql work parallel in database?
Problem
Environment:
Exadata X2-2
Oracle: 11.2.0.2.3
Linux version:2.6.18-274.18.1.0.1.el5
My customer came to me that the UPDATE statement which used to finish around 30 minutes, now lasts longer and does not finish.
After investigating the issue, the problem turns out to be parallelism problem.
Rephrasing the problem: Sessions cannot work parallel.
For a SQL like below, Oracle EM SQL Monitor detailed screenshot is given:
select /*+ PARALLEL (T 16) */ *from MYSCHEMA.MYTABLE;
Cause
Who knows ...
I checked below areas for discovering for the cause, but nothing found:
- execution plan: There exists PX COORDINATOR lines, no problem.
- parallel hint in the query:Syntax correct, it must work.
- parallel_max_servers: It is 256.
- select * from v$px_process: No rows returned.
- DEGREE value of table: 16.
- Checking parallelism value for consumer group: 16
- Disabling resource plans: Disabling resource plan does not change the behavior.
- alert.log: No parameter change or internal error in the log.
Solution
Killing user processes, make it work!
It got the parallelism.
Environment:
Exadata X2-2
Oracle: 11.2.0.2.3
Linux version:2.6.18-274.18.1.0.1.el5
My customer came to me that the UPDATE statement which used to finish around 30 minutes, now lasts longer and does not finish.
After investigating the issue, the problem turns out to be parallelism problem.
Rephrasing the problem: Sessions cannot work parallel.
For a SQL like below, Oracle EM SQL Monitor detailed screenshot is given:
select /*+ PARALLEL (T 16) */ *from MYSCHEMA.MYTABLE;
Cause
Who knows ...
I checked below areas for discovering for the cause, but nothing found:
- execution plan: There exists PX COORDINATOR lines, no problem.
- parallel hint in the query:Syntax correct, it must work.
- parallel_max_servers: It is 256.
- select * from v$px_process: No rows returned.
- DEGREE value of table: 16.
- Checking parallelism value for consumer group: 16
- Disabling resource plans: Disabling resource plan does not change the behavior.
- alert.log: No parameter change or internal error in the log.
Click on image, for bigger size.
Solution
Killing user processes, make it work!
It got the parallelism.
Thursday, June 7, 2012
Why parallel expdp gives unable to write error
Problem
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object "MYSCHEMA"."MYTABLE" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/gecici/expdp/MYSCHEMAS04.expdp" for write
ORA-19505: failed to identify file "/gecici/expdp/MYSCHEMAS04.expdp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Cause
Error triggered, because it is RAC and consists of two nodes, and the filesystem mounted only on one of the nodes.
I thought that , if datapump had started without parallel option, it would not go to other db node, and everything would go well.
Because it is not the case... Datapump is started in parallel mode, and it tries to start parallel processes in ALL nodes of the cluster.
Solution
In order to prevent to start the parallel process in other node, I changed the parameter parallel_force_local value as true.
And also ensure my TNS entry will allow to go to only to desired node, by giving SID instead of SERVICE_NAME in the connection description as below:
MYDB1=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydb1.turkcell.tgc)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = MYDB1)))
But it still continue to give the same error.
Here comes the solution:
In 11.2, CLUSTER parameter has come. It will enable expdp to work properly in RAC as below:
expdp system@MYDB1 SCHEMAS=MYSCHEMA1,MYSCHEMA2,MYSCHEMA3 DIRECTORY=DATAPUMPDIR LOGFILE=MYDB.log DUMPFILE=MYDB%U.expdp COMPRESSION=ALL EXCLUDE=STATISTICS PARALLEL=8 CLUSTER=N
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object "MYSCHEMA"."MYTABLE" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/gecici/expdp/MYSCHEMAS04.expdp" for write
ORA-19505: failed to identify file "/gecici/expdp/MYSCHEMAS04.expdp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Cause
Error triggered, because it is RAC and consists of two nodes, and the filesystem mounted only on one of the nodes.
I thought that , if datapump had started without parallel option, it would not go to other db node, and everything would go well.
Because it is not the case... Datapump is started in parallel mode, and it tries to start parallel processes in ALL nodes of the cluster.
Solution
In order to prevent to start the parallel process in other node, I changed the parameter parallel_force_local value as true.
And also ensure my TNS entry will allow to go to only to desired node, by giving SID instead of SERVICE_NAME in the connection description as below:
MYDB1=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mydb1.turkcell.tgc)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = MYDB1)))
But it still continue to give the same error.
Here comes the solution:
In 11.2, CLUSTER parameter has come. It will enable expdp to work properly in RAC as below:
expdp system@MYDB1 SCHEMAS=MYSCHEMA1,MYSCHEMA2,MYSCHEMA3 DIRECTORY=DATAPUMPDIR LOGFILE=MYDB.log DUMPFILE=MYDB%U.expdp COMPRESSION=ALL EXCLUDE=STATISTICS PARALLEL=8 CLUSTER=N
Thursday, May 31, 2012
Oracle user cannot write to ASM DATA diskgroup after succesful installation of grid infra in 11.2
Problem:
While restoring controlfile to test environment, from filesystem or tape environment after installing grid infrastructure.
I got following errors from both RMAN and DBCA, despite the fact that I could create directory alias in asmcmd.
RMAN> run {
2> allocate channel t1 device type 'SBT_TAPE'
3> parms 'ENV=(NSR_SERVER=bcksrv1, NSR_CLIENT=mydb, NSR_DATA_VOLUME_POOL=JB10)';
4> RESTORE controlfile to '+DATA' FROM '/tmp/ctl01.dbf';
5> release channel t1 ;
6> }
allocated channel: t1
channel t1: SID=386 device type=SBT_TAPE
channel t1: NMO v5.0.0.0
Starting restore at 29-MAY-12
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/29/2012 14:31:34
ORA-19504: failed to create file "+DATA"
ORA-15045: ASM file name '+DATA' is not in reference form
ORA-17502: ksfdcre:5 Failed to create file +DATA
ORA-15081: failed to submit an I/O operation to a disk
ORA-19600: input file is control file (/tmp/ctl01.dbf)
ORA-19601: output file is control file (+DATA)
Cause:
Access rights to oracle disk.
Solution:
I checked the asm disks. /dev/rdsk/disk * has grid:oinstall ownership.
Grid infra home and bin has grid:oinstall, while the oracle home and bin has oracle:oinstall, as told in the MOS.
In our case, access rights on disk device files, are not 77x. As the second 7 means access to group oinstall.
I changed the access rights to 777 and it worked.
While restoring controlfile to test environment, from filesystem or tape environment after installing grid infrastructure.
I got following errors from both RMAN and DBCA, despite the fact that I could create directory alias in asmcmd.
RMAN> run {
2> allocate channel t1 device type 'SBT_TAPE'
3> parms 'ENV=(NSR_SERVER=bcksrv1, NSR_CLIENT=mydb, NSR_DATA_VOLUME_POOL=JB10)';
4> RESTORE controlfile to '+DATA' FROM '/tmp/ctl01.dbf';
5> release channel t1 ;
6> }
allocated channel: t1
channel t1: SID=386 device type=SBT_TAPE
channel t1: NMO v5.0.0.0
Starting restore at 29-MAY-12
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/29/2012 14:31:34
ORA-19504: failed to create file "+DATA"
ORA-15045: ASM file name '+DATA' is not in reference form
ORA-17502: ksfdcre:5 Failed to create file +DATA
ORA-15081: failed to submit an I/O operation to a disk
ORA-19600: input file is control file (/tmp/ctl01.dbf)
ORA-19601: output file is control file (+DATA)
Cause:
Access rights to oracle disk.
Solution:
I checked the asm disks. /dev/rdsk/disk * has grid:oinstall ownership.
Grid infra home and bin has grid:oinstall, while the oracle home and bin has oracle:oinstall, as told in the MOS.
In our case, access rights on disk device files, are not 77x. As the second 7 means access to group oinstall.
I changed the access rights to 777 and it worked.
Tuesday, May 22, 2012
Defining database link by using Oracle Gateway
For configuring a remote non-Oracle database connections on Oracle gateway,
I followed below steps, hope this helps.
1) Create below file in the gateway, with appropriate values like the example below:
Beware of the value of X in the file name: init<X>.ora
X value will later be used in listener.ora file SID_NAME parameter.
Beware of connect string line: HS_FDS_CONNECT_INFO=10.1.1.1:4100/mysydb1
Ip,port and database name triple belongs to the remote sybase machine.
Server: Gateway
File name: /u01/app/oracle/product/11.1.0/dg4sybs/admin/initMYSYDB1.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for Sybase
#HS_FDS_TRACE_LEVEL=OFF
HS_FDS_CONNECT_INFO=10.1.1.1:4100/mysydb1
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_STRING="EnableSPColumnTypes=2"
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRACE_FILE_NAME = /tmp/dg4sybs_mysydb.trc
#
# Environment variables required for Sybase
#
set SYBASE=/u01/app/sybase
set LIBPATH=/u01/app/sybase/OCS-12_5/lib:/u01/app/sybase:/u01/app/oracle/product/11.1.0/lib
HS_FDS_QUOTE_IDENTIFIER=FALSE
2) In the gateway machine (in my example host ip: 10.1.1.200), Enter lines in listener.ora file:
Server: Gateway
File name: /u01/app/oracle/product/11.1.0/network/admin/listener.ora
MYSYDB_1522 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.200) (PORT = 1522))
)
)
)
SID_LIST_MYSYDB_1522 =
(SID_LIST =
(SID_DESC =
(SID_NAME = MYSYDB1)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0)
(PROGRAM = dg4sybs)
(ENVS=LIBPATH=/u01/app/oracle/product/11.1.0/dg4sybs/driver/lib:/u01/app/oracle/product/11.1.0/lib)
)
)
3) Start the service by issueing below command:
lnsrctl start MYSYDB_1522
4) Enter TNS entry to Oracle server where you created dblink.
If it is RAC you must re-enter this information in all nodes.
10.1.1.200 ip belongs to gateway server.
Server: Local DB(s)
File name: /u01/app/product/DB/network/admin/tnsnames.ora
REMOTEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.1.1.200)(PORT = 1522))
(CONNECT_DATA =
(SID = MYSYDB1)
)
(HS = OK)
)
5) Create dblink in the database side, using the tns entry in previous step:
CREATE PUBLIC DATABASE LINK MYDBLINK
CONNECT TO "user1"
IDENTIFIED BY <PWD>
USING 'REMOTEDB';
Troubleshooting :
1-
While testing with select * from dual@MYDBLINK;
it was giving below error. This was because of a sybase side logon trigger.
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC Sybase Wire Protocol driver]Socket closed. {08S01}[Oracle][ODBC Sybase Wire Protocol driver]Sybase Network Connection has terminated. If a transaction was in progress it has been aborted. {08S01}[Oracle][ODBC Sybase Wire Protocol driver][Sybase ASE]Execution of login script '"sp_adm_check_int_users"' failed with last error = 11039. See server errorlog for details.
{HY000,NativeErr = 1638}[Oracle][ODBC Sybase Wire Protocol driver]File not found '.odbc.ini'. {HY000,NativeErr = 1509}
ORA-02063: preceding 3 lines from MYDBLINK
sp_adm_check_int_users is a logon checking procedure, which authenticates conenctions according to source host ip/hostname.
2- You can check for any error logs in the following directory.
/u01/app/oracle/product/11.1.0/dg4sybs/log
-rw-r--r-- 1 oracle dba 11012 May 08 11:37 MYSYDB1_agt_65733070.trc
-rw-r--r-- 1 oracle dba 11012 May 08 11:39 MYSYDB1_agt_59441834.trc
-rw-r--r-- 1 oracle dba 11012 May 08 15:38 MYSYDB1_agt_54329962.trc
-rw-r--r-- 1 oracle dba 2113496 May 09 19:05 MYSYDB1_agt_22414304.trc
-rw-r--r-- 1 oracle dba 18585 May 10 15:46 MYSYDB1_agt_5701974.trc
3- You may get TNS error: ORA-12154: TNS:could not resolve the connect identifier specified
In my case, it was I forgot to enter the tns entry in other node of the RAC.
I followed below steps, hope this helps.
1) Create below file in the gateway, with appropriate values like the example below:
Beware of the value of X in the file name: init<X>.ora
X value will later be used in listener.ora file SID_NAME parameter.
Beware of connect string line: HS_FDS_CONNECT_INFO=10.1.1.1:4100/mysydb1
Ip,port and database name triple belongs to the remote sybase machine.
Server: Gateway
File name: /u01/app/oracle/product/11.1.0/dg4sybs/admin/initMYSYDB1.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for Sybase
#HS_FDS_TRACE_LEVEL=OFF
HS_FDS_CONNECT_INFO=10.1.1.1:4100/mysydb1
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_STRING="EnableSPColumnTypes=2"
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRACE_FILE_NAME = /tmp/dg4sybs_mysydb.trc
#
# Environment variables required for Sybase
#
set SYBASE=/u01/app/sybase
set LIBPATH=/u01/app/sybase/OCS-12_5/lib:/u01/app/sybase:/u01/app/oracle/product/11.1.0/lib
HS_FDS_QUOTE_IDENTIFIER=FALSE
2) In the gateway machine (in my example host ip: 10.1.1.200), Enter lines in listener.ora file:
Server: Gateway
File name: /u01/app/oracle/product/11.1.0/network/admin/listener.ora
MYSYDB_1522 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.200) (PORT = 1522))
)
)
)
SID_LIST_MYSYDB_1522 =
(SID_LIST =
(SID_DESC =
(SID_NAME = MYSYDB1)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0)
(PROGRAM = dg4sybs)
(ENVS=LIBPATH=/u01/app/oracle/product/11.1.0/dg4sybs/driver/lib:/u01/app/oracle/product/11.1.0/lib)
)
)
3) Start the service by issueing below command:
lnsrctl start MYSYDB_1522
4) Enter TNS entry to Oracle server where you created dblink.
If it is RAC you must re-enter this information in all nodes.
10.1.1.200 ip belongs to gateway server.
Server: Local DB(s)
File name: /u01/app/product/DB/network/admin/tnsnames.ora
REMOTEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.1.1.200)(PORT = 1522))
(CONNECT_DATA =
(SID = MYSYDB1)
)
(HS = OK)
)
5) Create dblink in the database side, using the tns entry in previous step:
CREATE PUBLIC DATABASE LINK MYDBLINK
CONNECT TO "user1"
IDENTIFIED BY <PWD>
USING 'REMOTEDB';
Troubleshooting :
1-
While testing with select * from dual@MYDBLINK;
it was giving below error. This was because of a sybase side logon trigger.
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC Sybase Wire Protocol driver]Socket closed. {08S01}[Oracle][ODBC Sybase Wire Protocol driver]Sybase Network Connection has terminated. If a transaction was in progress it has been aborted. {08S01}[Oracle][ODBC Sybase Wire Protocol driver][Sybase ASE]Execution of login script '"sp_adm_check_int_users"' failed with last error = 11039. See server errorlog for details.
{HY000,NativeErr = 1638}[Oracle][ODBC Sybase Wire Protocol driver]File not found '.odbc.ini'. {HY000,NativeErr = 1509}
ORA-02063: preceding 3 lines from MYDBLINK
sp_adm_check_int_users is a logon checking procedure, which authenticates conenctions according to source host ip/hostname.
2- You can check for any error logs in the following directory.
/u01/app/oracle/product/11.1.0/dg4sybs/log
-rw-r--r-- 1 oracle dba 11012 May 08 11:37 MYSYDB1_agt_65733070.trc
-rw-r--r-- 1 oracle dba 11012 May 08 11:39 MYSYDB1_agt_59441834.trc
-rw-r--r-- 1 oracle dba 11012 May 08 15:38 MYSYDB1_agt_54329962.trc
-rw-r--r-- 1 oracle dba 2113496 May 09 19:05 MYSYDB1_agt_22414304.trc
-rw-r--r-- 1 oracle dba 18585 May 10 15:46 MYSYDB1_agt_5701974.trc
3- You may get TNS error: ORA-12154: TNS:could not resolve the connect identifier specified
In my case, it was I forgot to enter the tns entry in other node of the RAC.
Monday, May 21, 2012
NID: renaming your database in a minute
NID Utility, "DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database". - excerpt from Oracle documentation.
When I first learnt from a guy, fortunately not newbie dba, it is a real shame for me to hear about nid very late in my dba career.
The day before I heard about nid, we were discussing with another 16 year dba, why Oracle could not rename db in year 2012, even preparing for version 12. But it was a real disaster that we discovered that there was a tool since year 2001, version 9. (Oracle documentation about nid utiliy in 9i) This gave real pain that I could not write about "nid" for two weeks, and rethink about continueing my career in dba position :)
Before nid, we were producing controlfile by backup controlfile to trace, changing its contents, etc. No need to these disgusting and a bit lengthy process.
Here's what I did, while changing the name of database which I had restored for making a test and development environment. Changing database name from MYDB to MYDBTEST.
[oracle@myhostt01 ~]$ nid TARGET=SYS DBNAME=MYDBTEST logfile='/tmp/mydb_nid.log'
Password:
[oracle@myhostt01 ~]$
-- log file details
[oracle@myhostt01 ~]$ tail -f /tmp/mydb_nid.log
Instance shut down
Database name changed to MYDBTEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database MYDBTEST changed to 1233471344.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
-- change dbname in init.ora
[oracle@myhostt01 ~]$ vi /tmp/init2.ora
MYDBTEST.__db_cache_size=4378853376
MYDBTEST.__java_pool_size=16777216
MYDBTEST.__large_pool_size=16777216
MYDBTEST.__pga_aggregate_target=1073741824
MYDBTEST.__sga_target=5368709120
MYDBTEST.__shared_io_pool_size=0
MYDBTEST.__shared_pool_size=922746880
MYDBTEST.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/MYDBTEST/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/mydb/controlfile/current.270.782998169'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='MYDBTEST'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBTESTXDB)'
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5368709120
*.undo_tablespace='UNDOTBS1'
[oracle@myhostt01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 11 14:11:52 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit
Disconnected
[oracle@myhostt01 ~]$ ps -ef|grep pmon
grid 15792 1 0 11:11 ? 00:00:00 asm_pmon_+ASM
oracle 29856 28284 0 14:12 pts/7 00:00:00 grep pmon
When I first learnt from a guy, fortunately not newbie dba, it is a real shame for me to hear about nid very late in my dba career.
The day before I heard about nid, we were discussing with another 16 year dba, why Oracle could not rename db in year 2012, even preparing for version 12. But it was a real disaster that we discovered that there was a tool since year 2001, version 9. (Oracle documentation about nid utiliy in 9i) This gave real pain that I could not write about "nid" for two weeks, and rethink about continueing my career in dba position :)
Before nid, we were producing controlfile by backup controlfile to trace, changing its contents, etc. No need to these disgusting and a bit lengthy process.
Here's what I did, while changing the name of database which I had restored for making a test and development environment. Changing database name from MYDB to MYDBTEST.
[oracle@myhostt01 ~]$ nid TARGET=SYS DBNAME=MYDBTEST logfile='/tmp/mydb_nid.log'
Password:
[oracle@myhostt01 ~]$
-- log file details
[oracle@myhostt01 ~]$ tail -f /tmp/mydb_nid.log
Instance shut down
Database name changed to MYDBTEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database MYDBTEST changed to 1233471344.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
-- change dbname in init.ora
[oracle@myhostt01 ~]$ vi /tmp/init2.ora
MYDBTEST.__db_cache_size=4378853376
MYDBTEST.__java_pool_size=16777216
MYDBTEST.__large_pool_size=16777216
MYDBTEST.__pga_aggregate_target=1073741824
MYDBTEST.__sga_target=5368709120
MYDBTEST.__shared_io_pool_size=0
MYDBTEST.__shared_pool_size=922746880
MYDBTEST.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/MYDBTEST/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/mydb/controlfile/current.270.782998169'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='MYDBTEST'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBTESTXDB)'
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5368709120
*.undo_tablespace='UNDOTBS1'
[oracle@myhostt01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 11 14:11:52 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit
Disconnected
[oracle@myhostt01 ~]$ ps -ef|grep pmon
grid 15792 1 0 11:11 ? 00:00:00 asm_pmon_+ASM
oracle 29856 28284 0 14:12 pts/7 00:00:00 grep pmon
Tuesday, March 27, 2012
Oracle Database Express Edition Licensing Information
I figured out that Resource Manager Feature is not enabled in Oracle Express Edition (XE).
I write this information here, because I could not have found any feature list whether XE supports Resource Manager feature at first.
In order to understand, if XE supports, I tried to create resource plan and resource plan directive and I got the below error.
BEGIN
SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
SYS.DBMS_RESOURCE_MANAGER.create_plan(
plan=>'sample',
comment=>'');
SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
ORA-00439: feature not enabled: Database resource manager
ORA-06512: at "SYS.DBMS_RMIN", line 63
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 111
ORA-06512: at line 5
Further, internal resource plan is enabled:
select * from v$rsrc_plan;
ID NAME IS_TO CPU INS PARALLEL_SERVERS_ACTIVE PARALLEL_SERVERS_TOTAL PARALLEL_EXECUTION_MANAGED
--------- -------------------------------- ----- --- --- ----------------------- ---------------------- --------------------------------
1 INTERNAL_PLAN_XE TRUE ON OFF 0 0 OFF
After googling around, I found that full feature list supported could have been found from below document:
Oracle Database Express Edition Licensing Information
http://docs.oracle.com/cd/E17781_01/license.112/e18068/toc.htm
So I will look for Licensing Information of any release from now on.
I write this information here, because I could not have found any feature list whether XE supports Resource Manager feature at first.
In order to understand, if XE supports, I tried to create resource plan and resource plan directive and I got the below error.
BEGIN
SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
SYS.DBMS_RESOURCE_MANAGER.create_plan(
plan=>'sample',
comment=>'');
SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
ORA-00439: feature not enabled: Database resource manager
ORA-06512: at "SYS.DBMS_RMIN", line 63
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 111
ORA-06512: at line 5
Further, internal resource plan is enabled:
select * from v$rsrc_plan;
ID NAME IS_TO CPU INS PARALLEL_SERVERS_ACTIVE PARALLEL_SERVERS_TOTAL PARALLEL_EXECUTION_MANAGED
--------- -------------------------------- ----- --- --- ----------------------- ---------------------- --------------------------------
1 INTERNAL_PLAN_XE TRUE ON OFF 0 0 OFF
After googling around, I found that full feature list supported could have been found from below document:
Oracle Database Express Edition Licensing Information
http://docs.oracle.com/cd/E17781_01/license.112/e18068/toc.htm
So I will look for Licensing Information of any release from now on.
Wednesday, March 14, 2012
How to see grid like backup report with SQL
In order to see the backup status historically from database side, you may check below SQL. I used in 11.2.0.2.
SELECT b.session_key,
b.session_recid,
b.session_stamp,
b.command_id,
b.status,
b.start_time,
b.END_TIME,
b.time_taken_display,
b.input_type,
b.output_device_type,
b.input_bytes_display,
b.output_bytes_display,
b.output_bytes_per_sec_display
FROM V$RMAN_BACKUP_JOB_DETAILS b
WHERE (b.start_time > to_date('04.12.2011 19:00','dd.mm.yyyy hh24:mi'))
order by b.start_time desc;
SELECT b.session_key,
b.session_recid,
b.session_stamp,
b.command_id,
b.status,
b.start_time,
b.END_TIME,
b.time_taken_display,
b.input_type,
b.output_device_type,
b.input_bytes_display,
b.output_bytes_display,
b.output_bytes_per_sec_display
FROM V$RMAN_BACKUP_JOB_DETAILS b
WHERE (b.start_time > to_date('04.12.2011 19:00','dd.mm.yyyy hh24:mi'))
order by b.start_time desc;
Wednesday, February 22, 2012
Data Scientist
Strata New York 2011: John Rauser, "What is a Career in Big Data?"
http://www.youtube.com/watch?feature=player_embedded&v=0tuEEnL61HM
Really cool video...
I liked mostly, the comparison Euler and Mayer, which seems to be living in same decades. Euler is Mathematician, Mayer is close to Engineering discipline. And the way they looked at the data.
http://www.youtube.com/watch?feature=player_embedded&v=0tuEEnL61HM
Really cool video...
I liked mostly, the comparison Euler and Mayer, which seems to be living in same decades. Euler is Mathematician, Mayer is close to Engineering discipline. And the way they looked at the data.
Monday, February 13, 2012
Real World Performance seminar
Thanks TROUG - TuRkish Oracle User Group, giving us a chance for one day Real World Performance seminar by Andrew Holdsworth, Tom Kyte, and Graham Wood.
There is nothing to say about the guys, I appreciate for their efforts to come and share their knowledge.
You could find the slides, videos and TROUG announcement from below external links:
Slides
DWH video
OLTP video
Announcement
There is nothing to say about the guys, I appreciate for their efforts to come and share their knowledge.
You could find the slides, videos and TROUG announcement from below external links:
Slides
DWH video
OLTP video
Announcement
Sunday, February 12, 2012
Weekend in Antalya
We had been to Antalya, for 2 nights, annual meeting of my company, based on invention and simplicity was held.
"Steal" Jobs video shown by our CEO is impressive to me at first...
Steve Jobs interviewed just before returning to Apple
http://www.youtube.com/watch?v=SaJp66ArJVI&feature=youtube_gdata_player
But after a day passed, only impressive thing was "simply" :
Beautiful sand, shore, and sea....
waves, sand and sea shells...
Check out stellarium, star map application, which I had a chance to try it in Antalya's open sky, in which you could easily sea stars... A snap from application:
"Steal" Jobs video shown by our CEO is impressive to me at first...
Steve Jobs interviewed just before returning to Apple
http://www.youtube.com/watch?v=SaJp66ArJVI&feature=youtube_gdata_player
But after a day passed, only impressive thing was "simply" :
Beautiful sand, shore, and sea....
waves, sand and sea shells...
Check out stellarium, star map application, which I had a chance to try it in Antalya's open sky, in which you could easily sea stars... A snap from application:
Wednesday, February 8, 2012
Distributed transaction error ORA-02049: time-out: distributed transaction waiting for lock
Problem
While working with dblinks, I got the below error:
ORA-02049: time-out: distributed transaction waiting for lock
Cause
More than one session is accessing the remote object.
Solution
I had issued commit, rollback for one of the session and the issue is resolved.
But, the key thing here is that; I could only reproduce the error while issueing below statement:
While trying it without APPEND hint it does not occur.
Honestly speaking, I did not know the real cause, because sometimes I could not reproduce the error.
The transactions are not listed in:
While working with dblinks, I got the below error:
ORA-02049: time-out: distributed transaction waiting for lock
Cause
More than one session is accessing the remote object.
Solution
I had issued commit, rollback for one of the session and the issue is resolved.
But, the key thing here is that; I could only reproduce the error while issueing below statement:
insert /*+ APPEND PARALLEL(4)*/ into admin.test2
select /*+ DRIVING_SITE(T) PARALLEL(4) */ * from admin1.test2@MYDB2.WORLD T where 1=1
;
While trying it without APPEND hint it does not occur.
Honestly speaking, I did not know the real cause, because sometimes I could not reproduce the error.
The transactions are not listed in:
select * from dba_2pc_pending;
select * from dba_2pc_neighbors;
Hardening and Healthcheck of DBs with its OS environment
Following there is a sample healthcheck table, which I prepared for easily monitor and create tasks for improvement of whole system by sharing with my colleques.
All of them are not have to be accomplished, there are also nice to have items.
The list is formed with the feedbacks coming from OS admin, DB admin.
HPOV is alarm integration tool. OS backup, filesystem backups needed, in OS category, DDMI, centrify and opsware are technologies for keeping track of automatic inventory, SSO, and OS installation&configuration respectively. Grid category is formed by basic UDM definitions. Logging category is, OSWatcher utility provided by Oracle, HW Inventory is inventory application of HW, DB and other configuration items. DNS definition is mainly needed for cluster scan IPs in RAC, and clients coming over names.
All of them are not have to be accomplished, there are also nice to have items.
The list is formed with the feedbacks coming from OS admin, DB admin.
HPOV is alarm integration tool. OS backup, filesystem backups needed, in OS category, DDMI, centrify and opsware are technologies for keeping track of automatic inventory, SSO, and OS installation&configuration respectively. Grid category is formed by basic UDM definitions. Logging category is, OSWatcher utility provided by Oracle, HW Inventory is inventory application of HW, DB and other configuration items. DNS definition is mainly needed for cluster scan IPs in RAC, and clients coming over names.
"Prerequisite check "CheckActiveFilesAndExecutables” failed error" While issueing opatch apply
Problem
While applying interim patch, I got Prerequisite check "CheckActiveFilesAndExecutables” failed error and resolved the issue with the help of fuser.
Cause
Something is using oracle library.
Solution
I had solved the issue first looking at the log file and saw that the inuse control made by fuser, this is also a clue for me to find who is using the library file.
After than I had successfully applied the patch.
While applying interim patch, I got Prerequisite check "CheckActiveFilesAndExecutables” failed error and resolved the issue with the help of fuser.
oracle@mydb1>opatch apply
Invoking OPatch 11.2.0.1.8
Oracle Interim Patch Installer version 11.2.0.1.8
Copyright (c) 2011, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.8
OUI version : 11.2.0.2.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-02-01_21-16-28PM.log
Applying interim patch '9578670' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:
Following executables are active :
/u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1
[ Error during Prerequisite for apply Phase]. Detail: ApplySession failed during prerequisite checks: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-02-01_21-16-28PM.log
Recommended actions: OPatch needs to modify files which are being used by some processes.
OPatch failed with error code 41
Cause
Something is using oracle library.
Solution
I had solved the issue first looking at the log file and saw that the inuse control made by fuser, this is also a clue for me to find who is using the library file.
oracle@mydb1>/sbin/fuser /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1
/u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1: 12195m
oracle@mydb1>
oracle@mydb1>ps -ef |grep 12195
oracle 12195 11944 0 Jan19 pts/7 00:00:00 adrci
oracle 22813 3717 0 21:18 pts/13 00:00:00 grep 12195
oracle@mydb1>kill -9 12195
After than I had successfully applied the patch.
Tuesday, February 7, 2012
Grid SQL Monitoring
In order to store and later analyze the SQLs in SQL Monitor in Enterprise Manager Grid window; you could note using below SQL:
First, create a temporary table in order not to lose the data in gv$ view.
create table my_sql_monitor as select * from gv$sql_monitor;
And later analyze, for instance, find long running session SQLs, whether they ran parallel or not, so on by below SQL.
select distinct sql_id,min(px_servers_requested),min(px_servers_allocated),
min(sql_exec_start),max(elapsed_time),max(concurrency_wait_time),sql_text from my_sql_monitor where sql_text is not null
group by sql_id,sql_text
order by max(elapsed_time) desc,1 desc;
First, create a temporary table in order not to lose the data in gv$ view.
create table my_sql_monitor as select * from gv$sql_monitor;
And later analyze, for instance, find long running session SQLs, whether they ran parallel or not, so on by below SQL.
select distinct sql_id,min(px_servers_requested),min(px_servers_allocated),
min(sql_exec_start),max(elapsed_time),max(concurrency_wait_time),sql_text from my_sql_monitor where sql_text is not null
group by sql_id,sql_text
order by max(elapsed_time) desc,1 desc;
Thursday, February 2, 2012
Grid and Oracle Home Housekeeping, in 11.2 with cron jobs and opatch util cleanup
Grid and oracle home filesystem space exhaust problems, is a real headache which needs to be handled before it happens.
It is not a "nice to have" job; because, you will find yourself in following situations ultimately:
- getting up at midnight because of a call coming from 7*24 personnel that your db archive process can not write redo
- you can find yourself in a situation that trying to find out who ate my disk space in the middle of patch apply/PSU upgrade scenario.
If you are not motivated enough, so here comes the results:
Below there is the filesystem size before the operation:
grid@node1:/u01/app/11.2.0:>df -g /u01
/dev/fslv00 99.00 28.50 72% 120470 2% /u01
After the cleanup:
grid@node1:/u01/app/11.2.0:>df -g /u01
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/fslv01 96.88 49.21 50% 89422 1% /u01
So what I did?
First of all I had set the critical alarm level of filesystem usage to 70 pecent, which was 95 before. And I got the alarms...
In order for cleaning up of u01 filesystem, I had used "opatch util cleanup" for unnecessary opatch stored files and cron jobs to age out or remove old trace or log files.
1. CRONTAB SCRIPTS:
oracle@node1:/u01/app/11.2.0/grid>crontab –l
## trace cleanup
0 15 * * * /usr/bin/find /u01/app/oracle/diag/rdbms/mydb/MYDB1/trace -type f -name "MYDB1*tr*" -mtime +10 -exec rm {} \; > /dev/null 2>&1
## audit cleanup
0 15 * * * /usr/bin/find /u01/app/oracle/admin/MYDB/adump -type f -name "*aud" -mtime +10 -exec rm {} \; > /dev/null 2>&1
## agent emd cleanup
0 15 * * * /usr/bin/find /u01/app/agent/agent11g/node1/sysman/emd -type f -name "*dmp" -mtime +3 -exec rm {} \; > /dev/null 2>&1
0 15 * * * /usr/bin/find /u01/app/agent/agent11g/node1/sysman/emd -type f -name "*trc" -mtime +3 -exec rm {} \; > /dev/null 2>&1
0 15 * * * /usr/bin/find /u01/app/agent/agent11g/node1/sysman/emd -type f -name "*core*" -mtime +3 -exec rm {} \; > /dev/null 2>&1
grid@node1:/home/grid:>crontab -l
## asm trace files cleanup
14 0 * * * /usr/bin/find /u01/app/grid/diag/asm/+asm/+ASM1/trace -type f -name "*ASM*.tr*" -mtime +20 -exec rm {} \; > /dev/null 2>&1
## listener log cleanup
15 0 * * * /usr/bin/find /u01/app/grid/diag/tnslsnr/node1/listener*/alert -type f -name "log*.xml" -mtime +3 -exec rm {} \; > /dev/null 2>&1
## audit cleanup
16 0 * * * /usr/bin/find /u01/app/11.2.0/grid/rdbms/audit -type f -name "*aud" -mtime +1 -exec rm {} \; > /dev/null 2>&1
## listener log aging
17 0 * * 0 /usr/bin/compress -f /u01/app/grid/diag/tnslsnr/node1/listener/trace/listener.log
17 0 * * 0 /usr/bin/compress -f /u01/app/11.2.0/grid/log/diag/tnslsnr/node1/listener_scan1/trace/*log
17 0 * * 0 /usr/bin/compress -f /u01/app/11.2.0/grid/log/diag/tnslsnr/node1/listener_scan2/trace/*log
17 0 * * 0 /usr/bin/compress -f /u01/app/11.2.0/grid/log/diag/tnslsnr/node1/listener_scan3/trace/*log
# rdbms log cleanup
0 15 * * * /usr/bin/find /u01/app/11.2.0/grid/rdbms/log -type f -name "*.tr*" -mtime +3 -exec rm {} \; > /dev/null 2>&1
2. OPATCH CLEANUP
DB HOME
oracle@node1:/u01/app/11.2.0/grid>opatch util cleanup
Invoking OPatch 11.2.0.1.5
Oracle Interim Patch Installer version 11.2.0.1.5
Copyright (c) 2010, Oracle Corporation. All rights reserved.
UTIL session
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.5
OUI version : 11.2.0.2.0
OUI location : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-01-25_11-41-54AM.log
Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt
Invoking utility "cleanup"
OPatch will clean up 'restore.sh,make.txt' files and 'rac,scratch,backup' directories.
You will be still able to rollback patches after this cleanup.
Do you want to proceed? [y|n]
y
User Responded with: Y
Size of directory "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage" before cleanup is 1159144429 bytes.
Size of directory "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage" after cleanup is 526164263 bytes.
UtilSession: Backup area for restore has been cleaned up. For a complete list of files/directories
deleted, Please refer log file.
OPatch succeeded.
GRID HOME
grid@node1:/u01/app/11.2.0:>opatch util cleanup
Invoking OPatch 11.2.0.1.5
Oracle Interim Patch Installer version 11.2.0.1.5
Copyright (c) 2010, Oracle Corporation. All rights reserved.
UTIL session
Oracle Home : /u01/app/11.2.0/grid
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.5
OUI version : 11.2.0.2.0
OUI location : /u01/app/11.2.0/grid/oui
Log file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch2012-01-25_11-38-10AM.log
Patch history file: /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch_history.txt
Invoking utility "cleanup"
OPatch will clean up 'restore.sh,make.txt' files and 'rac,scratch,backup' directories.
You will be still able to rollback patches after this cleanup.
Do you want to proceed? [y|n]
y
User Responded with: Y
Size of directory "/u01/app/11.2.0/grid/.patch_storage" before cleanup is 40399439700 bytes.
Size of directory "/u01/app/11.2.0/grid/.patch_storage" after cleanup is 23080457091 bytes.
UtilSession: Backup area for restore has been cleaned up. For a complete list of files/directories
deleted, Please refer log file.
OPatch succeeded.
Opatch cleanup MOS Note:
How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space. [ID 550522.1]
4. ) In preparation for patching systems, is there any way to predict the amount of space that will be required in $ORACLE_HOME/.patch_storage?
It is not a "nice to have" job; because, you will find yourself in following situations ultimately:
- getting up at midnight because of a call coming from 7*24 personnel that your db archive process can not write redo
- you can find yourself in a situation that trying to find out who ate my disk space in the middle of patch apply/PSU upgrade scenario.
If you are not motivated enough, so here comes the results:
Below there is the filesystem size before the operation:
grid@node1:/u01/app/11.2.0:>df -g /u01
/dev/fslv00 99.00 28.50 72% 120470 2% /u01
After the cleanup:
grid@node1:/u01/app/11.2.0:>df -g /u01
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/fslv01 96.88 49.21 50% 89422 1% /u01
So what I did?
First of all I had set the critical alarm level of filesystem usage to 70 pecent, which was 95 before. And I got the alarms...
In order for cleaning up of u01 filesystem, I had used "opatch util cleanup" for unnecessary opatch stored files and cron jobs to age out or remove old trace or log files.
1. CRONTAB SCRIPTS:
oracle@node1:/u01/app/11.2.0/grid>crontab –l
## trace cleanup
0 15 * * * /usr/bin/find /u01/app/oracle/diag/rdbms/mydb/MYDB1/trace -type f -name "MYDB1*tr*" -mtime +10 -exec rm {} \; > /dev/null 2>&1
## audit cleanup
0 15 * * * /usr/bin/find /u01/app/oracle/admin/MYDB/adump -type f -name "*aud" -mtime +10 -exec rm {} \; > /dev/null 2>&1
## agent emd cleanup
0 15 * * * /usr/bin/find /u01/app/agent/agent11g/node1/sysman/emd -type f -name "*dmp" -mtime +3 -exec rm {} \; > /dev/null 2>&1
0 15 * * * /usr/bin/find /u01/app/agent/agent11g/node1/sysman/emd -type f -name "*trc" -mtime +3 -exec rm {} \; > /dev/null 2>&1
0 15 * * * /usr/bin/find /u01/app/agent/agent11g/node1/sysman/emd -type f -name "*core*" -mtime +3 -exec rm {} \; > /dev/null 2>&1
grid@node1:/home/grid:>crontab -l
## asm trace files cleanup
14 0 * * * /usr/bin/find /u01/app/grid/diag/asm/+asm/+ASM1/trace -type f -name "*ASM*.tr*" -mtime +20 -exec rm {} \; > /dev/null 2>&1
## listener log cleanup
15 0 * * * /usr/bin/find /u01/app/grid/diag/tnslsnr/node1/listener*/alert -type f -name "log*.xml" -mtime +3 -exec rm {} \; > /dev/null 2>&1
## audit cleanup
16 0 * * * /usr/bin/find /u01/app/11.2.0/grid/rdbms/audit -type f -name "*aud" -mtime +1 -exec rm {} \; > /dev/null 2>&1
## listener log aging
17 0 * * 0 /usr/bin/compress -f /u01/app/grid/diag/tnslsnr/node1/listener/trace/listener.log
17 0 * * 0 /usr/bin/compress -f /u01/app/11.2.0/grid/log/diag/tnslsnr/node1/listener_scan1/trace/*log
17 0 * * 0 /usr/bin/compress -f /u01/app/11.2.0/grid/log/diag/tnslsnr/node1/listener_scan2/trace/*log
17 0 * * 0 /usr/bin/compress -f /u01/app/11.2.0/grid/log/diag/tnslsnr/node1/listener_scan3/trace/*log
# rdbms log cleanup
0 15 * * * /usr/bin/find /u01/app/11.2.0/grid/rdbms/log -type f -name "*.tr*" -mtime +3 -exec rm {} \; > /dev/null 2>&1
2. OPATCH CLEANUP
DB HOME
oracle@node1:/u01/app/11.2.0/grid>opatch util cleanup
Invoking OPatch 11.2.0.1.5
Oracle Interim Patch Installer version 11.2.0.1.5
Copyright (c) 2010, Oracle Corporation. All rights reserved.
UTIL session
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.5
OUI version : 11.2.0.2.0
OUI location : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-01-25_11-41-54AM.log
Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt
Invoking utility "cleanup"
OPatch will clean up 'restore.sh,make.txt' files and 'rac,scratch,backup' directories.
You will be still able to rollback patches after this cleanup.
Do you want to proceed? [y|n]
y
User Responded with: Y
Size of directory "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage" before cleanup is 1159144429 bytes.
Size of directory "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage" after cleanup is 526164263 bytes.
UtilSession: Backup area for restore has been cleaned up. For a complete list of files/directories
deleted, Please refer log file.
OPatch succeeded.
GRID HOME
grid@node1:/u01/app/11.2.0:>opatch util cleanup
Invoking OPatch 11.2.0.1.5
Oracle Interim Patch Installer version 11.2.0.1.5
Copyright (c) 2010, Oracle Corporation. All rights reserved.
UTIL session
Oracle Home : /u01/app/11.2.0/grid
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.5
OUI version : 11.2.0.2.0
OUI location : /u01/app/11.2.0/grid/oui
Log file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch2012-01-25_11-38-10AM.log
Patch history file: /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch_history.txt
Invoking utility "cleanup"
OPatch will clean up 'restore.sh,make.txt' files and 'rac,scratch,backup' directories.
You will be still able to rollback patches after this cleanup.
Do you want to proceed? [y|n]
y
User Responded with: Y
Size of directory "/u01/app/11.2.0/grid/.patch_storage" before cleanup is 40399439700 bytes.
Size of directory "/u01/app/11.2.0/grid/.patch_storage" after cleanup is 23080457091 bytes.
UtilSession: Backup area for restore has been cleaned up. For a complete list of files/directories
deleted, Please refer log file.
OPatch succeeded.
Opatch cleanup MOS Note:
How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space. [ID 550522.1]
4. ) In preparation for patching systems, is there any way to predict the amount of space that will be required in $ORACLE_HOME/.patch_storage?
Subscribe to:
Posts (Atom)
Windows (powershell) counterparts of Linux commands
You may find Windows mostly powershell equivalent of frequently used Linux commands here. I will update this post, with newer ones by the ti...
-
Simple but very handy way of taking exports without knowing database password. expdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFIL...
-
Problem: I cannot see Oracle ODBC driver in datasource administrator in Windows 2003 64 bit. Cause: 64 bit 32 bit incompatibility issue for ...
-
What is consolidation? At first, it seems a process which enables making things with smaller number of resources. Below picture that depicts...
