Duplicate/Move/Import/Rename Schema

I was asked to copy two schemas from two databases into another.

Problem: The copies would have different names and use other Tablespaces

Solution: Export via Datapump (expdp schema=schema_name [...]) and import with remap_schema and remap_tablespace parameter:

impdp schemas=schema_name remap_schema=schema_name:new_schema_name remap_tablespace=tablespace_name:new_tablespace_name [...]

Create Job: Oracle 10g

Today I researched something, our Apprentice asked.

To create an automatic job in Oracle 10g we will use the DBMS_SCHEDULER package.

First you create a program, that holds the activity you wish to perform

SQL> BEGIN
2 SYS.DBMS_SCHEDULER.CREATE_PROGRAM
3 (
4 program_name => 'SYS.TEST_JOB_PROG',
5 program_type => 'PLSQL_BLOCK',
6 program_action => 'begin
7 update test_job_table set LOHN=LOHN+100 where name=''Heinz''; -- Zwei single quotes!
8 end;'
9 number_of_arguments => 0,
10 enabled => TRUE,
11 comments => 'Test increase of table value',
12 );
13 /

PL/SQL procedure successfully completed.

Then you create the schedule:

SQL> begin
2 dbms_scheduler.create_schedule(
3 repeat_interval => 'FREQ=HOURLY;BYMINUTE=5,10,15,20,25,30,35,40,45,50,55,0', -- Erläuterungen
4 schedule_name => '"test_job_sched"');
5 end;
6 /

PL/SQL procedure successfully completed.

In the end you put connect the program with the schedule in a job

SQL> begin
2 dbms_scheduler.create_job(
3 job_name=> '"test_job_job"',
4 program_name=>'test_job_prog',
5 schedule_name=>'"test_job_sched"',
6 job_class=>'DEFAULT_JOB_CLASS',
7 auto_drop=>FALSE,
8 enabled=>TRUE);
9 end;
10 /

PL/SQL procedure successfully completed.

Source

Listener service problem

Dynamic listener was not able to register a service for a database that had the local_listener parameter set to another server in the spfile. Resulting in a ORA-12505 when trying to log on remotely. Unsetting the parameter solved the problem.

Refreshes

Refreshed two Test Databases from Production. Prod is 10.2.0.4, test were 10.2.0.3.

Notes:
- Target & Destination DB must be same version
- Don't forget to set the compatible parameter to the right version if you changed it.
- Set the source host (NB_ORA_CLIENT=source_host), i.e. where the backup was taken, in the .rcv file in when you define the backup channel.