This document to indicate the steps required to re-install a backup from the designer repository
Two scenario's are possible:
- import into a new schema
- import into an old schema - first clean the schema
logic:
B
A --< > D --> B --> D .....
C
A. Decide on the scenario
The first thing to do is to decide on wheather to reinstall the existing schema or create a new one. The result of the decision will depend on the reason for reinstallation.
If you are reinstalling from scratch you will need to create everything again.
In case something went wrong or as for the reason I am currently writing this guide, with a patchset, it may come out handy to keep the complete schema and create a new one too.
If however you created a new environment and something went wrong during the import for example, you will need to clean up the schema again.
B. Clean the schema
In order to reinstal in the same schema, all the objects of the schema must be dropped.
Run the next script and make sure the number of bytes in the dbms_output buffer is high enough.
set serveroutput on
declare
cursor c_obj
is
select object_name
, object_type
from user_objects
where object_type in ('TABLE', 'VIEW', 'PACKAGE', 'SEQUENCE', 'FUNCTION', 'SYNONYM', 'TYPE')
order by decode ( object_type
, 'FUNCTION', 1
, 'PACKAGE', 2
, 'SEQUENCE', 3
, 'VIEW', 4
, 'SYNONYM', 5
, 'TABLE', 8
, 'TYPE', 9
)
, object_name
;
begin
--
for r_obj in c_obj
loop
if ( r_obj.object_type = 'TABLE' )
then
dbms_output.put_line ( 'Drop '
|| r_obj.object_type
|| ' '
|| r_obj.object_name
|| ' cascade constraint purge;'
);
else
dbms_output.put_line ( 'Drop '
|| r_obj.object_type
|| ' '
|| r_obj.object_name
|| ';'
);
end if;
end loop;
--
end;
Paste the output into a new script file and ... execute this script.
Repeat this process until only two objects remain.
B. Create a new environment
B.0 Create the tablespaces
First create the tablespaces that are required.
Here are scripts ... change where needed
create tablespace designer_data datafile 'd:\oracle\product\10.2.0\oradata\jhelling\designer_data1.dbf' size 200M autoextend on next 100M maxsize 512M / create tablespace designer_index datafile 'd:\oracle\product\10.2.0\oradata\jhelling\designer_index1.dbf' size 200M autoextend on next 100M maxsize 512M /
B.1 Create the user
Alternatively you can create a new repository owner.
create user des identified by oradev default tablespace users temporary tablespace temp;
alter user des default tablespace designer_data;
B.2 Assign the correct roles:
grant connect, resource to des;
-- grant dba to des; // not realy needed so leave it out
B.3 Give system privileges
grant alter session to des;
grant create any synonym to des;
grant drop any synonym to des;
grant create database link to des;
grant create procedure to des;
grant create role to des;
grant create sequence to des;
grant create table to des;
grant create trigger to des;
grant create type to des;
grant create view to des;
grant select any sequence to des;
grant unlimited tablespace to des;
B.4 Give sys privileges
grant select on dba_rollback_segs to des;
grant select on dba_segments to des;
grant execute on dbms_lock to des;
grant execute on dbms_pipe to des;
grant execute on dbms_rls to des;
grant select on v_$nls_parameters to des;
grant select on v_$parameter to des;
C Importing the dump file.
C.1 Create types.
When creating two repositories on the same database while using imp, the import will fail during the first lines with the error ORA-2304 which is caused by the 'stupid' constraint that object types must have a unique ID within the same database instance. See herefor notes 1066139.1 and 113335.
Currently looking for a workaround ...
These are the types that are defined in script jrtypes.sql in the repadmxx directory of the installation. Seems that the export of the designer does not take these types as user objects and they are therefor lost in the export (create any type privilege)
The content of the scripts is as follows :
create or replace type jr_num_list as table of number
/
create or replace type jr_str_list as table of varchar2(300)
/
create or replace type sdd_acc_priv_list as varray(25) of varchar(1)
/
create or replace type sdd_sys_priv_list as varray(25) of varchar(1)
/
C.x Create role CKR_DESIGNER
Create this role before import because a lot of grants are done to this role.
C.2 Verify tablespaces
This is a step you should not foreget, surely when you are installing an additional schema.
As you will have noticed I explicitely changed the default tablespace of the owner to designer_data. I use to have an other tablespace designer_indexes.
The import will assume the tablespaces exist so, verify into detail what your requirements on this will be.
C.3 Start the import
Now it is time to import the backup-export from the designer owner.
Here you can use the following command
imp userid=system/xxxx@xxxxx fromuser=designer touser=des file=desdump.dmp log=import.log
In this example you should specify the system connect string, the correct owner of the exported objects (fromuser) and the future owner of the newobjects (touser). The filename, desdump.dmp, should be changed to the correct value.
Start this command.
In parallel you will be able to find the results of the import in the import.log file.
D Upgrade to the correct version.
If your software is of a higher version than the one loaded ...
D.1 Be sure to use the correct imp.exe.
Before you do so however, you should check if it is not required to perform the steps indicated in the chapter on import problems. See below for this.
D.2 Upgrade the repository
Go the RAU (repository administration utility)
Normally you will only see some buttons enabled if the version of the repository is not compatible with the version of the designer software installation.
Press the upgrade button and the upgrade of the repository will start.
D.3 Verify all the objects.
On the RAU press the 'view objects' button and select then 'all objects'.
You should see the number 5711 of objects.
When you select the invalid and missing objects from the dropdown, no objects may be shown.
Problems encountered
IMP hangs when upgrading the repository
When upgrading repository the import hangs
I guess this is documented in note 5236740 bug 5236740 base bug 5225496
The import loaded data in two tables and then stopped ... and waits ...
I clicked in the cmd window and pressed ctrl-c ... which made the import stop and the real upgrade of the repository start.
These are the symptoms of the problem.
The solution is to set registry variables to point to an other version of imp and exp.
It comes to this:
Lookup the registry key for the iDS s
- HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_\REPOS61
Now change the values of the string values EXECUTE_EXPORT and EXECUTE_IMPORT to the full path of imp.exe and exp.exe to be used, so the one in the different Oracle_Home.
Be aware that the path for this variable can be too long. You should than solve this by creating a subst drive ...
subst :
for example subst x: D:\Oracle\product\10.2.0\db_1
The command to put into the EXECUTE_EXPORT and EXECUTE_IMPORT entries becomes rather simple : x:\bin\exp.exe and x:\bin\imp.exe
Remember however that a 'subst'ed drive is only valid until you reboot the windows OS.
ORA-28100
While opening my ER diagram I got the error ora-28100.
The sql statement that failed was:
SELECT cel.id, cel.ivid, cel.name, cel.container_subtype FROM ci_container_elements cel WHERE cel.root_flag = 'Y' ORDER BY 3;
Running this script in sql gave the following error:
ORA-28100: policy function schema DESIGNER is invalid
The original owner of the designer repostitory, so the one owning the original objects, was designer. I imported the export into the des schema.
On metalink you can look for note 602437.
Execute the following sql statement (change designer in your provious repository owner)
select * from dba_policies where pf_owner = 'DESIGNER'
No comments:
Post a Comment