In today’s article, I will cover you about the Data Import process and the parts to pay attention to in this process.
IMPORT PROCESS
1. We drop the table we exported from the Scott schema.
|
1 |
SQL> drop table scott.emp; |
2. We start the import process with the Impdp tool.
|
1 |
-bash-3.2$ impdp emre/EMRE.123 directory=dump_dir dumpfile=emre.dmp logfile=emre_imp.log |
Points to Consider in the Import Process
1. First of all, it is learned whether there is enough space in the DB to be imported.
2. In the DB where we will import the data, the user of the table we exported must be found. Otherwise we will get an error like below.
|
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 |
-bash-3.2$ impdp oardahanli/Brc_Onr2516* directory=DATA_PUMP_DIR dumpfile=web_account.dmp Import: Release 11.2.0.4.0 - Production on Fri Jun 19 13:18:23 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "OARDAHANLI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "OARDAHANLI"."SYS_IMPORT_FULL_01": oardahanli/******** directory=DATA_PUMP_DIR dumpfile=web_account.dmp Processing object type TABLE_EXPORT/TABLE/TABLE ORA-39083: Object type TABLE:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" failed to create with error: ORA-01918: user 'WEBTELMWCORE' does not exist Failing sql is: CREATE TABLE "WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" ("ACC_UID" NUMBER, "NOTIF_DATE" DATE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_ Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT ORA-39112: Dependent object type OBJECT_GRANT:"WEBTELMWCORE" skipped, base object type TABLE:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" creation failed Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX ORA-39112: Dependent object type INDEX:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION_PK" skipped, base object type TABLE:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" creation failed Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION_PK" creation failed Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" creation failed Job "OARDAHANLI"."SYS_IMPORT_FULL_01" completed with 5 error(s) at Fri Jun 19 13:18:27 2015 elapsed 0 00:00:02 |
The following query is run to remove the error.
|
1 2 3 |
CREATE USER WEBTELMWCORE IDENTIFIED BY "webtelmwcore"DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CREATE SESSION TO WEBTELMWCORE; GRANT RESOURCE TO WEBTELMWCORE; |
2. In the DB from which we will import the data, the tablespace from which we exported must have a tablespace. If not, we will get an error as below.
|
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 |
-bash-3.2$ impdp oardahanli/Brc_Onr2516* directory=DATA_PUMP_DIR dumpfile=web_account.dmp Import: Release 11.2.0.4.0 - Production on Fri Jun 19 13:23:45 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "OARDAHANLI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "OARDAHANLI"."SYS_IMPORT_FULL_01": oardahanli/******** directory=DATA_PUMP_DIR dumpfile=web_account.dmp Processing object type TABLE_EXPORT/TABLE/TABLE ORA-39083: Object type TABLE:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" failed to create with error: ORA-00959: tablespace 'WEBTVNEW' does not exist Failing sql is: CREATE TABLE "WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" ("ACC_UID" NUMBER, "NOTIF_DATE" DATE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLAS Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT ORA-39112: Dependent object type OBJECT_GRANT:"WEBTELMWCORE" skipped, base object type TABLE:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" creation failed Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX ORA-39112: Dependent object type INDEX:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION_PK" skipped, base object type TABLE:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" creation failed Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION_PK" creation failed Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" creation failed Job "OARDAHANLI"."SYS_IMPORT_FULL_01" completed with 5 error(s) at Fri Jun 19 13:23:48 2015 elapsed 0 00:00:01 |
The following query is run to remove the error.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLESPACE WEBTVNEW DATAFILE '/oracle/ora11g/data_ONURDB/ONURDB/webtvnew01.dbf' SIZE 1024M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON; |
3. We may get GRANT errors while importing the data. This is not so important. For example, errors like the following are received.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-bash-3.2$ impdp oardahanli/Brc_Onr2516* directory=DATA_PUMP_DIR dumpfile=web_account.dmp Import: Release 11.2.0.4.0 - Production on Fri Jun 19 13:33:46 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "OARDAHANLI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "OARDAHANLI"."SYS_IMPORT_FULL_01": oardahanli/******** directory=DATA_PUMP_DIR dumpfile=web_account.dmp Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" 2.583 MB 151525 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT ORA-39083: Object type OBJECT_GRANT failed to create with error: ORA-01917: user or role 'MW_ROLE1' does not exist Failing sql is: GRANT SELECT ON "WEBTELMWCORE"."ACCOUNT_SSO_NOTIFICATION" TO "MW_ROLE1" Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "OARDAHANLI"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Jun 19 13:33:50 2015 elapsed 0 00:00:03 |
Completing the Import Process
1. The import process is left in the following step.
2. It seems that it stays in “executing” even though the import is stopped. We saw this with the following query.
|
1 2 |
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1, 2; |
3. The database is requested to be stopped.
|
1 2 3 4 |
[oracle@mwdb1 ~]$ srvctl status database -d tivibudb Instance tivibudb1 is running on node mwdb1 Instance tivibudb2 is running on node mwdb2 [oracle@mwdb1 ~]$ srvctl stop database -d tivibudb |
4. The database did not stop. We look at the reason from the Alert Logs and understand that the reason is FRA.
NOTE: The reason why the database cannot be closed when the FRA field is full is because it wants to save it in Archive files to ensure the consistency of the transactions made at that moment. Because the checkpoint information in Controlfile, Datafile and Archivelog must be the same for consistent opening while opening.
5. The database smon process is killed and forced to shut down.
|
1 2 3 4 5 6 7 |
[oracle@mwdb2 ~]$ ps -ef | grep smon root 4911 1 0 2015 ? 00:26:09 /u01/app/grid/product/11.2.0/grid/bin/osysmond.bin oracle 5363 1 0 2015 ? 00:00:00 asm_smon_+ASM2 oracle 12482 1 0 16:36 ? 00:00:00 ora_smon_tivibudb2 oracle 21036 12330 0 17:11 pts/1 00:00:00 grep smon [oracle@mwdb2 ~]$ kill -9 12482 |
6. The database is closed. When we connect with “sqlplus / as sysdba”, we see that the instance is idle.
7. The database is opened in the nomount step. [Every 2 Nodes]
|
1 |
startup nomount; |
8. The deletion is performed.
|
1 2 3 |
rman target / delete noprompt archivelog all completed before 'sysdate-2'; |
9. Crosscheck is done.
|
1 2 3 4 5 6 7 |
RMAN> crosscheck archivelog all; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1003 instance=tivibudb1 device type=DISK ... Crosschecked 3371 objects |
NOTE: The crosscheck command checks whether the backup in the RMAN repository is physical on the disk or tape. If the file does not exist physically on the disk or tape (it may have been deleted through the operating system), RMAN repository marks the relevant backup as “EXPIRED”. Afterwards, backup information can be deleted from the RMAN repository with the “DELETE EXPIRED” command.
10. It is checked whether there is enough space.
|
1 |
select name, free_mb, total_mb from v$asm_diskgroup; |
If there is not enough space, we enter the fra field and check what can be deleted.
We connect to ASM with “asmcmd” to see what can be deleted from FRA with RMAN.
|
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 |
[root@mwdb1 ~]# su - grid su: user grid does not exist [Grid kullanıcısı olmadığı için kullanıcıya geçilemedi. Bu nedenle "oracle" kullanıcısı ile grid' in environment' ına geçilir ve asm' e bağlanılır. ] [root@mwdb1 ~]# su - oracle [oracle@mwdb1 ~]$ grid_env [oracle@mwdb1 ~]$ asmcmd ASMCMD> ls DATA/ FRA/ ASMCMD> cd fra ASMCMD> ls TIVIBUDB/ ASMCMD> cd tivibudb ASMCMD> ls ARCHIVELOG/ CONTROLFILE/ ONLINELOG/ ASMCMD> cd ARCHIVELOG ASMCMD> ls 2016_01_01/ 2016_01_02/ 2016_01_03/ 2016_01_04/ 2016_01_05/ 2016_01_06/ 2016_01_07/ ASMCMD> cd .. ASMCMD> ls ARCHIVELOG/ CONTROLFILE/ ONLINELOG/ ASMCMD> cd ONLINELOG ASMCMD> ls group_1.257.843241075 group_2.258.843241075 group_3.259.843241107 group_4.260.843241107 group_5.264.892999317 group_6.265.892999317 group_7.266.892999319 group_8.267.892999319 ASMCMD> |
11. We check whether the fields used in asm_diskgroup and v$recovery_file_dest are the same.
|
1 2 |
select name,(space_limit/1024/1024) "Size MB",(space_used/1024/1024) "Used MB" from v$recovery_file_dest order by name; |
|
1 2 3 4 5 6 7 8 |
[root@mwdb1 ~]# su - oracle [oracle@mwdb1 ~]$ grid_env [oracle@mwdb1 ~]$ asmcmd ASMCMD> ls -s Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name 512 4096 1048576 255996 101222 0 101222 0 Y DATA/ 512 4096 1048576 153597 114655 0 114655 0 N FRA/ ASMCMD> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
RMAN> delete expired archivelog all; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1003 instance=tivibudb1 device type=DISK ... archived log file name=+FRA/tivibudb/archivelog/2015_12_31/thread_2_seq_6773.2636.899936537 RECID=2909 STAMP=899936538 deleted archived log archived log file name=+FRA/tivibudb/archivelog/2015_12_31/thread_2_seq_6774.2637.899940475 RECID=2910 STAMP=899940474 Deleted 2364 EXPIRED objects ORA-00245: control file backup failed; target is likely on a local file system RMAN-08132: WARNING: cannot update recovery area reclaimable file list |
|
1 |
alter database mount; |
14. The database opens. [Every 2 Nodes]
|
1 |
alter database open; |
