A lot of sql developers are wondering if:
• decode can evaluate a boolean expression
• there is any IIF function in Oracle SQL
Unfortunately, the answer is NO for both questions.
There is no SQL boolean type in Oracle. Just PL SQL.
There is no IIF function, either.
The solution is:
select Case When 1=2 Then 'YES' else 'NO' End from dual;
You can even select multiple columns without brackets.
select 1, Case When 1=1 Then 'YES' else 'NO' End from dual;
and, of course, you can evaluate table columns within.
Enjoy!
luni, 22 decembrie 2014
luni, 15 decembrie 2014
How to delete all the records containing a given value on a given named column from all the tables continaing that named column, in the schema.
Let's say, we have to delete all the records containing a given value on a given named column from all the tables continaing that named column, in the schema. In my example, the column is THE_NAME_OF_THE_COL and the value is 40322.
This can be a way to do it:
first, create the tables:
/*all the tables containing the wanted column:*/
create table tabele_codmis as (select table_name from user_tab_columns a where (a.column_name='THE_NAME_OF_THE COL' ) and (select count(*) from user_tables b where a.table_name=b.table_name)>0 );
/*all the table containing the wanted column and having at least one record containing a given value on that column (will be emptied and filled within the procedure: */
create table tabele_codmis_ex as select * from tabele_codmis;
DECLARE
cursor r1 is select * from tabele_codmis;
cursor r2 is select * from tabele_codmis_ex;
cursor r4 is select * from constr_mis_ex;
cate number;
miscod number;
sql2 varchar2(1000);
numet varchar2(1000);
BEGIN
/* empty the table continaing the names of tables containing the given column*/
execute immediate 'delete from tabele_codmis';
/* fill the above table*/
execute immediate 'insert into tabele_codmis select table_name from user_tab_columns a where (a.column_name=''THE_NAME_OF_THE_COL'' ) and (select count(*) from user_tables b where a.table_name=b.table_name)>0';
/* empty the table containing the given value*/
execute immediate 'delete from tabele_codmis_ex';
/* fill the table above */
FOR c1 IN r1
loop
cate:=0;
miscod:=40322; -- this was my value
execute immediate 'select count(*) from '||c1.table_name||' where THE_NAME_OF_THE_COL =:1' into cate using miscod;
if cate>0 then
sql2:='insert into tabele_codmis_ex values (:1)';
select c1.table_name into numet from dual;
execute immediate sql2 using numet;
end if; END LOOP;
/* drop the table containing the enabled constraints contained by the wanted tables*/
dbms_utility.exec_ddl_statement('drop table constr_mis_ex');
/* create and fill table containing the enabled constraints contained by the wanted tables*/
dbms_utility.exec_ddl_statement('create table constr_mis_ex as select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED from user_constraints where constraint_type=''R'' and status=''ENABLED'' and table_name in (select table_name from tabele_codmis_ex)');
/* we are interested only in referential constraints*/
/* disable all the constraints from above */
for c4 in r4
loop
dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' read only'); /* this is not mandatory, but you could find it useful, otherwise you can experience some locking erros */
dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' disable novalidate constraint ' || c4.constraint_name);
end loop;
/* delete all the records containing the given value */
FOR c2 IN r2
loop execute immediate 'delete from '||c2.table_name||' where THE_NAME_OF_THE_COL =:1' using miscod;
END LOOP;
/* enable back the constraints back */
for c4 in r4
loop
dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' enable novalidate constraint ' || c4.constraint_name); dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' read write');
end loop;
end;
/
Now all the unwanted records should have been erased. Enjoy!
This can be a way to do it:
first, create the tables:
/*all the tables containing the wanted column:*/
create table tabele_codmis as (select table_name from user_tab_columns a where (a.column_name='THE_NAME_OF_THE COL' ) and (select count(*) from user_tables b where a.table_name=b.table_name)>0 );
/*all the table containing the wanted column and having at least one record containing a given value on that column (will be emptied and filled within the procedure: */
create table tabele_codmis_ex as select * from tabele_codmis;
DECLARE
cursor r1 is select * from tabele_codmis;
cursor r2 is select * from tabele_codmis_ex;
cursor r4 is select * from constr_mis_ex;
cate number;
miscod number;
sql2 varchar2(1000);
numet varchar2(1000);
BEGIN
/* empty the table continaing the names of tables containing the given column*/
execute immediate 'delete from tabele_codmis';
/* fill the above table*/
execute immediate 'insert into tabele_codmis select table_name from user_tab_columns a where (a.column_name=''THE_NAME_OF_THE_COL'' ) and (select count(*) from user_tables b where a.table_name=b.table_name)>0';
/* empty the table containing the given value*/
execute immediate 'delete from tabele_codmis_ex';
/* fill the table above */
FOR c1 IN r1
loop
cate:=0;
miscod:=40322; -- this was my value
execute immediate 'select count(*) from '||c1.table_name||' where THE_NAME_OF_THE_COL =:1' into cate using miscod;
if cate>0 then
sql2:='insert into tabele_codmis_ex values (:1)';
select c1.table_name into numet from dual;
execute immediate sql2 using numet;
end if; END LOOP;
/* drop the table containing the enabled constraints contained by the wanted tables*/
dbms_utility.exec_ddl_statement('drop table constr_mis_ex');
/* create and fill table containing the enabled constraints contained by the wanted tables*/
dbms_utility.exec_ddl_statement('create table constr_mis_ex as select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED from user_constraints where constraint_type=''R'' and status=''ENABLED'' and table_name in (select table_name from tabele_codmis_ex)');
/* we are interested only in referential constraints*/
/* disable all the constraints from above */
for c4 in r4
loop
dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' read only'); /* this is not mandatory, but you could find it useful, otherwise you can experience some locking erros */
dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' disable novalidate constraint ' || c4.constraint_name);
end loop;
/* delete all the records containing the given value */
FOR c2 IN r2
loop execute immediate 'delete from '||c2.table_name||' where THE_NAME_OF_THE_COL =:1' using miscod;
END LOOP;
/* enable back the constraints back */
for c4 in r4
loop
dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' enable novalidate constraint ' || c4.constraint_name); dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' read write');
end loop;
end;
/
Now all the unwanted records should have been erased. Enjoy!
marți, 9 decembrie 2014
How to empty an Oracle database schema
Seems to be easy, but there are some itchy problems.
First, disable all the constraints, but mind the disabled one, don't enable them aftermath. Same for the triggers.
In order to do this, you have to keep them recorded somehow.
drop table my_constraints; --if exists
create table my_constraints as select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED from user_constraints;
--don't use the "select * from" syntax, and you will avoid a "long
--datatype" error
--maybe you don't need all those fields, but it doesn't cost so
--much to store them
--if you want to empty the recycle bin (three rows bellow you can see why):
purge recyclebin;
DECLARE
cursor r1 is select * from my_constraints where status='ENABLED' and upper(substr(constraint_name,1,3))<>'BIN'; --recycle bin objects
cursor r2 is select * from user_tables;
BEGIN
FOR c1 IN r1
loop
for c2 in r2
loop
if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN
dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable novalidate constraint ' || c1.constraint_name);
end if;
end loop;
END LOOP;
end;
/
--now, the triggers
drop table my_triggers;
create table my_triggers as select TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, TABLE_NAME, COLUMN_NAME, REFERENCING_NAMES, WHEN_CLAUSE, STATUS, DESCRIPTION, ACTION_TYPE,
CROSSEDITION, BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, AFTER_STATEMENT, INSTEAD_OF_ROW, FIRE_ONCE, APPLY_SERVER_ONLY from user_triggers;
DECLARE
cursor r1 is select * from my_triggers where status='ENABLED' and upper(substr(trigger_name,1,3))<>'BIN';
--remember? recycle bin objects
BEGIN
FOR c1 IN r1
loop
dbms_utility.exec_ddl_statement('alter trigger ' || c1.trigger_name || ' disable ' );
END LOOP;
end;
/
--create a table to hold the name of the tables you want to empty them.
drop table empty_tables; --if exists
-- be carefull, do not empty the my_constraints , my_triggers, empty_tables tables
create table empty_tables as select table_name from user_tables where lower(table_name) not in (.. some list of names..., 'my_constraints' , 'my_triggers' , 'empty_tables');
-- if you want to use other way than "truncate", feel free to do it, but mind the size of the archivelogs and the load of the system resources
DECLARE
cursor r1 is select * from empty_tables;
BEGIN
FOR c1 IN r1
loop
dbms_utility.exec_ddl_statement('truncate table ' || c1.table_name || ' drop storage ' );
END LOOP;end;
/
-- now, reinforce the constraints and the triggers:
-- first, the primary constraints, because, otherwise, you will receive an error when you try to
-- enable a FK constraint, referring a non primary key column.
DECLARE
cursor r1 is select * from user_tables where table_name in (select table_name from my_constraints where constraint_type='P');
BEGIN
FOR c1 IN r1
loop
dbms_utility.exec_ddl_statement('alter table ' || c1.table_name || ' enable primary key ' );
END LOOP;
end;
/
-- now, the unique constraints, same reason from above
declare
cursor r1 is select * from my_constraints where constraint_type='U' and constraint_name not like 'SYS%' order by constraint_name ;
-- i put there the "sys" exception because i received some errors, you can imagine which type of
BEGIN
FOR c1 IN r1
loop dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable novalidate constraint ' || c1.constraint_name);
END LOOP;
end;
/
-- now, the rest:
declare
cursor r1 is select * from my_constraints where constraint_name not like 'SYS%' order by constraint_name ;
-- i put there the "sys" exception because i received some errors, you can imagine which type of
BEGIN
FOR c1 IN r1
loop dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable novalidate constraint ' || c1.constraint_name);
END LOOP;
end;
/
-- and, eventually, the triggers:
DECLARE
cursor r1 is select * from my_triggers where status='ENABLED' and upper(substr(trigger_name,1,3))<>'BIN';
BEGIN
FOR c1 IN r1
loop
dbms_utility.exec_ddl_statement('alter trigger ' || c1.trigger_name || ' enable ' );
END LOOP;
end;
/
And that's all! Your schema is empty, except the tables you mentioned when you populate the empty_tables table.
And now drop the unnecessary tables:
drop table my_constraints;
drop table my_triggers;
drop table empty_tables;
First, disable all the constraints, but mind the disabled one, don't enable them aftermath. Same for the triggers.
In order to do this, you have to keep them recorded somehow.
drop table my_constraints; --if exists
create table my_constraints as select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED from user_constraints;
--don't use the "select * from" syntax, and you will avoid a "long
--datatype" error
--maybe you don't need all those fields, but it doesn't cost so
--much to store them
--if you want to empty the recycle bin (three rows bellow you can see why):
purge recyclebin;
DECLARE
cursor r1 is select * from my_constraints where status='ENABLED' and upper(substr(constraint_name,1,3))<>'BIN'; --recycle bin objects
cursor r2 is select * from user_tables;
BEGIN
FOR c1 IN r1
loop
for c2 in r2
loop
if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN
dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable novalidate constraint ' || c1.constraint_name);
end if;
end loop;
END LOOP;
end;
/
--now, the triggers
drop table my_triggers;
create table my_triggers as select TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, TABLE_NAME, COLUMN_NAME, REFERENCING_NAMES, WHEN_CLAUSE, STATUS, DESCRIPTION, ACTION_TYPE,
CROSSEDITION, BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, AFTER_STATEMENT, INSTEAD_OF_ROW, FIRE_ONCE, APPLY_SERVER_ONLY from user_triggers;
DECLARE
cursor r1 is select * from my_triggers where status='ENABLED' and upper(substr(trigger_name,1,3))<>'BIN';
--remember? recycle bin objects
BEGIN
FOR c1 IN r1
loop
dbms_utility.exec_ddl_statement('alter trigger ' || c1.trigger_name || ' disable ' );
END LOOP;
end;
/
--create a table to hold the name of the tables you want to empty them.
drop table empty_tables; --if exists
-- be carefull, do not empty the my_constraints , my_triggers, empty_tables tables
create table empty_tables as select table_name from user_tables where lower(table_name) not in (.. some list of names..., 'my_constraints' , 'my_triggers' , 'empty_tables');
-- if you want to use other way than "truncate", feel free to do it, but mind the size of the archivelogs and the load of the system resources
DECLARE
cursor r1 is select * from empty_tables;
BEGIN
FOR c1 IN r1
loop
dbms_utility.exec_ddl_statement('truncate table ' || c1.table_name || ' drop storage ' );
END LOOP;end;
/
-- now, reinforce the constraints and the triggers:
-- first, the primary constraints, because, otherwise, you will receive an error when you try to
-- enable a FK constraint, referring a non primary key column.
DECLARE
cursor r1 is select * from user_tables where table_name in (select table_name from my_constraints where constraint_type='P');
BEGIN
FOR c1 IN r1
loop
dbms_utility.exec_ddl_statement('alter table ' || c1.table_name || ' enable primary key ' );
END LOOP;
end;
/
-- now, the unique constraints, same reason from above
declare
cursor r1 is select * from my_constraints where constraint_type='U' and constraint_name not like 'SYS%' order by constraint_name ;
-- i put there the "sys" exception because i received some errors, you can imagine which type of
BEGIN
FOR c1 IN r1
loop dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable novalidate constraint ' || c1.constraint_name);
END LOOP;
end;
/
-- now, the rest:
declare
cursor r1 is select * from my_constraints where constraint_name not like 'SYS%' order by constraint_name ;
-- i put there the "sys" exception because i received some errors, you can imagine which type of
BEGIN
FOR c1 IN r1
loop dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable novalidate constraint ' || c1.constraint_name);
END LOOP;
end;
/
-- and, eventually, the triggers:
DECLARE
cursor r1 is select * from my_triggers where status='ENABLED' and upper(substr(trigger_name,1,3))<>'BIN';
BEGIN
FOR c1 IN r1
loop
dbms_utility.exec_ddl_statement('alter trigger ' || c1.trigger_name || ' enable ' );
END LOOP;
end;
/
And that's all! Your schema is empty, except the tables you mentioned when you populate the empty_tables table.
And now drop the unnecessary tables:
drop table my_constraints;
drop table my_triggers;
drop table empty_tables;
marți, 11 noiembrie 2014
Need a lot of undo capabilities? You have to set this...
When you experience the "error ORA-01555: snapshot too old", i am sure you wish to have had larger undo provisioned space.
First:
sql> show parameter undo_retention
undo_retention integer 300000
Keep in mind the displayed value is measured in seconds. The first thought will be to raise ad infinitum the value of the above parameter. But this is not posible, because you need a lot of space, and the database storage is not endless. Anyway, the database engine will not honour any request of keeping undo information beyond of its capabilities. Will erase the oldest undo info and will continue to keep in shape the data.
So, you need to set properly the undo_retention parameter. Take the following script and use it to obtain the optimal value:
select (SELECT SUM(a.bytes)
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#)
/
(
( SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'db_block_size') -
(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)
) FROM v$undostat))
"undo_retention optimal value"
from dual;
First:
sql> show parameter undo_retention
undo_retention integer 300000
Keep in mind the displayed value is measured in seconds. The first thought will be to raise ad infinitum the value of the above parameter. But this is not posible, because you need a lot of space, and the database storage is not endless. Anyway, the database engine will not honour any request of keeping undo information beyond of its capabilities. Will erase the oldest undo info and will continue to keep in shape the data.
So, you need to set properly the undo_retention parameter. Take the following script and use it to obtain the optimal value:
select (SELECT SUM(a.bytes)
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#)
/
(
( SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'db_block_size') -
(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)
) FROM v$undostat))
"undo_retention optimal value"
from dual;
marți, 4 noiembrie 2014
How to workaround a hanging situation when run an Oracle APEX saved report
The problem seems to be impossible, at first sight. As a regular user you cannot do anything but wait.
Log on as developer or admin OF THE WORKSPACE WHERE THE REPORT BELONGS. Go to:
Home>>Administration>>Manage Service>>Interactive Report Settings (link on the right of the page) >>Saved Reports
select the desired saved report and click <delete>. Unfortunately, there is no other way to stop the hung report. Of course, maybe it will need to rebuild the report, but, anyway, it was designed in a poorly fashion, since is not finishing the run and displaying some data.
vineri, 12 septembrie 2014
Import Excel data in Oracle tables using TOAD
Creation of the table:
Database >> Create >> Table >> Load Cols from File >> choose your file of choice >> OK >> OK
Filling the table:
Schema Browser >> Tables >> Right click the table to be filled >> Import from Text, Excel or Access >> Next >> Next >> choose the file >> Next >> set some or Next >> choose the sheet (if you want two or more sheets, will import one at a time >> Next >> Execute
Enjoy!
Database >> Create >> Table >> Load Cols from File >> choose your file of choice >> OK >> OK
Filling the table:
Schema Browser >> Tables >> Right click the table to be filled >> Import from Text, Excel or Access >> Next >> Next >> choose the file >> Next >> set some or Next >> choose the sheet (if you want two or more sheets, will import one at a time >> Next >> Execute
Enjoy!
luni, 4 august 2014
Solve "Enq: TM Contention" when unindexed foreign keys is the cause
If you notice a very slow behaviour of your database and, from the multiple ways you can go further, you issue the following command
SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('&User')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;
and you receive a line like this:
SID BLOCK EVENT TYPE MODE REQ OBJECT OBJECT_TYPE
83 54 enq: TM - contention TM 0 2 STUDENTS TABLE
you should take care of this problem, because will not disappear by itself.
First, locate the unindexed foreign keys:
SELECT * FROM (SELECT a.table_name, b.column_name, b.position column_position
FROM user_constraints a, user_cons_columns b
WHERE a.constraint_name = b.constraint_name
AND a.constraint_type = 'R'
MINUS
SELECT c.table_name, d.column_name, d.column_position
FROM user_indexes c, user_ind_columns d
WHERE c.index_name = d.index_name
)
ORDER BY table_name, column_position;
and take the appropriate action, either drop some foreign key constraints if you don't need them or create the proper indexes.
Good luck!
SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('&User')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;
and you receive a line like this:
SID BLOCK EVENT TYPE MODE REQ OBJECT OBJECT_TYPE
83 54 enq: TM - contention TM 0 2 STUDENTS TABLE
you should take care of this problem, because will not disappear by itself.
First, locate the unindexed foreign keys:
SELECT * FROM (SELECT a.table_name, b.column_name, b.position column_position
FROM user_constraints a, user_cons_columns b
WHERE a.constraint_name = b.constraint_name
AND a.constraint_type = 'R'
MINUS
SELECT c.table_name, d.column_name, d.column_position
FROM user_indexes c, user_ind_columns d
WHERE c.index_name = d.index_name
)
ORDER BY table_name, column_position;
and take the appropriate action, either drop some foreign key constraints if you don't need them or create the proper indexes.
Good luck!
luni, 28 iulie 2014
How to overcome "ORA-01610: recovery using the BACKUP CONTROLFILE option must be done"
SQL> startup mount
ORACLE instance started.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
This problem can appear after a loss of the controlfile (and will assume you don't have any backup) or if you mess with point-in-time recovery.
Solution:
SQL> recover database using BACKUP CONTROLFILE;
-this command will lead to some interaction about what file (controlfile backup) the instance should use in order to recover.
I will assume that you can't provide any file an the problem persists.
Now take a backup of the current controlfile:\
SQL> alter database backup controlfile to '/home/oracle/back.ctl';
Create a trace of the current controlfile:
SQL> alter database backup controlfile to trace as '/home/oracle/trace.sql';
Bounce the instance to the nomount state:
SQL>shutdown immediate;
SQL>startup nomount;
Now copy the following command from the trace file:
CREATE CONTROLFILE REUSE DATABASE "PIDB" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/opt/oracle/oradata/pidb/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/opt/oracle/oradata/pidb/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/opt/oracle/oradata/pidb/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/opt/oracle/oradata/pidb/system01.dbf',
'/opt/oracle/oradata/pidb/sysaux01.dbf',
'/opt/oracle/oradata/pidb/undotbs01.dbf',
'/opt/oracle/oradata/pidb/users01.dbf',
'/opt/oracle/oradata/pidb/example01.dbf',
'/opt/oracle/oradata/smis.dbf',
'/opt/oracle/oradata/smis2.dbf',
'/opt/oracle/oradata/smis3.dbf',
'/opt/oracle/oradata/pidb/STATSPACK.DBF',
'/opt/oracle/oradata/pidb/FLOW_1046809878404304.dbf',
'/opt/oracle/oradata/pidb/apex01.dbf',
'/opt/oracle/oradata/pidb/APEX_2146717548170215.dbf'
CHARACTER SET WE8MSWIN1252
;
and issue it through sqlplus command line.
You can easily observe that the TEMP datafiles are not the, and after:
SQL>alter database open;
you cannot do everything you want, because of the emptiness of TEMP tablespace.
So, create a temporary datafile:
SQL>alter tablespace TEMP add tempfile '/opt/oracle/oradata/pidb/tempo2.dbf' size 1024m autoextend on;
and everything will work fine.
Be aware, if the database is after an incomplete recovery, you should replace the NORESETLOGS word with RESETLOGS, within the create controlfile command, and the further command will be
SQL> alter database open resetlogs;
All done! Enjoy!
marți, 15 iulie 2014
Some Glassfish issues: instalation, stuff.....
In order to have the Glassfish server working, you must install JDK 1.7xxxxx.
Now, let's assume that you have the jdk folder under /usr/java and glassfish4 folder under /opt.
Make an environment script:
vi envjava.sh
export JAVA_HOME=/usr/java/jdk1.7.0_60/bin
export PATH=$JAVA_HOME/bin:/opt/glassfish4/bin:$PATH
export PATH=$JAVA_HOME/bin:/opt/glassfish4/bin:$PATH
and run the script.
All you have to do now is to start the domain:
/opt/glassfish4/bin/asadmin start-domain
Let's study some problems that can appear:
• Exception in thread "main" java.lang.UnsupportedClassVersionError: org/glassfish/admin/cli/AsadminMain : Unsupported major.minor version 51.0
the problem is java executable is another version, lower one. Make a symbolic link:
rm /usr/bin/java
ln -s /usr/java/jdk1.7.0_60/bin/java /usr/bin/java
• java.net.UnknownHostException: ws1: ws1: Temporary failure in name resolution
your /etc/hosts file is wrong.
• There is a process already using the admin port 4848 -- it probably is another instance of a GlassFish server.
Could be the same problem as above, the file /etc/hosts should resolve the ip properly, or can be a problem with that port. The netstat will help:
netstat -natp
if you'll receive something like this:
tcp 0 0 :::4848 :::* LISTEN 7943/java
just kill that process, if it is not a wanted process for you:
ps -ef | grep java
root 7943 1 15 11:12 pts/1 00:00:38 /usr/java/jdk1.7.0_60/bin/java .....................
root 7943 1 15 11:12 pts/1 00:00:38 /usr/java/jdk1.7.0_60/bin/java .....................
kill -9 7943
and that's all.
Now, you should see this:
/opt/glassfish4/bin/asadmin start-domain
Waiting for domain1 to start .......
Successfully started the domain : domain1
domain Location: /opt/glassfish4/glassfish/domains/domain1
Log File: /opt/glassfish4/glassfish/domains/domain1/logs/server.log
Admin Port: 4848
Command start-domain executed successfully.
Waiting for domain1 to start .......
Successfully started the domain : domain1
domain Location: /opt/glassfish4/glassfish/domains/domain1
Log File: /opt/glassfish4/glassfish/domains/domain1/logs/server.log
Admin Port: 4848
Command start-domain executed successfully.
Open in a browser
http:<your hostname or ip>:4848
Now, you can experience the following error:
Secure Admin must be enabled to access the DAS remotely
/opt/glassfish4/bin/asadmin change-admin-password
the old password is blank (or adminadmin, or admin, ...). As far as i know, is blank.
After the password is changed:
/opt/glassfish4/bin/asadmin
>enable-secure-admin
>exit
and now everything should be fine.
The admin console has to be there.
Now, let's create a jdbc connection pool:
copy the ojdbc6.jar from here and put it here:
/opt/glassfish4/glassfish/domains/domain1/lib/
then restart the domain:
/opt/glassfish4/bin/asadmin
>restart-domain
> create-jdbc-connection-pool --restype javax.sql.DataSource --datasourceclassname oracle.jdbc.pool.OracleDataSource --property "user=hr:password=hr: url=jdbc\\:oracle\\:thin\\:@ws1\\:1521\\:ws1" ws1pool
where we assume that hr/hr is the user credentials, ws1 is the hostname, 1521 the listener port and ws1 the service name.
Now go to the admin console, jdbc connection pools, click on the new connection pool name, and then ping it. You should receive the message "ping succeeded".
marți, 8 iulie 2014
How to reset root password under LINUX
You will find this tutorial on hundreds of places over the web, but it's never too much for a useful thing to be found on how many places can be posible.
1. Start the computer.
2. Soon after the BIOS messages, pres ESC and you'll have the GRUB menu displayed.
3. Press <E> and in the following menu, use the arrows to choose the second line, usually, the one with kernel = ....
4. Press <E> again and begin to edit the line, by adding the word "single" at the end (keep in mind you can use the word "1". Just that, the "1" character).
5. ESC to go back
6. Press <B> to boot the kernel.
The booting process will end in single user mode, with root prompt (sh#).
Now you can change the password easily, like you would be logged as root.
sh# passwd
etc etc
Enjoy!
1. Start the computer.
2. Soon after the BIOS messages, pres ESC and you'll have the GRUB menu displayed.
3. Press <E> and in the following menu, use the arrows to choose the second line, usually, the one with kernel = ....
4. Press <E> again and begin to edit the line, by adding the word "single" at the end (keep in mind you can use the word "1". Just that, the "1" character).
5. ESC to go back
6. Press <B> to boot the kernel.
The booting process will end in single user mode, with root prompt (sh#).
Now you can change the password easily, like you would be logged as root.
sh# passwd
etc etc
Enjoy!
vineri, 4 iulie 2014
Different time between connected through listener and connected locally. RAC Infrastructure 11gR2
I don't know if the problem arises with the 11gR2 or was there, under the previous releases. If you experience this problem with no matter which previous release, i suppose the workaround is ok.
The issue is below one:
sqlplus sys@tnsname as sysdba
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------
04-JUL-14 03.46.03.250689 AM +03:00
sqlplus / as sysdba
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------
04-JUL-14 10.46.03.250689 AM +03:00
Unfortunately, all the OLTP applications you will use to populate your database will work through listener, and having a wrong timestamp reference can be very annoying for the users.
Briefly, the workaround:
connect as root and:
1. change the TZ variable. In order to make this permanent, after every single machine startup, you have to put a script under the /etc/profile.d folder (doesn't matter the name of the script, make it xxx.sh and will run).
export TZ=Europe/Bucharest (of course, you will provide the desired timezone)
2. edit the $ORACLE_HOME/crs/install/s_crsconfig_name-of-your-instance_env.txt where the ORACLE_HOME is the GRID_HOME
change the line
TZ=.....
accordingly
I don't know why, whatever you set, conercning the system timezone or database timezone, here you will find, by default, America/NewYork.
Now, connect as oracle user and edit the below files:
$ORACLE_HOME/network/admin/endpoints_listener.ora
$ORACLE_HOME/network/admin/listener.ora
and add the line
ENVS='TZ=Europe/Bucharest'
Again, be aware of your correct data.
By some people, now you have just to bounce the listener or the ohasd services.
/etc/init.d/ohasd stop
/etc/init.d/ohasd start
For me, didn't work. I restarted all the operating systems of all the instances in order to achieve the purpose.
Enjoy!
The issue is below one:
sqlplus sys@tnsname as sysdba
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------
04-JUL-14 03.46.03.250689 AM +03:00
sqlplus / as sysdba
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------
04-JUL-14 10.46.03.250689 AM +03:00
Unfortunately, all the OLTP applications you will use to populate your database will work through listener, and having a wrong timestamp reference can be very annoying for the users.
Briefly, the workaround:
connect as root and:
1. change the TZ variable. In order to make this permanent, after every single machine startup, you have to put a script under the /etc/profile.d folder (doesn't matter the name of the script, make it xxx.sh and will run).
export TZ=Europe/Bucharest (of course, you will provide the desired timezone)
2. edit the $ORACLE_HOME/crs/install/s_crsconfig_name-of-your-instance_env.txt where the ORACLE_HOME is the GRID_HOME
change the line
TZ=.....
accordingly
I don't know why, whatever you set, conercning the system timezone or database timezone, here you will find, by default, America/NewYork.
Now, connect as oracle user and edit the below files:
$ORACLE_HOME/network/admin/endpoints_listener.ora
$ORACLE_HOME/network/admin/listener.ora
and add the line
ENVS='TZ=Europe/Bucharest'
Again, be aware of your correct data.
By some people, now you have just to bounce the listener or the ohasd services.
/etc/init.d/ohasd stop
/etc/init.d/ohasd start
For me, didn't work. I restarted all the operating systems of all the instances in order to achieve the purpose.
Enjoy!
Abonați-vă la:
Postări (Atom)