notsadrobotxyz - Tumblr blog

notsadrobotxyz · 6 years ago

Text

Analyzing Materialized Views for Fast Refresh

The article will describe how materialized views can be analyzed and optimized to ensure they can be FAST REFRESHED, through this way refresh time will reduced from more than 5hours to less than 1hour. The tools used to perform the FAST REFRESH analysis are:– DBMS_MVIEW– MV_CAPABILITIES_TABLEThe Oracle provided DBMS_MVIEW.explain_mview procedure is used to analyze each of the existing materialized views FAST REFRESH capabilities and write the results to the MV_CAPABILITIES_TABLE.The MV_CAPABILITIES TABLE is created in the owner’s schema by running the following Oracle 9i script:SQL> @$ORACLE_HOME/rdbms/admin/utlxmv.sql Steps:– Created the necessary materialized view logs for each of the master tables making sure to include all columns referenced in any of the materialized view aggregations along with the ROWID, SEQUENCE, and INCLUDING NEW VALUESclauses based on the Oracle 9i documentation FAST REFRESH requirements.– Created materialized views including the REFRESH FORCE ON DEMANDoption, in order to prepare for the detailed analysis of the respective FAST REFRESHcapabilities.– Iterated through each existing materialized view analyzing and re-factoring as necessary to meet the specific FAST REFRESHrequirements using the contents of the MV_CAPABILITIES_TABLE MSGTXT column as based on the oracle 9i guide. Truncated the MV_CAPABILITIES_TABLE to remove any prior analysis written to the table by the DBMS_MVIEW.explain_mviewprocedure.SQL> TRUNCATE TABLE hrms.mv_capabilities_table; Executed dbms_mview.explain_mview()procedure to explain the materialized view:SQL> EXEC dbms_mview.explain_mview(‘hrms.payroll_MV’); To check MV_CAPABILITIES_TABLEfor each FAST REFRESHcapability:SQL> SELECT capability_name, possible, substr(msgtxt,1,60) AS msgtxt FROM hrms.mv_capabilities_table WHERE capability_name like '%FAST%'; Examples: To Create FAST_REFRESH materialized view:SQL> CREATE MATERIALIZED VIEW hrms.payroll_v_MVNOLOGGINGPARALLELBUILD IMMEDIATEREFRESH FORCE ON DEMANDENABLE QUERY REWRITEASselect * from payroll_v/SQL> Truncate table mv_capabilities_table;SQL> exec dbms_mview.explain_mview('hrms.payroll_v_mv'); SQL> SELECT capability_name, possible, SUBSTR(msgtxt,1,60) AS msgtxt FROM hrms.mv_capabilities_table WHERE capability_name like '%FAST%'; SQL> DROP MATERIALIZED VIEW hrms.payroll_v_MV; SQL> CREATE MATERIALIZED VIEW hrms.payment_master_v_MVNOLOGGINGPARALLELBUILD IMMEDIATEREFRESH FORCE ON DEMANDENABLE QUERY REWRITEASselect * from payment_master; SQL> TRUNCATE TABLE mv_capabilities_table;SQL> EXEC dbms_mview.explain_mview('hrms.payment_master_v_mv'); SQL> SELECT capability_name, possible, SUBSTR(msgtxt,1,60) AS msgtxt FROM mv_capabilities_table WHERE capability_name like '%FAST%'; Example: Aggregation of Materialized view with required M_Veiw logSQL> CREATE MATERIALIZED VIEW LOG ON hrms.payrollWITH SEQUENCE, ROWID (EMPLOYEE_NUMBER, DEPT_CODE, BANK_TRANSFER, CASH_TRANSFER, OTHERS_TRANSFER)INCLUDING NEW VALUES; SQL> CREATE MATERIALIZED VIEW LOG ON hrms.payment_masterWITH SEQUENCE, ROWID (EMPLOYEE_NUMBER)INCLUDING NEW VALUES; SQL> CREATE MATERIALIZED VIEW hrms.salary_mv NOLOGGING PARALLEL BUILD IMMEDIATE REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS SELECT e.employee_number, e.employee_name, d.payslip_number, d. payslip_date, d.sum(Amount) FROM payment_master e, pay_payment_detail d WHERE e.employee_number=d.employee_number and d.payslip_date > ’31-SEP-2014’; GROUP BY e.employee_number;

0 notes

notsadrobotxyz · 6 years ago

Text

Toad: Cannot Load oci.dll: While Connecting

I installed oracle 10g on windows 7 successfully. I found everything is working except toad. I can connect the database through SQL*plus but when I open toad, my oracle installation is not appearing in the “connect using” box. When I go through the toad user guide, find the following information how it finds client:1. Toad first look in the toad command line for oci.dll2. If it is not found, then it checks the command line argument ORACLE-HOME3. If there is no toad home defined then toad will display the home that is set as the default home active in dropdown. 4. To populate the drop down, toad searches the registry as follows:5. First, Toad reads the list of Oracle home names from the keys under HKEY_ LOCAL_MACHINESOFTWAREORACLEALL_HOMES If no Oracle homes are found there, then the Oracle home is set to HKEY_ LOCAL_MACHINESOFTWAREORACLE Otherwise, Toad finds the ORACLE_HOME value for each Oracle home, if it exists. Then Toad checks the system environment variable called PATH to see whether it contains the "bin" folder under ORACLE_HOME. Toad selects the Oracle home whose path appears first in PATH. If Toad still hasn't found an Oracle home, it uses HKEY_LOCAL_ MACHINESOFTWAREORACLE.6. Toad looks for the client DLL in the "Bin" folder under the path found for the Oracle home. If that fails, Toad looks for the ORACLE_HOME key under HKEY_LOCAL_ MACHINEORACLESOFTWARE, and look for the client dll in the "Bin" folder. If that also fails, Toad looks for the client dll in every bin directory in PATH. Sometimes the error “cannot load oci.dll” occurs whenever we try to connect with the oracle database by using the third-party tools such as Toad, sqltools and others. This error may occur because of the following reason:1. The oci.dll error may occur because you have not set the correct ORACLE_HOME and path in environment variables. 2. It might be possible that the oci.dll file may be corrupt or may not exist on the correct path.3. May be possible that oci.dll may not be correct version such as 32bit s/w will load a 32bit DDL. It cannot use a 64bit DLL for a 32bit executable. To solve this issue, consider the below points:1. Check the ORACLE_HOME and Path setting in the environment variable.2. Check the correct location of the oci.dll path. The path of the oci.dll file is $ORACLE_HOMEbinoci.dll3. Check the oci.dll correct version.I checked my registry, and my oracle installion does appear in a 10g folder within HKEY_ LOCAL_MACHINESOFTWAREORACLE. In my case, I am facing this issue because of incompatible with 64 bit installation of oracle. Toad looks for oracle installation under HKEY_LOCAL_MACHINE/SOFTWARE/WOW6432NODE/ORACLE which is where, because that is where 32 bit installations are registered. So I installed a 32 bit version of oracle, then after I am able to open every thing including toad.Conclusion: Toad works with both 32 bit and 64 bit oracle server where as toad only work with 32 bit client. If you need 64 bit client for other applications, you can install both 32 bit and 64 bit client on a single machine and just tell the toad to use the 32 bit client.

0 notes

notsadrobotxyz · 6 years ago

Text

Useful Query for both DBA and Developer

In toad you just copy and paste these script. It will be requested to enter the schema owner and the tablename. At SQL*PLUS You will need Column format setting for proper output layout.Show the Table Structure, DATA_LENGTH, SYSWHERE ) TABLE_NAME upper(BYPCT_FREE, NEXT_EXTENT, AVG_ROW_LEN. OWNER upper( ='&&table'Show the actual Maximum Size of a RowSUM( . OWNER upper( ='&&table'Show the Number of Physical EXTENTS that have been allocated Attributes,*FROMDBA_EXTENTS='&&owner'AND ) ;SELECT ) SYSWHERE ) SEGMENT_NAME upper(BYIND,TABLE_OWNER.IND,COLUMN_NAME. SYS,DBA_IND_COLUMNS COL. ) IND='&&owner'ANDTABLE_NAME . IND=INDEX_OWNER. COLANDINDEX_NAME .IND, TABLE_OWNER.IND, COLUMN_NAME.IND, PCT_INCREASE.IND, MAX_EXTENTS,IND='&&table'ANDTABLE_OWNER upper(. COLANDOWNER . IND=TABLE_OWNER . COL;SELECTOWNER. COL,.DECODECON,,'R','unique', FROM DBA_CONSTRAINTS CON. ) COL='&&table'ANDANDOWNER . COL=TABLE_NAME . CONORDERCOL,POSITIONShow the Foreign Keys on this table pointing at other tables PrimaryCON,TABLE_NAME. CON,. COL,POSITION-- CON1.OWNER,.,CONSTRAINT_NAME -- COL1.COLUMN_NAME "Ref Column",FROMDBA_CONS_COLUMNS COLWHEREOWNER upper( . ) CON= . CONANDTABLE_NAME . COL=CONSTRAINT_NAMEANDOWNER . CON1=R_CONSTRAINT_NAME. 'P''U'-- but has a multiplying effect on the query results.--AND COL1.TABLE_NAME = CON1.TABLE_NAMECON1,TABLE_NAME. CON1,STATUS. CON,POSITION. DBA_CONSTRAINTS CON, CON='&&owner'ANDTABLE_NAME upper(((CONSTRAINT_TYPE 'P'ORCON=)) COL=TABLE_NAME .CON1ANDOWNER . CON1=CONSTRAINT_NAME. GROUP CON1,TABLE_NAME. CON1,STATUS. CON,POSITION. TABLE_NAME' add constraint 'CONSTRAINT_NAME' check ( ',WHERE ) TABLE_NAME upper(=;SELECT TRIGGER_NAME,,WHERE ) TABLE_NAME upper( Get the First day of the Month: SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month" FROM DUAL; Get the last day of the Month: SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month" FROM DUAL; Get the First day of the year: SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL; Get the last day of the year: SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL; Get number of days in current month SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days FROM DUAL; Get number of days left in Current month SELECT SYSDATE, LAST_DAY (SYSDATE) "Last", LAST_DAY (SYSDATE) - SYSDATE "Days left" FROM DUAL; Get number of days between two dates SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0) num_of_days FROM DUAL; OR SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees; Display each months start and end date upto last month of the year: SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date, TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date FROM XMLTABLE ('for $i in 0 to xs:int(D) return $i' PASSING XMLELEMENT (d, FLOOR (MONTHS_BETWEEN (ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12), SYSDATE))) COLUMNS i INTEGER PATH '.'); Get number of seconds passed since today (since 00:00 hr): SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning FROM DUAL; Get number of seconds left today (till 23:59:59 hr) SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left FROM DUAL; Check if a table exists in the current database schema: SELECT table_name FROM user_tables WHERE table_name = 'TABLE_NAME'; Check if a column exists in a table: SELECT column_name AS FOUND FROM user_tab_cols WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME'; Showing the Table Structure: SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL; Getting Current Schema SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL; Convert Number to Words: SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL; Find the Last records from the Table: SELECT * FROM employees WHERE ROWID IN (SELECT MAX (ROWID) FROM employees); -(or)- SELECT * FROM employees MINUS SELECT * FROM employees WHERE ROWNUM < (SELECT COUNT (*) FROM employees); Random number generator in Oracle: SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL; ', PRIVILEGE, ' TO;&&ownertable', PRIVILEGE, ' ) ', TABLE_NAME, ''&&ownertable

0 notes

notsadrobotxyz · 6 years ago

Text

Useful Query for DBA

Database default information: Select username,profile,default_tablespace,temporary_tablespace from dba_users; Database Structure information: SELECT /*+ ordered */ d.tablespace_name tablespace, d.file_name filename , d.bytes filesize, d.autoextensible autoextensible, d.increment_by * e.value increment_by, d.maxbytes maxbytes FROM sys.dba_data_files d, v$datafile v , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e WHERE (d.file_name = v.name) UNION SELECT d.tablespace_name tablespace, d.file_name filename, d.bytes filesize, d.autoextensible autoextensible , d.increment_by * e.value increment_by, d.maxbytes maxbytes FROM sys.dba_temp_files d, (SELECT value FROM v$parameter WHERE name = 'db_block_size') e UNION SELECT '', a.member, b.bytes, null, TO_NUMBER(null), TO_NUMBER(null) FROM v$logfile a, v$log b WHERE a.group# = b.group# UNION SELECT '', a.name, TO_NUMBER(null), null, TO_NUMBER(null), TO_NUMBER(null) FROM v$controlfile a ORDER BY 1,2; Database Character Set Informations: Select * from nls_database_parameters; Database Segment Managment Informations: Select TABLESPACE_NAME, BLOCK_SIZE, EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT from dba_tablespaces; Database Object Information: Select owner,object_type,count(*) from dba_objects Where owner not IN ('SYS','MDSYS','CTXSYS','HR','ORDSYS','OE','ODM_MTR','WMSYS','XDB','QS_WS', 'RMAN','SCOTT','QS_ADM','QS_CBADM', 'ORDSYS', 'OUTLN', 'PM', 'QS_OS', 'QS_ES', 'ODM', 'OLAPSYS','WKSYS','SH','SYSTEM','ORDPLUGINS','QS','QS_CS') Group by owner,object_type order by owner; Find the last record from a table? select * from employees where rowid in(select max(rowid) from employees);select * from employees minus select * from employees where rownum select scn_to_timestamp(8843525) from dual;Find UNDO information Table: select to_char(begin_time,'hh24:mi:ss'),to_char(end_time,'hh24:mi:ss') , maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat order by undoblks ; Shared Pool Information: select to_number(value) shared_pool_size, sum_obj_size, sum_sql_size, sum_user_size, (sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool from (select sum(sharable_mem) sum_obj_size from v$db_object_cache where type 'CURSOR'), (select sum(sharable_mem) sum_sql_size from v$sqlarea), (select sum(250 * users_opening) sum_user_size from v$sqlarea), v$parameter where name = 'shared_pool_size'; How to determine whether the datafiles are synchronized or not? select status, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY 0 ORDER BY elapsed_seconds; How can we see the oldest flashback available? You can use the following query to see the flashback data available.to_char(sysdate, to_char(oldest_flashback_time'YYYY-MM-DD HH24:MI', .*60 v$database d;How to get current session id, process id, client process id?select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session bwhere a.addr = b.paddr and b.audsid = userenv('sessionid');V$SESSION.SID and V$SESSION.SERIAL# are database process idV$PROCESS.SPID – Shadow process id on the database serverV$SESSION.PROCESS – Client process id, on windows it is “:” separated the first # is the process id on the client and 2nd one is the thread id.How to find running jobs in oracle database select sid, job,instance from dba_jobs_running;‎select sid, serial#,machine, status, osuser,username from v$session where username!='NULL'; --all active usersselect owner, job_name from DBA_SCHEDULER_RUNNING_JOBS; --for oracle 10gHow to find long running jobs in oracle databaseselect username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, time_remaining remaining, message from v$session_longops where time_remaining = 0 order by time_remaining descReport Longest Rman Backup Job Select username, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, TOTALWORK, SOFAR COMPLETED, time_remaining remaining, ELAPSED_SECONDS, message from v$session_longops where time_remaining = 0 and message like 'RMAN%' order by ELAPSED_SECONDS DESC; Last SQL Fired from particular Schema or Table: Select CREATED, TIMESTAMP, last_ddl_time from all_objects WHERE OWNER='HRMS' AND OBJECT_TYPE='TABLE' AND OBJECT_NAME='PAYROLL_MAIN_FILE';Display Log on Information of database: Select SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours" from sys.v_$session where sid=1; Note: The above query will display since how many days and time your database is up. That means you can estimate the last login days and time. Here Sid=1 is the PMON How do you find whether the instance was started with pfile or spfile SELECT name, value FROM v$parameter WHERE name = 'spfile'; This query will return NULL if you are using PFILE SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;If the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile: SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';How can you check which user has which Role: Sql>Select * from DBA_ROLE_PRIVS order by grantee;How to detect Migrated and chained row in a TableYou must execute script UTLCHAIN.SQL from before doing actual query SQL> ANALYZE TABLE scott.emp LIST CHAINED ROWS; SQL> SELECT * FROM chained_rows; You can also detect migrated and chained rows by checking the ‘table fetch continued row’ statistic in the v$sysstat view. SQL> SELECT name, value FROM v$sysstat WHERE name = ‘table fetch continued row’; Find Top 10 SQL from Database:SELECT * FROM (SELECT rownum Substr(a.sql_text 1 200) sql_text Trunc(a.disk_reads/Decode(a.executions 0 1 a.executions)) reads_per_execution a.buffer_gets a.disk_reads a.executions a.sorts a.address FROM v$sqlarea a ORDER BY 3 DESC)WHERE rownum < 10; How to Get Database Version:SELECT * from v$version; SELECT VALUE FROM v$system_parameter WHERE name = 'compatible'; Find the Size of Schema:SELECT SUM (bytes / 1024 / 1024) "size"FROM dba_segments WHERE owner = '&owner';Oracle SQL query over the view that shows actual Oracle Connections.SELECT osuser, username, machine, programFROM v$session ORDER BY osuser; SELECT program application, COUNT (program) Numero_SesionesFROM v$session GROUP BY programORDER BY Numero_Sesiones DESC;Showing the Table Structure:SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;Getting Current Schema:SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;How to find the last time a session performed any activity?select username, floor(last_call_et / 60) "Minutes", status from v$sessionwhere username is not null order by last_call_et;How to find parameters that will take into effect for new sessions?SELECT name FROM v$parameter WHERE issys_modifiable = 'DEFERRED';How to find tables that have a specific column name?SELECT owner, table_name, column_nameFROM dba_tab_columns WHERE column_name like 'AMOUNT' ORDER by table_name;Display database Recovery status: SELECT * FROM v$backup; SELECT * FROM v$recovery_status; SELECT * FROM v$recover_file; SELECT * FROM v$recovery_file_status; SELECT * FROM v$recovery_log;

0 notes

notsadrobotxyz · 6 years ago

Text

ORA-28002: the password will expire within 5 days

Today one user written to me when he is trying to connect the database through toad getting the following error: select username, profile,account_status,expiry_date, created from dba_users where default_tablespace not in ('SYSAUX','SYSTEM') --and expiry_date is not null and account_status not like '%LOCKED' order by expiry_date; In fact it is NOT measured since from the current time. Therefore, any accounts affected by the changed profile whose last password change time was more than PASSWORD_LIFE_TIMEdays ago immediately expire and enter their grace period on their next connection, issuing the ORA-28002 warning. SQL> ALTER PROFILE test LIMIT PASSWORD_LIFE_TIME UNLIMITED; This change only affects accounts those have not entered their grace period. The accounts that have entered their grace period will have to change their passwords.

0 notes

notsadrobotxyz · 6 years ago

Text

DBA Daily/Weekly/Monthly or Quarterly Checklist

In response of some fresher DBA I am giving quick checklist for a production DBA. Here I am including reference of some of the script which I already posted as you know each DBA have its own scripts depending on database environment too. Please have a look on into daily, weekly and quarterly checklist. Note: I am not responsible of any of the script is harming your database so before using directly on Prod DB. Please check it on Test environment first and make sure then go for it.Please send your corrections, suggestions, and feedback to me. I may credit your contribution. Thank you.------------------------------------------------------------------------------------------------------------------------Daily Checks:Verify all database, instances, Listener are up, every 30 Min. Verify the status of daily scheduled jobs/daily backups in the morning very first hour.Verify the success of archive log backups, based on the backup interval.Check the space usage of the archive log file system for both primary and standby DB. Check the space usage and verify all the tablespace usage is below critical level once in a day. Verify Rollback segments.Check the database performance, periodic basis usually in the morning very first hour after the night shift schedule backup has been completed.Check the sync between the primary database and standby database, every 20 min. Make a habit to check out the new alert.log entry hourly specially if getting any error.Check the system performance, periodic basis.Check for the invalid objectsCheck out the audit files for any suspicious activities. Identify bad growth projections.Clear the trace files in the udump and bdump directory as per the policy.Verify all the monitoring agent, including OEM agent and third party monitoring agents.Make a habit to read DBA Manual.Weekly Checks:Perform level 0 or cold backup as per the backup policy. Note the backup policy can be changed as per the requirement. Don’t forget to check out the space on disk or tape before performing level 0 or cold backup.Perform Export backups of important tables.Check the database statistics collection. On some databases this needs to be done every day depending upon the requirement.Approve or plan any scheduled changes for the week.Verify the schedule jobs and clear the output directory. You can also automate it.Look for the object that break rule. Look for security policy violation. Archive the alert logs (if possible) to reference the similar kind of error in future. Visit the home page of key vendors.Monthly or Quarterly Checks:Verify the accuracy of backups by creating test databases.Checks for the critical patch updates from oracle make sure that your systems are in compliance with CPU patches.Checkout the harmful growth rate. Review Fragmentation. Look for I/O Contention. Perform Tuning and Database Maintenance.Verify the accuracy of the DR mechanism by performing a database switch over test. This can be done once in six months based on the business requirements.------------------------------------------------------------------------------------------------------------------------------------------------------- Below is the brief description about some of the important concept including important SQL scripts. You can find more scripts on my different post by using blog search option.Verify all instances are up: Make sure the database is available. Log into each instance and run daily reports or test scripts. You can also automate this procedure but it is better do it manually. Optional implementation: use Oracle Enterprise Manager's 'probe' event.Verify DBSNMP is running:Log on to each managed machine to check for the 'dbsnmp' process. For Unix: at the command line, type ps –ef | grep dbsnmp. There should be two dbsnmp processes running. If not, restart DBSNMP.Verify success of Daily Scheduled Job:Each morning one of your prime tasks is to check backup log, backup drive where your actual backup is stored to verify the night backup. Verify success of database archiving to tape or disk:In the next subsequent work check the location where daily archiving stored. Verify the archive backup on disk or tape.Verify enough resources for acceptable performance:For each instance, verify that enough free space exists in each tablespace to handle the day’s expected growth. As of , the minimum free space for : . When incoming data is stable, and average daily growth can be calculated, then the minimum free space should be at least days’ data growth. Go to each instance, run query to check free mb in tablespaces/datafiles. Compare to the minimum free MB for that tablespace. Note any low-space conditions and correct it.Verify rollback segment:Status should be ONLINE, not OFFLINE or FULL, except in some cases you may have a special rollback segment for large batch jobs whose normal status is OFFLINE. Optional: each database may have a list of rollback segment names and their expected statuses.For current status of each ONLINE or FULL rollback segment (by ID not by name), query on V$ROLLSTAT. For storage parameters and names of ALL rollback segment, query on DBA_ROLLBACK_SEGS. That view’s STATUS field is less accurate than V$ROLLSTAT, however, as it lacks the PENDING OFFLINE and FULL statuses, showing these as OFFLINE and ONLINE respectively.Look for any new alert log entries:Connect to each managed system. Use 'telnet' or comparable program. For each managed instance, go to the background dump destination, usually $ORACLE_BASE//bdump. Make sure to look under each managed database's SID. At the prompt, use the Unix ‘tail’ command to see the alert_.log, or otherwise examine the most recent entries in the file. If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The recovery log is in .Identify bad growth projections.Look for segments in the database that are running out of resources (e.g. extents) or growing at an excessive rate. The storage parameters of these segments may need to be adjusted. For example, if any object reached 200 as the number of current extents, upgrade the max_extents to unlimited. For that run query to gather daily sizing information, check current extents, current table sizing information, current index sizing information and find growth trendsIdentify space-bound objects:Space-bound objects’ next_extents are bigger than the largest extent that the tablespace can offer. Space-bound objects can harm database operation. If we get such object, first need to investigate the situation. Then we can use ALTER TABLESPACE COALESCE. Or add another datafile. Run spacebound.sql. If all is well, zero rows will be returned.Processes to review contention for CPU, memory, network or disk resources:To check CPU utilization, go to =>system metrics=>CPU utilization page. 400 is the maximum CPU utilization because there are 4 CPUs on phxdev and phxprd machine. We need to investigate if CPU utilization keeps above 350 for a while.Make a habit to Read DBA Manual:Nothing is more valuable in the long run than that the DBA be as widely experienced, and as widely read, as possible. Readingsshould include DBA manuals, trade journals, and possibly newsgroups or mailing lists.Look for objects that break rules:For each object-creation policy (naming convention, storage parameters, etc.) have an automated check to verify that the policy is being followed. Every object in a given tablespace should have the exact same size for NEXT_EXTENT, which should match the tablespace default for NEXT_EXTENT. As of 10/03/2012, default NEXT_EXTENT for DATAHI is 1 gig (1048576 bytes), DATALO is 500 mb (524288 bytes), and INDEXES is 256 mb (262144 bytes). To check settings for NEXT_EXTENT, run nextext.sql. To check existing extents, run existext.sqlAll tables should have unique primary keys:To check missing PK, run no_pk.sql. To check disabled PK, run disPK.sql. All primary key indexes should be unique. Run nonuPK.sql to check. All indexes should use INDEXES tablespace. Run mkrebuild_idx.sql. Schemas should look identical between environments, especially test and production. To check data type consistency, run datatype.sql. To check other object consistency, run obj_coord.sql.Look for security policy violations:Look in SQL*Net logs for errors, issues, Client side logs, Server side logs and Archive all Alert Logs to historyVisit home pages of key vendors:For new update information made a habit to visit home pages of key vendors such as: Oracle Corporation: http://www.oracle.com, http://technet.oracle.com, http://www.oracle.com/support, http://www.oramag.com Quest Software: http://www.quests.comSun Microsystems: http://www.sun.com Look for Harmful Growth Rates:Review changes in segment growth when compared to previous reports to identify segments with a harmful growth rate. Review Tuning Opportunities and Perform Tuning Maintainance:Review common Oracle tuning points such as cache hit ratio, latch contention, and other points dealing with memory management. Compare with past reports to identify harmful trends or determine impact of recent tuning adjustments. Make the adjustments necessary to avoid contention for system resources. This may include scheduled down time or request for additional resources.Look for I/O Contention:Review database file activity. Compare to past output to identify trends that could lead to possible contention.Review Fragmentation:Investigate fragmentation (e.g. row chaining, etc.), Project Performance into the FutureCompare reports on CPU, memory, network, and disk utilization from both Oracle and the operating system to identify trends that could lead to contention for any one of these resources in the near future. Compare performance trends to Service Level Agreement to see when the system will go out of bounds. -------------------------------------------------------------------------------------------- Useful Scripts: -------------------------------------------------------------------------------------------- Script: To check free, pct_free, and allocated space within a tablespace SELECT tablespace_name, largest_free_chunk, nr_free_chunks, sum_alloc_blocks, sum_free_blocks , to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') || '%' AS pct_free FROM ( SELECT tablespace_name, sum(blocks) AS sum_alloc_blocks FROM dba_data_files GROUP BY tablespace_name), ( SELECT tablespace_name AS fs_ts_name, max(blocks) AS largest_free_chunk , count(blocks) AS nr_free_chunks, sum(blocks) AS sum_free_blocks FROM dba_free_space GROUP BY tablespace_name ) WHERE tablespace_name = fs_ts_name; Script: To analyze tables and indexes BEGIN dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ; END ; Script: To find out any object reaching SELECT e.owner, e.segment_type , e.segment_name , count(*) as nr_extents , s.max_extents , to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , '999,999.90') as MB FROM dba_extents e , dba_segments s WHERE e.segment_name = s.segment_name GROUP BY e.owner, e.segment_type , e.segment_name , s.max_extents HAVING count(*) > &THRESHOLD OR ( ( s.max_extents - count(*) ) < &&THRESHOLD ) ORDER BY count(*) desc; The above query will find out any object reaching level extents, and then you have to manually upgrade it to allow unlimited max_extents (thus only objects we expect to be big are allowed to become big. Script: To identify space-bound objects. If all is well, no rows are returned. SELECT a.table_name, a.next_extent, a.tablespace_name FROM all_tables a,( SELECT tablespace_name, max(bytes) as big_chunk FROM dba_free_space GROUP BY tablespace_name ) f WHERE f.tablespace_name = a.tablespace_name AND a.next_extent > f.big_chunk; Run the above query to find the space bound object . If all is well no rows are returned if found something then look at the value of next extent. Check to find out what happened then use coalesce (alter tablespace coalesce;). and finally, add another datafile to the tablespace if needed. Script: To find tables that don't match the tablespace default for NEXT extent. SELECT segment_name, segment_type, ds.next_extent as Actual_Next , dt.tablespace_name, dt.next_extent as Default_Next FROM dba_tablespaces dt, dba_segments ds WHERE dt.tablespace_name = ds.tablespace_name AND dt.next_extent !=ds.next_extent AND ds.owner = UPPER ( '&OWNER' ) ORDER BY tablespace_name, segment_type, segment_name; Script: To check existing extents SELECT segment_name, segment_type, count(*) as nr_exts , sum ( DECODE ( dx.bytes,dt.next_extent,0,1) ) as nr_illsized_exts , dt.tablespace_name, dt.next_extent as dflt_ext_size FROM dba_tablespaces dt, dba_extents dx WHERE dt.tablespace_name = dx.tablespace_name AND dx.owner = '&OWNER' GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent; The above query will find how many of each object's extents differ in size from the tablespace's default size. If it shows a lot of different sized extents, your free space is likely to become fragmented. If so, need to reorganize this tablespace. Script: To find tables without PK constraint SELECT table_name FROM all_tables WHERE owner = '&OWNER' MINUS SELECT table_name FROM all_constraints WHERE owner = '&&OWNER' AND constraint_type = 'P'; Script: To find out which primary keys are disabled SELECT owner, constraint_name, table_name, status FROM all_constraints WHERE owner = '&OWNER' AND status = 'DISABLED' AND constraint_type = 'P'; Script: To find tables with nonunique PK indexes. SELECT index_name, table_name, uniqueness FROM all_indexes WHERE index_name like '&PKNAME%' AND owner = '&OWNER' AND uniqueness = 'NONUNIQUE' SELECT c.constraint_name, i.tablespace_name, i.uniqueness FROM all_constraints c , all_indexes i WHERE c.owner = UPPER ( '&OWNER' ) AND i.uniqueness = 'NONUNIQUE' AND c.constraint_type = 'P' AND i.index_name = c.constraint_name; Script: To check datatype consistency between two environments SELECT table_name, column_name, data_type, data_length,data_precision,data_scale,nullable FROM all_tab_columns -- first environment WHERE owner = '&OWNER' MINUS SELECT table_name,column_name,data_type,data_length,data_precision,data_scale,nullable FROM all_tab_columns@&my_db_link -- second environment WHERE owner = '&OWNER2' order by table_name, column_name; Script: To find out any difference in objects between two instances SELECT object_name, object_type FROM user_objects MINUS SELECT object_name, object_type FROM user_objects@&my_db_link; For more about script and Daily DBA Task or Monitoring use the search concept to check my other post. Follow the below link for important Monitoring Script: http://shahiddba.blogspot.com/2012/04/oracle-dba-daily-checklist.html

0 notes

notsadrobotxyz · 6 years ago

Text

Oracle 9i Database Deletion Manually (When required)

Database deletion task is not coming to DBA on regular interval. I was lucky to get few tasks for production database deletion. I have 10 year experience in database and first time, I got the opportunity to delete the production database after migration database on new server and updated from 9i to 11g. Delete the database on windows is easy but deletion of database manually without use of tool is a tricky task. To drop database is 9i is not so easy like 10g and above version, because 9i does not support drop database command. Here I am sharing my experience with DBA who is going to perform such type task in future. This step by step guide will give you how to delete database in UNIX environment where you are not able to use DBCA tools. Make sure before deleting the database unregistered database from catalog if you are using for backup & recover (Unregistered database from catalog). In fact there is no direct command in oracle 9i to drop the database manually. You can only delete the database whenever required.Steps to remove Oracle database refrences from Windows Env.:Ensure you are login with administrator user.Go to service.msc and stop all oracle related services.Remove oracle service using oradim utilities. For more about: How to Remove Oracle SID in Windows Env. Use OUI to uninstall the oracle products.Go to regedit to remove all oracle keys:HKEY_LOCAL_MACHINESOFTWARE HKEY_LOCAL_MACHINESOFTWAREClasses, remove all keys that begin with Ora or ORCL HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesEventLogApplication HKEY_LOCAL_MACHINESOFTWAREODBC remove all keys related with the "Oracle ODBC Driver" Remove Environment variable and all the path of oracle references:Example: C:ORACLEORA81BIN;C:PROGRAM FILESORACLEJRE1.1.7BIN Delete all related oracle Icons from the drive location.Remove any Oracle-related *.INI files that may exist in the Windows directory. Typical Oracle .INI files include ORADIM73.INI, ORADIM80.INI, ORACLE.INI,ORAODBC.INI Finally reboot the system.

0 notes

notsadrobotxyz · 6 years ago

Text

Database Startup fails with ORA-00444, ORA-07446

One day when I try to start my database my instance fails with following error:SQL> startup; ORA-00444: background process "MMAN" failed while starting ORA-07446: sdnfy: bad value '' for parameter . Cause:Database startup event is logged into alert log file and alert log file is defined by the background_dump_dest parameter inside the initialization parameter. While starting up the instance, if fail to write into the alert log file then instance crushes and fail to startup.Solution:·

0 notes

notsadrobotxyz · 6 years ago

Text

Script: To Monitor Tablespaces/datafiles

Important Note: If any of the script in this blog is not running then please re-type it or try to retype quotation, command and braces (may be format is changed). I am using toad so if you are using SQL then try to fix column length before exectunig the script (if any). ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- To check Tablespace free space: SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) "Size (MB)" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME; To check Tablespace by datafile: SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE group by tablespace_name, file_id; To Check Tablespace used and free space %: SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace, fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used FROM v$temp_space_header GROUP BY tablespace_name,bytes_free,bytes_used) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used ORDER BY 4 DESC; --or-- Select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2) as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace; Tablespace (File wise) used and Free space SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name ORDER BY file_name; To check Growth rate of Tablespace Note: The script will not show the growth rate of the SYS, SYSAUX Tablespace. T he script is used in Oracle version 10g onwards. SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days, ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id AND ts.tsname = dt.tablespace_name AND ts.tsname NOT IN ('SYSAUX','SYSTEM') GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname ORDER BY ts.tsname, days; List all Tablespaces with free space < 10% or full space> 90% Select a.tablespace_name,sum(a.tots/1048576) Tot_Size, sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add from (select tablespace_name,0 tots,sum(bytes) sumb from dba_free_space a group by tablespace_name union Select tablespace_name,sum(bytes) tots,0 from dba_data_files group by tablespace_name) a group by a.tablespace_name having sum(a.sumb)*100/sum(a.tots) < 10 order by pct_free; Script to find all object Occupied space for a Tablespace Select OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024 "SZIE IN MB" from dba_segments where TABLESPACE_NAME = 'SDH_HRMS_DBF' group by OWNER, SEGMENT_NAME; Which schema are taking how much space Select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB" from (select owner, count(*) obj_cnt from dba_objects group by owner) obj, (select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) seg where obj.owner = seg.owner(+) order by 3 desc ,2 desc, 1; To Check Default Temporary Tablespace Name: Select * from database_properties where PROPERTY_NAME like '%DEFAULT%'; To know default and Temporary Tablespace for particualr User: Select username,temporary_tablespace,default_tablespace from dba_users where username='HRMS'; To know Default Tablespace for All User: Select default_tablespace,temporary_tablespace,username from dba_users; To Check Datafiles used and Free Space: SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name ORDER BY file_name; To check Used free space in Temporary Tablespace: SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE FROM V$temp_space_header GROUP BY tablespace_name; SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total; Sort (Temp) space used by Session SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial; Sort (Temp) Space Usage by Statement SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+) AND T.tablespace = TBS.tablespace_name ORDER BY S.sid; Who is using which UNDO or TEMP segment? SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser,s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'; Who is using the Temp Segment? SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE", a.sid||','||a.serial# SID_SERIAL, a.username, a.program FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks; Total Size and Free Size of Database: Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size", round(free.p / 1024 / 1024/1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used, (select sum(bytes) as p from dba_free_space) free group by free.p; To find used space of datafiles: SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments; IO status of all of the datafiles in database: WITH total_io AS (SELECT SUM (phyrds + phywrts) sum_io FROM v$filestat) SELECT NAME, phyrds, phywrts, ((phyrds + phywrts) / c.sum_io) * 100 PERCENT, phyblkrd, (phyblkrd / GREATEST (phyrds, 1)) ratio FROM SYS.v_$filestat a, SYS.v_$dbfile b, total_io c WHERE a.file# = b.file# ORDER BY a.file#; Displays Smallest size the datafiles can shrink to without a re-organize. SELECT a.tablespace_name, a.file_name, a.bytes AS current_bytes, a.bytes - b.resize_to AS shrink_by_bytes, b.resize_to AS resize_to_bytes FROM dba_data_files a, (SELECT file_id, MAX((block_id+blocks-1)*&v_block_size) AS resize_to FROM dba_extents GROUP by file_id) b WHERE a.file_id = b.file_id ORDER BY a.tablespace_name, a.file_name; Scripts to Find datafiles increment details: Select SUBSTR(fn.name,1,DECODE(INSTR(fn.name,'/',2),0,INSTR(fn.name,':',1),INSTR(fn.name,'/',2))) mount_point,tn.name tabsp_name,fn.name file_name, ddf.bytes/1024/1024 cur_size, decode(fex.maxextend, NULL,ddf.bytes/1024/1024,fex.maxextend*tn.blocksize/1024/1024) max_size, nvl(fex.maxextend,0)*tn.blocksize/1024/1024 - decode(fex.maxextend,NULL,0,ddf.bytes/1024/1024) unallocated,nvl(fex.inc,0)*tn.blocksize/1024/1024 inc_by from sys.v_$dbfile fn, sys.ts$ tn, sys.filext$ fex, sys.file$ ft, dba_data_files ddf where fn.file# = ft.file# and fn.file# = ddf.file_id and tn.ts# = ft.ts# and fn.file# = fex.file#(+) order by 1;

0 notes

notsadrobotxyz · 6 years ago

Text

How to deal online redo log files Corruption

If you suspect that the database is having problems writing redo logfiles, You can configure Oracle to use checksums to verify blocks in the redo logfiles. FROM ;Setting the LOG_BLOCK_CHECKSUM initialization parameter to TRUE will enable redo log block checking. The default value of LOG_BLOCK_CHECKSUM is TRUE in oracle 9i and FALSE in earlier version. When redo log block checking is enabled, Oracle computes a checksum for each redo log block written to the current log and writes that checksum in the header of the block. Oracle uses the checksum to detect corruption in a redo log block and will try to verify the redo log block when it writes the block to an archivelog files or when the block is read from an archived log during recovery..name, . syswherestatistic# ( . ;If a redo log block is corrupted while the archive is being written, Oracle will try to read the block from another member in the group. If all members have a corrupted block, archiving will stop., BYTESFROM, FROMALTER UNARCHIVED DATABASE LOGFILE DATABASE GROUPgroup_number

0 notes

notsadrobotxyz · 6 years ago

Text

Migrate from 32 bit to 64 bit using RMAN

Tested: Oracle 9i R2 OS: Windows 2003 serverThis article describes the very simple procedure to migrate a database from Windows 32 bit system to 64 bit using RMAN. Follow the steps below when the need arises to build a new Oracle Applications database instance (for a test migration or when re-organizing an existing production instance, for example). Before starting, plan carefully for such issues as the location of new DATAFILES and space considerations. The steps below specifically address the need to refresh a User-Test / Development instance with current production data. Migrating through RMAN is much faster than Import/export but using the export/import utilities to rebuild your instance instead of cloning because an export/import rebuild provides the opportunity for clean-up and correction.Step 1: Take a Backup on Source 32 Bit Server:Connect the rman target to create a full backup of the database including all archivelogs:allocate channel d1 type disk FORMAT "D:BACKUPRMAN_BACKUPHOTBACKUPMonthly_%T_FULL_%d-%s_%p.db"; backup format "D:BACKUPRMAN_BACKUPHOTBACKUPMonthly_%T_FULL_%d-%s_%p.db" backup format "D:BACKUPRMAN_BACKUPHOTBACKUPMonthly_%T_FULL_%d-%s_%p.arc";release channel d3; create spfile from pfile = D:Backuporcl3pfile.sql’;Now connect the target database through RMAN and restore controlfile. For more details how to restore check my other post Disaster Recovery from the scratch and Recovery from Complete loss of database‎ C: rman target / nocatalog RMAN> set dbid=4130713932;Restore the controlfile:RMAN> restore controlfile from ‘D:backup_32bitcontrol01.ctl’;RMAN> alter database mount;RMAN> restore database;Identify the archivelog till you want to do the recovery and check if you have all the backups you need to do it and run:RMAN> run {set until sequence xxxx;recover database;}RMAN> exitStep 6: Migrate to 64 Bit:SQL> recover database until cancel using backup controlfile;cancelSQL> alter database open resetlogs migrate;SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sqlSQL> @$ORACLE_HOME/rdbms/admin/utlrp.sqlSQL> shutdown immediate;SQL> startup;Note: Do not forget to check tns connection.

0 notes

notsadrobotxyz · 6 years ago

Text

Plan B: Renovate old Apps Server Hardware

Overview: I had a situation to renovate the hardware of very old server and we do not have idea about some of the application running, It may be fresh installation of application can create performance issue and also we are facing the complexity of same patched version oracle binaries. I planned to restore the whole system through the system restore policy and restore and recover the database with the RMAN cold backup and after performance tuning and testing the different aspect of oracle will provide the same IP configuration to the new server.Scenario:This scenario assumes that we have two networked Windows 2003 hosts, DBSERVER and DBSERVER1. A target database named SADHAN is on DBSERVER and is registered in Recovery catalog RMAN. We want to Restore & Recovery of SADHAN on DBSERVER1, while original database server DBSERVER is completely offline. In my case the O/S is Windows 2003 and the Oracle Database Product is 9i R2 (9.2.0.7) Standard Edition patched oracle binaries are restored through system restore procedure. Click for More info: Backup & Restoring System using AcronisTheory:If our goal is to perform a Test run of our disaster recovery procedures, or to permanently move a database to a new host, then we can use the Restore & Recovery Procedure.If we use Restore & Recovery procedure, then the DBID for the restored database will be the same as the DBID for the original database, so after the restore & recovery procedure is completed, if we connect to the Test database and the recovery catalog DB, the recovery catalog will be updated with information about the Test DB that can interfere with RMAN's ability to restore and recover the source database (due to same DBID)If our goal is to create a new copy of our target database for ongoing use on a new host, then use the RMAN DUPLICATE command instead of Restore & Recovery procedure. The DUPLICATE command assigns a new DBID to the database it creates and enabling it to be registered in the same recovery catalog as the original database.Descriptions:In the night we planned to take the system restore by shutting down the original database and in the early morning we opened the original database for end user. In the day time we done the system restore to new server using Acronis system restore policy. Finally we are able to get the same oracle binaries. In the next Day I have taken the sufficient downtime for RMAN backup and restore policy synchronies the database. To do a consistent" backup with RMAN, the database must be mounted as RMAN needs to access and update the control files. With an OS scripted backup pre-RMAN, the database was truly "COLD" -- there would be no Oracle processes running.At the moment I had to restore the database on the new server the old server was not available anymore.STEPS:Taking RMAN COLD backup including Current Controlfile Backup.Shutdown the Old Database to restrict any modification.Copy the backup on other host on the same location as per the DBserver.Create PFILE from current server and Copy the Destination server location.Copy or Create tnsname.ora, listener.ora and password file to the destination host folder.Testing and Validating Listener and RMAN Repository Connection.Create Oracle Service for windows (in our case just check it is started) and check all other folder is already restored through system restore.Start the database on new host with the Pfile.Restore Control fileRestore whole databaseRecover whole database.Validate the Restore & Recovery procedure Correctness.Enter change the IP address, name of Old server to New server.Check the Application is running successfully.After successful testing make the server available for end users.I have already password file and server parameter file into the new $ORACLE_HOME/dbsDue to system restore. I also have same directory structure on the new server.I create a pfile from spfile and copied it into the new server/C:>SET ORACLE_SID = SADHANC:SQLPLUS /NOLOGSQL> CONNECT / AS SYSDBAConnected to an idle instance. SQL>STARTUP FORCE NOMOUNT PFILE='D:oracleora92dbssadhan.sql';SQL>CREATE SPFILE FROM PFILE='D:oracleora92dbssadhan.sql';File created.SQL> shutdown immediate; --bounce back the databaseSQL> Startup nomount;Caution: Make sure RMAN is not connected to recovery catalog, otherwise RMAN records information about restored database in recovery catalog as the DBID is same. Thus you are not able to restore and recover the database in future with this RMAN. C:> rman target /connected to target database: SADHAN (not mounted)RMAN:>SET DBID 63198018;RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;RMAN> restore controlfile from "H:ORABACKC-1103102985-20100216-02";RMAN> alter database mount;RMAN> sql ‘alter database mount’;Note: The Blue mark command you can choose optionally. RMAN> list backup;RMAN> Restore database;RMAN> ALTER DATABASE OPEN RESETLOGS;Method: Restore database (when CRD file is Ok) through RMANWhen Database is all ready running only need to synchronies database file. C:>rman RMAN> connect target / connected to target database (not started) RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;RMAN> ALTER DATABASE MOUNT;

0 notes

notsadrobotxyz · 6 years ago

Text

ORA-01012: not logged on

ORA-01012: not logged on This may occur generally due to heavy load or out of available connection in the database. If there is maximum number of sessions connected to the database, which is defined by ‘processes‘ parameter and database does not allow sysdba as well as other users to connect to the database. Sometimes it may also occur due to improper shutdown the database. In that case it shows connected but it does not allow to happening any query to the database instead it fails with ORA-01012: not logged on

Sometimes connecting with sysdba shows database in idle instance but whenever you issue startup it fails with ORA-01081: cannot start already-running ORACLE - shut it down first.In the above case:Either shutdown the Application as well as DB server (if Possible) or Kill unused oracle processes which holding the resource (Make sure do not kill BG processes. You can use toad for killing the process). Then login with sysdba to increase the value of parameter ‘processes’.SQL> show parameter sessionsSQL> show parameter processesSQL> show parameter transactionsSQL> SELECT value FROM V$PARAMETER WHERE NAME = 'processes';SQL> Alter system set processes=300 scope=both sid='*';SQL> Alter system set sessions=355 scope=both sid='*';SQL> Alter system set transactions=410 scope=both sid='*';

0 notes

notsadrobotxyz · 6 years ago

Text

How to Validate or Change User’s Password through Form

Through this post you are able to create a simple logon screen for the user to enter the software by providing username and password or facilitate the user to change his password through software at any time. Design a simple form ‘TESTSEC_NEW’ with the field ‘USERNAM’ and ‘PASSWD’ along with ‘Exit’ button.

Write When_Button_Pressed Trigger on button ‘EXIT’EXIT_FORM(NO_VALIDATE);Write a Key_Next_Item Trigger on item ‘PASSWD’:GLOBAL.USER := :BLOCK1.USER_NAM;:GLOBAL.PASSWD := :BLOCK1.PASSWD;GET_SECURITY; ---Get security is a procedure described belowCLEAR_RECORD;CALL_FORM('.glgl_tree.FMX');Write ‘ON LOGON’ form Level Trigger.logon('orafin','oracle'||'@'||'sadhan.world');Where the ‘orafin’ is the database user or schema and ‘oracle’ is the password for that schema and ‘Sadhan.world’ is the connect string for the database.Write GET_SECURITY procedure in Program Unit.PROCEDURE GET_SECURITY IS DUM�� NUMBER(4); BRN NUMBER(2); DUM_PASS VARCHAR2(10); CURSOR SEC IS SELECT GROUP_ID,USER_BRANCH FROM USER_PROGRAM WHERE USER_NAME = :GLOBAL.USER AND USER_PASSWORD = DUM_PASS;BEGIN --DUM_PASS := --EYD_ENCYPHER(:GLOBAL.PASSWD); DUM_PASS := :GLOBAL.PASSWD; OPEN SEC; FETCH SEC INTO DUM,BRN; IF SEC%NOTFOUND THEN FOR i in 1..5 LOOP BELL; SYNCHRONIZE; END LOOP; GET_MESS(106); CLOSE SEC; RAISE FORM_TRIGGER_FAILURE; END IF; :GLOBAL.GROUP_ID := DUM; :GLOBAL.BRANCH := BRN; CLOSE SEC; exception when form_trigger_failure then raise form_trigger_failure; when others then show_message(2,2); raise form_trigger_failure; END;Similarly if you required you can write the procedure to Encrypt or Decrypt your password with ‘Eyd_Encypher’ or ‘Eyd_Decypher’ and facility to change or validate password by the users.FUNCTION Eyd_Decypher( Cypher_T VARCHAR2 )RETURN VARCHAR2 IS Z NUMBER; PLAIN_T VARCHAR2(10) := NULL; CURSOR GET_SEC IS SELECT SYS_NUMBER FROM GEN_SYSTEM WHERE SYS_MAJ = 111 AND SYS_MIN = 0;BEGIN OPEN GET_SEC; FETCH GET_SEC INTO Z; IF GET_SEC%NOTFOUND THEN CLOSE GET_SEC; GET_MESS(274); RAISE FORM_TRIGGER_FAILURE; ELSE IF NVL(LENGTH(CYPHER_T), 0) > 10 THEN RETURN(NULL); END IF; FOR I IN 1..NVL(LENGTH(CYPHER_T), 0) LOOP PLAIN_T := PLAIN_T || CHR( 256 - ASCII(SUBSTR(CYPHER_T,I,1)) - Z + I ); END LOOP; END IF; CLOSE GET_SEC; RETURN(PLAIN_T);END;FUNCTION Eyd_Encypher( Plain_T VARCHAR2 )RETURN VARCHAR2 IS Z NUMBER; CYPHER_T VARCHAR2(10) := NULL; ASCII_T NUMBER; CURSOR GET_SEC IS SELECT SYS_NUMBER FROM GEN_SYSTEM WHERE SYS_MAJ = 111 AND SYS_MIN = 0;BEGIN OPEN GET_SEC; FETCH GET_SEC INTO Z; IF GET_SEC%NOTFOUND THEN CLOSE GET_SEC; GET_MESS(274); RAISE FORM_TRIGGER_FAILURE; ELSE IF NVL(LENGTH(PLAIN_T), 0) > 10 THEN RETURN(NULL); END IF; FOR I IN 1..NVL(LENGTH(PLAIN_T), 0) LOOP ASCII_T := ASCII(SUBSTR(PLAIN_T,I,1)); IF ASCII_T BETWEEN 48 AND 122 THEN CYPHER_T := CYPHER_T || CHR( 256 + ( - ASCII_T ) - Z + I ); ELSE RETURN(NULL); END IF; END LOOP; END IF; CLOSE GET_SEC; RETURN(CYPHER_T);END; Procedure to facilitate User’s to change his Password:BEGIN GO_ITEM('PASS1'); DUM := :PASS2;--ENCYPHER(:PASS2); RAISE FORM_TRIGGER_FAILURE; END IF; GO_ITEM('PASS1');

0 notes

notsadrobotxyz · 6 years ago

Text

Add Contact Form to a Specific Page with your Blogger‎

To add the Contact Form to specific page you have to add it first on the blogger column. If you already having the contact form on blogger layout then just ignore this steps.Sign in into your blogger dashboard and go to layout.Now click on “Add Gadget” link of “More Gadget”.Now find “Contact form” page element and click on it.Now Create the New Contact Page where you want to add this page. Ignore the below steps if you have already created the contact page:1. Create New Contact Page.2. After Creating the page switch to HTML mode of the page.3. Paste the below code in required position.

To add contact form into particular page you have to hide the official contact form from side bar. You can do this either removing the contact form code or adding some code to hide the contact form from side bar.Go to the Template -> Edit HTML button and find this code:

0 notes

notsadrobotxyz · 6 years ago

Text

Steps by Step Oracle database Upgration/Migration

What are required Steps for Oracle 10g to 11g upgradation:1. Installing oracle 11g home: we cannot upgrade the existing oracle home since 11g is not patchset. We have to install 11g separate oracle home in parallel to 10g oracle home.2. Run Pre-upgrade utility: From oracle 11g homeadmin folder copy utlu111i.sql file and keep it into temporary location accessible to oracle 10g home then open the oracle 10g database and from the SQL prompt run this utility to check the compatibility issues. If you find any critical warning message try to check these issues before running upgradation process.3. Correcting version of existing Timezone issues: Check the version of existing time zone if it is less than 4 then you must apply the patch for version 4 timezone files. Check the metalink note for more details:If there is no official patchset for the version you are currently having then you can identify the utltzuv2.sql and timezdiff.csv combination patch to find the correct patchset.SQL> Select * from v$timezone_file;Timezlrg.dat2Once identify the correct patchset Apply it normally then bounce back the database and check the version again. Try to run the utlu111i.sql utility again check the critical issues if any:4. Correcting DB containing stale optimizer statistics: To remove this issue connect with sys user and gather statisticsSQL> EXE DBMS_STATS.GATHER_DICTIONARY_STATS;SQL> EXE DBMS_STATS.GATHER_SCHEMA_STATS (‘SYS’);SQL> EXE DBMS_STATS.GATHER_SCHEMA_STATS (‘SYSMAN’);Finally run the utility again to ensure you don’t have any critical issue to run the upgrade:5. Starting Upgrade: From the source of oracle 11g home directory to connect the database sys as sysdba. You will connected as an idle instance:SQL> startup upgrade;6. Post-Upgradation Task: Once the upgrade will finish it will shut down the database automatically.Login again as sysdba and startup in normal modeSQL> startup;SQL> run utlul111i.sql script to display the results upgradeSQL> run catuppst.sql from admin directory to perform upgrade actionSQL> run utrp.sql to recompile invalid object Upgrading Oracle Database From 11.2.0.4 to 12.2.0.1Run SQL scripts and utilities from command line to upgrade a database to the new Oracle Database release.Current ORCALE_HOME=/u01/app/oracle/product/11.2.0.4/db_1New ORACLE_HOME=/u02/app/oracle/product/12.2/db_11. Run the preupgrade tool:SYNTAX: /u01-/app/oracle/product/11.2.0.4/db_1/jdk/bin/java -jar /u02/app/oracle/product/12.2/db_1/rdbms/admin/preupgrade.jar FILE DIR /u01/Run the preupgrade_fixup.sql from 11g homeSQL> @ preupgrade_fixup.sqlA> Run the /u02/app/oracle/product/12.2/db_1/rdbms/admin/emremove.sql to remove the EM console from 12c home B> Run this block to refresh the materialized views.Increase the number of processes and purge the recycle binSQL> alter system set processes = 300 scope = spfile;SQL> PURGE DBA_RECYCLEBIN;1. SHUTDOWN THE DATABASE2. COPY THE PFILE AND PASSWORD FILE FROM 11G HOME TO 12C HOME.3. SET NEW ORACLE HOMEexport ORACLE_HOME=/u01/app/oracle/product/12.2/db_1/Start the database in upgrade mode from 12c home$ sqlplus / as sysdbaConnected to idle instanceSQL> startup nomount;SQL> alter database mount;SQL> alter database open upgrade;4. Run catctl.pl5. Run “postupgrade_fixups.sql” To fix the time zone from 14 to 26 we need to download dbms_dst scriptV1.9.zip from metalink and apply it to the new oracle home.SQL> select version from v$timezone_file;6. Execute utlu122s.sql:7. Execute catuppst.sql:8. Execute utlrp.sql script to compile invalid objects.9. Set COMPATIBALE parameter value for 12.2.0 as well as start the listener with 12.2 home.Migrating a Database across platforms.The export utility are the only method to migrate database running from one platform to another.1. Query in source database to find the name of tablespace and make full export of source database.SQL> select tablespace_name from dba_tablespaces; $>exp system/manager FULL=y FILE=myfullexp.dmp 2. Move the dump file to the target database and before import the dumpfile create required tablespace.> imp system/manager FULL=y IGNORE=y FILE=myfullexp.dmp

0 notes

notsadrobotxyz · 6 years ago

Text

DB File Sequential Read Wait/ DB File Scattered Read

A sequential read reads operation reads data into contiguous memory. Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads. To determine the actual object being waited can be checked by the p1, p2, p3 info in v$session_wait. Select* from v$session_eventwhere event = 'db file sequential read'orderbytime_waited; Selectsegment_name, partition_name, segment_type, tablespace_namefrom dba_extents a, v$session_wait bwhere b.p2 between a.block_id and (a.block_id + a.blocks - 1)and a.file_id = b.p1and b.event = 'db file sequential read'; Selecta.sid,a.serial#, a.username, a.osuser, b.sql_textfrom v$session a, v$sqltext bwhere a.sql_hash_value = b.hash_valueand a.sql_address = b.address and a.sidin(select sid from v$session_waitwhere event = 'db file sequential read')orderbya.sid,b.hash_value, b.piece;Note: Where P1 = file#, P2 = block#, P3 = blocks 9 (should be 1)Generally the entire database having some wait event doing IO for index scan usually. But if you see seconds in waiting greater then 0, you must tune index I/O.To reduce this wait event follow the below points:1. Tuning SQL statements to reduce unnecessary I/O request is the only guaranteed way to reduce "db file sequential read" wait time.2. Distribute the index in different file system to reduce the contention for I/O Tuning Physical devices, the data on different disk to reduce the I/O. 3. Use of Faster disk reduces the unnecessary I/O request. Increase db_block_buffers or larger buffer cache sometimes can help.DB File Scattered Read:This is indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads. However, scattered read will never read just one block (p3 is always >= 2). The DBA should be concerned with average I/O time and session that spend time on this event. The average multi block I/O should not exceeds 1/100 sec.SELECTa.average_wait "SEQ READ", b.average_wait "SCAT READ"FROM sys.v_$system_event a,sys.v_$system_eventbWHERE a.event = 'db file sequential read' AND b.event = 'db file scattered read';SEQ READ SCAT READ ---------- ---------- .74 1.6 Select* from v$session_eventwhere event = 'db file scattered read'orderbytime_waited; Selecta.sid,b.name,a.valuefrom v$sesstat a, v$statname bwhere a.statistic# = b.statistic#and a.value 0 and b.name='table scan blocks gotten'orderby3,1;If the average I/O wait time for the db file scattered read event is acceptable, but the event indicates waits in a certain session, then this is an application issue.In this case DBA needs to determine which objects is being read the most from P1 and P2 values, check the relevant SQL statement, explain plan for that SQL, Perform SQL tuning. The motive is to reduce both the logical and physical I/O calls: link to check SQL or Application Tuning.If an application that has been running fine for suddenly starts indicating the db file scattered read event then this could be an index issue. One or more indexes may have been dropped or become unusable.To determine which indexes have been dropped, the DBA can compare the development, test, and production databases. The ALTER TABLE MOVE command marks all indexes associated with the table as unusable. Certain partitioning operations can also cause indexes to be marked unusable. This includes adding a new partition or coalescing partitions in a hash-partitioned table, dropping a partition from a partitioned table or global partitioned index, modifying partition attributes, and merging, moving, splitting or truncating table partitions. A direct load operation that fails also leaves indexes in an unusable state. This can easily be fixed by rebuilding the indexes.

0 notes