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;

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 ;

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

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

NO

               15360

/appmktp/oradata/oradbtemp/temp03.dbf

NO

               30720

/appmktp/oradata/oradbtemp/temp04.dbf

NO

               23552

/appmktp/oradata/oradb01/temp02.dbf

NO

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