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'
When creating manually a pfile from an spfile
create pfile ='mypfile.ora' from spfile;
I noticed that many parameters are missing in the generated pfile, there are just some 20 parameters instead of 250 ! where are the others ?
I noticed my DB is generating a lot of "small" .arc files and I am usure why. As you can see from the v$log query my log file size is set to 50MB. But yet BLOCKS*BLOCK_SIZE never adds up to 50MB.
Is there anything else I can look into to see how to make the .arc files larger?
SQL> select group#, thread#, bytes from v$log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 52428800
2 1 52428800
3 2 52428800
4 2 52428800
select blocks, block_size, blocks*block_size from v$archived_log where sequence# between 63876 and 72851 and thread# = 1
BLOCKS BLOCK_SIZE BLOCKS*BLOCK_SIZE
---------- ---------- -----------------
28 512 14336
28 512 14336
28 512 14336
55 512 28160
[code]...
One of the users received the error
ora-01555: rollback segment too small
I have read about the error and saw that it is caused by transactions made upon same data, filling to maximum one of the UNDOTBS rollback segments.It happens only once in a while, each time on a different Rollback Segment.
I've read that i should do a few things to enlarge those segments, such as bring a 1. segment offline, 2. drop it and then 3. create it with a bigger size & possibly a bigger extent setting
i've also read that those actions aren't relevant if you have the parameter UNDO_MANAGEMENT set on AUTO, which is actually the case.is that why when i execute ALTER ROLLBACK SEGMENT RB_SEG_NAME_11$ OFFLINE;
1. How do i solve my issue with the Rollback segment being too small to contain the snapshot with the requested SCN?
2. what does the parameter UNDO_MANAGEMENT mean? should i change it, in order to adjust my Rollback Segments attributes, to prevent my error of re-occurring?
what's the difference between having BIG datafile (32GB) vs many small datafiles (4GB) replacing a Big one.
View 3 Replies View RelatedWhat would be the steps involved if I will change the IP address of my Oracle GoldenGate target database?
Below are the steps that I thought of:
1. OGG Target: Stop the running change capture replicat and GG manager
2. OGG Target: Implement change process of IP address up to database startup
3. OGG Source: Stop the running data pump extract
4. OGG Source: Update data pump extract parameter RMTHOST pointing to the new IP address of the target
5. OGG Source: Start the data pump extract
6. OGG Target: start GG manager and the change capture replicat
Will it work or I missed a critical step?
OGG Source (PROD): Oracle 10g; Oracle GoldenGate 11.1.1; OS HPUX
OGG Target (Datawarehouse): Oracle 11g; Oracle GoldenGate 11.2.1.0.1; OS Linux
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 ?
I've installed OEM grid control 11.1.0.1.0 on server "A" which has an Oracle Database version 11.2.0.1.The OS on Server "A" is OEL 5.5. The grid control installed on this server is working fine and I've deployed agents on remote hosts which I am able to monitor them successfully using this grid control.
Now I would like to add the server ("A") on which the grid control is installed to be monitored by the grid control. is the same way as we deploy agents on remote hosts, but here the agent is already installed at the time of installing Grid Control.
O.S Version HP-UX B.11.31 U ia64
Oracle DB Version 11.2.0.3.0
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Storage : ASM Diskgroups
-------------------------------------------------------------------------------------------------------------------------------------
I am new to Oracle, like to get clarified on below;
1. From where can we run the RMAN Clone script?
Source DB : PRDDB HOST: PRDHOST
Destination DB: DUPDB HOST: DEVHOST
Are there any constraints on running the RMAN Script from the Source DB Host - PRDHOST?
If I am running it from PRDHOST, do I have to have the initdupdb.ora available on PRDHOST?
What are advantages and disadvantages on running the script on Source & Destination Host?
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
View 3 Replies View RelatedWe are migrating from a 9i db to 11g and we've been testing our apps on a similar (but not exact) machine as our production box.
Normally when we take a full export of the production data (on 9i) and import it into another 9i DB, the tables and indexes are created with the initial size large enough to hold the entire table. We also do our export with the compress extents param set to 'Y'.
However, we've noticed that when we import our data into the 11g DB, that tables are being created with multiple extents...sometimes up to 10 or 15. This seems to happen even with tables that don't even have extents on db that the export was taken from.
There ARE some differences in our 11g DB that i imagine might be the culprit, i've just been unable to narrow one of them down.
the differences i know of are:
a) the target DB has locally managed tablespaces while the source 9i DB had dictionary managed tablespaces
b) the block size is larger on the target 11g DB. 8192 vs 2048
c) the nchar character set on the source DB is AL16UTF16 and the target is UTF8 (we actually only have an nchar column in one of our tables...and also, the UTF8 setting was actually a mistake that we're correcting this weekend with a fresh DB and fresh import)
What would cause the import to produce all these extra tablespaces?
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 have a 10g database and on a specific day of everymonth, a query appears to cause a performance issue. As i have analyzed, there are two small tables of 11MB in size and the query goes for higher number of executions/gets/reads/elapsed time etc. So as the tables are very small in size, i am planning to pin both of these tables in SGA buffer pool keep memory. I need few clarifications...
*These tables are Dynamic in nature and the row data tends to change everyday. If i pin these objects in Keep buffer pool, will this dynamic changes to the table made also be reflected in the pinned buffer pool?
*Will this have any adverse effect?
*My SGA_TARGET is set equal to SGA_MAX_SIZE which is 1532MB. Do i need to change any memory setting before pinning these objects?
*On every restart of the database, do i need to again execute the pin commands for these objects.
I have a cluster setup, and i want to retrieve host name for my small app.Is there any query which gives me the HOSTNAME where oracle is running??
View 14 Replies View RelatedI did some google searches about large number of extents and ASSM. I see bits and pieces on the web. This is something I need to look at while testing an application. Not looking to go into 'why' I would use smaller extents, I just want to make sure I have what I need to look for during testing..Issues with massive numbers of extents:
1. DBA_EXTENTS query is really slow.
2. issues truncating tables (due to having to read lots of extents)
3. issues splitting maxvalue partitions and with dropping partitions.
4. if I stay away from ASSM, would this reduce these issues? Are there any other performance issues or other issues I need to know about to check when I do tests?
Any issues with query or insert wait time? The tables that would get smaller events would have thousands of partitions/sub-partitions . Most of these sub-partitions will be rather smaller.I just want to test for a variety of different cases. The 'why' will come out during testing.
I read the Website URL....and searching now for an alternative way for a small Oracle DB around 120 - 250 GB Data.I cant find any DRAM SSD that seems to suite the needs of such a small amount of data to a valuable price.
The only Hdd i found is URL... but im not sure if thats the right thing and if its possible to still buy such a device.where do we get such a device in Europe(swiss) and what would be the price for such a device?
Do i need to make a special setup within oracle (11g R2) to get the maximum out of such a DRAM SSD?
I'm trying to create a table with a select statement. I want to populate this new table with the aggregated value from a VIEW. Following is the code used for creating the VIEW,
create or replace view FINAL_WEB_LOG
as
select SESSION_ID,
SESSION_DT,
C_IP,
CS_USER_AGENT,
tab_to_string(CAST(COLLECT(web_link) AS t_varchar2_tab)) WEBLINKS
from web_views_tab
group by C_IP, CS_USER_AGENT, SESSION_DT;
I want to create a table with WEBLINKS and SESSION_ID which is a sequence from another table.
CREATE TABLE FINAL_WEB
AS
SELECT weblinks
FROM final_web_log
UNION
SELECT session_id
FROM WEB_VIEWS_TAB;
This now gives me the following error,
SQL Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
This has to do with the field, Weblinks, it does have longer values.
I have a string:
"This is just for testing 123.
This is just for testing 45654.
This is just for testing 5567876.
This is just for testing 53456547.
This is just for testing 123423.
This is just for testing 98090.
This is just for testing 099473.
This is just for testing.
This is just for testing.
This is just for testing 3.
This is just for testing 34983245983.
This is just for testing 6432."
I need to divide this sting after every 100 characters, as the length of column to insert is 100. And i do not want to modify the column as it has great impact. I need to divide the string, such that it should be less then 100 characters also the string is not cut in between.
like:
first string: "This is just for testing 123.
This is just for testing 45654.
This is just for testing 5567876."
then 2nd string: "This is just for testing 53456547.
This is just for testing 123423.
This is just for testing 98090."
then 3rd string: "This is just for testing 099473.
This is just for testing.
This is just for testing.
This is just for testing 3."
While taking backup of schema using Expdp, I got the following error on one of the tables in the schema due to which export for this table could not be done.
Re: ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2$" too small.
Undo retention time is 14400 sec and Undo Tablespace is Auto Extensible. Table to be exported contains 100 crore rows.
I need to export 20 small records from one table in Oracle 9i into an xml file. Each record has 4 fields. what the command would be in sql plus.
View 1 Replies View RelatedI have researched this problem and checked my variable sizes over and over again. I have tested the procedure within the Oracle Express environment and it works fine; HOWEVER, when the procedure is called from my C# app it produces the ORA-06502 error.
The stored procedure signature looks like this...
Original - SQL Code
create or replace save_new_project (p_custorgid in number,
p_title in varchar2,
p_AOIName in varchar2,
p_description in varchar2,
p_receiveddate in date,
p_deadlinedate in date,
p_startdate in date,
[code]....
The OracleParameter in my C# app for the last out param is defined as such...
cmd.Parameters.Add(new OracleParameter("p_statustypedescrip", OracleDbType.Varchar2, 30, ParameterDirection.Output));
As I said at the beginning of this post, the procedure works fine in the Oracle environment. So why is it not working by simply calling it from C#? I've tried changing the OracleDbType to CLOB which eliminates the error but it returns a bizarre result. It returns this string, "Oracle.DataAccess.Types.OracleClob".
Since CLOB doesn't really work either I switch back to Varchar2 and specify a size of 5000 (in the database the field I am querying is defined as Varchar(30)). I still get the ORA-06502 error.
I am clueless as to what the problem is. It should work and it does if I run a series of SQL statements in an Oracle SQL Command window. The test that works fine looks like this...
Original - SQL Code
declare
v_projid projects.projectid%type;
v_statustypedescrip projectstatustypes.type%type; /* this is a varchar(30) */
begin
save_new_project(2, 'Some input text goes here', 'More input text', 'And more again','26-APR-2007','26-APR-2007','26-APR-2007','26-APR-2007','users name as inpujt text
[code]....
But calling save_new_project from C# throws ORA-06502. It identifies line 40 of my stored procedure. This is line 40...
Original - SQL Code
p_statustypedescrip := v_statustypedescrip;
p_statustypedescrip := v_statustypedescrip;
when I want to create a table.When I run my procedure I received :
PL/SQL: numeric or value error: character string buffer too small
create or replace PROCEDURE p_create_tmp_tables (p_result OUT NUMBER ) IS
string_sql varchar2(1000);
result NUMBER;
BEGIN
string_sql := 'CREATE TABLE TMP_CATEGORIES (CODE_CATEGORY NUMBER(6,0), NAME_CATEGORY VARCHAR2(25 BYTE))';
execute immediate string_sql;
[code]...
I want to pass a damn long query(which includes a lot of column names, tables, joins, union, where clauses etc. and whose length is more than 120000) in a Ref cursor (that's length is more than 32767). Query is stored in a LONG type variable V_QRY in stored procedure, and I am opening that ref cursor like below-
OPEN P_RPT_TEST FOR V_QRY;
at run time its giveing string buffer too small error.
I am trying to execute this
begin
-- Initialize owa with an empty array, just so we can use htp.
owa.init_cgi_env( param_val=>owa.cgi_var_name );
-- Add a header to avoid dealing with header-related code in htp.
htp.prn('Content-Type:text/plain');
htp.print('');
[code]....
The error I am getting is
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.HTP", line 1550
ORA-06512: at line 32
what i feel is that oracle Database UTF8 or AL32UTF8 characterset does not support htp package properly. When i run the code on another characterset, it runs errorfree except in UTF8 or AL32UTF8.
I have a small application with 5 forms and a small database. Is it possible to convert it into an exe file?
View 4 Replies View RelatedI have several large tables in the live system! Those table are store historical information.
current situation:
Now, A table record was 129 million rows.
Every month added 4.5M records to this table.
This table data size 17GB and index size 28GB.
I have only 30 GB available free space on disk!
How to split this table to small pieces (partition table by month)?
What is the best approach?
I would like to do partitioning on this table month by month.
Actually I am getting the following error while running my script.
Error:(Error): ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 9
Seems to be getting eror in the following line but not sure why its coming."select adary.dilution_strategy_seq.NEXTVAL into :dil_strat_seq_new from dual;"
Code snippet:
---------------------------------------------------------------
declare
dil_strat_seq_new NUMBER :=0;
disc_conc_seq_new NUMBER :=0;
disc_fold_seq_new NUMBER :=0;
begin
[code]...
i am getting above error while doing
ORA-06502 numeric or value error: character string buffer too small
ORA-06512 itw_item_add_ch at line 17
CREATE OR REPLACE procedure itw_item_add_ch (header_id1 number, folio out varchar2,tariff out varchar2) as
cursor item_add(header_id1 number) is
SELECT
DISTINCT c.attribute4 item_tariff_no,
c.attribute5 item_folio
FROM OE_ORDER_LINES_ALL b,
[code].....
Following are my declarations and query inside my procedure.i am getting "ORA-06502: PL/SQL: numeric or value error: character string buffer too smal" error when i try to execute the procedure
code :
request_owner request.SCC_USER_ID%TYPE := NULL;
receipt_location request.RECEIPT_LOCATION_LKP_ID%TYPE := NULL;
receipt_type request.RECEIPT_TYPE_LKP_ID%TYPE := NULL;
item_id request.ITEM_ID%TYPE := NULL;
[code]...
I am learning pl/sql and this is procedure i had created which got created successfully.then i created two variables name v_name, v_loc with command.
variable v_name varchar2; etc.
and executed procedure like
execute dept_proc2(10, :v_name, :v_loc);
and i am getting the error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
1 create or replace procedure dept_proc2
2 (v_dno in number, v_name out varchar2, v_loc out varchar2)
3 is
4 cursor dept_cur is select dname, loc from dept
5 where deptno=v_dno;
[code]...