Helpful statement to end the backup for the tablespaces which are in the online backup mode:
1- Create a script through the following commands:
svrmgrl> spool endhotbackup.sql
svrmgrl> select 'ALTER DATABASE DATAFILE '''||name||''' END BACKUP ' FROM V$DATAFILE;
2- Cleanup endhotbackup.sql through vi
3- svrmgrl> @endhotbackup.sql
4- startup the database
Showing posts with label Useful Oracle Questions and Answers. Show all posts
Showing posts with label Useful Oracle Questions and Answers. Show all posts
Backup and Recovery Tips
Datafiles Efficiency: More Files vs Increase the Size
Which is more efficient; adding more data files or increasiong the size of the data files of a tablespace?
Running on oracle 9.2 on win 2000 server.
It's mostly a personal preference. One thing to consider is recovery. If you have fewer, larger files then restoring and recovering a file with a bad block will take longer than if you had more, smaller files.
If you are not using raid then it is better to spread the data on multiple files on multiple disks.
It depends what kind is your storage: local disks or RAID
For local disks - you want to have many datafiles on as much local disks as possible
For RAID - it really doesn't matter. The datafiles are stripped anyway. it's a more for convenience not to have huge datafiles, since if you have to move them latter it's harder to find 20 GB disk space than 5 chunks of 4GB.
If you are going to be using RAID-5 (which is common), I cannot see how chopping up your database will help a lot. Unless you have multiple ranks of RAID-5 storage, all your data is stored in one big blob over which you have little physical control, if any.
I usually separate my table-tablespaces and index-tablespaces into different tablespaces and subtending datafiles, more of a matter of management than of performance, since I am not a big fan of self-extending Oracle storage.
RAID-5 automatically scatters your data when it is stored; it is part of its reliability functions and also what can help your performance in queries. If I were you, I would separate the database logically, and not arbitrarily separate the storage on physical boundaries, given they are meaningless in the RAID-5 environment unless you have multiple controllers and RAID ranks.
Oracle : Unlimited Tablespace, User Creation
I’m new to Oracle 9i using R2. I’m currently logged in as sysdba and currently tried creating a new user:
SQL> create user john identified by oracle
2 default tablespace users;
User created.
SQL> grant connect to john;
Grant succeeded.
SQL> grant create any table to john;
Grant succeeded.
SQL> grant create table to john;
Grant succeeded.
SQL> grant unlimited tablespace to john;
Grant succeeded.
Why is it that if I don’t grant unlimited tablespace, john will not be able to create table? It gives an error :
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
But I have stated him to use tablespace users when created the user name at first?
---------->
You have to assign a quota.
Assign Tablespace Quotas to Users
Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object's segment. The security administrator is responsible for granting the required privileges to create objects to database users and for assigning tablespace quotas, as necessary, to database users.
---------->
just drop the user......john
and try this command....
create user john identified by oracle
default tablespace users
quota 5m on users
after firing up this command give required privileges to john to create a table....
then try whether it is working perfectly without granting that unlimited tablespace....
---------->
You don't have to delete the user. Just alter it.
alter user john quota unlimited on users;
---------->
When you creates the User and Grants him the Connect option by default the User gets allocated with the Unlimited Tablespace only you need to do is eigther Revoke that Option. or as bobanjayan said u just alter the user and add the Quota to that user.
---------->
Connect role does't have the privilege of unlimited tablespace.
Unlimited tablespace is with Resource role.
If you grant Resource role to user, he will get unlimited quota on SYSTEM tablespace also.
Be aware.
Dynamic Table Names and Moving Table
I'm fairly new to PL/SQL and I could use some help. I'm trying to create a cursor and build the tablename based on an input parameter. Currently I'm using DBMS.SQL statements, which I find cumbersome to use. I'd like to find an easier way if possible. Here is an example of what I'd like to do;
Cursor DYNAMIC is
SELECT column1 from I_PARAM||restoftablename
in the above example I_PARAM is an input paramter.
You can try using "EXECUTE IMMEDIATE" statement create a table dynamically.....also do get a second opinion.
*****************
declare
my_table varchar2(400);
begin my_table := 'create table dynamic_table (my_name varchar2(20))';
EXECUTE IMMEDIATE my_table;
end;
*****************
In Oracle DBS 7.3, how can I move a table from one tablespace to another without losing permissions, index's...etc for this table?
Hmmm good question ....i used diff methods but nothing like this in 7x...but still i'll try....
* First i would take an export of that table (say mytable.dmp)
* Open mytable.dmp in note pad.
* Edit the tablespace to the new tablespace name(whichever tablespace u wanna move).
* Save the file
* Try to import the dump file(mytable.dmp) into the new user schema u want to.
I can't gaurentee that this procedure would work but its worth a try on test table first.
Changing Prompt in SQL Plus
I have more then one Oracle 8 database servers. I need to have SQL promp to be my connect string. Username whenever I login in SQL Plus. Connect String is the same as Server Name.
Example:
Server 1 = aaaaa
Server 2 = bbbbb
Username = Naaa
Prompt should be
aaaaa.Naaa> or bbbbb.Naaa>
>>>>>
I use the following sql script to change my prompt. Modify it according to your requirements.
Copy and paste the below script (between .............) into a file named login.sql.
Copy this login.sql file in the working directory of your sqlplus (like c:\oracle\ora81\bin).
Oracle looks for a file named login.sql in its working directory. If it finds one then it executes it.
..................
set lines 1000
set pages 50
set serverout on size 500000
set head off
set pages 0
set termout off
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
/
spool z1.sql
select 'set sqlprompt '''||lower(global_name)||':'||user||'>''' from sys.global_name
/
spool off
@z1
set head on
set pages 60
set termout on
...................
>>>>>
I take a slightly different approach. I use the "new_value" clause of the column statement.
Here is the script:
-- setPrompt.sql
-- -------------
set term off
column prompt_col new_value prompt_var
define prompt_var = "not connected> "
set prompt off
select lower(user) || '@' || ' (' || global_name || ')' || chr(10) || ' > ' prompt_col
from global_name
/
set sqlprompt "&prompt_var"
set term on
Unable to locate statement causing error
When I ran the procedure below, I am running into the following error:
The error is ORA-00936: missing expression
I tried to put DBMS_OUTPUT.PUT_LINE statements to see where it is failing. I received the following output
SQL> exec proc_load_user_privileges
The error is 6
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 4
The error is 6
The error is ORA-00936: missing expression
It looks like right after the loop is done, the error is happening. How can I zero in on the statement that is causing the problems? I tried to run all the sql statements by themselves in sql*plus and they are working fine. I am not sure where the syntax error is. Any help would be greatly appreciated.
Here is the procedure text
CREATE OR REPLACE PROCEDURE proc_load_user_privileges
AS
CURSOR cur_list_of_cols
IS
SELECT column_name FROM user_tab_columns WHERE table_name = 'TEMP_NSDA_USER_PRIVILEGE' AND column_name NOT IN ('SUN_ID', 'NODE_ID', 'OPERATION', 'FIRST_NM', 'LAST_NM', 'FUNCTIONAL_AREA_NM');
CURSOR cur_temp_nsda_user_privilege
IS
SELECT SUN_ID,NODE_ID,OPERATION,FIRST_NM,LAST_NM,FUNCTIONAL_AREA_NM FROM TEMP_NSDA_USER_PRIVILEGE;
v_current_sun_id VARCHAR2(30);
v_current_node_id NUMBER(20);
v_current_privilege_nm VARCHAR2(30);
v_current_user_privilege_val VARCHAR2(30);
v_current_column_name_in VARCHAR2(30);
v_block_str VARCHAR2(500);
v_dynamic_query_handle INTEGER;
feedback INTEGER;
v_curr VARCHAR2(30);
v_operation VARCHAR2(30);
v_first_nm VARCHAR2(30);
v_last_nm VARCHAR2(30);
v_functional_area VARCHAR2(30);
BEGIN
FOR rec_temp_nsda_user_priv IN cur_temp_nsda_user_privilege
LOOP
v_current_sun_id := rec_temp_nsda_user_priv.sun_id;
v_current_node_id := rec_temp_nsda_user_priv.node_id;
v_operation := rec_temp_nsda_user_priv.operation;
v_first_nm := rec_temp_nsda_user_priv.first_nm;
v_last_nm := rec_temp_nsda_user_priv.last_nm;
v_functional_area := rec_temp_nsda_user_priv.functional_area_nm;
IF v_operation = 'UPDATE' THEN
DELETE FROM NSDA_USER_PRIVILEGE_T
WHERE sun_id = v_current_sun_id;
END IF;
DBMS_OUTPUT.PUT_LINE('The error is 6');
FOR current_col IN cur_list_of_cols
LOOP
v_current_privilege_nm:= current_col.column_name;
v_block_str:=
'SELECT ' || v_current_privilege_nm || ' FROM TEMP_NSDA_USER_PRIVILEGE WHERE sun_id = ' || v_current_sun_id || ' AND node_id = ' || v_current_node_id ;
EXECUTE IMMEDIATE v_block_str INTO v_current_user_privilege_val;
BEGIN
SELECT
REPLACE(v_current_privilege_nm, '_', ' ')
INTO v_current_privilege_nm
FROM DUAL;
END;
DBMS_OUTPUT.PUT_LINE('The error is 1');
IF (v_current_user_privilege_val = 'Y')
THEN
INSERT INTO NSDA_USER_PRIVILEGE_T (sun_id, node_id, privilege_nm) VALUES (v_current_sun_id, v_current_node_id, v_current_privilege_nm);
ELSIF (v_current_user_privilege_val IS NOT NULL) AND (v_current_user_privilege_val != 'N')
THEN
v_current_user_privilege_val:= TO_NUMBER(v_current_user_privilege_val);
DBMS_OUTPUT.PUT_LINE('The error is 2');
INSERT INTO NSDA_USER_PRIVILEGE_T
(sun_id, node_id, privilege_nm, user_privilege_value)
VALUES
(v_current_sun_id,v_current_node_id,v_current_privilege_nm,v_current_user_privilege_val);
END IF;
DBMS_OUTPUT.PUT_LINE('The error is 3');
END LOOP;
DBMS_OUTPUT.PUT_LINE('The error is 4');
END LOOP;
DBMS_OUTPUT.PUT_LINE('The error is 5');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The error is ' || SQLERRM);
END proc_load_user_privileges;
Deleting all objects from a tablespace
Just want to know what's the best way to delete all the objects (tables, indexes, squences, etc..) from a tablespace?
Delete the tablespace and recreate it?
Just drop the tables from the User(s) whatever exists in that tablespace.This way u can be sure everything is removed.You don't need to drop the tablespace. If you are using Oracle Enterprise Manager(OEM) its very simple...just click on the database-schema-tables-select all the tables in that user and remove them from the schema....do the same for the views/synonyms/seq...
(OR)
If you are not having OEM then u can write a sql stm to drop the tables/views/synonyms/....from each user individually.
(OR)
If you are not worried about the users and the data in it....simply drop the user which will remove everything.
First list out all the users in that tablespace and proceed.
I do have OEM installed. When I try to run it, it will not let me connect. I'm leaving the management server box blank (the database is local) and using all the different username/password combos I can think of that oracle came with. Am I missing something here?
For logon to OEM u might be using a right password but may be the TNSNAMES.ora file have some issues.Check your sqlnet.ora file and all the tns aliases in the tnsnames.ora file and make sure ur using .world in your host or just the string.
Ex: system/manager@orcl.world --if sqlnet.ora has names.default_domain=world
Ex: system/manager@orcl --if sqlnet.ora dont have any entry or commented for names.default_domain=world
The thing is OEM configures itself with the TNSNAMES.ora once installed so while conecting it only expects you to connect using uname/pwd as the host name is already picked by it.In the worst case create a new database tree and try.
Multiplexing Redo Logs Onto Raid
I have a situation where I would like to multiplex the redo logs but the other drive on which to multiplex is a raid. I have heard that putting redo logs on raids is bad. Is it better in this situation just not multiplexing? Or should I just go ahead and put the redo logs on the raid?
raid 1 --- reading and writing fast
raid 5 --- reading fast but writing slow
If your log groups are not on raid you must multiplex them.If they r on raid 1 no need to multiplex as it is already mirrored by raid 1
How about on Raid 5?
On raid 5 too mirroring is done but compressed (with parity bits), better you have a multiplexed log
RAID 0 - Striping. Fast access to disks
RAID 1 - Mirror, you have only half of the total space, ecause the other half is the mirror.
This are the two RAID levels (0+1) recomended to use with Oracle. RAID 5 is slower. Remember that redologs and archivelogs, are two components that typically include a great contention in I/O operations. So that, it is recomended to put that type of files in fast and separated storage device.
Whats the difference or relationship between Oracle undo tablespace and redo logs?
UNDO tablespace stores the before image of the data blocks, so the rest of the users can see consistent data before the user modifying the data commits its changes.
The redo logs on the other side store the already commited data and are used for instance recovery. When an instance recovery is required, Oracle first rolls the data forward (reading the commited transactions from the redo logs) , then rolls backward and rolls the uncommited transactions, so the database is in consistent state.
It's in the concepts book. A simple answer could be:
Rollback segments are used to rollback or "undo" transactions. They're also used to provide read consistency. I believe they contain both the before and after images of all changes made during a transaction. They also can be overwritten after the transaction completes (commit or rollback).
Redo logs are used for recovery. They're essentially a sequential, chronological, history of all changes made to the database. For example if you need to restore an old version a datafile, redo logs(both archived and online) are used to bring the restored datafile back in sync with the database.
It might help to think of rollback as "Undo". Rollback remembers all the changes made to a database during a transaction. If the transaction fails or if you decide it wasn't what you wanted to do, you (or Oracle itself) can "Undo" the changes and revert to the original data in the database.
Redo, as it's name suggests, allows Oracle to roll forward and re-apply changes that may have been lost, for instance in the event of a database crash. When the instance starts up, Oracle checks the "age" of the data files against the control file and, if it finds a datafile is out of date, applies the redo (if available!) to bring it in line with the control file. If necessary it can then roll back any transactions which were incomplete at the time of the crash.
I'm sure you'll get lots more technical explanations, but that's the gist of it. Have a look at the Server Administrators Guide more information.
The redo logs are used to redo or do over things that have been entered into the database, and the rollback segments are used to undo things that have been 'entered' into the database.
Lets say you have a transaction that affects 1 million rows, all of the changes will be stored as both redo and undo. Now lets say that you type commit, now everything in the log buffer will be written to your online redo log files and your rollback segments will no longer hold the extents that held this transaction.Now you have a disk failure.you will apply the redo to redo (do over) that transaction.
Next example:
Same transaction, but right before you type commit there is a power surge and your session just dies or you type rollback. Now all the changes that you made (before commiting) will be rolled back and undone.
Shell Script To Dynamically Generate Init.ora File
I am working on a need to clone databases..and in that I need to write a shell script which will 'dynamically generate' the init.ora file for target database using that of source database and a temp file which will hold changed value of parameters.
For example in temp file there is a param like:
db_name =targetdb
and in source init.ora file db_name=db1 then the shell script should read the temp file and be able to create a new init.ora file with changed value
ie db_name=targetdb (so all other parameters that are not specified in temp file will remain same, but all specified parameters will be changed.)
Can you suggest some similar scripts for such work?
--------------------------------------------/
Try something this:
CODE
set pages 0 term off feed off trims on lin 132 ver off
def fdb='FromDB'
def tdb='ToDB'
spo /tmp/init&&tdb.ora
Select NAME||'='||replace(VALUE,'&&fdb','&&tdb')
From DBA_PARAMETERS;
spo off
How much memory is been utilized by the SGA
We are running SAP 4.6c with ORACLE 9.2.0.5 on AIX. Database is configured with Dynamic SGA, and we have set SGA_MAX_SIZE also.
When we look at the memory management at OS level it shows high utilization of memory.
We don’t know whether the memory allocated to SGA is completely used or not.
How to find out how much memory is been utilized by the SGA, whether it needs to be allocated more
memory or can be reduced from the SGA.
v$sga will give you the current sga stats
Run statspack to analyze whether you reqiure more sga or not
You can also look at V$SGASTAT view. Try running the following script (note the part about free memory):
CODE
set verify off
set pagesize 23
set linesize 80
set pause on
set pause 'Hit enter to continue'
set feedback off
set showmode off
set echo off
col num format 999 heading "Nbr"
col name format a20 heading "Name"
col type format 999 heading "Type"
col value format a10 heading "Value"
col meg format 99.99 heading "Size|Meg"
col isdefault format a10 heading "IsDefault"
TTITLE "SHARED POOL PARAMETERS (DICTIONARY AND LIBRARY CACHE)"
select num, name, type, value, (to_number(value) / 1024) / 1024 meg,
isdefault
from v$parameter
where name = 'shared_pool_size';
col AA format 99.99 heading "Size MB"
col BB format 99.99 heading "Free MB"
col CC format 99.99 heading "% Free"
col DD format 99999999 heading "Executions"
col EE format 999999 heading "Reloads"
col FF format 999.99 heading "% Reload"
col GG format a20 heading "Parameter"
col HH format 99,999,999 heading "Count|(entries)"
col II format 99,999,999 heading "Usage|(valid entries)"
col JJ format 99,999,999 heading "Gets|(memory)"
col KK format 9,999,999 heading "Misses|(disk)"
col LL format 99.99 heading "% Misses"
TTITLE CENTER 'V$PARAMETER (SHARED POOL SIZE) AND V$SGASTAT (FREE MEMORY)
REPORT '
select (to_number(VP.value) / 1024) / 1024 AA,
VS.bytes / 1048576 BB,
(VS.bytes / to_number(VP.value)) * 100 CC
from v$parameter VP, v$sgastat VS
where VS.name = 'free memory'
and VP.name = 'shared_pool_size';
PROMPT
PROMPT More than 5% Free = lower shared_pool_buffer parameter
TTITLE CENTER 'V$LIBRARYCACHE (SHARED SQL AREA) SUMMARY REPORT' STITLE
CENTER 'V$LIBRARYCACHE (SHARED SQL AREA) SUMMARY REPORT'
select sum(VL.pins) DD,
sum(VL.reloads) EE,
(sum(VL.reloads) / sum(VL.pins)) * 100 FF
from v$librarycache VL;
PROMPT
PROMPT More than 1% Reloads = raise shared_pool_size parameter
TTITLE CENTER 'V$ROWCACHE ENTRIES DETAIL REPORT (DICTIONARY)'
select parameter GG,
/* count HH, */
/* usage II, */
gets JJ,
getmisses KK,
(getmisses / (gets + getmisses + 1)) * 100 LL
from v$rowcache
order by parameter;
PROMPT
PROMPT Not tunable at this level of detail, provided for information
only.
TTITLE CENTER 'V$ROWCACHE ENTRIES SUMMARY REPORT (DICTIONARY)'
select sum(gets) JJ,
sum(getmisses) KK,
(sum(getmisses) / (sum(gets) + sum(getmisses) + 1)) * 100 LL
from v$rowcache;
PROMPT
PROMPT Not tunable at this level of detail, provided for information
only.
TTITLE CENTER 'V$ROWCACHE ENTRIES SUMMARY REPORT (DICTIONARY)'
select sum(gets) JJ,
sum(getmisses) KK,
(sum(getmisses) / (sum(gets) + sum(getmisses) + 1)) * 100 LL
from v$rowcache;
PROMPT
PROMPT More than 5% Misses (summary) = raise shared_pool_buffer parameter
exit;
Unable to Create a New Instance
I am attempting to create a new instance in my sun solaris 8 server. I have set the environment and prepared the scripts. I do not have gui to perform the db creation therefore i need to use the scripts and run from sqlplus.
it's a 9.2.0.1 database. I have added in oratab. However when I run this script in sqlplus - I get the following error:
SQL> @/oracle/ora01/app/oracle/admin/xmbdb/scripts/CreateDB.sql
Connected to an idle instance.
SQL> spool /oracle/ora01/app/oracle/product/9.2.0/assistants/dbca/logs/CreateDB.
log
SQL> startup nomount pfile="/oracle/ora01/app/oracle/admin/xmbdb/pfile/init.ora"
;
LRM-00109: could not open parameter file '/oracle/ora01/app/oracle/admin/xmbdb/p
file/init.ora'
ORA-01078: failure in processing system parameters
SQL> CREATE DATABASE xmbdb
2 MAXINSTANCES 1
3 MAXLOGHISTORY 1
4 MAXLOGFILES 5
5 MAXLOGMEMBERS 5
6 MAXDATAFILES 100
7 DATAFILE '/oracle/ora02/oradata/xmbdb/system01.dbf' SIZE 325M REUSE AUTOEXT
END ON NEXT 10240K MAXSIZE UNLIMITED
8 UNDO TABLESPACE "UNDOTBS" DATAFILE '/oracle/ora02/oradata/xmbdb/undotbs01.d
bf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
9 CHARACTER SET US7ASCII
10 NATIONAL CHARACTER SET AL16UTF16
11 LOGFILE GROUP 1 '/oracle/ora02/oradata/xmbdb/redo01.log') SIZE 100M,
12 GROUP 2 ('/oracle/ora02/oradata/xmbdb/redo02.log') SIZE 100M,
13 GROUP 3 ('/oracle/ora02/oradata/xmbdb/redo03.log') SIZE 100M;
CREATE DATABASE xmbdb
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL> spool off
SQL> exit;
Disconnected
How can I rectify this problem? Please Advice.
The problem you are encountering here is that you have a Permission issue. Try to check all the files you created are under the user Oracle and dba Group. It seems like you have some folders created under diff user other than Oracle.When you try to aceess a folder which Oracle may not have permissions will fail. Just make sure all the folder and file permissions are consistent with Oracle:dba
I have checked the permissions for the folders - they all belong to oracle user. DoIi need to create udump, bdump, cdump and pfile folders and put in the init.ora file into pfile folder before running the scripts?
There's no need to create all the dump folders. They are included in the init.ora file by default...unless you are trying to create your own init.ora file. Its always better to use an existing init.ora file of other db and modify accordingly. Try that method and run the script again.
How to Drop undo Tablespace
I create a new undo tablespace, then I want to drop the org undo ts and recreate is with a smaller size.
However, after switching Ii am not always able to offline the org tablespace.
I get an Ora-30042:Unable to offline the undo TS.
This error is always returned when using the procedure. This is in a test db, where I'm the only one logged in (sqlplus /nolog).
Lookin in de alert.log it states:
Undo tablespace 1 moved to Pending Switch-Out State.
What does this mean? How can I make sure the undo TS is not being used anymore, so I can offline it?
Using this command shift user UNDO and the try to drop old one.
ALTER SYSTEM SET UNDO_TABLESPACE = ....
Note of cautious:
An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails. However, since DROP TABLESPACE drops an undo tablespace even if it contains unexpired undo information (within retention period), you must be careful not to drop an undo tablespace if undo information is needed by some existing queries
How to init an object type
I have the following code :
CREATE OR REPLACE TYPE ObjB IS OBJECT (
B1 varchar2(10)
,B2 varchar2(10)
);
CREATE OR REPLACE TYPE ObjC IS OBJECT (
C1 varchar2(10)
,C2 varchar2(10)
);
CREATE OR REPLACE TYPE ObjCArr IS VARRAY(100) OF ObjC;
CREATE OR REPLACE TYPE ObjA IS OBJECT (
A1 varchar2(10)
,A2 varchar2(10)
,Ab ObjB
,Ac ObjCArr
);
a ObjA;
I've tried to initialize a, but each time I have an ORA-06530 error.
Any idea how to initialize it using Oracle 8.1.7 ?
Nusa
If you want to initialize, use:
DECLARE a ObjA := ObjA(NULL,NULL,ObjB(NULL,NULL),ObjCArr());
Note: Since element a.Ac is a VARRAY of objects ObjC, you would have to use
a.Ac.EXTEND;
a.Ac(1) := ObjC('X','Y');
to add elements to VARRAY. You can not add it using:
a.Ac.EXTEND;
a.Ac(1).C1 := 'X';
a.Ac(2).C2 := 'Y';
You can use already existing a.Ac(1).C1 or a.Ac(1).C2 for existing elements only.
How to Start the Service Manager
I am new to oracle. I want to know how to start the service manager and where should I give the command. What is the password for newlly installed oracle. Please help me out.
If you want to run SVRMGRL on UNIX then just log on to the UNIX with your user name and password.
1. Type SVRMGRL
2. Type connect internal
The message will say connected. Now you are connected to Oracle. If you are new to Oracle then make sure of you your commands, SVMGRL has a lot of power.
Now if you are connecting to an NT machine, then you need to start the command prompt.
1. Type SVRMGRL
2. It will ask you for a password, this is the same password that you created when you did the fresh install of oracle. Once you issue a password you should be in.
Thanks for the reply but I am working on Windows NT not Unix so let me know how to start svrmgr in it. Your help will be appreciated.
For NT do the following....assuming u have Oracle installed on c:drive if u have oracle on d: e: or anyother drive just replace the c:> with ur drive.
c:> set ORACLE_SID=ORCL ( whatever db name u have)
c:> svrmgrl
svrmgrl > connect internal
svrmgrl > select * from v$database; -- if Oracle not available ...do this
svrmgrl > startup
svrmgrl > select * from v$database;
You need to start the Listener too...for sqlplus conectivity..
c:> lsnrctl
lsnrctl > status -- if shows errors then you need to start the listener
lsnrctl > start
If you happen to have a named listener then u need to specify the name....
lsnrctl > start ORCL (whatever listener name you have ..check listener.ora file for that)
You should be all set
But remember man, server manager "svrmgrl" is unsupported 9i onwards. You have to use only sqlplus to get connected.
The person who posted this query didn't mention the version
I am new to oracle. I try to connect the oracle sever 8i (8.1.7) from windows 2000, I got error message no listner, then I try to start listner: start orcl and got another error message:
TNS-01151: Missing listerner name, oracle, in LISTENER.ORA
This is my listerner.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 2481))
)
)
What I need to do ? Please help.
According to your listener.ora file you don't have a named listener so while starting the listener you must do the following
lsnrctl start -- Correct
lsnrctl start orcl -- Incorrect
NOTE : Only for named listener you need to start like ... lsnrctl start orcl
Oracle Service Consuming Lot of CPU Resource
System Config :
OS : Win 2000 A.S
Oracle 11.5.8
Rdbms : 8.1.7
Usaully my Oracle use to work very good but some times once in a blue moon , when I restarted my Database after performing a nightly backup , it startup quickly but when I look into the Task Manager , it shows Oracle.exe service is consuming a lot of CPU ( 70 – 80 % ) even wen no user is connected , usually it won't.
Because of this behavior my Oracle Applicatrion services takes up a long time to get UP. And after sometime Users starts complaining that Application is very slow. I didn't understand what background process is running that is consuming a lot of CPU .
I was told to create a statspack but since I am new to DBA, can you pls. guide me how to create a statspack report.
Not getting it up quick enough is a real problem.
It could be a couiple of things. First, it takes time for your cache to "warm up". Initial database queries will take longer to run because of this. Second, maybe your database is not being brought down cleanly and it takes some time for SMON to clean things up.
Either way, I'd look at not using a cold backup.
Creating STATSPACK:
1. Create a tablespace for this purpose called perfstat or any good name
2. Run catdbsyn.sql dbmsspool.sql
Now you must understand the data collection level. Each collection is in one of this level:
a. Level 0
b. Level 5
c. Level 6
d. Level 7
e. Level 10
- Study the STATSPACK architecture -. Very important
3. Do a snapshot to capture the RAM structure
SQL> exec statspack.snap;
Statspack report is easy to see the current dabase situation
1) create a tablespace of 100mb size
2) run the script: oracle_home\rdbms\admin\spcreate.sql
It will prompt for your default tablespace then give the above tablespace name. After that just give the temporary tablespace name other than system..
3) take some snapshots under perfstat user.
the command will be:
exec perfstat.statspack.snap
take as many snap shots u want within a time interval.
4) now run the script
oracle_home\rdbms\admin\spreport.sql
it will ask for your destination and name of the output report file..
provide those...with to snapshot nos between which you want to see the reports...
now open the report file and enjoy
Reducing Database Fragmentation
Oracle9i database admin guide says, a fragmented database should be reorganized to make space. The solution is to make a full db export, delete the db, recreate the db, and a full db import. Is there another easier option?
os: win2000 server
oracle 9.2.0.1.0
----->
Yes, there is an easier option. Use locally managed tablespaces. Do you have a fragmentation problem? Are you currently using locally managed or dictionary managed tablespaces?
----->
Yes, we are using locally managed tablespaces, does it mean I don't have to worry about fragmentation?
----->
It means you don't have to worry about it as much. Are you using uniform extent sizes or system managed extent sizes? If you are using uniform extent sizes, then you won't have any fragmentation issues. All extent sizes are the same. If you are using system managed extent sizes then fragmentation is greatly reduced, to the point that you shouln't have to be concerned with it. Have you identified that you have a fragmentation issue?
----->
No we havent, that's another problem, I don't know how to identify if we have... I will look it up.
Best way to compare two Databases
What is the best way to compare two databases. Especially tables+columns, indexes+columns, constraints, views, stored procedures, triggers.
It is not about data.
It is not possible to make a network connection to the two databases, so a dblink is not a possible solution.
-------------------------
select table_name from s1.user_tables
minus
select table_name from s2.user_tables
&
vice versa
&&
for all the other objects in question write sql the like
or use dbms_metadata for both schemas and then use your favourite editor to compare the files will all that ddl included
or take an exp from both schemas
imp /
@sid file=your.dmp fromuser=s1 show=yes log=s1.log
imp /
@sid file=your.dmp fromuser=s2 show=yes log=s2.log
then compare the logs and retrieve the differences
-------------------------
Use OEM Change Management to take the diff between two databases. Of course you would need OMS if want to take snapshots, but for simple comparison - it's not neccessary
Tablespace Change
I created a user as below
create user paymaster identified by paymaster
default tablespace users
temporary tablespace temp
this created user paymaster.
however when i queried
select owner,tablespace_name from dba_tables
the output was as below:-
OWNER TABLESPACE_NAME
PAYMASTER SYSTEM
the paymaster should be in tablespace 'PAYDATA'
this tablespace is already created.
How do i create user PAYMASTER to use PAYDATA?
I had earlier dropped user paymtr from the database as the client wants paymtr to be changed to paymaster.
I created as above and granted the roles.
However when I tried to import from *.dmp file that was exported from paymaster in another machine(which was in system tablespace) and I queried from dba_tables to find all tables are in system tablespace.
How can I retify this?
Change the tablespace from SYSTEM to PAYDATA without dropping the tables.
sql > Alter user PAYMASTER default tablespace PAYDATA
temporary tablespace TEMP;
and for each table - move them to PAYDATA tablespace...try to create a script for the below for all the tables ..
sql > Alter table tablename default tablespace PAYDATA;
Also check the indexes ...if they happen to be in SYSTEM tspace do the above and move them into PAYDATA tablespace or anyother where you wish.
I have managed to move the tables from system to paydata. However there is one table with datatype long which I am unable to move.
Any suggestions on how to solve this.
In order to move the table with a LONG col datatype you can generate a table script manually and import the data into that table.
If you have problem generating table script use TOAD to generate the table script and also the INSERT statement script and run the script from sqlplus.You should be good.
Shutting Down Oracle On Windows
What is the difference between shutting down an Oracle database (8i and up) on Windows by logging into the instance and issuing the shutdown command vs stoping the Oracle service (OracleServicedbname)?
I think there's no difference - but you can see many more informations by logging in with sqlplus/svrmgrl and shutting down the DB with the shutdown command.
Using MMC and shutting down the service shows only a status bar.
Depends on the version of Oracle and how the instance is configured in the Windows registry. For 9i (not sure about 8i, don't have an 8i instance anymore), each instance has a key in the registry called ORA_
That being said, in my opinion it is always best to shutdown the database manually thru SQL*Plus. That way you know what is going on.
There is a difference.
The service must be running in order to have the database running.
The service can be running without the database running.
It's simple to see: start the service, start the database (if not set to automatic start), then connect as sysdba and do a shutdown. Then go look at the services - the OracleServiceSID still shows started.
Stop the service, and then start a SQL*Plus session. Try to connect or start, and you get Oracle not available error.
You can shut down the database either through the sql prompt or by shutting down the associated oracle service , in both the cases the database will shutdown its same if your purpose is to shutdown the database a service is continuously running program which helps other program to run or execute.
But when you stop the oracle service you close the door for everything associated with the service so if you stop the service you cannot start the database unless and until you start the service again.
If you look at the oracle database from programmers perspective you can feel oracle database is not one program, its number of programs running togerther and the oracle service you can think of as the link between all these programs.
On Windows you have "services" which could be started or stopped. You can have Oracle DB up/down only if the service is started.
You can also enable Oracle DB instance to shutdown and startup with the service shutdown/startup. On *NIX you don't have services and so such restrictions.
Database not Opening
We have an amazing situation that we don't have the lastdays backup (as backup was not properly taken up)
The database gets mounted but don't get opened as one of the data file is missing.
Is there any way to recover the datafile and make the oracle server live once again.
Good question! There are ways to bring the DB up.
*********
SVRMGR> alter database datafile '/u01/oradata/ORCL/data/users01.dbf' offline drop;
SVRMGR> alter database open;
SVRMGR> drop tablespace users including contents;
*********
In the first stmt you can either use DROP or just use OFFLINE.It totally depends on what kind of data is out in that datafile.If un-necessary data then just drop it...if u happen to have valuable data then you can bring it offline and try to recover the DB.
Also you haven't mentioned if you are running a COLD or HOT bkp ?
Do you at least have an EXPORT.dmp of the tablespace?
If you have then just export the Tablespace.
I am not sure this might workout for your scenario but it may bring the DB up for now.
Thanks a lot , by taking datafiles offline , server could be opened and is now live.
but all the files taken offline are showing files 0 kb size.
Was it due to virus or anyother reason?
and these files now require auto recovery.
can these files be repaired now
However thanks for the advice
I ran the following command and the output came like this
Can you explain me the reason for the following output and also how the files can be recovered.
SQL> set autorecovery on;
SQL> recover automatic datafile 11;
ORA-00283: recovery session canceled due to errors
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'E:\ORACLE\ORADATA\HHML\D_HH_ATEND.ORA'
Seems like the only option in your scenario would be dropping the tablespace and re-creating a new one.
If you happen to have an export dump for the tablespace or objects in that tspace just import them after creating a new ts.
Subscribe to:
Posts (Atom)
