Tuesday, October 14, 2014
how to increase the size of db_recovery_file_dest_size in oracle RAc
1.Please check enough room is there for ASM disk
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 ~]#
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'
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))
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
[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
[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
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
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
Subscribe to:
Posts (Atom)