Database Technology: October 2014

nz_abort will be used to abort the session on NPS server,if we did not provide any argument ,then it will abort all session on current user

nz_abort [-all|<dbname>|<username>]

How to put Netezza database to maintenance mode?

we can put the netezza server /db's to maintenance mode,but admin can able to login to the server /database.it wont disturb the existing connection ,it will not allow the new connection to the database.

nz_maintenance_mode -on/off <db_name>,if db name not mentioned it will make it for entire NPS.

How to find locks on netezza database?

nz_lock <db name>

how to list various component memory usage in NPS?

[nz@Netezza]$ nz_host_memory

2014-10-24 11:58:10

Memory Types:
mt inUse Used Max Perm Name
2 193595 20817124 263156 0 anonymous

how to update the contact details on NPS server?

use the below file to edit the contact details on NPS server?

/nz/data.1.0/config/callHome.txt

how to find rollback status in Netezza Database?

you can view the below query to check the roll back status.
select XID,START_TIME,TIME_REMAINING from _V_ROLLBACK_STATUS;

how to check count of all objects in NPS?

nzstats -type dbms

Field Name Value
------------------- -----
Num Databases 11
Num Groups 11
Num Users 72
Num Tables 7801
Num Views 203
Num SQL Sessions 7
Num Queries 0
Num Queries Running 0
Num Queries Waiting 0
Num Transactions 1

how to check overall health of Netezza system?

nz_health will tell you overall health of system
Hw issues,S?W issue/code issues/failed sidk,disk details etc.

how to check H/w status on NPS?

nzhw -issues
============
No entries found

nzds -issues
============
No entries found

nzds -regenStatus
=================
No entries found

how to check the groom status of database?

progress/status of ongoing GROOM operations can be monitored via the
system view _V_GROOM_STATUS

nz_groom [dbname [tablename <...>]] [ optional args ]

Purpose: A wrapper around the 'groom' command to provide additional functionality.

nz_reclaim is automatically invoked when running NPS 4.x and 5.x
nz_groom is automatically invoked when running NPS 6.x +

Inputs: The database name is optional. If not specified then all databases will be
processed.

The tablename is optional. If not specified then all tables in the database
will be processed. Or, you can specify one (or many) tablenames to be
processed.

-scan The default option. This will report on the amount of space
that would be reclaimed IF you were to do a

"groom table <name> records all;"

Gathering this information simply involves a full table scan.

-pages Run a page based groom against the entire table.

Any empty pages (128KB) will be removed from the scan list (although
they will still exist in the table). Any empty extents (3MB) will
be removed the from table.

Where "empty" means that the page/extent only contains deleted rows,
and those rows are currently reclaimable -- i.e. not of interest.

"groom table <name> pages all;"

The goal here is to remove as many rows as possible ... using the
fastest method possible.

-records Run the actual "groom table <name> records all;" against the
table. This option will, by far, take the longest to run as
it basically involves a read+rewrite of the entire table.
Therefore, when you choose this option, the script will
automatically do the following:

a) First perform the "-pages" operation, since it is faster
to throw away pages/extents of data, than individual records.

b) Then perform the "-scan" operation to see if there are
any rows that actually need reclaiming. If not, we'll
be able to skip the actual record level grooming as that
would not be of any benefit.

c) IF, and only IF, a record level groom is warranted, it
will then be run against the table. (The table must
have reclaimable rows, and must meet/exceed any of the
following thresholds that you are allowed to specify).

The following switches are optional, and apply to the
-scan/-records options. They can be used in any combination
to limit the output report to just those tables that meet/
exceed the specified thresholds. If multiple conditions are
specified, they will be OR'ed together.

If a table does not meet at least one of the thresholds it
will not be included in the report (the "-scan" option) nor
will the script do a record level groom against the table
(the "-records" option).

-rows <nnn> # Example: -rows 1000000
# Only show tables with >= 1M reclaimable rows

-percent <nnn> # Example: -percent 50
# Only show tables where >= 50% of the rows
# are reclaimable

-size <nnn> # Example: -size 1000000000
# Only show tables with >= 1 GB of reclaimable
# space

If no thresholds are specified, then:
o All tables will be displayed
(for purposes of the "-scan" option).
o Only tables with >= 1 reclaimable row will be reclaimed
("-records" option)

-records <all|ready>

There may be times when you want to FORCE a record level groom
to be performed (even if there are no logically deleted rows in
the table to be reclaimed). For example:
o) To rewrite a table, after you have enabled compression,
so that it will now be compressed
o) To "organize" the rows within a clustered base table.

This option allows you to FORCE the record level groom (of your
choosing) to be performed. Note that this script will skip the
initial page level groom, and subsequent scan, of each table ...
as that just adds additional overhead, and it won't make any
difference (since the script is always going to end up doing the
record level groom that you requested anyway).

-mview If a table has one (or more) active materialized views associated
with it, the table cannot be groom'ed until any such MView has been
suspended. So, by default, this script will skip over those tables.

Add this switch to the command line if you want the script to
o Automatically suspend any such materialized views
o Perform the GROOM operation
o Refresh the materialized views upon completion

-version If you have a versioned table (which is the result of doing an
'ALTER TABLE <table> [ADD|DROP] COLUMN ...' operation against
it), then the ONLY groom operation that is allowed against the
table is a GROOM VERSIONS. By default, this script will skip
over any such table.

Add this switch to the command line if you want the script to
automatically do a GROOM VERSIONS against such tables.

-backupset <[backupsetid | NONE]>

This switch will allow you to override the default backupset. If used, this
information is simply passed along to the "groom" command for it to handle.

The system synchronizes your groom request with the most recent backup set to
avoid reclaiming rows not yet captured by incremental backups. In other words,
groom will not remove any unbackedup data that has been deleted or updated.

In addition, groom will not remove any data that an old/outstanding (yet
still active) transaction might still have an interest in.

So ... there may very well be logically deleted rows in a table ... that
groom is not willing to physically delete at this point in time (for the
above mentioned reasons). The scripts 'nz_invisible' and 'nz_transactions'
may be useful in such a situation.

For additional information, refer to the "System Administrator's Guide"

For this script to function, you must have SELECT access to the following
objects:
_T_BACKUP_HISTORY
_VT_HOSTTXMGR

Notes: If an unexpected error is encountered at any point in time, this script
will echo the error message and then exit. It will not continue processing
other tables if any problem is encountered.

The progress/status of ongoing GROOM operations can be monitored via the
system view _V_GROOM_STATUS

Outputs: For the default "-scan" option, a report such as this will be produced.

The "Size"s are estimates (based on each table's average row size).

The "Remaining Rows/Remaining Size" represents what would remain in the
table once the actual "groom table <name> records all;" is processed.
This includes visible rows -- as well as deleted rows that cannot yet
be groom'ed (for whatever reason).

The "NON-Groomable Rows" column represents the number of deleted rows that
are NOT elgible for grooming at this point in time, which may be due to
o an outstanding transaction that might have an interest in them
o the rows have not yet been captured in the most recent backup set

Name Remaining Rows Remaining Size Reclaimable Rows Reclaimable Size NON-Groomable Rows
-------- -------------- -------------- ---------------- ---------------- ------------------

how to List the administrative privileges that a user has been granted to a database.

nz_get_admin [-user <user>] <database>

To list out ALL of the objects that a given user has access to

nz_my_access <username> [-nopublic]

nz_get_acl

how to collect statistic on Netezza tables

nz_genstats [-info] [-full|-express|-basic] <database> [table ...]

Purpose: Generate statistics on those tables that don't have up-to-date statistics.

The optimizer uses statistics to guide its decisions on how best to
execute a query. The more reliable and up-to-date the statistics are,
the more accurate the optimizer's decisions are likely to be.

You can easily "GENERATE STATISTICS;" for an entire database;

Or you could issue a "GENERATE STATISTICS ON <table>;"
or a "GENERATE EXPRESS STATISTICS ON <table>;".
In this case, you must issue the sql statement on a table-by-table basis.

But what if a given table already has up-to-date statistics? Then
regenerating statistics for it would be a waste of your time and the
system's resources.

This script first checks the state of the statistics for each table,
and then only regenerates statistics on those tables that have outdated
statistics. (Statistics are either 100% up-to-date+accurate+reliable,
or they aren't at 100%. There is no other statistical measure that
measures statistics).

Note: If you wish to get/display the current statistic values for any
given table you can use the script "nz_get"

Inputs: The database name is required.

The table name is optional. If not specified then all tables in the
database will be checked, and the statistics refreshed as appropriate.

Or, you can specify one (or many) table names to be checked.

If you have a file that contains a list of tablenames to be checked,
it can be used via the following syntax:

nz_genstats DBNAME `cat /tmp/the_list_of_tables`

-info An optional switch

If included, then statistics won't actually be generated on any table.
Instead, this script will simply report information about what tables
have up-to-date statistics and what tables need to have statistics
generated against them.

[-full|-express|-basic] An optional switch

You can control whether FULL, EXPRESS, or BASIC stats are generated for
the tables. (For an explanation of the difference between the three
choices, see "nz_get -help" ). If not specified, then this script will
adhere to whatever type of stats each individual table currently has
associated with it.

Full statistics take the longest to generate -- but provide more accurate
dispersion information (the # of unique values) for each column.

Express statistics use a faster (but less accurate) mathematical formula
to determine the dispersion.

Basic statistics can be generated the fastest -- because they skip the
dispersion calculation entirely. (This is new as of NPS Version 4.6).

Full statistics are recommended for smaller dimension tables ... which
usually have fewer rows ... where the accuracy of the dispersion value
tends to have more of an impact on query planning.

For larger fact tables, express statistics or basic statistics are
typically used because of the time savings in generating the statistics.

[-min <nnn>] Optional switches (default is -min 0)
[-max <nnn>] (default is -max 99999999999999999)

These switches allow you to control which tables are to have a GENSTATS
run against them ... based on their table rowcount being between the -min
and -max values specifed. The default is for all tables to be included.
Example: Specifying '-max 1000000' will result in only tables with
<= 1M rows having a GENSTATS performed against them (and then only if
necessary ... if the statistics aren't already up-to-date).

Outputs: Sample output. The first run included the "-info" switch. No changes
to the database were made. The script simply lists what it WOULD have
done if you let it.

The second run actually invoked GENSTATS on three of the tables. The
elapsed runtime for the individual operations is included.

how to check statistic value of table in NPS

use nz_get to get the value of statistic status.

Usage: nz_get [ database [ table ]]

Purpose: Get (and display) the statistics for a user table or a system table.

Statistics are used by the NPS optimizer in order to plan the best
way to run each SQL query (for queries that are run against user data,
and also for queries that are run against the system catalogs).

Some of these statistics are automatically maintained by the system
(i.e., 'always-there' statistics). Other values are dynamically
calculated+saved when a "GENERATE [EXPRESS] STATISTICS" command is
issued.

This script will dump out all of the known statistics for a table.

Inputs: The database and table names are optional. If not specified, then
all tables in all databases will be reported upon.

In lieu of a table name, you may instead specify a synonym name or
materialized view name -- in which case the statistics for the
underlying table will be displayed.

The table name may also be any one of the "SYSTEM TABLE" or
"MANAGEMENT TABLE" names.

o For a list of these names, see 'nz_get_sysmgmt_table_names'

o By default, normal (non-ADMIN) users do not have access
to any of these SYSTEM/MANAGEMENT tables. They are only
allowed to access them indirectly (thru SYSTEM/MANAGEMENT
views).

o Some of these tables are global in nature, so it does not
matter which database name you specify on the command line.
The results will always be the same. e.g. _VT_SPU_ZMAP_INFO

o Some of these tables are local in nature (database specific)
so the database you specify is relevant. e.g. _T_ATTRIBUTE

o If you wanted to look at the statistics for all of the
SYSTEM/MANAGEMENT tables, you could issue a command such as this:

for TABLE in `nz_get_sysmgmt_table_names system`; do nz_get system $TABLE; done

Outputs: The output of this script will include the following information

Database: name, objid

Table: name, objid, distribution clause, row count(statistic)

Per-Column: attnum the logical column number (from 1-1600)
Column Name
Statistics Status the validity/freshness of the statistics
Minimum Value
Maximum Value
# of Unique Values also known as the dispersion value
# of NULLs
MaxLen The MaxLen+AvgLen are only collected for
AvgLen columns of type VARCHAR, NCHAR, NVARCHAR

Regarding the "Statistic Status" ... the following indicates that statistics
have been explicitly generated against this table/column (via a GENSTATs
command) and that they are 100% up-to-date. The ONLY difference between the
three is in how the "# of Unique Values" (the dispersion) is generated.
The disperion value is the most complex statistic to gather, in terms of time +
memory + cpu usage.

Full Similar to doing a "COUNT(DISTINCT columname)". It generates the
most accurate dispersion value, but has the most overhead associated
with it.
Express It uses math to estimate the dispersion value (a hash is generated
for each column value, and then the number of unique hash keys is
added up). Much faster, but less precise.
Basic The dispersion calculation is skipped entirely. All of the other
"basic" statistics are still collected.

So, statistics are either 100% correct and up-to-date, or they're not. Any change
to a table ... even if it involves just 1 row being inserted/updated/deleted ...
results in the statistics no longer being 100% up-to-date. In other words, they
would be outdated. This doesn't mean that they would be bad or wrong. Just outdated.

When rows are nzload'ed/INSERT'ed into a table, NPS automatically compares
each column value against the MIN/MAX statistic for the column, and updates the
table's statistics accordingly. This is also referred to as "always there
statistics". This applies to all columns/all datatypes -- EXCEPT for text
columns (CHAR, NCHAR, VARCHAR, NVARCHAR). So that means that the MIN/MAX values
for the column are still OK -- they are still up-to-date (at the same time, the
table's rowcount statistic is also updated). But the other statistics values
(the dispersion and # of nulls) are not up-to-date as they can only be recomputed
via an explicit GENSTATS. In this case, the "Statistics Status" will show

Full Min/Max OK
Express Min/Max OK
Basic Min/Max OK

As mentioned, this doesn't apply to text columns. So all of the statistics that
are maintained for text columns would be outdated ... and can only be refreshed
via an explicit GENSTATS. In this case, the "Statistics Status" will show

Full Outdated
Express Outdated
Basic Outdated

If you never ever bothered to do a GENSTATS on a particular table, then the only
per-column statistics that would be available are the "always there statistics"
that NPS automatically collects. So this means you will have MIN/MAX statistics
(and only MIN/MAX statistics) for those columns. Which will be displayed as

Min/Max Only

As mentioned, "always there statistics" aren't collected for text columns. So
if you've never done an explicit GENSTATS ... and if NPS has never automatically
collected any statistics ... then there will be no statistics at all available
to the optimizer. In this case the "Statistics Status" will show

Unavailable

Other situations in which "Unavailable" will be used
o if the table is empty
o if you've loaded a compressed external file into an empty table

We don't attempt to collect statistics on very wide text columns, which are
CHAR/VARCHAR columns with a defined length >= 24565
NCHAR/NVARCHAR columns with a defined length >= 6142
The "Statistics Status" for those columns will be displayed as

not maintained

Regarding "always there statistics" ...

when rows are nzload'ed/INSERT'ed into a table we can easily compare the column
values against the MIN+MAX statistics, and update them accordingly (if needed)

when rows are DELETE'd from a table, even though you might be deleting a row
that matches a particular MIN value or a particular MAX value, there is no
way to answer the following
a) is this the only row in the table with that particular value ?
b) and if so, what is the next MIN value or the next MAX value ?
The only way to determine those answers is via a GENSTATS (which processes
every row in the table). Thus, a DELETE operation never shrinks the
MIN/MAX values. But we still know that we can use+trust those statistics ...
that there is no value in the table column that is outside the current
MIN/MAX range.

when rows are UPDATE'd, we actually do an INSERT+DELETE. See above.

Regarding GENERATE [EXPRESS] STATISTICS ...

as of 4.6, there is really only one GENERATE STATISTICS command now. Both
GENERATE STATISTICS and GENERATE EXPRESS STATISTICS do the same thing.
GENERATE EXPRESS STATISTICS will someday be retired.

the only difference between the two has been in how they calculate the
"# of Unique Values" ... the dispersion value ... for a column. That
still occurs, but now NPS decides for itself which method to use.

If the table's rowcount is <= 10 * SPU_COUNT
Then FULL stats will be collected
where we actually try to do a COUNT(DISTINCT on_each_column)
# Of Columns Processed Per Scan: set STATS_COL_GRP_LIMIT = 10;

If the table's rowcount is <= JIT_DISP_MIN_ROWS (the default is 500,000,000)
Then EXPRESS stats will be collected
where we hash the column values to approximate the dispersion value
# Of Columns Processed Per Scan: set SAMPLED_STATS_COL_GRP_LIMIT = 50;

Else we skip the dispersion calculation alltogether (all other statistics
for the column will still be gathered)
# Of Columns Processed Per Scan: currently unbounded

what the ways to generate DDL of various objects in NPS?

nz_get_view_definition

Usage: nz_get_view_definition <database> <view>

Purpose: Display the definition (the SQL) that the view will execute.

nz_ddl*

nz_ddl_aggregate*

nz_ddl_comment*

nz_ddl_database*

nz_ddl_diff*

nz_ddl_ext_table*

nz_ddl_function*

nz_ddl_grant_group*

nz_ddl_grant_user*

nz_ddl_group*

nz_ddl_history_config*

nz_ddl_library*

nz_ddl_mview*

nz_ddl_object*

nz_ddl_owner*

nz_ddl_procedure*

nz_ddl_security*

how to check the creator of the user in Netezza?

Usage: nz_get_user_owner <user>

Purpose: List the owner (creator of) the specified user.

Inputs: The user name is required.

Outputs: The owner of the user is returned

[nz@nps bin]$ nz_get_user_owner bala
ADMIN

how to check the table fragmentation details of tables on Netezza Database?

nz_frag -h

Usage: nz_frag <database> <table/mview>

Purpose: Dump out extent/page allocations in order to visualize table fragmentation.

Storage is allocated an extent at a time (3MB). Within the extent, it is
then filled up with records a page at a time (128KB). The pages are filled
front-to-back. Once all of the available space in the extent is used up, a
new extent is allocated.

Usually, all of the 24 pages within an extent are in-use. But there are
exceptions.

o The very last extent for a table (on any given dataslice) will probably
only be partially filled. So any remaining pages will be unused (empty).
Unused pages are not scanned.

o If you have done a "GROOM TABLE <tablename> PAGES ALL;" then any pages
that contain 100% deleted/groomable rows will be marked as being empty.
They will no longer be used/scanned, though they still exist within the
extent. If all 24 pages in the extent are empty, the extent will be
removed from the table and added back to the global storage pool.

o Clustered Base Tables (those created with an ORANIZE ON clause) may only
partially fill any given cluster/extent.

Inputs: The database and table/mview names are required.

-dsid <nn> By default, data slice 1 (the 1st amongst all data slices)
will be reported upon. You can choose to look at the
information for a different data slice if you wish.

Storage allocation/usage is specific to each dataslice.
Rather than showing all info for all dataslices (which
could be voluminous), this script will concentrate on
just one dataslice, which should provide a good
representative sample.

However, if you want to see ALL information for ALL
dataslices ALL at once, then specify: -dsid all

Outputs: A dump of the extent+page storage information for this table/mview (for
a single dataslice).

The "Extent ID" that is displayed is the system assigned extent number.
The values for contiguous extents will vary by 32.

The "#" column is a simple, one-up number ... to make things easier to
read.

The "gap" column is used to indicate whether the extents are contiguous
on disk. If the extents are contiguous (if the gap is 0) then a blank
will be displayed. Otherwise, this number will represent the number of
other extents (not belonging to this table) between this extent and the
prior extent.

"Used/Unused Pages (./0)" is used to represent which of the 24 pages
within each extent are (or are not) in use. A "." indicates the page
is in use. A "0" indicates the page is not being used.

Example follows:

$ nz_frag SYSTEM TEST_TABLE

Database: SYSTEM
Object Name: TEST_TABLE
Object Type: TABLE
Object ID : 10578974
Data Slice: 1

Extent ID | DataSlice | # | gap | Used/Unused Pages (./0)
-----------+-----------+----+-----+--------------------------
38886368 | 1 | 1 | | 0.......................
38886432 | 1 | 2 | 1 | .0......................
38886560 | 1 | 3 | 3 | ..0.....................

how to find objects owned by specific owners in Netezza database?

Find objects that are owned by users -- other than the 'admin' user
nz_find_object_owners [user]

how to search object in Netezza box?

How to generate DDL for user in Netezza?

nz_ddl_user <USER_NAME>

\echo

\echo ***** Creating user: "<USER_NAME>"

CREATE USER <USER_NAME> WITH PASSWORD 'password' ;

\echo

\echo ***** Updating password for user: "<USER_NAME>"

UPDATE _t_user_options SET passwd = '$1$/+CQzTNi+1I9$QqgdELb4KLkNiFqr8cN4Jg==' WHERE usename = ^<USER_NAME>^;

UPDATE _t_user SET passwd = '$1$/+CQzTNi+1I9$QqgdELb4KLkNiFqr8cN4Jg==' WHERE usename = ^<USER_NAME>^;

it will set the password as 'password'

how to add a user user to power group in Netezza box?

Adding users to ACCESS groups

ALTER GROUP POWER_USER ADD USER <username>

How to check how many objects details in Netezza database?

use nz_stats will tell you the details about various versions of tables and object count.

nz_stats

Host Name nsdwt03
Model Number IBM PureData System for Analytics N1001-005
Software Revision 7.0.2.P8
Today's Date 2014-10-24
Uptime (In Days) 384.82
Instance 69
#'s Of Objects
Databases 10
Tables 7,801
External Tables 251
Row Secure Tables 0
Views 203
Materialized Views 0
Sequences 0
Synonyms 5
Users 72
Groups 11
Aggregates 0
Functions 26
Procedures 3,763
Libraries 0
# Of Versioned Tables 58
Total # Of Versions 120
Table Size (MB)
Minimum 0
Average 1,920
Maximum 687,092
Total 14,985,326
MView Size (MB)
Minimum 0
Average 0
Maximum 0
Total 0
Tables Per Database
Minimum 6
Average 774
Maximum 1,898
MViews Per Database
Minimum 0
Average 0
Maximum 0
MViews Per Table (Max) 0
Columns Per Table
Minimum 1
Average 14
Maximum 265
Columns Per MView
Minimum 0
Average 0
Maximum 0
Table Row Size (Bytes)
Minimum 28
Average 825
Maximum 52,064
MView Row Size (Bytes)
Minimum 0
Average 0
Maximum 0
Column Types (for TABLEs)
BYTEINT 1,057
SMALLINT 2,621
INTEGER 21,324
BIGINT 1,154
NUMERIC 25,577
FLOAT 0
DOUBLE 1,083
CHAR 1,191
VARCHAR 44,552
NCHAR 0
NVARCHAR 44
DATE 5,161
TIME 74
TIMESTAMP 5,083
TIMETZ 0
INTERVAL 5
BOOLEAN 2
ST_GEOMETRY 0
VARBINARY 0
Column Types (for MVIEWs)
BYTEINT 0
SMALLINT 0
INTEGER 0
BIGINT 0
NUMERIC 0
FLOAT 0
DOUBLE 0
CHAR 0
VARCHAR 0
NCHAR 0
NVARCHAR 0
DATE 0
TIME 0
TIMESTAMP 0
TIMETZ 0
INTERVAL 0
BOOLEAN 0
ST_GEOMETRY 0
VARBINARY 0
Column Types (for VIEWs)

How to check total and used space on Netezza applicance

you may use the below query to find the NPS DS used and free space details.

nz_storage_stats

nz@Netezza1 bin]$ nz_storage_stats

# Of DataSlices 92
Extents Per Dataslice 121,515
Storage Per DataSlice (GB) 356.000
Storage Used (GB)
Minimum 128.259
Average 136.918
Maximum 150.911
Storage Used (%)
Minimum 36.028
Average 38.460
Maximum 42.391
Total Storage
Available (TB) 31.984
Used (TB) 12.301
Used (%) 38.460
Remaining (TB) 19.683
Remaining (%) 61.540

Tuesday, October 14, 2014

voting disk and OCR IN RAC

OCR: It created at the time of Grid Installation. It’s store information to manage Oracle cluster-ware and it’s component such as RAC database, listener, VIP,Scan IP & Services.
Minimum 1 and maximum 5 copy of OCR is possible.
Voting Disk: It manage information about node membership. Each voting disk must be accessible by all nodes in the cluster.If any node is not passing heat-beat across other note or voting disk, then that node will be evicted by Voting disk.
Minimum 1 and maximum 15 copy of voting disk is possible.

New Facts:

We can store OCR And Voting disk on ASM or certified cluster file system.
We can dynamically add or replace voting disk & OCR.
Backup of Voting disk using “dd” command not supported.
Voting disk and OCR can be keep in same disk-group or different disk-group
Voting disk and OCR automatic backup kept together in a single file.
Automatic backup of Voting disk and OCR happen after every four hours, end of the day, end of the week
You must have root or sudo privilege account to manage it.

To find current location of Voting disk:

-bash-3.2$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE 9095dc8a0e4d4f40bfd3a8b6ed02d7c1 (/dev/dbq_rawvote) [OCRVD]

Located 1 voting disk(s).

to check OCR

bash-3.2$ ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 3

Total space (kbytes) : 262120

Used space (kbytes) : 3308

Available space (kbytes) : 258812

ID : 376930295

Device/File Name : +OCRVD

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

to check local OLR

ddcfusdbq01 /grid/app/11.2.0/grid/bin #./ocrcheck -local

Status of Oracle Local Registry is as follows :

Version : 3

Total space (kbytes) : 262120

Used space (kbytes) : 2704

Available space (kbytes) : 259416

ID : 367014315

Device/File Name : /grid/app/11.2.0/grid/cdata/ddcfusdbq01.olr

Device/File integrity check succeeded

Local registry integrity check succeeded

Logical corruption check succeeded

how to increase the size of db_recovery_file_dest_size in oracle RAc

1.Please check enough room is there for ASM disk
2.ALTER SYSTEM SET db_recovery_file_dest_size='40G' SCOPE=BOTH SID='*';
it will reflect to all nodes.

How to check the Netezza HA cluster setup status?

[root@node1 ~]# service heartbeat status
heartbeat OK [pid 20408 et al] is running on node1 [node1]...
[root@node1 ~]# service drbd status
drbd driver loaded OK; device status:
version: 8.2.6 (api:88/proto:86-88)
GIT-hash: 3e69822d3bb4920a8c1bfdf7d647169eba7d2eb4 build by root@nps22094, 2010-11-18 14:52:01
m:res cs st ds p mounted fstype
0:r1 Connected Primary/Secondary UpToDate/UpToDate C /export/home ext3
1:r0 Connected Primary/Secondary UpToDate/UpToDate C /nz ext3
[root@node1 ~]#

if you get the above output from server ,then you may confirm the database service running on node1

how to do restore in sql server 2008 for whole database

1.take the full backup from ms studio
2.transfer the backup piece from source to target (i.e cmd prompt and c: or shared folder to target server.
3.
you may generate the script from msq sql studio alter this wherever you want to place the mdf,ldf files alter it based on the available disk space.

RESTORE DATABASE [DB__name] FROM DISK = N'E:\backup\DB__name_july28\DB__name_full_bakup.bak' WITH FILE = 1, MOVE N'DB__name_Data' TO N'E:\SQLData\DB__name.MDF', MOVE N'DB__name_Log' TO N'F:\SQLLog\DB__name_1.LDF', NOUNLOAD, STATS = 5

4.run dbcc to check the integrity of the database.

5.check all the user permission everything good.

6.fix the orphan user account using the below steps
EXEC sp_change_users_login 'Report'

step to create Data guard from RAC to stand alone database

This summary is not available. Please click here to view the post.

steps to create db link from oracle to MSSQL using gateway server

1.CREATE PUBLIC DATABASE LINK "DB_link_name"
CONNECT TO "PTC_SSRSRMS" IDENTIFIED BY VALUES '06D0D'
USING 'DB__name'

2.tnsnames.ora on oracle server oracledb_servername

DB__name=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP) (PORT=1621) (HOST=gateway_servername))
(CONNECT_DATA= (SID=DB__name)) (HS=OK))

3.update on C:\Oracle\11g\dg4msql
initDB__name.ora

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=sqlserver name//sql db name
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

4.listener.ora
SID_DESC =
(SID_NAME = DB__name)
(ORACLE_HOME = c:\oracle\11g)
(PROGRAM = dg4msql)
)

5.tnsnames.ora
DB__name.WORLD =
(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP) (PORT=1621) (HOST=gateway_servername))
(CONNECT_DATA= (SID=DB__name)) (HS=OK))

restore Netezza table alone when you have table level backup

lease follow the below syntax to restore the table when you take the backup using nz_backup for single table.

[nz@SERVER_NAME ~]$ nz_restore -format binary -dir /netback/backup/test/Netezza/NZ81331-H1/TEST/whdev/ -db TARGTE_DB_NAME-t ADMINISTRATOR_140923_152501

nz_restore of database TARGTE_DB_NAMEstarted on 2014-02-28 14:22:18

NZ_HOST:

NZ_USER: ADMIN

NZ_DATABASE: DB_NAME1

Backup Directory: /netback/backup/test/Netezza/NZ81331-H1/TEST/whdev/

Backup Format: binary

Custom Script:

# Of Threads: 1

Using stableTXid: 0

The lastTXid assigned was: 0

Debug Mode: Disabled

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

Info: table 1 of 1 ADMINISTRATOR_140923_152501

Info: restore process started 2014-02-28 14:22:19

Info: # of bytes to be read 1,650,875

Info: reloading data ( thread 1 of 1 )

Info: data flowing ...

Info: reload finished ( thread 1 of 1 ) seconds 1

Info: reload results ( thread 1 of 1 ) INSERT 0 57840

Info: data flow finished

Info: restore process ended 2014-02-28 14:22:20

Info: rate (restore file size / # of seconds elapsed) 1,650,875

Info: # of records reloaded 57,840

Info: # of seconds elapsed 1

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

nz_restore of database TARGTE_DB_NAMEfinished on 2014-02-28 14:22:20

Total # of tables processed: 1

Total # of records reloaded: 57,840

Total # of seconds elapsed : 1

TOTAL restore file size : 1,650,875

rate (bytes per second) : 1,650,875

steps to do table level backup in netezza

Please find the steps and syntax to use backup the single table

[nz@SERVER_NAME DB_NAME]$ nz_backup -db DB_NAME -t ADMINISTRATOR_140923_152501 -dir /netback/backup/test/Netezza/NZ81331-H1/TEST/DB_NAME -format binary

nz_backup of database DB_NAME started on 2014-02-28 14:09:47

NZ_HOST:

NZ_USER: ADMIN

NZ_DATABASE: DB_NAME

Backup Directory: /netback/backup/test/Netezza/NZ81331-H1/TEST/DB_NAME

Backup Format: binary

Custom Script:

# Of Threads: 1

Using lastTXid: 3327032

The stableTXid value was: 3327031

Debug Mode: Disabled

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

Info: table 1 of 1 ADMINISTRATOR_140923_152501

Info: backup process started 2014-02-28 14:09:51

Info: estimated # of records 57,840

Info: unloading data ( thread 1 of 1 )

Info: data flowing ...

Info: unload finished ( thread 1 of 1 ) seconds 0

Info: unload results ( thread 1 of 1 ) INSERT 0 57840

Info: data flow finished

Info: backup process ended 2014-02-28 14:09:51

Info: source table size 6,029,312

Info: backup file size 1,650,875

Info: rate (backup file size / # of seconds elapsed) 1,650,875

Info: ratio (source table size / backup file size) 3.65

Info: # of records unloaded 57,840

Info: # of seconds elapsed 0

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

nz_backup of database DB_NAME finished on 2014-02-28 14:09:51

Total # of tables processed: 1

Total # of records unloaded: 57,840

Total # of seconds elapsed : 0

TOTAL source table size : 6,029,312

TOTAL backup file size : 1,650,875

rate (bytes per second) : 1,650,875

ratio (source / backup) : 3.65

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

[nz@SERVER_NAME DB_NAME]$ ls -ltr

total 1620

-rw-r--r-- 1 nz nz 1650875 Feb 28 14:09 ADMINISTRATOR_140923_152501.1

steps by steps table refresh between Netezza server

pre -request
target tables should have same strcuture as source tables structure.

then issue the below command to migrate the tables.

nz_migrate -shost SRCHOST_NAME -thost TRGETHOST_NAME -format binary -sdb SRCDB_NAME -tdb TRGET_NAME -suser admin -t FT_INSTOCK -threads 4 -cksum Yes -genStats Express -TruncateTargetTable Yes

script to table level restore in Netezza database?

pre -request
1.drop the tables in target
or truncate the table
2.check the table structure as like source
3.restore the database

nzrestore -db NETEZZA_CUSTOMER_SERVICE -sourcedb ABC -connector /nz/backup/STRUCT/whdev/Netezza/NZ81331-H1/ABC/20130506082020/ -dir /nz/backup/STRUCT/whdev/Netezza/NZ81331-H1/ABC/20130506082020/1/SCHEMA/md -tables table_cust -u admin

query to check db access for each user in NPS?

select username from _v_usergroups;

scrip to restore Netezza database to another database

pre -request.

we have to drop the target database before restore ,NPS restore process automatically will create new database.once the restore is completed we will rename the database

nzrestore -db test1 -u admin -dir /netback/backup/test/ -backupset 20130605233051

Sunday, October 12, 2014

How to rebalance the SPU in netezza after DAc replacement

check whether able to ping the SPU
ping sassw01a
then do
nzhw rebalance

Friday, October 24, 2014

Tuesday, October 14, 2014

1.Please check enough room is there for ASM disk2.ALTER SYSTEM SET db_recovery_file_dest_size='40G' SCOPE=BOTH SID='*';it will reflect to all nodes.

Sunday, October 12, 2014

1.Please check enough room is there for ASM disk
2.ALTER SYSTEM SET db_recovery_file_dest_size='40G' SCOPE=BOTH SID='*';
it will reflect to all nodes.