i am running 11.2.0.1 on HP-UX PA-RISC (64-bit). My Memory max target is defined as 34 GB and memory target is 32 GB on the database. The host has 256 GB physical memory. i see memory utlization is almost 90% used when seen from Grid Control on the host and wondering, what's going on? when i see into more details, i see the following 3 HP Processes consuming around 3.6 GB memory and rest very small is consumed by Oracle processes. Who's using the rest of the memory?
I have a confusion with MEMORY_TARGET and MEMORY_MAX_TARGET parameter. if i set SGA_TARGET, SGA_MAX_SIZE along with MEMORY_TARGET and MEMORY_MAX_TARGET then how oracle will manage the memory? Because as per my understanding if we set MEM
We are using the 11.1.0.7 database, we implemented the Memory_Max_Target and Memory_target in the database.Here is the value of the memory parameters:
SQL> show parameter memory_
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ hi_shared_memory_address integer 0 memory_max_target big integer 3G memory_target big integer 2G shared_memory_address integer 0
We want to increase the value of the Memory_target=3G, means, I want to increase the value of the memory_target upto Memory_max_target by using below command:alter system set MEMORY_TARGET=3G scope=both SID='OLTP1'; but I am getting below error:
ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00846: could not shrink MEMORY_TARGET to specified value
I tried to give the memory_target value less than the memory_max_target value like:alter system set MEMORY_TARGET=2900M scope=both SID='OLTP1'; but get the same error:
ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00846: could not shrink MEMORY_TARGET to specified value
I am aware that from 11g, memory_target is sufficient for memeory management between SGA and PGA.
what happens if MEMORY_TARGET set to non-zero and SGA_TARGET set to zero values in a 11g database? Does it enable automatic memory management within the SGA?
We regularly hit by ORA-4031 errors. Also, memory_target advisory (v$memory_target_advice) does not show any advisory information.
for eg: memory_max_target = 500m memory_target = 500m
SQL> select name,decode(unit,'bytes',value/1024/1024,value) as mb from v$pgastat; NAME MB---------------------------------------------------------------- ----------aggregate PGA target parameter 25600aggregate PGA auto target 2724.14648global memory bound 1024total PGA inuse 22601.7333total PGA allocated 26653.6230maximum PGA allocated
[code]....
I understand I have soft limit( aggregate PGA target parameter) which was overlimited (maximum PGA allocated = 35374.4638) hence we have over allocation count>0.Extra bytes read/written=13GB,hence we have excessive 13Gb that we had to flush on disk(excessive I/O operations) cause of limitation in 1024MB(global memory bound)(it's not enough to join or to sort something so we must do onepassor multipass) ,which defines the size of single operation of sort or join(so does it mean that it's some kind of sort_area_size and hash_area_size for automatic workarea_size_policy? and in this case what about _smm_max_size?)aggregate PGA auto target - is the amount of space(total) that Oracle can give for work areas running in automatic mode.
So I can't understand ratio between global memory bound and aggregate PGA auto target - why does the aggregate PGA auto target such tiny?(relatively process count)?Is the global memory bound static for particular aggregate PGA target parameter?
I can change it only by redefining aggregate PGA target parameter?What would be with aggregate PGA auto target if I started 10 sort operation and each of them takes about 1Gb of memory.How huge it would be? 10Gb?
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 3G sga_target big integer 2G
from what I read I beleive this will initially grab 2GB of memory on startup and will grab up to to 3GB of memory total for the SGA. The "total" memory can be allocated to different peices of the SGA when needed but will never exceed 3GB. Is this correct or would these settings infringe on any available memory on a system that is already tight on memory?
Secondly, what happens if both these values are set to the same value?
Can i know the internal process of initialization of DB into memory in timesten , when a new connection is establishing? Will timesten create tables and indexes in RAM when first connection is established if the RAM policy is default?
want to know the internal functional flow of timesten when any command is fired against it.
While i am trying to execute this proc,i am facing source does not have a runnable target issue.
CREATE OR REPLACE PROCEDURE GET_CHILDS_SUB ( nid IN VARCHAR2 ) AS n varchar2; CURSOR cur is SELECT node_id FROM test_tbl WHERE parent_id = nid; BEGIN [code]....
We are using oracle 10.2.0.3.0 version on HP-UX B.11.23 U ia64.
We are facing a lot of delay in TNS ping reply. It is taking 8000+ msec.
$ tnsping XXXXXX 20
TNS Ping Utility for HPUX: Version 10.2.0.3.0 - Production on 27-FEB-2013 12:37:15
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files: $ORACLE_HOME/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxx)(PORT = 1531))) (CONNECT_DATA = (SERVICE_NAME = xxxxxxx))) OK (1250 msec) OK (1150 msec) OK (860 msec) OK (860 msec) OK (790 msec) OK (810 msec) OK (790 msec) OK (570 msec) OK (650 msec) OK (630 msec) OK (790 msec) OK (1240 msec) OK (1090 msec) OK (1030 msec) OK (870 msec) OK (690 msec) OK (740 msec) OK (650 msec) OK (490 msec) OK (560 msec) $
In SQL server the cursor is defined to retrieve row by row and process it. We can fetch records as FIRST, LAST, NEXT, PREVIOUS and SCROLL like that. Can we do the above in oracle. Else what is the use of cursor in Oracle. I know that it is a private area in which the records are processed.
I dont know where was the mistake, getting column ambiguously defined error
CREATE OR REPLACE PROCEDURE FLXUSER.testgg AS BEGIN /*---------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------*/ BEGIN
I want clear the concept of auxiliary database. What's the use/advantages of it. Is it the another oracle instance? What is the difference between Auxiliary Database and Target Database?
I would like to have some inputs on RMAN backup that we are planning to perform for the database that has to be now discarded; and which may have have to be restored in distant future.
Current Env-- DB Ver: 10gR2 OS: Solaris Backup mode: RMAN WITHOUT CATALOG(i.e. using controlfile for storing rman info)
From what I know(I might be wrong), that for duplicating the 10gR2 DB to another machine we have to have the target(original) database running and connected. In my case we need to take the rman backup and then use the target db for fresh installation and hence the db will be deleted.
How can i increase the sga_target in this case.Im unable to create a pfile.
SQL> startup ORA-01078: failure in processing system parameters ORA-00821: Specified value of sga_target 2048M is too small, needs to be at least 4112M SQL> startup nomount
[code]...
The $ORACLE_HOME/dbs location has a spfile.
more initQR01MRA1.ora SPFILE='+DB_DATA/QR01MRA/spfile'
There are about 10.000 rows where ID1, ID2 and/or AMOUNT contains characters. These rows I don't want to insert as the columns in the target table are INTEGER. I simply want to discard these.
The capture process and the propogation rule and scn instantiation on the source were executed fine withut issue, I am having problem in the apply process.
I am trying to create & use a new data type but keep getting a ora-0902 invalid data type error running on 10g express. The create type and select statements execute fine and select confirms the ssn_t is a type. The create table statement fails with the invalid data type error.
Here is an example.
CREATE TYPE ssn_t AS OBJECT ssn_type CHAR(11)); SELECT object_name, object_type FROM user_objects WHERE object_type = 'TYPE'; CREATE TABLE Z (A CHAR(4), B SSN_T);
Can we override the pre defined functions in oracle. I am able to do this in my schema. But if DB server contains more than one schema do I need to write the same procedure in all schemes.
we can call the same procedure with schema name from different schemes also . But is there any way to over ride the actual function provided by oracle (just like over riding the equals method of object class in java).
create or replace function to_number(var varchar2) return number is no number := var; begin return 99999; end;
i insall grid contrlol on r12(12.1.1) and it work very fine detect all target r12 database plus oracle application server all target...but my problem is when i connect it to another system r12(12.1.3) by installing agent silent installation...it detect oracle r12 database only but not detect any target related to oracle application server such as oracle ebusiness suit,http server or any target related to application server. os linux 5.4. acutally i want to configure plug in 4.0...i follow metalink doc 1224313.1 refer me any doc any blog or any thig by which i can detect oracle application target in grid control.
We have a requirement to archive and purge the tables dynamically based on the control table input. For that we have to design a control table to gather the necessary information and passed to generate the queries.
I have designed the table as below.But in this case I am not able to handle the parent and child relation ship.
Suppose one table needs to be archived and purged and that table is parent table and it is having 2 child tables, so first required data will be inserted into target table and delete from source parent and child tables. so before deleting from parent we have to delete data from all 2 child tables.
Suppose one table needs to be purged and that table is parent table and it is having 5 child tables, so before deleting from parent we have to delete data from all 5 child tables.
To handle this scenario how can I design my control table.
For archive and purge the query like this. INSERT INTO towner_name.ttable_name (SELECT * FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30)); DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);
for purge the quey is like this. DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);
This is my control table and I have 300 tables list to archive and purge.
CID SOWNER_NAME STABLE_NAME TOWNER_NAME TTABLE_NAME CONDITION_COLUMN PERIOD UNIT TYPE 1 wedb_au OFFER_HEADER wedb_au OFFER_HEADER LAST_DATE 30 D A 1 wedb_sa OFFER_CUSTOMER wedb_sa OFFER_CUSTOMER LAST_DATE 60 D A 1 wedb_au OFFER_SERVICE LAST_DATE 1 Y P 1 wedb_us OFFER_CUSTOMER LAST_DATE 90 D P 1 wedb_cn OFFER_CARDS UPDATE_DT 2 Y P 2 wedb_au ORDER_HEAD wedb_au ORDER_HEAD LAST_DATE 120 D A 2 wedb_us ORDER_CUSTOMER wedb_us ORDER_CUSTOMER LAST_DATE 150 D A 2 wedb_sa ORDER_HEAD wedb_sa ORDER_HEAD CREATION_DT 1 Y A 3 wedb_us DELIVERY_HEAD wedb_us DELIVERY_HEAD UPDATE_DT 50 D A 3 wedb_au DELIVERY_CARDS wedb_au DELIVERY_CARDS UPDATE_DT 200 D A 3 wedb_au DELIVERY_SERVICE wedb_au DELIVERY_SERVICE LAST_DT 100 D A
WHERE TYPE=P means insert and delete TYPE=A means only delete
wedb_au.OFFER_HEADER is Parent Table. child tables for wedb_au.OFFER_HEADER are wedb_au.OFFER_SERVICE,wedb_au.OFFER_BODY,wedb_au.OFFER_EMAIL,OFFER_TAX. wedb_au.OFFER_SERVICE is child table and parent for this table is wedb_au.OFFER_HEADER wedb_sa.OFFER_CUSTOMER Stand alone table no relationship wedb_us.OFFER_CUSTOMER Stand alone table no relationship [code].......
If I have specify the SGA_TARGET, do I still need to specify the amount of memory for SHARED_POOL_SIZE? I thought once the SGA_TARGET, auto memory management is in place and SHARED_POOL is not neccessary.
I am trying to insert rec into target table if those rec are not existing and trying to update those rec if they already exists from three source tables.I had seen in posts that merge cannot be used with cursor.
SQL> create or replace 2 PACKAGE sis_l_cpl_sis_reb_pgm_hist_pkg 3 IS 4 /******************************************************************** ****************** 5 PACKAGE: sis_load_cpl_sis_reb_pgm_hist 6 PURPOSE: Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_p h_dtl,cmply_sis_sls_dtl, 7 cmply_sis_excl_dtl(intial load) 8 ********************************************************************* ******************/ [code].......
Package created.
SQL> create or replace 2 PACKAGE BODY sis_l_cpl_sis_reb_pgm_hist_pkg 3 IS 4 /********************************************************************** ****************** 5 PACKAGE: sis_l_cpl_sis_reb_pgm_hist_pkg 6 PURPOSE: Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_pur h_dtl,cmply_sis_sls_dtl, 7 cmply_sis_excl_dtl(intial load) [code].......
Warning: Package Body created with compilation errors.
SQL> sho err Errors for PACKAGE BODY SIS_L_CPL_SIS_REB_PGM_HIST_PKG:
Source and Target db version : 10.2.0.4.0 Source Os :Cent OS 5.4 and Target OS:Sun OS 5.10
We are loading data from source DB to target DB using dblink.Source db is having 15.4 crore records of number and varchar2 data types.
Using the logic as follows. insert into table1 as select * from table1@dblinkname[/email];
DB link is working.If I give "select * from scott.REPORT@DBLINK[/email]" in target db alone i could retrieve records.
Actual query:
Create Or replace procedure test_abcd as begin Insert into test select * from scott.REPORT@DBLINK[/email] dbms_output.put_line('Hello world'); end;
When i give the above query in sqlplus ,it is hanging. When i see the wait events i could find
"Wait Event: SQL*Net more data from dblink"
How to get the above things working.? when checked with network team they says there is no issue in the network. Do we need to modify any database/network level parameter settings.there is no firewall between source and target db.
We have two databases dev and prod in one server. I would like to connect to target database prod without a recovery catalog. Issued the below command and getting the following error message.
rman target sys/sys_password nocatalog.
Error we are getting is : Recovery Manager: Release 11.2.0.2.0 - Production on Fri Nov 18 11:31:59 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
on 11g R2 on Win 2008, I want to duplicate my target DB which is on a a remote server using RMAN backups. The destination is on local server. I will run RMAN on local server.
In initnewdb.ora, I should add :
# Convert file names to allow for different directory structure if necessary. #DB_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/DB11G/,/u01/app/oracle/oradata/NEWSID/) #LOG_FILE_NAME_CONVERT=(/u01/app/oracle/oradata/DB11G/,/u02/app/oracle/oradata/NEWSID/)
my questions :
1-In my case would it be :
# Convert file names to allow for different directory structure if necessary. #DB_FILE_NAME_CONVERT=(\remoteserver:/u01/app/oracle/oradata/DB11G/,/u01/app/oracle/oradata/NEWSID/) #LOG_FILE_NAME_CONVERT=(\remoteserver:/u01/app/oracle/oradata/DB11G/,/u02/app/oracle/oradata/NEWSID/)
2- should we keep the convert parameters in init.ora file after duplication for always ?
