Archive for the 'Backup & Recovery' Category

July 14th 2012

ORA-02304: invalid object identifier literal

Error tersebut didapat ketika melakukan import dumpfile ke user yang berbeda pada database yang sama. Bila dilihat pada import log, error terjadi pada saat melakukan import object TYPE. Error lengkapnya seperti berikut :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import server uses AL32UTF8 CHARACTER SET (possible charset conversion)
 IMP-00017: following statement failed WITH ORACLE error 2304:
 "CREATE TYPE "VOUCHER_TY" TIMESTAMP '2011-11-25:15:05:54' OID '4E7DE9FF18F58"
 "182E040007F01006FA7'                                                       "
 "                     as object  ("
 "  amount                                      number(3)"
 ", name                                        varchar2(12)"
 ")"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed WITH ORACLE error 2304:
 "CREATE TYPE "LOCATION_TY" TIMESTAMP '2011-11-25:15:05:54' OID '4E7DE9FF1901"
 "8182E040007F01006FA7'                                                      "
 "                      as object  ("
 "  location_id                        varchar2(3)"
 ", location_name                   varchar2(20)"
 ")"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal

Hal tersebut terjadi object TYPE yaitu LOCATION_TY dan VOUCHER_TY sudah memiliki object identifier, ketika melakukan export maka object identifier tersebut ikut terbawa. Pada saat melakukan import, maka akan terjadi konflik karena pada object TYPE LOCATION_TY dan VOUCHER_TY sudah terdapat object identifier yang sebelumnya di export. Type tersebut tidak akan tercreate dan akan muncul error ORA-02304. Hal ini hanya terjadi ketika kita melakukan export/import dengan user berbeda pada database yang sama.

Untuk mengantisipasi hal tersebut, pada utilitas impdp terdapat option TRANSFORM=oid:n. Dengan menggunakan option tersebut maka object identifier (OID) tidak akan diimport dengan kata lain maka untuk object type tersebut akan tergenerate object identifier yang baru.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
[oracle@svrdb01 dump]$ impdp vasacct/vasacct remap_schema=vasacct:vasacct01 transform=oid:n directory=vasacct dumpfile=vasacct.dmp logfile=vasacct.imp.20120714.log
 
Import: Release 11.1.0.6.0 - Production ON Saturday, 14 July, 2012 14:56:28
 
Copyright (c) 2003, 2007, Oracle.  ALL rights reserved.
 
Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.1.0.6.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
Master TABLE "VASACCT01"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "VASACCT01"."SYS_IMPORT_FULL_01":  interacct03/******** remap_schema=vasacct:vasacct01 transform=oid:n directory=vasacct dumpfile=vasacct.dmp logfile=vasacct.imp.20120714.log 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"VASACCT01" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "VASACCT01"."PT_CDR"                      1.813 GB 11060716 rows
. . imported "VASACCT01"."CDR_DETAIL":"CDR_DETAIL_P6"  82.86 KB     590 rows
. . imported "VASACCT01"."CDR_DETAIL":"CDR_DETAIL_P7"  9.558 MB   85425 rows
. . imported "VASACCT01"."USSD_USER"                   9.023 KB       4 rows
. . imported "VASACCT01"."CDR_LAYER3"                  31.16 MB  240743 rows
. . imported "VASACCT01"."STATS"                       24.34 MB  598903 rows
. . imported "VASACCT01"."ONE_CR_CDR"                  6.285 MB   91321 rows
. . imported "VASACCT01"."CDR_LAYER3_TMP"              4.379 MB   29456 rows
. . imported "VASACCT01"."SMS_BLAST_TMP"               158.3 KB    8883 rows
. . imported "VASACCT01"."CDR_CONTROL"                 121.0 KB     679 rows
. . imported "VASACCT01"."SNMP_TRAP_ALERT"             13.48 KB      39 rows
. . imported "VASACCT01"."RVR_STATUS"                  227.9 KB     317 rows
. . imported "VASACCT01"."PARAMETER_LOOKUP_TABLE"      7.414 KB       4 rows
. . imported "VASACCT01"."ACCESS_GROUP"                7.210 KB       3 rows
. . imported "VASACCT01"."ACCOUNT"                     6.601 KB       9 rows
. . imported "VASACCT01"."BUSINESS_INTERFACE"          9.156 KB      13 rows
. . imported "VASACCT01"."CDR_LAYER2"                  18.42 KB      40 rows
. . imported "VASACCT01"."CLIENT"                      5.710 KB      14 rows
. . imported "VASACCT01"."DIALOG_TYPE"                 7.101 KB      11 rows
. . imported "VASACCT01"."EAP_PAYEE"                   5.523 KB       4 rows
. . imported "VASACCT01"."EMAIL_ISP"                   7.765 KB       4 rows
. . imported "VASACCT01"."ERROR_CODE"                  5.687 KB       5 rows
. . imported "VASACCT01"."HISTORY"                     19.82 KB      82 rows
. . imported "VASACCT01"."LANGUAGES_SUPPORTED"         5.468 KB       3 rows
. . imported "VASACCT01"."NETWORK_CONNECTION"          6.406 KB       3 rows
. . imported "VASACCT01"."NETWORK_CONNECTION_USSD"     10.65 KB       3 rows
. . imported "VASACCT01"."NETWORK_OPERATOR"            5.554 KB       4 rows
. . imported "VASACCT01"."NETWORK_PROTOCOL"             6.75 KB      16 rows
. . imported "VASACCT01"."NETWORK_RESOURCE"            6.843 KB       2 rows
. . imported "VASACCT01"."NETWORK_RESOURCE_GROUP"      5.515 KB       1 rows
. . imported "VASACCT01"."NETWORK_RESOURCE_TCPIP"      12.85 KB       2 rows
. . imported "VASACCT01"."NETWORK_RESOURCE_TYPE"       5.515 KB       2 rows
. . imported "VASACCT01"."PARAMETER"                   7.406 KB       8 rows
. . imported "VASACCT01"."REC_RECEIVE_CONTROL"         6.875 KB       2 rows
. . imported "VASACCT01"."REC_RECEIVING"               8.109 KB       1 rows
. . imported "VASACCT01"."REC_SENDING"                 12.77 KB      31 rows
. . imported "VASACCT01"."REC_SEND_CONTROL"            8.296 KB       2 rows
. . imported "VASACCT01"."RVR_CDR_BEFORE_FILTER"       6.523 KB       4 rows
. . imported "VASACCT01"."RVR_RECON_HISTORY"           6.921 KB       4 rows
. . imported "VASACCT01"."RVR_STATUS_HISTORY"          8.484 KB      36 rows
. . imported "VASACCT01"."SERVICE"                     10.52 KB       7 rows
. . imported "VASACCT01"."SERVICE_DIALOG"              26.04 KB      45 rows
. . imported "VASACCT01"."SERVICE_TEMP"                10.57 KB      19 rows
. . imported "VASACCT01"."STATS_SUMMARY"               8.921 KB      19 rows
. . imported "VASACCT01"."SYSTEM_PARAMETER"            6.085 KB       5 rows
. . imported "VASACCT01"."TCU_ERRORCODES"              7.546 KB      57 rows
. . imported "VASACCT01"."TEMP_CLIENT"                 5.718 KB      14 rows
. . imported "VASACCT01"."TEMP_NETWORK_RESOURCE_TCPIP"  12.84 KB       7 rows
. . imported "VASACCT01"."TRANSACTION_TYPE"            5.562 KB       6 rows
. . imported "VASACCT01"."TRX_ID_FROM_HISTORY"         6.523 KB       4 rows
. . imported "VASACCT01"."USERS"                       9.570 KB       7 rows
. . imported "VASACCT01"."USERS_SETTING"               11.24 KB       1 rows
. . imported "VASACCT01"."USERS_TYPE"                  7.656 KB       4 rows
. . imported "VASACCT01"."USSD_USER_ACCOUNT"           5.710 KB       9 rows
. . imported "VASACCT01"."USSD_USER_EMAIL_PROFILE"     7.625 KB       2 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"VASACCT01"."PROCESS_CDR" created with compilation warnings
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "VASACCT01"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 14:57:52

Bila kita lihat pada dba_types, maka object identifier pada schema asal dengan schema hasil import berbeda.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL> SELECT owner,type_name,type_oid FROM dba_types WHERE owner IN ('VASACCT','VASACCT01');
 
OWNER                          TYPE_NAME
------------------------------ ------------------------------
TYPE_OID
--------------------------------
VASACCT                      LOCATION_TY
4E7DE9FF19018182E040007F01006FA7
 
VASACCT                      VOUCHER_TY
4E7DE9FF18F58182E040007F01006FA7
 
 
OWNER                          TYPE_NAME
------------------------------ ------------------------------
TYPE_OID
--------------------------------
VASACCT01                   LOCATION_TY
C4C588F7C091D912E040007F01007B92
 
VASACCT01                   VOUCHER_TY
C4C588F7C087D912E040007F01007B92
 
6 ROWS selected.

No Comments yet »

December 19th 2011

Migrasi database cross platform menggunakan RMAN Convert Database

Migrasi dilakukan dari platform Windows 32 bit ke Linux 32 bit dengan versi database 10.2.0.3 pada mesin source dan 10.2.0.5 pada mesin target. Migrasi menggunakan RMAN Convert database mensyaratkan untuk setting database dalam keadaan read only, disamping itu endian format harus sesuai/sama antara platform source dengan target . Berikut ini tahapan-tahapan yang dilakukan :

Pada mesin source

1. Cek endian format apakah bisa dilakukan migrasi dengan beda platform.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> SELECT platform_name,endian_format FROM v$transportable_platform ORDER BY endian_format;
 
PLATFORM_NAME                            ENDIAN_FORMAT
---------------------------------------- --------------------
HP-UX IA (64-bit)                        Big
Solaris[tm] OE (32-bit)                  Big
Apple Mac OS                             Big
HP-UX (64-bit)                           Big
IBM zSeries Based Linux                  Big
AIX-Based Systems (64-bit)               Big
Solaris[tm] OE (64-bit)                  Big
IBM POWER Based Linux                    Big
Solaris Operating System (x86)           Little
Microsoft Windows 64-bit FOR AMD         Little
Linux 64-bit FOR AMD                     Little
 
PLATFORM_NAME                            ENDIAN_FORMAT
---------------------------------------- --------------------
Microsoft Windows IA (64-bit)            Little
HP OPEN VMS                              Little
HP Tru64 UNIX                            Little
Linux IA (32-bit)                        Little
Microsoft Windows IA (32-bit)            Little
Linux IA (64-bit)                        Little
 
17 ROWS selected.

2. Ubah database menjadi read only.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> conn /AS sysdba
SQL> shutdown immediate;
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area  184549376 bytes
Fixed SIZE                  1272696 bytes
Variable SIZE              83887240 bytes
DATABASE Buffers           96468992 bytes
Redo Buffers                2920448 bytes
DATABASE mounted.
SQL> ALTER DATABASE OPEN READ ONLY;
 
DATABASE altered.

3. Cek apakah database bisa dilakukan migrasi ke platform tujuan.

1
2
3
4
5
6
7
8
9
SQL> SET serveroutput ON
SQL> DECLARE
  2  db_ready BOOLEAN;
  3  BEGIN
  4  db_ready := dbms_tdb.check_db('Windows IA (32-bit)');
  5  END;
  6  /
 
PL/SQL PROCEDURE successfully completed.

Bila checking sukses (procedure successfully completed), artinya siap untuk dilakukan transport.

4. Cek apakah terdapat external table, BFILE. Convert database tidak akan bisa berjalan bila terdapat object tersebut.

1
2
3
4
5
6
7
8
9
10
11
SQL> SET serveroutput ON
SQL> DECLARE
  2  external BOOLEAN;
  3  BEGIN
  4  external := dbms_tdb.check_external;
  5  END;
  6  /
The following directories exist IN the DATABASE:
SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR
 
PL/SQL PROCEDURE successfully completed.

5. Setelah semua tahapan check sudah dilakukan, lalu jalankan perintah convert database ke platform tujuan.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
C:\> rman target / nocatalog
 
Recovery Manager: Release 10.2.0.3.0 - Production ON Sun DEC 4 03:26:37 2011
 
Copyright (c) 1982, 2007, Oracle.  ALL rights reserved.
 
connected TO target DATABASE: DBVPX (DBID=2023668327)
USING target DATABASE control file instead OF recovery catalog
 
RMAN> CONVERT DATABASE NEW DATABASE 'DBVPX'
2> TRANSPORT SCRIPT 'C:\transport\transport.sql'
3> TO PLATFORM 'Linux IA (32-bit)'
4> DB_FILE_NAME_CONVERT 'C:\oradata\dbvpx' 'C:\transport';
 
Starting CONVERT at 04-DEC-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
 
Directory SYS.ORACLE_OCM_CONFIG_DIR found IN the DATABASE
Directory SYS.DATA_PUMP_DIR found IN the DATABASE
 
USER SYS WITH SYSDBA AND SYSOPER privilege found IN password file
USER AGUS WITH SYSDBA privilege found IN password file
channel ORA_DISK_1: starting datafile conversion
INPUT datafile fno=00001 name=C:\oradata\dbvpx\system01.dbf
converted datafile=C:\transport\system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed TIME: 00:00:35
channel ORA_DISK_1: starting datafile conversion
INPUT datafile fno=00003 name=C:\oradata\dbvpx\sysaux01.dbf
converted datafile=C:\transport\sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed TIME: 00:00:25
channel ORA_DISK_1: starting datafile conversion
INPUT datafile fno=00005 name=C:\oradata\dbvpx\vasdata.dbf
converted datafile=C:\transport\vasdata.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed TIME: 00:00:07
channel ORA_DISK_1: starting datafile conversion
INPUT datafile fno=00002 name=C:\oradata\dbvpx\undotbs01.dbf
converted datafile=C:\transport\undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed TIME: 00:00:03
channel ORA_DISK_1: starting datafile conversion
INPUT datafile fno=00004 name=C:\oradata\dbvpx\users01.dbf
converted datafile=C:\transport\users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed TIME: 00:00:01
Run SQL script C:\transport\transport.SQL ON the target platform TO CREATE DATABASE
Edit init.ora file C:\oracle\product\10.2.0\db_1\DATABASE\init_00mtb5oo_1_0.ora. This PFILE will be used TO CREATE the DATABASE ON the target platform
TO recompile ALL PL/SQL modules, run utlirp.SQL AND utlrp.SQL ON the target platform
TO CHANGE the internal DATABASE identifier, USE DBNEWID Utility
Finished backup at 04-DEC-11
 
RMAN>

6. Bila convert database sudah selesai dilakukan, kembalikan database menjadi mode open.

Pada mesin target

1. Copy seluruh file yang berada di direktori transport di mesin source (datafile,transport script, parameter file) ke mesin target.

2. Lakukan perubahan path pada parameter INIT file dan TRANSPORT.SQL

Sample isi TRANSPORT.SQL yang lokasinya harus disesuaikan di mesin target

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
 
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
 
STARTUP NOMOUNT PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00MUFGQK_1_0.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "DBVPX" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-DBVPX_ID-3343724400_S-2_T-1_A-770161535_00MUFGQK'  SIZE 50M,
  GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-DBVPX_ID-3343724400_S-0_T-1_A-770161535_00MUFGQK'  SIZE 50M,
  GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-DBVPX_ID-3343724400_S-1_T-1_A-770161535_00MUFGQK'  SIZE 50M
DATAFILE
  'C:\TRANSPORT\SYSTEM01.DBF',
  'C:\TRANSPORT\UNDOTBS01.DBF',
  'C:\TRANSPORT\SYSAUX01.DBF',
  'C:\TRANSPORT\USERS01.DBF',
  'C:\TRANSPORT\VPX01.DBF',
  'C:\TRANSPORT\VPXIDX01.DBF'
CHARACTER SET WE8MSWIN1252
;
 
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
 
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-DBVPX_I-3343724400_TS-TEMP_FNO-1_00MUFGQK'
     SIZE 20971520  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--
 
SET echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your DATABASE has been created successfully!
prompt * There are many things TO think about FOR the NEW DATABASE. Here
prompt * IS a checklist TO help you stay ON track:
prompt * 1. You may want TO redefine the location OF the directory objects.
prompt * 2. You may want TO CHANGE the internal DATABASE identifier (DBID) 
prompt *    OR the global DATABASE name FOR this DATABASE. USE the 
prompt *    NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
SHUTDOWN IMMEDIATE 
STARTUP UPGRADE PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00MUFGQK_1_0.ORA'
@@ ?/rdbms/admin/utlirp.SQL 
SHUTDOWN IMMEDIATE 
STARTUP PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00MUFGQK_1_0.ORA'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.SQL 
SET feedback 6;

3. Setelah itu, masuk sebagai sysdba, lalu jalankan file TRANSPORT.SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL*Plus: Release 10.2.0.5.0 - Production ON Thu DEC 8 06:56:39 2011
 
Copyright (c) 1982, 2010, Oracle.  ALL Rights Reserved.
 
SQL> conn /AS sysdba
Connected TO an idle instance.
SQL> @TRANSPORT.SQL
ORACLE instance started.
 
Total System Global Area  268435456 bytes
Fixed SIZE                  1273176 bytes
Variable SIZE              88081064 bytes
DATABASE Buffers          176160768 bytes
Redo Buffers                2920448 bytes
 
Control file created.
 
ALTER DATABASE OPEN RESETLOGS

4. Database siap digunakan di platform yang baru.

Incoming search terms:

  • migrasi data
  • apakah artinya disable platform
  • migrasi data dengan 3rd party tools

No Comments yet »

November 25th 2010

restore backup

Beberapa hari ini ada kerjaan besar terkait persiapan patch database ldap, dikarenakan versi yang sekarang digunakan (10.2.0.3) terdapat bugs. Kasusnya sendiri terjadi saat dini hari di idul adha saat database tiba-tiba mati, paginya ketika dicheck alert log dan trace filenya ternyata error ORA-00600 (internal error). Search di metalink, ternyata solusinya yaitu harus di patch ke versi 10.2.0.4. Setelah meeting dengan customer, maka sebelum dilakukan patch di mesin production harus dilakukan testing terlebih dahulu di mesin development untuk mengetahui downtime yang bakal terjadi. Maka dicloning lah database production ke mesin development as is data hari ini. Agar tidak lupa, berikut ini catatan saat melakukan restore di mesin development :

1. Copykan data hasil backup yang ada di mesin production ke development (incremental level 0 dan controlfile nya).

2. Connect ke RMAN lalu setelah itu startup nomount database development.

Copyright (c) 1982, 2005, Oracle. All rights reserved.

bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Nov 25 15:36:48 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 17179869184 bytes

Fixed Size 2158608 bytes

Variable Size 13918605296 bytes

Database Buffers 3254779904 bytes

Redo Buffers 4325376 bytes

3. Restore controlfile hasil copy dari backup production tersebut (jangan lupa untuk menyesuaikan path dimana hasil backup tersebut ditaruh di mesin development).

RMAN> restore controlfile from '/archivelog/ctl_c-3604205073-20101123-01';

Starting restore at 25-NOV-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=1102 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output filename=/oradata10/nldapdb/control01.ctl

output filename=/oradata10/nldapdb/control02.ctl

output filename=/oradata10/nldapdb/control03.ctl

Finished restore at 25-NOV-10

4. Mounting database ke status mount.

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> exit;

Recovery Manager complete.

5. Setelah controlfile direstore, lalu kemudian dilanjutkan untuk melakukan restore datafile. Buat file restore.rcv lalu kemudian copy script untuk melakukan restore database :

run

{

allocate channel dbrestore1 type disk;

allocate channel dbrestore2 type disk;

allocate channel dbrestore3 type disk;

allocate channel dbrestore4 type disk;

restore database;

recover database;

release channel dbrestore1;

release channel dbrestore2;

release channel dbrestore3;

release channel dbrestore4;

}

6. Bila file restore.rcv sudah terbentuk, jalankan rman lagi untuk melakukan restore datafile tersebut. Sebaiknya rman dijalankan menggunakan nohup (background process) karena akan memakan waktu yang lama (apalagi datanya 1,3 TB). Jangan lupa buatkan log nya juga biar bisa melakukan monitor..

-bash-3.00$ nohup /oracle/app/product/10.2.0/db_1/bin/rman target / cmdfile='restore.rcv' log='restore.log' &

[1] 11909

-bash-3.00$ Sending output to nohup.out

-bash-3.00$ ps -ef | grep rman

oracle 11925 6807 0 10:22:34 pts/3 0:00 grep rman

oracle 11909 6807 1 10:22:29 pts/3 0:03 /oracle/app/product/10.2.0/db_1/bin/rman target / cmdfile=restore.rcv log=resto

-bash-3.00$ tail -f restore.log

restoring datafile 00076 to /oradata6/ldap/ldapdb/gcats7_oid.dbf

restoring datafile 00078 to /oradata6/ldap/ldapdb/ldapidx23.dbf

channel dbrestore3: reading from backup piece /VT2/backup/database/INCR0_NLDAPDB_15695_1_735783309

channel dbrestore4: starting datafile backupset restore

channel dbrestore4: specifying datafile(s) to restore from backup set

restoring datafile 00062 to /oradata7/ldap/ldapdb/attrs16_oid.dbf

restoring datafile 00083 to /oradata9/ldap/ldapdb/gdefault5_oid.dbf

restoring datafile 00105 to /oradata11/ldap/ldapdb/ldapidx36.dbf

restoring datafile 00112 to /oradata10/ldap/ldapdb/attrs21_oid.dbf

channel dbrestore4: reading from backup piece /VT2/backup/database/INCR0_NLDAPDB_15694_1_735783309

Incoming search terms:

  • cara restore database oracle 11g
  • bagaimana cara backup database oracle

No Comments yet »

August 12th 2010

RMAN ( Catalog )

1). Set Catalog

Untuk menggunakan catalog kita harus membuat terlebih dahulu user catalog yang akan digunakan.

CREATE USER rman IDENTIFIED BY password

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED TO users;

GRANT RECOVERY_CATALOG_OWNER TO rman;

2). Create Catalog

Setelah membuat user rman, maka tahap selanjutnya yaitu membuat rman catalog.

rman CATALOG rman/password@rman_sid

CREATE CATALOG

3). Register Database

Database target yang akan dimonitor / dibackup menggunakan catalog harus diregister terlebih dahulu.

rman TARGET sys/oracle@orcl CATALOG rman/passwd@rman_sid

REGISTER DATABASE

4). Check register database

Setelah melakukan register database target kedalam catalog, kita dapat melihat informasi dari database target tersebut menggunakan catalog. Contohnya, kita dapat melihat tablespace schema :

rman TARGET sys/oracle@orcl CATALOG rman/passwd@rman_sid

REPORT SCHEMA

5). Unregister database

Untuk melakukan unregister database menggunakan script dibawah :

rman TARGET sys/oracle@orcl CATALOG rman/passwd@rman_sid

UNREGISTER DATABASE

6). Unregister Catalog

Untuk menghapus catalog menggunakan script berikut :

rman CATALOG rman/passwd@rman_sid

DROP CATALOG

7). Export Catalog

Setelah catalog dibuat dan database target diregister ke dalam catalog, ada baiknya untuk melakukan backup terhadap catalog dengan menggunakan export.

exp rman/passwd@rman_sid file=catalog.dmp owner=rman

No Comments yet »