Database Technology: 2018

Thursday, October 4, 2018

Rman DDboost issue when starting the backup

Issue:
we have installed DDboost for rman backup configuration and the setup completed successfully,However when testing the backup got the below error.

allocated channel: c1
channel c1: SID=77 device type=SBT_TAPE
channel c1: Data Domain Boost API

Starting backup at 02-OCT-18
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 02-OCT-18
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c1 channel at 10/02/2018 11:38:37
ORA-19506: failed to create sequential file, name="03telatp_1_1", parms=""
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: non RMAN, but media manager or vendor specific failure, error text:
sbtbackup: dd_rman_connect_to_backup_host failed

Root cause:
looks like some connection credential issStarting Control File and SPFILE Autobackup at 02-OCT-18

piece handle=LPMDMD_cf_c-292763313-20181002-01 comment=API Version 2.0,MMS Version 1.2.1.0

Solution:

make sure you have the feed the variable like the below.

RUN {

ALLOCATE CHANNEL t1 TYPE SBT_TAPE PARMS 'BLKSIZE=1048576, SBT_LIBRARY=$ORACLE_HOME/lib/libddobk.so,ENV=(STORAGE_UNIT=$STORAGE_UNIT,BACKUP_HOST=$DATADOMAIN_HOST,ORACLE_HOME=$ORACLE_HOME)' FORMAT '%U-%d';

send 'set username $DDBOOST_USER password $PASSWORD servername $DATADOMAIN_HOST';

RELEASE CHANNEL t1;

}

Starting Control File and SPFILE Autobackup at 02-OCT-18

piece handle=DB_cf_c-292763313-20181002-01 comment=API Version 2.0,MMS Version 1.2.1.0

Finished Control File and SPFILE Autobackup at 02-OCT-18

released channel: c1

Monday, September 10, 2018

oracle golden gate 12.3 silent installation on Linux server

Here is the step by step to install the oracle golden gate using silent method

1)export the below variable
export JAVA_HOME=/usr/java6

export PATH=$JAVA_HOME/bin:$PATH
export LIBPATH=$JAVA_HOME/bin:$LIBPATH

export JAVA_HOME=/usr/bin/java

export PATH=$JAVA_HOME/bin:$PATH
export LIBPATH=$JAVA_HOME/bin:$LIBPATH

oggusr@<server_name>:/appshr/oggusrQL/9.5> uname -a
Linux <server_name> 2.6.32-573.7.1.el6.x86_64 #1 SMP Tue Sep 22 14:04:58 CDT 2015 x86_64 x86_64 x86_64 GNU/Linux

./runInstaller -silent -responseFile /usr/local/ogg/sw/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

2)edit the response file

cat oggcore.rsp

####################################################################
## Copyright(c) Oracle Corporation 2017. All rights reserved. ##
## ##
## Specify values for the variables listed below to customize ##
## your installation. ##
## ##
## Each variable is associated with a comment. The comment ##
## can help to populate the variables with the appropriate ##
## values. ##
## ##
## IMPORTANT NOTE: This file should be secured to have read ##
## permission only by the oracle user or an administrator who ##
## own this installation to protect any sensitive input values. ##
## ##
####################################################################

#-------------------------------------------------------------------------------
# Do not change the following system generated value.
#-------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2

################################################################################
## ##
## Oracle GoldenGate installation option and details ##
## ##
################################################################################

#-------------------------------------------------------------------------------
# Specify the installation option.
# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
# ORA11g for installing Oracle GoldenGate for Oracle Database 11g
#-------------------------------------------------------------------------------
INSTALL_OPTION=ORA11g

#-------------------------------------------------------------------------------
# Specify a location to install Oracle GoldenGate
#-------------------------------------------------------------------------------
SOFTWARE_LOCATION=/ogg/ggs_home

#-------------------------------------------------------------------------------
# Specify true to start the manager after installation.
#-------------------------------------------------------------------------------
#START_MANAGER=

#-------------------------------------------------------------------------------
# Specify a free port within the valid range for the manager process.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
#MANAGER_PORT=

#-------------------------------------------------------------------------------
# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
#DATABASE_LOCATION=

################################################################################
## ##
## Specify details to Create inventory for Oracle installs ##
## Required only for the first Oracle product install on a system. ##
## ##
################################################################################

#-------------------------------------------------------------------------------
# Specify the location which holds the install inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
#INVENTORY_LOCATION=

#-------------------------------------------------------------------------------
# Unix group to be set for the inventory directory.
# This parameter is not applicable if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------

SOFTWARE_LOCATION=/ogg/ggs_home
3)invoke the runinstaller
./runInstaller -silent -responseFile /usr/local/ogg/sw/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

oggusr@<server_name>:/usr/local/ogg/sw/fbo_ggs_Linux_x64_shiphome/Disk1> ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp <
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 3685 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3642 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-09-07_01-07-05PM. Please wait ...oggusr@<server_name>:/usr/local/ogg/sw/fbo_ggs_Linux_x64_shiphome/Disk1> X11 connection rejected because of wrong authentication.
You can find the log of this install session at:
/appshr/oraInventory/logs/installActions2018-09-07_01-07-05PM.log
The installation of Oracle GoldenGate Core was successful.
Please check '/appshr/oraInventory/logs/silentInstall2018-09-07_01-07-05PM.log' for more details.
Successfully Setup Software.

4)make sure the golden gate installation successful and check the logs

oracle golden gate installation error on IBM AIX

Issue:
when I tried to install oracle golden gate 12.3 with IBM AIX server got the below error.
$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 2024 MB Passed
Checking swap space: must be greater than 150 MB. Actual 8192 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-07-10_02-44-46PM. Please wait ...cvidntcrkdwh1 {lpndwd} /appndwd/oradata/lpndwd.export/ogg/fbo_ggs_AIX_ppc_shiphome/Disk1>
$ Error: Port Library failed to initialize: -125
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

Solution:
looks like Java environment was not set properly and set the Java & other settings and issue will resolve.

root cause:
Jave & LIBPATH seeting has to be configured properly.

export JAVA_HOME=/usr/java6
export PATH=$JAVA_HOME/bin:$PATH

export LIBPATH=$JAVA_HOME/bin:$LIBPATH

Monday, August 20, 2018

How to get the DDL for the group in Netezza database

Issue:
How to get the DDL for the group in Netezza database

Solution:
Netezza we have utility to get the DDL for the group.

nz_ddl_group <group_name>

/export/home/nz> nz_ddl_group TESTGRP

\echo
\echo ***** Creating group: "TESTGRP"
CREATE GROUP TESTGRP WITH RESOURCE MINIMUM 1 RESOURCE MAXIMUM 5 ;

Monday, August 13, 2018

oracle DB crashed when opened after the full restore

Issue:
we have got the below issue after opening the database from restore and it crashed the instance and throw the below error.
Tue Aug 07 22:47:12 2018
SMON: enabling cache recovery
Errors in file /apporadb1/oracle/product/11.2.0/log/diag/rdbms/oradb1/oradb1/trace/oradb1_ora_25952386.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Errors in file /apporadb1/oracle/product/11.2.0/log/diag/rdbms/oradb1/oradb1/trace/oradb1_ora_25952386.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Error 704 happened during db open, shutting down database
USER (ospid: 25952386): terminating the instance due to error 704
Instance terminated by USER, pid = 25952386
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (25952386) as a result of ORA-1092

Root cause:after checking this error and looks like Db asking for upgrade and we noticed the source database was 11.2.0 and target would be 11.2.0.4 .so in order to open the database ,we just need to upgrade the database to 11.2.0.4.

Solution:

1)startup the DB in upgrade mode

2)run the catupgrd.sql

3)run the utlrp.sql

4)do the sanity check on the database again.

Friday, July 27, 2018

what are the situations golden gate replicate process will abend?

There are many reasons behind when the golden gate replicat process abending,However I am listing few reasons for that and you can take action against it.

1)respective table-space 100% full
2)map table does not have right partition
3)discard file size fully used and space exhusetd
4) trail file location (File system) 100% used
5)Whenever we get bad data and OGG unable to push the data to table
6)whenever replicat process unable to read the trail file

Solution:
look for the report file error & ggs error log an take action accordingally
.

Wednesday, June 20, 2018

How to redirect the oracle golden gate output to another files

Here is the simple code to redirect the OGG output to new text file
you need to add the command on the text file and need to pass on this script.

cat obey_cmds.txt

info all

echo obey /appods/ggshp/v11110078/obey_cmds.txt | $GGS_HOME/ggsci > Replication_info_all.log

Wednesday, June 13, 2018

Nz_migrate throws the error

Issue:
when I tried to migrate the date from source to target ,got the below error and looks like all syntax and everything correct,even though got the below error.

nz@<netezza_server_name>:nz@NPS_HOST:/export/home/nz> nz_migrate -shost NPS_HOST -thost NPS_HOST -sdb DBA_DB -tdb NPS_DB_OLD -tableFile /export/home/nz/NPS_DB_OLD_tbl.log -suser admin -spassword ****** -tuser admin -tpassword ****** -CreateTargetTable YES -TruncateTargetTable NO -format binary -threads 4

-bash: !0: event not found

Solution:
just use single quote on the password character,it will run without any issues.double quote wont work.

nz_migrate -shost NPS_HOST -thost NPS_HOST -sdb DBA_DB -tdb NPS_DB_OLD -tableFile /export/home/nz/NPS_DB_OLD_tbl.log -suser admin -spassword '****** '-tuser admin -tpassword '******' -CreateTargetTable YES -TruncateTargetTable NO -format binary -threads 4

Monday, June 11, 2018

Step by step troubleshooting for Netezza database down

Here is the steps troubleshoot for Netezza database down issue

1.look for the contents of sysmgr.log under/nz/kit/log/sysmgr
2.look for the content of the pg.log under /nz/ki/log/postgres/
3.grep the timeline of the DB down from the pg.log

nz@<server_name>:/export/home/nz> grep -i "shut down" /nz/kit/log/postgres/pg.log.1
2018-06-05 05:07:16.858990 EDT [30134] NOTICE: database system was shut down at 2018-06-05 05:07:16 EDT
2018-06-05 14:00:39.323065 EDT [1983] NOTICE: database system was shut down at 2018-06-05 14:00:39 EDT
2018-06-05 16:29:28.631563 EDT [17920] NOTICE: database system was shut down at 2018-06-05 16:29:28 EDT
2018-06-05 17:21:01.303872 EDT [27842] NOTICE: database system was shut down at 2018-06-05 17:21:01 EDT

2018-06-05 18:35:51.314193 EDT [2921] NOTICE: database system was shut down at 2018-06-05 18:35:51 EDT

4.look for the core files and convert the core files to readable format.

456 2018-06-05 23:56:17 nzgdb -core /nz/data/base/1/core.1528233612.21424 -bt > 21424.bt
457 2018-06-05 23:56:51 nzgdb -core /nz/data/base/1/core.1528230523.11816 -bt > 11816.bt
458 2018-06-05 23:57:29 nzgdb -core /nz/data/base/1/core.1528221590.28144 -bt > 28144.bt
459 2018-06-05 23:58:03 nzgdb -core /nz/data/base/1/core.1528189585.23904 -bt > 23904.bt

460 2018-06-05 23:58:46 nzgdb -core /nz/data/base/1/core.1528185575.5528 -bt > 5528.bt

5.grep the PID with pg.log find out what went wrong on the system during the core dump generated time.

466 2018-06-06 00:05:22 grep '\[27208\]' /nz/kit/log/postgres/pg.log

467 2018-06-06 00:09:44 2018-06-05 18:34:36.874070 EDT [27208] DEBUG: connection: host=10.128.72.44 user=NZKOMUSER database=CVKOMNZP remotepid=5177762 fetype=1

468 2018-06-06 00:09:44 2018-06-05 18:34:36.874088 EDT [27208] DEBUG: Session id is 16279

469 2018-06-06 00:09:44 2018-06-05 18:34:36.877957 EDT [27208] DEBUG: QUERY: select current_catalog, current_user

470 2018-06-06 00:09:44 2018-06-05 18:34:36.879496 EDT [27208] DEBUG: QUERY: exec dta_box_inv_hist_incr_load(' TARGET_TABLE==DTA_STG_nz_tbl_HISTORY><TARGET_FINAL_TABLE==nz_tbl_HISTORY><SRC_LAST_MODIFIED_DATE_COL==dtm_last_updated><SRC_PULL_DATE_COL==BOXINVTRY_ID><SRC_PARALLEL_TASK_COL==><SOURCE_TABLE==nz_tbl_HISTORY><SOURCE_SCHEMA==KOMUSER><SOURCE_FILTER==><SRC_PK_COL==BOXINVTRY_ID><DTA_JOB_ID==503><TPPIL_KLMDATE==04-jun-2018 06:49:29 AM ')

471 2018-06-06 00:09:44 2018-06-05 18:34:36.897353 EDT [27208] DEBUG: UpdateStatsInsertDummy called on table 'TEMPFUNC13279476'

472 2018-06-06 00:09:44 2018-06-05 18:34:36.906701 EDT [27208] DEBUG: STORED PROCEDURE EXEC: SELECT dta_nz_parse_parameter_str(ltrim( $1 )) - Variables ( TARGET_TABLE==DTA_STG_nz_tbl_HISTORY><TARGET_FINAL_TABLE==nz_tbl_HISTORY><SRC_LAST_MODIFIED_DATE_COL==dtm_last_updated><SRC_PULL_DATE_COL==BOXINVTRY_ID><SRC_PARALLEL_TASK_COL==><SOURCE_TABLE==nz_tbl_HISTORY><SOURCE_SCHEMA==KOMUSER><SOURCE_FILTER==><SRC_PK_COL==BOXINVTRY_ID><DTA_JOB_ID==503><TPPIL_KLMDATE==04-jun-2018 06:49:29 AM )

473 2018-06-06 00:09:44 2018-06-05 18:34:36.914496 EDT [27208] DEBUG: STORED PROCEDURE EXEC: SELECT * from table(REGEXP_SPLIT_ROWS( $1 ,'><')) - Variables (TARGET_TABLE==DTA_STG_nz_tbl_HISTORY><TARGET_FINAL_TABLE==nz_tbl_HISTORY><SRC_LAST_MODIFIED_DATE_COL==dtm_last_updated><SRC_PULL_DATE_COL==BOXINVTRY_ID><SRC_PARALLEL_TASK_COL==><SOURCE_TABLE==nz_tbl_HISTORY><SOURCE_SCHEMA==KOMUSER><SOURCE_FILTER==><SRC_PK_COL==BOXINVTRY_ID><DTA_JOB_ID==503><TPPIL_KLMDATE==04-jun-2018 06:49:29 AM )

474 2018-06-06 00:09:44 2018-06-05 18:34:36.927852 EDT [27208] DEBUG: STORED PROCEDURE EXEC: insert into TEMPFUNC13279476 values( 'TARGET_TABLE','DTA_STG_nz_tbl_HISTORY')

475 2018-06-06 00:09:44 2018-06-05 18:34:41.076371 EDT [27208] DEBUG: 1 rows inserted

476 2018-06-06 00:09:44 2018-06-05 18:34:41.084061 EDT [27208] DEBUG: STORED PROCEDURE EXEC: insert into TEMPFUNC13279476 values( 'TARGET_FINAL_TABLE','nz_tbl_HISTORY')

477 2018-06-06 00:09:44 2018-06-05 18:34:44.982932 EDT [27208] DEBUG: 1 rows inserted

478 2018-06-06 00:09:44 2018-06-05 18:34:44.990653 EDT [27208] DEBUG: STORED PROCEDURE EXEC: insert into TEMPFUNC13279476 values( 'SRC_LAST_MODIFIED_DATE_COL','dtm_last_updated')

479 2018-06-06 00:09:44 2018-06-05 18:34:47.670894 EDT [27208] DEBUG: 1 rows inserted

480 2018-06-06 00:09:44 2018-06-05 18:34:47.678735 EDT [27208] DEBUG: STORED PROCEDURE EXEC: insert into TEMPFUNC13279476 values( 'SRC_PULL_DATE_COL','BOXINVTRY_ID')

481 2018-06-06 00:09:44 2018-06-05 18:34:52.229912 EDT [27208] DEBUG: 1 rows inserted

482 2018-06-06 00:09:44 2018-06-05 18:34:52.237441 EDT [27208] DEBUG: STORED PROCEDURE EXEC: insert into TEMPFUNC13279476 values( 'SRC_PARALLEL_TASK_COL','')

483 2018-06-06 00:09:44 2018-06-05 18:34:53.913637 EDT [27208] DEBUG: 1 rows inserted

484 2018-06-06 00:09:44 2018-06-05 18:34:53.926314 EDT [27208] DEBUG: STORED PROCEDURE EXEC: insert into TEMPFUNC13279476 values( 'SOURCE_TABLE','nz_tbl_HISTORY')

485 2018-06-06 00:09:44 2018-06-05 18:34:55.508722 EDT [27208] DEBUG: 1 rows inserted

486 2018-06-06 00:09:44 2018-06-05 18:34:55.518331 EDT [27208] DEBUG: STORED PROCEDURE EXEC: insert into TEMPFUNC13279476 values( 'SOURCE_SCHEMA','KOMUSER')

487 2018-06-06 00:09:44 2018-06-05 18:34:56.975557 EDT [27208] DEBUG: 1 rows inserted

488 2018-06-06 00:09:44 2018-06-05 18:34:56.983190 EDT [27208] DEBUG: STORED PROCEDURE EXEC: insert into TEMPFUNC13279476 values( 'SOURCE_FILTER','')

489 2018-06-06 00:09:44 2018-06-05 18:34:59.367628 EDT [27208] DEBUG: 1 rows inserted

490 2018-06-06 00:09:44 2018-06-05 18:34:59.375228 EDT [27208] DEBUG: STORED PROCEDURE EXEC: insert into TEMPFUNC13279476 values( 'SRC_PK_COL','BOXINVTRY_ID')

491 2018-06-06 00:09:44 2018-06-05 18:35:00.948244 EDT [27208] DEBUG: 1 rows inserted

492 2018-06-06 00:09:44 2018-06-05 18:35:00.955864 EDT [27208] DEBUG: STORED PROCEDURE EXEC: insert into TEMPFUNC13279476 values( 'DTA_JOB_ID','503')

493 2018-06-06 00:09:44 2018-06-05 18:35:04.072782 EDT [27208] DEBUG: 1 rows inserted

494 2018-06-06 00:09:44 2018-06-05 18:35:04.080479 EDT [27208] DEBUG: STORED PROCEDURE EXEC: insert into TEMPFUNC13279476 values( 'TPPIL_KLMDATE','04-jun-2018 06:49:29 AM ')

495 2018-06-06 00:09:44 2018-06-05 18:35:06.168680 EDT [27208] DEBUG: 1 rows inserted

496 2018-06-06 00:09:44 2018-06-05 18:35:06.401591 EDT [27208] DEBUG: STORED PROCEDURE EXEC: delete from nz_tbl_

we had checked all the coredump generated during the time all the time NPS executing the same query and crashing the NPS and finally we asked user to stop executing the query and later IBM found that it was bug on the 7.0 version system

work around for this bug:
1)upgrade the NPS to recent version
2)clean the versioning of the table

we cleared the version of the table and NPs stable after that.

unable to start the Netezza database

issue:
we have done some maintanance on Netezza 7.0 and after maintenance we are unable to start the Netezza database,it throws the below error,while starting the NPS.

nz@<server_name>:/export/home/nz> nzstart
ERROR: System limit for 'nofile' is too low (1024); must be 9600
ERROR: System limit for 'nice' is too low (0); must be 20
nz@<server_name>:/export/home/nz> nzstate
System state is 'Stopped'.

Solution:
when we check the log,we dont have anything on sysmgr.log,because ,it not even started HW components like SPU & DISK,Then we relaised the ulimit value would be small to startup the system and increased the ulimit and issue resolved.

However to increase the ulimit on the Netezza server you may need root privilege to update the new value on the server.

This is happened because our NPS got failover to HA2 and we never had HA2 as active server and this server does not have sufficient ulimit value and fixed later.

ulimit -n 9600

Friday, June 8, 2018

How to resolve version tables in Netezza database?

whenever an alter happened on the Netezza table and NPS will make that table as version and collect the data from both version which is considered as costlier operation.

So we have to clear the table versions periodically.

Versioned tables come about as a result of doing an

ALTER TABLE <tablename> [ADD|DROP] COLUMN ...

This results in multiple data stores for the table. When you go to query

the table, NPS must recombine the separate data stores back into a single

entity. This action will be performed automatically and on-the-fly. But

it does result in additional query overhead. Therefore, it is a best

practice to reconstitute the table as soon as practical by doing a

Here is the script to find out the table versions in Netezza

/nz> nz_altered_tables

# Of Versioned Tables 1

Total # Of Versions 2

Database | Table Name | Size (Bytes) | #

Of Versions

----------+----------------------------+--------------------------+-----

----------

NZ_DB1 | TEST_TABLE_HISTORY | 197,591,957,504 |

once you find the table ,then you need to clear the version using the below command.

GROOM TABLE <tablename> VERSIONS;

Friday, June 1, 2018

what will happen when you do the full Netezza database restore without specifying the back upset number?

When you try to restore the Netezza database without specifying the back upset ,it will success and it will perform the restore and It will pick the latest backup set from the list and do the restore.

nz@Netezza_Server:/backup1/Netezza/NZ81258-H1/Netezza_DB> ls -ltr
total 24
drwxr-xr-x 3 nz nz 4096 Apr 18 17:53 20180417210222
drwxr-xr-x 3 nz nz 4096 May 14 10:30 20180508000011
drwxr-xr-x 3 nz nz 4096 May 14 10:30 20180501000013
drwxr-xr-x 3 nz nz 4096 May 14 10:30 20180424000009
drwxr-xr-x 3 nz nz 4096 May 14 20:51 20180515000109
drwxr-xr-x 3 nz nz 4096 May 26 23:18 20180522000016

/nz/kit/bin/nzrestore -db Netezza_DB -dir /backup1 -lockdb true -npshost NZ81258-H1

Restore of increment 1 from backupset 20180522000016 to database 'Netezza_DB' committed.

from the above demo,we have 6 backup set with full backup on database,but when we do the Netezza database restore,it picked the last backup and recent backup set and completed the restore.

what will happen when you tries to restore the database and DB name already exist on the netezza server?

I did small test on restore.
I was trying to do the restore of the database which name already exist on the server and that DB has the same content .

when I did the restore with same name it throws the error and Netezza did not allow the restore.So when you want to restore the database on Netezza ,just make sure the DB name should not have conflict.

nz@Netezza_Server:/backup1/Netezza/NZ81258-H1/Netezza_DB> /nz/kit/bin/nzrestore -db Netezza_DB -dir /backup1 -lockdb true -backupset 20180522000016 -npshost NZ81258-H1
Error: Creation of database failed: Netezza_DB, SQL Error: 'ERROR: CREATE DATABASE: object Netezza_DB already exists as a DATABASE.'.

before doing the full restore of exiting database,we need to drop or rename the database and apply the restore,NZrestore cant override the restore of database.

steps to collect plan file for SQL in Netezza database.

Here is the steps to generate the plan file for a SQL in netezza database

nzsql -d databasename -f <create file with SQL statement that has issue> -plndir /nz/data/

you need to pass the database name
full sql statement that has the issue and location for the plan file

once you execute the above command ,it will generate the below files and under these directory you see the plan files.

nz@servername:/nz/data> ls -ltr
total 1540
nz@servername:/nz/data> ls -ltr
total 1540
-rw-r--r-- 1 nz 500 6727 Jan 15 12:33 full_sql.sql
drwx------ 2 nz 500 4096 Jan 15 12:36 45176
drwx------ 2 nz 500 4096 Jan 15 12:36 45177
drwx------ 2 nz 500 4096 Jan 15 12:36 45178
drwx------ 2 nz 500 4096 Jan 15 12:37 45179

Thursday, April 19, 2018

small test on the Netezza database using nz_backup

Problem:
I did test on the nz_backup for backup & restore.
steps:
1)took backup of table using nz_backup
2)restore the table using nz_restore ,it went fine
3)but the pre-request is the table to be available,if the table structure is NOT available it should throw the error.
4)when I change the table name on target it throws the error,if the source table & target table name differant it should throw the error.
5)but there is a work around for this,if you take the backup if table TBL1 and backup piece should be TBL1_1 and if the same table does not exist on the target ,it will throw the error,so the work around would be change the backup file name according to Target table name.
lets say target table would be TBL2 then backup file should be TBL2_1

nz_backup -db NZ_DB1 -t Table_ACCOUNT_BK -format binary -dir /nz/migration -thread 1

nz_restore -db NZ_DB1 -t Table_ACCOUNT_BK -format binary -dir /nz -thread 1

nz_restore -db NZ_DB1 -t Table_ACCOUNT_BK1 -format binary -dir /nz -thread 1

NZ_DB1(ADMIN)=> create table Table_ACCOUNT_BK1 as select * from Table_ACCOUNT_BK where 1=2;
INSERT 0 0
NZ_DB1(ADMIN)=> \q
nz@nz_server_name:/nz> nz_restore -db NZ_DB1 -t Table_ACCOUNT_BK1 -format binary -dir /nz -thread 1

nz_restore of database NZ_DB1 started on 2018-04-18 15:20:44

NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_DB1
Backup Directory: /nz
Backup Format: binary
Custom Script:
# Of Threads: 1
Using stableTXid: 0
The lastTXid assigned was: 0
Debug Mode: Disabled

=======================================================================================

ERROR: Unable to load table Table_ACCOUNT_BK1
ERROR: Missing data file: /nz/Table_ACCOUNT_BK1.1

ERROR: 0 tables were processed !!

No tables (matching your specifications) were found.
Or, perhaps this database account doesn't have access to those tables.

=======================================================================================

nz_restore of database NZ_DB1 finished on 2018-04-18 15:20:44

Total # of tables processed: 0
Total # of seconds elapsed : 0

=======================================================================================

*****
***** ERROR
*****
***** Problems were encountered during the running of this script.
***** Inspect the output from this script for more details.
***** Following is a list of tables that encountered problems.
*****

Table_ACCOUNT_BK1

nz@nz_server_name:/nz> ls -ltr
t
-rw-r--r-- 1 nz nz 195775113 Apr 18 15:17 Table_ACCOUNT_BK.1
nz@nz_server_name:/nz> cp -p Table_ACCOUNT_BK.1 Table_ACCOUNT_BK1.1
nz@nz_server_name:/nz> nz_restore -db NZ_DB1 -t Table_ACCOUNT_BK1 -format binary -dir /nz -thread 1

nz_restore of database NZ_DB1 started on 2018-04-18 15:22:00

NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_DB1
Backup Directory: /nz
Backup Format: binary
Custom Script:
# Of Threads: 1
Using stableTXid: 0
The lastTXid assigned was: 0
Debug Mode: Disabled

=======================================================================================

Info: table 1 of 1 Table_ACCOUNT_BK1
Info: restore process started 2018-04-18 15:22:00
Info: # of bytes to be read 195,775,113
Info: reloading data ( thread 1 of 1 )
Info: data flowing ...
Info: reload finished ( thread 1 of 1 ) seconds 12
Info: reload results ( thread 1 of 1 ) INSERT 0 18025675
Info: data flow finished
Info: restore process ended 2018-04-18 15:22:12
Info: rate (restore file size / # of seconds elapsed) 16,314,592
Info: # of records reloaded 18,025,675
Info: # of seconds elapsed 12

=======================================================================================

nz_restore of database NZ_DB1 finished on 2018-04-18 15:22:12

Total # of tables processed: 1
Total # of records reloaded: 18,025,675
Total # of seconds elapsed : 12

TOTAL restore file size : 195,775,113
rate (bytes per second) : 16,314,592

=======================================================================================

nz@nz_server_name:/nz>

script to check the users associated with group in Netezza database

database & table privileges in Netezza database:

select GROUPNAME,
OBJECTNAME,
DATABASENAME,
OBJECTTYPE,
case when GOPOBJPRIV = 0 then 'None'
when GOPOBJPRIV = 1 then 'List'
when GOPOBJPRIV = 2 then 'Select'
when GOPOBJPRIV = 3 then 'List, Select'
else GOPOBJPRIV||' Undefined'
end privileges
from _v_group_priv
where GOPOBJPRIV <= 3
and DATABASENAME NOT in ('SYSTEM')
and OBJECTTYPE in ('TABLE','VIEW')
order by DATABASENAME;

users associated with group in Netezza database
select groupname, username from _V_GROUPUSERS where groupname <> 'PUBLIC'
order by groupname, username;

user Read write Privilges on database

select USERNAME,
OBJECTNAME,
DATABASENAME,
OBJECTTYPE,
case when UOPOBJPRIV = 0 then 'None'
when UOPOBJPRIV = 1 then 'List'
when UOPOBJPRIV = 2 then 'Select'
when UOPOBJPRIV = 3 then 'List, Select'
else UOPOBJPRIV||' Undefined'
end privileges
from _v_user_priv
where UOPOBJPRIV <= 3
and DATABASENAME not in ('SYSTEM')
and OBJECTTYPE in ('TABLE','VIEW')
order by DATABASENAME;

Friday, April 13, 2018

script to list Netezza object in database

Here is the various view used in Netezza to list the database objects

select count(*) from _v_table where objtype='TABLE';

select count(viewname) from _v_view where OBJTYPE='VIEW';

select count(*) from _v_synonym where OBJTYPE='SYNONYM';

select count(FUNCTION) from _v_function;

select count(*) from _V_AGGREGATE;

select count(PROCEDURE) from _v_procedure where OBJTYPE='PROCEDURE';

select count(*) from _v_sequence where OBJTYPE='SEQUENCE';

select count(*) from _v_library ;

Thursday, April 12, 2018

Netezza database error.

Issue:
when the application job ran on the netezza system,it throws the below error and aborted the job.its a huge select statement

ERROR: Records trailing spring space set to 512 is too small : Bump it up using the environment variable NZ_SPRINGFIELD_SIZE

Root cause:

This error can occur when a query includes multiple aggregates against varchar or nvarchar columns. As the query is being processed the system allocates memory for each row of the result set. This includes each of the GROUP BY columns and each of the aggregates. For queries doing aggregates on variable length columns, the total length of the record will vary during the process

solution

To avoid this error message, increase the value for this parameter as follows:

/nz/data/config/system.cfg file. (Create the file if it does not exist.)

system.maxSpringFieldSize = 1024
2. nzstop
3. nzstart

Tuesday, April 10, 2018

oracle database error

issue :
we recently faced issue with oracle database version 11204 running on AIX server which throwed the error and DB crashed

ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 205 (block # 2089728)
ORA-01110: data file 205: '/apporadb/oradata/DB1.temp/temp05.dbf'
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: -1
Additional information: 24576
DBW0 (ospid: 50593896): terminating the instance due to error 63999
Fri Apr 06 22:02:39 2018
Dumping diagnostic data in directory=[cdmp_20180406220239], requested by (instance=1, osid=50593896 (DBW0)), summary=[abnormal instance termination].
Instance terminated by DBW0, pid = 50593896

cause:

when we try to look into the log initially nothing reported and later it got updated on the log when the DB crashed.

Solution:

when I checked on the server one of the temp file eaten up the space on the file system and it grown up and filled the file system to 100% used ,so incoming DB connection unable to do thier operation and crashed the DB and looks like it was bug on the system.

Its not even allow to resize the temp file.

SQL> alter database tempfile '/appmktp/oradata/oradb.temp/temp05.dbf' resize 20000m;

alter database tempfile '/appmktp/oradata/oradb.temp/temp05.dbf' resize 20000m

ERROR at line 1:

ORA-00376: file 205 cannot be read at this time

ORA-01110: data file 205: '/appmktp/oradata/oradb.temp/temp05.dbf'

But we are able to resize other tempfiles.

moreover it does not showing the auto extend status

SQL> select sum(bytes/1024/1024) ,file_name,AUTOEXTENSIBLE from dba_temp_files group by file_name,AUTOEXTENSIBLE ;

SUM(BYTES/1024/1024)

--------------------

FILE_NAME

----------------------------------------------------------------------------------------------------

AUT

---

/appmktp/oradata/oradbtemp/temp05.dbf

27000

/appmktp/oradata/oradbtemp/temp01.dbf

15360

/appmktp/oradata/oradbtemp/temp03.dbf

30720

/appmktp/oradata/oradbtemp/temp04.dbf

23552

/appmktp/oradata/oradb01/temp02.dbf

SQL> alter database tempfile '/appmktp/oradata/oradbtemp/temp04.dbf' resize 25000m;

Database altered.

even I rebooted the DB and its NOT allowed me to resize the temp file.

However I can dropped the temp file and recreated one.

SQL> alter database tempfile '/appmktp/oradata/oradbtemp/temp05.dbf' autoextend off;

alter database tempfile '/appmktp/oradata/oradbtemp/temp05.dbf' autoextend off

ERROR at line 1:

ORA-00376: file 205 cannot be read at this time

ORA-01110: data file 205: '/appmktp/oradata/oradbtemp/temp05.dbf'

SQL> alter database tempfile '/appmktp/oradata/oradbtemp/temp05.dbf' drop including datafiles;

Database altered.

Then the issue got resolved and application able to run their query without any issues.

How to migrate the Netezza full database using nz_migrate

Here is the script to migrate the full database using nz_migrate utility

pre -request.
This will create the new database and there should not be DB available on target side with same name.
it will be helpful to migrate the UDF and it will ask you to enter the target OS user password to scp the files/ddl from source to target

nz_migrate -shost <source_server_name> -thost <target_server_name> -sdb <source_db_name> -tdb <target_db_name> -CreateTargetDatabase yes -table <tablename> -CreateTargetUDX YES -tuser admin -tpassword 'password' -CreateTargetTable yes -TruncateTargetTable False -format binary -threads 4

=======================================================================================

nz_migrate started on 2018-03-22 15:57:48

Migrating <sourcer_server_name>:"source_DB_name". --> <target_server_name>:"source_DB_name_NEW".

Data Format : binary
Log Directory : /tmp/nz_migrate.20180322_155615.28562
Log File : /tmp/nz_migrate.20180322_155615.28562/nz_migrate.output

Initiated From: SOURCE host
Top Level PID : 28562

SOURCE Version: 7.0 # of Dataslices: 46 Unloaded Via: nzsql
TARGET Version: 7.0 # of Dataslices: 184 Loaded Via: nzload
script version: 7.1

=======================================================================================

nz_migrate table 'Table_name'

.....processing table 1 of 1
.....migration process started at 2018-03-22 15:57:48
.....estimated # of records 165,915
.....nzload starting ( thread 1 of 6 )
.....nzload starting ( thread 2 of 6 )
.....nzload starting ( thread 3 of 6 )
.....nzload starting ( thread 4 of 6 )
.....nzload starting ( thread 5 of 6 )
.....nzload starting ( thread 6 of 6 )
.....unloading data ( thread 1 of 6 )
.....unloading data ( thread 2 of 6 )
.....unloading data ( thread 3 of 6 )
.....unloading data ( thread 4 of 6 )
.....unloading data ( thread 5 of 6 )
.....unloading data ( thread 6 of 6 )
.....data flowing.....
.....unload results ( thread 3 of 6 ) INSERT 0 28965
.....unload finished ( thread 3 of 6 ) elapsed seconds: 1
.....unload results ( thread 2 of 6 ) INSERT 0 28825
.....unload finished ( thread 2 of 6 ) elapsed seconds: 1
.....unload results ( thread 1 of 6 ) INSERT 0 28572
.....unload finished ( thread 1 of 6 ) elapsed seconds: 1
.....unload results ( thread 6 of 6 ) INSERT 0 25393
.....unload finished ( thread 6 of 6 ) elapsed seconds: 1
.....unload results ( thread 4 of 6 ) INSERT 0 29011
.....unload finished ( thread 4 of 6 ) elapsed seconds: 1
.....unload results ( thread 5 of 6 ) INSERT 0 25149
.....unload finished ( thread 5 of 6 ) elapsed seconds: 1
.....nzload finished ( thread 3 of 6 ) elapsed seconds: 6
.....nzload successful ( thread 3 of 6 )
.....nzload finished ( thread 2 of 6 ) elapsed seconds: 6
.....nzload successful ( thread 2 of 6 )
.....nzload finished ( thread 1 of 6 ) elapsed seconds: 6
.....nzload finished ( thread 6 of 6 ) elapsed seconds: 6
.....nzload successful ( thread 1 of 6 )
.....nzload successful ( thread 6 of 6 )
.....nzload finished ( thread 4 of 6 ) elapsed seconds: 6
.....nzload successful ( thread 4 of 6 )
.....nzload finished ( thread 5 of 6 ) elapsed seconds: 6
.....nzload successful ( thread 5 of 6 )
.....migration process ended at 2018-03-22 15:57:54
.....data flow finished
.....actual # of records unloaded 165,915
.....
.....migration completed TOTAL seconds: 6
.....
.....cksum process started at 2018-03-22 15:57:54
.....cksum process ended at 2018-03-22 15:57:54
.....confirmed cksum: 0.0 165915 Table_name
.....
.....cksum completed TOTAL seconds: 0

=======================================================================================

nz_migrate finished on 2018-03-22 15:57:54

Migrating <sourcer_server_name>:"source_DB_name". --> <target_server_name>:"source_DB_name_NEW".

Data Format : binary
Log Directory : /tmp/nz_migrate.20180322_155615.28562
Log File : /tmp/nz_migrate.20180322_155615.28562/nz_migrate.output

Total # of tables processed: 1
Total # of records unloaded: 165,915

# of seconds to unload/load the records : 6
# of seconds to compare/cksum the tables: 0

=======================================================================================

Don't forget to run GENERATE [EXPRESS] STATISTICS on your tables!

################################################################################
################################################################################
################################################################################

Per your request ( -CreateTargetDatabase YES ), the target database
was created for you. Additional details can be found here:

Source DDL: /tmp/nz_migrate.20180322_155615.28562/source.ddl
Target Output: /tmp/nz_migrate.20180322_155615.28562/target.ddl.out

################################################################################

Counts Statements/Messages
======= ============================
18 ALTER PROCEDURE
2 ALTER SEQUENCE
430 ALTER SYNONYM
27 ALTER TABLE
4 CREATE AGGREGATE
1 CREATE DATABASE
258 CREATE FUNCTION
2 CREATE LIBRARY
18 CREATE PROCEDURE
2 CREATE SEQUENCE
430 CREATE SYNONYM
27 CREATE TABLE
50 GRANT
1 SET VARIABLE

################################################################################
################################################################################
################################################################################

The script completed successfully with no errors.

How to unload the data from Netezza to flat file

script to unload the data with specific delimiter from Netezza database

nzsql -host <server_name> -u <username> -pw <password> -db <DBname>F 'delimiter' -A -t -c "select * from <table_name>" -O output_file_name.log

Friday, March 30, 2018

steps to collect stats for Netezza database.

There is an utility to collect the stats on Netezza nz_genstats

z@server name:/export/home/nz> jobs -l
[1]+ 9193 Running nohup /nz/support/bin/nz_genstats NZ_DB > NZ_DB_genstats_march30.log &
nz@server name:/export/home/nz> jobs -l
[1]+ 9193 Running nohup /nz/support/bin/nz_genstats NZ_DB > NZ_DB_genstats_march30.log &
You have new mail in /var/spool/mail/nz
nz@server name:/export/home/nz> view NZ_DB_genstats_march30.log

This is how it looks on the database session

nz@server name:/export/home/nz> nzsession

ID Type User Start Time PID Database Schema State Priority Name Client IP Client PID Command
------- -------- --------- ----------------------- ----- -------- ------- ------ ------------- -------------- ---------- ------------------------
3046454 sql ADMIN 30-Mar-18, 16:46:04 EDT 24892 NZ_DB ADMIN active normal 127.0.0.1 24891 GENERATE STATISTICS ON "

Wednesday, March 28, 2018

netezza database table restore using NZ_backup & NZ_restore

Here is the step by step table restore using Netezza utility nz_backup & nz_restore
pre-request:table must be created prior to restore.

nz@<nz_server_name>:/nzscratch/test> nz_backup -db NZ_PRD1 -t NZ_table_1 -format binary -dir /nzscratch/test

nz_backup of database NZ_PRD1 started on 2018-03-28 11:21:10

NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_PRD1
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 1
stableTXid: 6148525
lastTXid: 6148610
This backup is based on TXid: 6148608
with an invisibility list of: (6148526)
Debug Mode: Disabled

=======================================================================================

ERROR: 0 tables were processed !!

No tables (matching your specifications) were found.
Or, perhaps this database account doesn't have access to those tables.

=======================================================================================

nz_backup of database NZ_PRD1 finished on 2018-03-28 11:21:10

Total # of tables processed: 0
Total # of seconds elapsed : 0

TOTAL source table size : 0
TOTAL backup file size : 0
rate (bytes per second) : 0
ratio (source / backup) : 0

=======================================================================================

nz@<nz_server_name>:/nzscratch/test> nz_find_object NZ_table_1

The Object Name Is | It Is Of Type | Its 'objid' Is | In The Database
-----------------------------+---------------+----------------+------------------
NZ_table_1 | TABLE | 18413626 | NZ_PRD1_MARCH23
(1 row)

nz@<nz_server_name>:/nzscratch/test> nz_backup -db NZ_PRD1_MARCH23 -t NZ_table_1 -format binary -dir /nzscratch/test

nz_backup of database NZ_PRD1_MARCH23 started on 2018-03-28 11:22:21

NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_PRD1_MARCH23
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 1
stableTXid: 6148525
lastTXid: 6148616
This backup is based on TXid: 6148614
with an invisibility list of: (6148526)
Debug Mode: Disabled

=======================================================================================

Info: table 1 of 1 NZ_table_1
Info: backup process started 2018-03-28 11:22:21
Info: estimated # of records 14,534,983
Info: unloading data ( thread 1 of 1 )
Info: data flowing ...
Info: unload finished ( thread 1 of 1 ) seconds 5
Info: unload results ( thread 1 of 1 ) INSERT 0 14534983
Info: data flow finished
Info: backup process ended 2018-03-28 11:22:26
Info: source table size 947,781,632
Info: backup file size 851,962,520
Info: rate (backup file size / # of seconds elapsed) 170,392,504
Info: ratio (source table size / backup file size) 1.11
Info: # of records unloaded 14,534,983
Info: # of seconds elapsed 5

=======================================================================================

nz_backup of database NZ_PRD1_MARCH23 finished on 2018-03-28 11:22:26

Total # of tables processed: 1
Total # of records unloaded: 14,534,983
Total # of seconds elapsed : 5

TOTAL source table size : 947,781,632
TOTAL backup file size : 851,962,520
rate (bytes per second) : 170,392,504
ratio (source / backup) : 1.11

=======================================================================================

nz@<nz_server_name>:/nzscratch/test> ls -ltr
total 832816
-rw-r--r-- 1 nz nz 851962520 Mar 28 11:22 NZ_table_1.1
nz@<nz_server_name>:/nzscratch/test> rm NZ_table_1.1
nz@<nz_server_name>:/nzscratch/test>
nz@<nz_server_name>:/nzscratch/test> nz_backup -db NZ_PRD1_MARCH23 -t NZ_table_1 -format binary -dir /nzscratch/test -thread 4

nz_backup of database NZ_PRD1_MARCH23 started on 2018-03-28 11:24:11

NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_PRD1_MARCH23
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 4
stableTXid: 6148525
lastTXid: 6148624
This backup is based on TXid: 6148622
with an invisibility list of: (6148526)
Debug Mode: Disabled

=======================================================================================

Info: table 1 of 1 NZ_table_1
Info: backup process started 2018-03-28 11:24:11
Info: estimated # of records 14,534,983
Info: unloading data ( thread 1 of 4 )
Info: unloading data ( thread 2 of 4 )
Info: unloading data ( thread 3 of 4 )
Info: unloading data ( thread 4 of 4 )
Info: data flowing ...
Info: unload finished ( thread 4 of 4 ) seconds 5
Info: unload results ( thread 4 of 4 ) INSERT 0 3306067
Info: unload finished ( thread 1 of 4 ) seconds 5
Info: unload results ( thread 1 of 4 ) INSERT 0 3963043
Info: unload finished ( thread 2 of 4 ) seconds 5
Info: unload results ( thread 2 of 4 ) INSERT 0 3963680
Info: unload finished ( thread 3 of 4 ) seconds 6
Info: unload results ( thread 3 of 4 ) INSERT 0 3302193
Info: data flow finished
Info: backup process ended 2018-03-28 11:24:17
Info: source table size 947,781,632
Info: backup file size 851,963,336
Info: rate (backup file size / # of seconds elapsed) 141,993,889
Info: ratio (source table size / backup file size) 1.11
Info: # of records unloaded 14,534,983
Info: # of seconds elapsed 6

=======================================================================================

nz_backup of database NZ_PRD1_MARCH23 finished on 2018-03-28 11:24:17

Total # of tables processed: 1
Total # of records unloaded: 14,534,983
Total # of seconds elapsed : 6

TOTAL source table size : 947,781,632
TOTAL backup file size : 851,963,336
rate (bytes per second) : 141,993,889
ratio (source / backup) : 1.11

=======================================================================================

nz@<nz_server_name>:/nzscratch/test> ls -ltr
total 832844
-rw-r--r-- 1 nz nz 193783237 Mar 28 11:24 NZ_table_1.4
-rw-r--r-- 1 nz nz 232343901 Mar 28 11:24 NZ_table_1.2
-rw-r--r-- 1 nz nz 232283008 Mar 28 11:24 NZ_table_1.1
-rw-r--r-- 1 nz nz 193553190 Mar 28 11:24 NZ_table_1.3
nz@<nz_server_name>:/nzscratch/test> nzsql
Welcome to nzsql, the IBM Netezza SQL interactive terminal.

Type: \h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

SYSTEM(ADMIN)=> \l
List of databases
DATABASE | OWNER
------------------------------+---------
NZ_PRD1 | CVADMIN
NZ_PRD1_MARCH23 | ADMIN
NZ_PRD1_OLD | ADMIN

SYSTEM | ADMIN
(16 rows)

SYSTEM(ADMIN)=> \q
nz@<nz_server_name>:/nzscratch/test> nz_restore -db NZ_prd2_db_OLD -t NZ_table_1 -format binary -dir /nzscratch/test -thread 4

nz_restore of database NZ_prd2_db_OLD started on 2018-03-28 11:28:49

NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_prd2_db_OLD
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 4
Using stableTXid: 0
The lastTXid assigned was: 0
Debug Mode: Disabled

=======================================================================================

ERROR: 0 tables were processed !!

No tables (matching your specifications) were found.
Or, perhaps this database account doesn't have access to those tables.

=======================================================================================

nz_restore of database NZ_prd2_db_OLD finished on 2018-03-28 11:28:49

Total # of tables processed: 0
Total # of seconds elapsed : 0

=======================================================================================

nz@<nz_server_name>:/nzscratch/test> nz_ddl_table NZ_PRD1_MARCH23 NZ_table_1

\echo
\echo ***** Creating table: "NZ_table_1"

CREATE TABLE NZ_table_1
(
LOAD_DATE timestamp,
RCC character varying(50),
NWM_FACILITY_DESC character varying(50),
NWM_CMTS_DESC character varying(50),
NODE_DESC character varying(10),
DATA_DEVICES integer,
TOTAL_CUSTOMERS integer,
NODE_HEALTH_SCORE numeric(10,8),
NODE_HEALTH_SCORE_LAST_3_DAYS numeric(10,8),
FLAG integer,
TX_SCORE numeric(10,8),
RCV_SCORE numeric(10,8),
DS_SNR_SCORE numeric(10,8),
DS_CER_SCORE numeric(10,8),
US_SNR_SCORE numeric(10,8),
US_CER_SCORE numeric(10,8),
US_CCER_SCORE numeric(10,8),
CM_T3_SCORE numeric(10,8),
TOTAL_OPEN_TICKETS integer,
TOTAL_MODULATIONS_LAST_3_DAYS integer,
SUM_DURATION_MIN_PAST_3_DAYS numeric(10,2),
TOTAL_OSP_REF integer,
TOTAL_TSG_BUS_REF integer,
TOTAL_TSG_RES_REF integer,
TOTAL_TROUBLE_CALLS integer,
CRITICAL character varying(3),
DATE_STAMP timestamp,
KOM_LAST_MODIFIED_DATE timestamp
)
DISTRIBUTE ON RANDOM
;

/*
Number of columns 28

(Variable) Data Size 158 - 322
Row Overhead 34
====================== =============
Total Row Size (bytes) 192 - 356
*/
nz@<nz_server_name>:/nzscratch/test> nzsql
Welcome to nzsql, the IBM Netezza SQL interactive terminal.

Type: \h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

SYSTEM(ADMIN)=> \l
List of databases
DATABASE | OWNER
------------------------------+---------
NZ_PRD1 | CVADMIN
NZ_PRD1_MARCH23 | ADMIN
NZ_PRD1_OLD | ADMIN
SYSTEM | ADMIN
(16 rows)

SYSTEM(ADMIN)=> \c NZ_prd2_db_old
You are now connected to database NZ_prd2_db_old.
NZ_prd2_db_OLD(ADMIN)=> CREATE TABLE NZ_table_1
NZ_prd2_db_OLD(ADMIN)-> RCC character varying(50),
NWM_FACILITY_DESC character varying(50),
NWM_CMTS_DESC character varying(50),
NODE_DESC character varying(10),
DATA_DEVICES integer,
TOTAL_CUSTOMERS integer,
NODE_HEALTH_SCORE numeric(10,8),
(
NZ_prd2_db_OLD(ADMIN)(> RCV_SCORE numeric(10,8),
DS_SNR_SCORE numeric(10,8),
DS_CER_SCORE numeric(10,8),
LOAD_DATE timestamp,
NZ_prd2_db_OLD(ADMIN)(> US_CCER_SCORE numeric(10,8),
CM_T3_SCORE numeric(10,8),
TOTAL_OPEN_TICKETS integer,
TOTAL_MODULATIONS_LAST_3_DAYS integer,
SUM_DURATION_MIN_PAST_3_DAYS numeric(10,2),
TOTAL_OSP_REF integer,
TOTAL_TSG_BUS_REF integer,
TOTAL_TSG_RES_REF integer,
TOTAL_TROUBLE_CALLS integer,
CRITICAL character varying(3),
DATE_STAMP timestamp,
KOM_LAST_MODIFIED_DATE timestamp
)
RCC character varying(50),
NZ_prd2_db_OLD(ADMIN)(> NWM_FACILITY_DESC character varying(50),
NZ_prd2_db_OLD(ADMIN)(> NWM_CMTS_DESC character varying(50),
NZ_prd2_db_OLD(ADMIN)(> NODE_DESC character varying(10),
NZ_prd2_db_OLD(ADMIN)(> DATA_DEVICES integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_CUSTOMERS integer,
NZ_prd2_db_OLD(ADMIN)(> NODE_HEALTH_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> NODE_HEALTH_SCORE_LAST_3_DAYS numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> FLAG integer,
NZ_prd2_db_OLD(ADMIN)(> TX_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> RCV_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> DS_SNR_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> DS_CER_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> US_SNR_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> US_CER_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> US_CCER_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> CM_T3_SCORE numeric(10,8),
NZ_prd2_db_OLD(ADMIN)(> TOTAL_OPEN_TICKETS integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_MODULATIONS_LAST_3_DAYS integer,
NZ_prd2_db_OLD(ADMIN)(> SUM_DURATION_MIN_PAST_3_DAYS numeric(10,2),
NZ_prd2_db_OLD(ADMIN)(> TOTAL_OSP_REF integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_TSG_BUS_REF integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_TSG_RES_REF integer,
NZ_prd2_db_OLD(ADMIN)(> TOTAL_TROUBLE_CALLS integer,
NZ_prd2_db_OLD(ADMIN)(> CRITICAL character varying(3),
NZ_prd2_db_OLD(ADMIN)(> DATE_STAMP timestamp,
NZ_prd2_db_OLD(ADMIN)(> KOM_LAST_MODIFIED_DATE timestamp
NZ_prd2_db_OLD(ADMIN)(> )
NZ_prd2_db_OLD(ADMIN)-> DISTRIBUTE ON RANDOM
NZ_prd2_db_OLD(ADMIN)-> ;
CREATE TABLE
NZ_prd2_db_OLD(ADMIN)=> \q
nz@<nz_server_name>:/nzscratch/test> nz_restore -db NZ_prd2_db_OLD -t NZ_table_1 -format binary -dir /nzscratch/test -thread 4

nz_restore of database NZ_prd2_db_OLD started on 2018-03-28 11:30:13

NZ_HOST:
NZ_USER: admin
NZ_DATABASE: NZ_prd2_db_OLD
Backup Directory: /nzscratch/test
Backup Format: binary
Custom Script:
# Of Threads: 4
Using stableTXid: 0
The lastTXid assigned was: 0
Debug Mode: Disabled

=======================================================================================

Info: table 1 of 1 NZ_table_1
Info: restore process started 2018-03-28 11:30:13
Info: # of bytes to be read 851,963,336
Info: reloading data ( thread 1 of 4 )
Info: reloading data ( thread 2 of 4 )
Info: reloading data ( thread 3 of 4 )
Info: reloading data ( thread 4 of 4 )
Info: data flowing ...
Info: reload finished ( thread 3 of 4 ) seconds 38
Info: reload results ( thread 3 of 4 ) INSERT 0 3302193
Info: reload finished ( thread 4 of 4 ) seconds 38
Info: reload results ( thread 4 of 4 ) INSERT 0 3306067
Info: reload finished ( thread 2 of 4 ) seconds 41
Info: reload finished ( thread 1 of 4 ) seconds 41
Info: reload results ( thread 2 of 4 ) INSERT 0 3963680
Info: reload results ( thread 1 of 4 ) INSERT 0 3963043
Info: data flow finished
Info: restore process ended 2018-03-28 11:30:54
Info: rate (restore file size / # of seconds elapsed) 20,779,593
Info: # of records reloaded 14,534,983
Info: # of seconds elapsed 41

=======================================================================================

nz_restore of database NZ_prd2_db_OLD finished on 2018-03-28 11:30:54

Total # of tables processed: 1
Total # of records reloaded: 14,534,983
Total # of seconds elapsed : 41

TOTAL restore file size : 851,963,336
rate (bytes per second) : 20,779,593

=======================================================================================

nz@<nz_server_name>:/nzscratch/test> nz_find_object NZ_table_1

The Object Name Is | It Is Of Type | Its 'objid' Is | In The Database
-----------------------------+---------------+----------------+------------------
NZ_table_1 | TABLE | 18572914 | NZ_prd2_db_OLD
NZ_table_1 | TABLE | 18413626 | NZ_PRD1_MARCH23
(2 rows)