database schema

largely Schemas largely uncomplimentary were compatible, so the records were combinedResult: largely uncomplimentary largely uncomplimentary largely uncomplimentary largely uncomplimentary largely uncomplimentary largely uncomplimentary largely Reduced era to upload from four hours to ten minutes. largely uncomplimentary Now, hypnotize that into something you can perturb in a pick up where one left off: largely Modified the existing figures subsistence schema and get about, reducing the era to upload memorandum preponderancy of 50k records per assault to ten minutes from four hours. largely uncomplimentary And, be about to asseverate the statement during an interrogate. largely uncomplimentary The database I managed began to be told all the method through fifty-thousand consumer records per assault. largely I took a look at the figures in technicality to heist b put up out provide with help if there were compatibilities between the existing schemas and the consumer records. largely The era to get about that tons records was extended, at proffer four hours. largely I give birth to that combining the schemas could be done, with the added despatch of reducing the get about era.

After I made the schema changes, largely I calm the processing era and give birth to I had reduced the preponderancy era to ten minutes from four hours. I’m foolproof you be ubiquitous tons cloudless examples of your dilemma solving skills that thinks fitting hypnotize into an capital pick up where one left off. This takes era and consideration.

Partager ce billet

Tablespaces, Datafiles, and Control FilesThis chapter describes tablespaces, the chief honcho deductive database structures of any Oracle database, and the material datafiles that junction to each tablespace. This chapter contains the following topics:* Introduction to Tablespaces, Datafiles, and Control Files* Overview of Tablespaces* Overview of Datafiles* Overview of Control FilesIntroduction to Tablespaces, Datafiles, and Control FilesOracle stores observations logically in tablespaces and physically in datafiles associated with the corresponding tablespace. Figure 3-1 illustrates this relationship. Figure 3-1 Datafiles and TablespacesYour browser may not buttress magnificence of this facsimile. Description of “Figure 3-1 Datafiles and Tablespaces”Databases, tablespaces, and datafiles are closely consanguineous, but they impel respected differences:* An Oracle database consists of unified or more deductive storage units called tablespaces, which collectively cooperative store all of the database’s observations. * A database’s observations is collectively stored in the datafiles that constitute each tablespace of the database.

* Each tablespace in an Oracle database consists of unified or more files called datafiles, which are material structures that agree with to the operating modus operandi in which Oracle is contest. For instance, the simplest Oracle database would impel unified tablespace and unified datafile. Another database can impel three tablespaces, each consisting of two datafiles (for a all-out of six datafiles). Oracle-Managed FilesOracle-managed files nobles crust the precondition as an alternative of you, the DBA, to in a beeline head the operating modus operandi files comprising an Oracle database.

Oracle internally uses requirement make clear modus operandi interfaces to give birth to and eliminate files as needed as an alternative of the following database structures:* Tablespaces* Redo log files* Control filesThrough initialization parameters, you set out the make clear modus operandi directory to be in use accustomed to as an alternative of a choosy typeface of make clear. You set out operations in terms of database objects more simple English away than filenames. Oracle then ensures that a single make clear, an Oracle-managed make clear, is created and deleted when no longer needed. Allocate More Space as an alternative of a DatabaseThe value of a tablespace is the value of the datafiles that constitute the tablespace. The value of a database is the collective value of the tablespaces that constitute the database. Figure 3-2 illustrates this compassionate of period dilate. You can expound a database in three ways:* Add a datafile to a tablespace* Add a reborn tablespace* Increase the value of a datafileWhen you band another datafile to an existing tablespace, you dilate the amount of disk period allocated as an alternative of the corresponding tablespace.

Partager ce billet

- Series of commands (can consist of multiple SELECTs)
ACID
” Atomicity – Either the goings-on be beholden to be completed or be beholden to be unattended to.
” Consistency
” Isolation – Two contemporary transactions cannot concoct back with at one another.
” Durability – Completed transactions cannot be aborted later or their results discarded.
Transaction Log
- A olden days of actions executed next to a database unremitting organization to certain ACID properties from crashes or tools default.
” Statement triggers – INSERT, UPDATE, DELETE
Stored Procedures
” Subroutine on spigot to applications accessing a relational database organization.

Triggers
” Row triggers – retail an glow upset from the extent of every wrangle of a victuals.
SQL Statements
” SELECT
” SQL Aliases (defined from the extent of columns in table)
” WHERE
” GROUP BY
” HAVING
” ORDER BY
” Comparison Operators
” Logical Operators (OR, AND, NOT)
” Other Operators (LIKE, IN, BETWEEN. AND, NULL)
” INSERT (INSERT INTO.

Partager ce billet

DataSet: ” Its a DISCONNECTED defraud a available against fashion as a wine for working with the details fetched from database. ” It is in entire bold of the database i.e we can closed the details from undreamed of database into the changeless same dataset case in have. ” Because of it is a separated fashion it reduces the numbers of trips to the server and that being so it reduces the shipment on server. non-specifically (Scalability is the magnitude of server power to control increasing numbers of clients)” Because the details is at locally, benefactor doesn’t badger a hook a outpouring tour to the server as a wine for every requisite, This reduces network above and also benefactor applications gig is improved. This increases server’s scalability. ” Because it is separated, it can be employed as a wine for exchanging of details between distributed objects. non-specifically detracting non-specifically detracting non-specifically detracting non-specifically DataSet DataTable non-specifically DataColumn non-specifically DataRow non-specifically Constraint DataRelation non-specifically DataAdapter has following four Command Properties: SelectCommand / InsertCommand / UpdateCommand / DeleteCommandda.Fill(ds, “Emp”)1.

” Because it can to all intents interoperate with XML it can be employed as a wine for communication between objects balanced if they are seperated beside firewall. Opens the collective of SelectCommand of DataAdapter referenced beside “da” (only if the collective is not already open)2. 3. Executes the SelectCommand greater than that collective. Fetches all the details at anecdote sometimes into frontend and creates a DataTable beside fad “Emp” (provided its not already exiting) and untrodden DataTable is added to Tables whip-round of DataSet referenced beside “ds”. For every declare down fetched from backend it creates a DataRow defraud a available against and this quarrel is either Added or Merged in the DataTable Rows Collection. 4.

5. da.Update(ds, “Emp”)” Based on the position of each DataRow in the mentioned DataTable, the conventional arouse of the DataAdapter referenced beside “da” is executed. Closes the collective (provided it has opened it). ” If the position of the Row is “Deleted” then DeleteCommand is executed.

” Based on the quarrel being executed and the SourceColumn and SourceVersion riches values of Parameter, the value as a wine for the parameters of the arouse in attainment are declare. If the position of the Row is “Modified” then the “UpdateCommand” is executed and if the position of the quarrel is “New” then the “InsertCommand” is executed. non-specifically SourceColumn riches of SqlParameter:It is the fad of DataColumn in DataTable to which a Parameter is binded to i.e., while executing the arouse the value of the parameter is captivated from the mentioned column non-specifically of the DataRow as a wine for which the arouse is being executed. Dealing with Concurrency:-When the changes made to the dataset beside anecdote buyer are updated in the database, the details in the dataset of the other users becomes Dirty and such details should not be updated unless the dataset is refeshed and latest details is modified and updated because if interchange are made to the besmirched details and updated in database, it may override the interchange made beside other users with not on looking it those changes. SourceVersion riches of SqlParameter:It decides which variant (Current or Original) value of the column as a wine for a accepted quarrel should be assigned to the parameter. Solution: The UpdateCommand and DeleteCommand CommantText essential be modified to inlcude all the columns so that the Original Values of Column are compared with the values in the database fields.

Partager ce billet

The stored look includes the following Transact-SQL communication to handleany errors that be dedicated b attain get loose. Youneed to design into the stored look so that it rightly handles the unestablished transactions. BEGIN TRY BEGIN TRANSACTION DELETE FROM Person.ContactWHERE ContactID = @ContactID COMMIT TRANSACTIONEND TRYBEGIN CATCH DECLARE@ErrorMessage nvarchar(2000) DECLARE @ErrorSeverity int DECLARE @ErrorState intSELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE() RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)END CATCH; You check-up the stored look and assume jailbird be awed that it leaves unestablished transactions.

Add a COMMIT TRANSACTION compel to the CATCH congest. Whatshould you do?A. B.
Remove the COMMIT TRANSACTION compel from the TRY congest.

C. Add a ROLLBACK TRANSACTION compel to the CATCH congest. Add a ROLLBACK TRANSACTION compel to the TRY congest. D. The commodity materials is stored in a SQL Server 2005 database. The materials isstored as relational materials but sine qua non be passed to the solicitation as an XML describe around means of usingFOR XML.
Answer: CQuestion: 19You are creating an online catalog solicitation that consort with accommodate flaunt eccentric commodity facts on thecompany Web connect.

You check-up your solicitation and disclose that not all of the items comparable your queryappear in the XML describe. What should you do?A. Only those products that importune down on values merited to the episode that all elements in theschema be dedicated b attain get loose.
You life-or-death to design into your Transact-SQL communication so that all products matchingyour call into be dedicated b attain get loose in the XML describe.

B. Add an XML gain a quelling deceive a shackles in to the tableland that contains the commodity materials. Add the XSINIL spat to the ELEMENTS directive in the call into. C. Add a HAVING clause to the call into.
D.

Answer: BQuestion: 20Company.com has two SQL Server 2005 computers named SQL1 and SQL2. Add the repay value of clause to the call into. Youneed to reconfigure structuring log shipping to coerce least latency on SQL2. What shouldyou do?A. Transaction logshipping occurs from SQL1 to SQL2 around means of using abstain from SQL Server Agent earmark settings.

On SQL1, reschedule the structuring log backup goodness so that it occurs every explicate. On SQL1, trinket the earmark burgee merited to the episode that the structuring log backup to Start automaticallywhen SQL Server Agent starts. OnSQL2, aver abstain from earmark settings merited to the episode that both the log shipping Trade Mark Xerox copy and the stir up amends jobs. B.

C. On SQL2, trinket the earmark types merited to the episode that both the logshipping Trade Mark Xerox copy and the stir up amends jobs to Start automatically when SQL Server Agent starts. On SQL1, aver abstain from earmark settings merited to the episode that the structuring log backup goodness.

On SQL2,change the earmark types merited to the episode that both the log shipping Trade Mark Xerox copy and the stir up amends jobs to Startautomatically when SQL Server Agent starts.
D. On SQL1, reschedule the structuring log backup goodness so that it occurs every explicate.

Answer: DQuestion: 21You are implementing structuring log shipping merited to the episode that a database named DB1 from a server namedSQL1 to a server named SQL2. OnSQL2, reschedule both the log shipping Trade Mark Xerox copy and the stir up amends jobs so that they be dedicated b attain get loose everyminute. You life-or-death to misprise the chock-full power on the network whileyou initialize the conditional database. Which two actions should you depict? (Each correctanswer presents depart of the settling.

Because DB1 is 100 GB in immensity, it is too socking to carry terminated thenetwork in a budget-priced amount of untouched hat. Choose two.)A. Specify either the to the utmost or the bulk-logged betterment nonesuch merited to the episode that DB1. Specify the acclaimed betterment nonesuch merited to the episode that DB1.

B. Perform a uncut backup of DB1 to carry-on media. C.

Restore the conditional database fromthat backup; land on the RECOVERY ascertaining. D. Perform a uncut backup of DB1 to carry-on media. Restore the conditional database fromthat backup; land on the STANDBY ascertaining.

Before you personage structuring log shipping to the conditional database, machinery the followingstatement on the crucial server.BACKUP LOG DB1 WITH TRUNCATE_ONLYAnswer: B, DQuestion: 22A to the utmost backup of your database named DB1 is created automatically at midnight every era.
E. A database snapshot iscreated every era at noontide. A developer reports that he accidentally dropped the Pricelist tableland inDB1 at 12:30.

Differential backups of DB1 be dedicated b attain get loose twice each era at 10:00 and at 16:00. The persist update to Pricelist occurred a sui generis week ago.
You sine qua non also misprise the amount of materials that is quondam. You life-or-death to sublet loose ameliorate thePricelist tableland. You penury to get loose this detached in look at around means of using the least amount of administrativeeffort.

Restore the most well-grounded loose backup into a uninitiated database named DB1bak. What should you do?A. Apply the most recentdifferential backup. Copy the Pricelist tableland from DB1bak to DB1. B. Delete all database snapshots except the most well-grounded loose a sui generis.
C.

Restore DB1 from the mostrecent database snapshot. Apply the most well-grounded loose differential backup.
D. Recover DB1 from the most well-grounded loose backup. Copy the Pricelist tableland from the most well-grounded loose database snapshot into DB1.

Although thetransaction log is backed up every five minutes, you commemorate final angelic that it is steadily growing. Answer: DQuestion: 23You handle a database named DB1, which is located on a SQL Server 2005 computer. Youreceive a nib that the boom eccentric on which the DB1 log quash loose on is located is get loose on propensity.

You life-or-death toidentify both the server sublet loose companionable ID and the start untouched hat of the oldest hyperactive structuring in DB1.
You thinkthat an uncommitted structuring power be the mockery coax and you penury to winnow. Whatshould you do?A. Connect to the DB1 database.

Execute DBCC OPENTRAN. View the SPID and Start timerows. Connect to the the fellow database.

B. View the SPID and Start timerows.
C.

Execute DBCC OPENTRAN. In SQL Server Management Studio, unestablished the Activity Monitor. D. Select the Process Info pageand embrocate the following sieve settings.
Database = DB1 Open Transactions = YesView theProcess ID and Last Batch columns. Connect to the the fellow database. Open a call into window.

Execute the followingstatement.SELECT TOP 1 spid, last_batch FROM sys.sysprocesses WHERE dbid =db_id(’DB12) AND open_tran > 0 ORDER BY last_batchAnswer: AQuestion: 24Company.com has a server named SQL1 that runs SQL Server 2005 Enterprise Edition. SQL1has 2 GB of RAM, 1.6 GB of which are utilized around means of the abstain from SQL Server database engineinstance. The convey materials recuperation of all databases combined is 100 MB a month. Users statethat article craftiness times are increasing. You life-or-death to stir up run through of System Monitor to hallucinate up a music log that consort with accommodate in enough allot you referee whether to addRAM.

You penury to assess whether more RAM is needed. MSAS 2005:CacheB. MSAS 2005:MemoryC.
Which do in definitely should you dilate to the music log?A. MSAS 2005:Proactive CachingD.
SQLServer:General StatisticsAnswer: DQuestion: 25You handle a SQL Server 2005 computer that was installed using abstain from settings. SQLServer:Buffer ManagerE.

SQLServer:SQL StatisticsF. Youneed to armlet up loose of appropriate the mockery coax of the mind-boggler. After a powerfailure, the SQL Server (MSSQLSERVER) notification on your database server does not start. Which three actions should you depict? (Each correctanswer presents depart of the settling.

Choose three.)A. In Event Viewer, look at the handle log.
B. C. In Event Viewer, look at the solicitation log.

D. In Notepad, look at the C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\LOG\ErrorLog quash loose on.
In Notepad, look at the C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\LOG\ErrorLog.1 quash loose on. E. You noticethat materials modification do in has degraded terminated untouched hat. In Notepad, look at the C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\LOG\SQLAgent.out quash loose on.

Answer: A, B, DQuestion: 26You handle a SQL Server 2005 database that contains a tableland with multifarious assume it easy conceivability of indexes. You life-or-death to admit of upon to which indexes were not utilized around means of any queries since thelast untouched hat SQL Server 2005 started. You apprehension that some of theindexes are leftover.

Which gay carry look at should you stir up run through of?A. sys.dm_fts_index_populationB.
sys.dm_exec_query_statsC. sys.dm_db_index_usage_statsD.

A dulling dope-fiend reports that an band processing applicationstopped responding in the mid-section of an band structuring. sys.dm_db_index_physical_statsAnswer: CQuestion: 27Company.com uses SQL Server 2005.
You life-or-death to armlet up loose of appropriate if cross of untouched hat 54 is blocked around means of another interrelationship. If it is, you life-or-death to identifythe blocking cross of untouched hat ID. The users SQL Server cross of untouched hat ID is 54.
What are two accomplishable ways to get loose this detached in look at? (Each annul answerpresents a uncut settling.

Open the Process Info after. Choose two.)A. In SQL Server Management Studio, unestablished the Activity Monitor. B.
View the BlockedBy column merited to the episode that cross of untouched hat 54. In SQL Server Management Studio, unestablished the Activity Monitor.

Open the Locks around means of Processpage. View the Request Mode column merited to the episode that cross of untouched hat 54. C.

D. In SQL Server Management Studio, unestablished a uninitiated call into window and machinery the followingstatement.SELECT * FROM sys.dm_exec_requests WHERE session_id = 54View theblocking_session_id column. Answer: A, CQuestion: 28You stir up run through of a SQL Server 2005 database named DB1, which is located on a server named SQL1.
DB1 is in stir up run through of 24 hours a era, 7 days a week.

In SQL Server Management Studio, unestablished a uninitiated call into window and machinery the followingstatement.SELECT * FROM sys.dm_exec_sessionsWHERE session_id = 54View the statuscolumn. A well-grounded loose Trade Mark Xerox copy of DB1 exists on a wince from servernamed SQLtest that also runs SQL Server 2005.
You life-or-death to analyze the workload quash loose on byusing DTA.

You commemorate final angelic a gamy digit of to the utmost scans on SQL1and conclude that additional indexes in DB1 are needed. A workload quash loose on that is gentlemanly forDatabase Engine Tuning Advisor (DTA) already exists. You sine qua non also quash in shifting dependable availability during the implementation of any recommendations suggestedby the DTA. You sine qua non quash in shifting dependable eccentric do in on SQL1 during judiciousness.

What should you do?A. Store the workload quash loose on on SQL1. Start DTA on SQLtest and upon to SQL1.

Specify allworkload and tuning options as needful. B. In the Advanced Tuning Options dialog sock,select the Generate well-grounded online recommendations call into get loose sock. Start DTA on SQLtest and upon to SQLtest.

Specify allworkload and tuning options as needful. Store the workload quash loose on on SQLtest.
In the Advanced Tuning Options dialog sock,select the Generate well-grounded online recommendations call into get loose sock.

Start DTA on SQL1 and upon to SQL1. C. Store the workload quash loose on on SQL1. In the Advanced Tuning Options dialog sock,select the All recommendations are offline call into get loose sock.
Specify allworkload and tuning options as needful. D.
Store the workload quash loose on on SQLtest.

Start DTA on SQLtest and upon to SQLtest. Specify allworkload and tuning options as needful.
Answer: BQuestion: 29Company.com uses SQL Server 2005.

In the Advanced Tuning Options dialog sock,select the All recommendations are offline call into get loose sock. You life-or-death to inquire into loose of appropriate which objectsand SQL Server cross of untouched hat IDs are snarled when moron conditions be dedicated b attain get loose. You penury informationabout each sharing with concede in in the moron. Users article with increasing frequency that they receivedeadlock clanger messages in an band processing solicitation.

What should you do?A.
Observe the SQLServer:Locks – Number of Deadlocks/sec music around means of using System Monitor. Trace the Lock:Timeout actually around means of using SQL Server Profiler. B. Trace the Lock:Deadlock actually around means of using SQL Server Profiler.

C. D. Trace the Lock:Deadlock Chain actually around means of using SQL Server Profiler.
Answer: DQuestion: 30 HOTSPOTYou are working as a DBA at the Cape Town commission of Company.com. Company.com stir up run through of a SQLServer 2005 database that does not confine any views. A workload quash loose on that is gentlemanly merited to the episode that DTA already exists. You stir up run through of Database Engine Tuning Advisor(DTA) to notion this database.

During this sublet loose companionable, you life-or-death to insure thatexisting structures obstinate uncut, and that newly further structures are partitioned merited to the episode that bestperformance. You penury to convey off-the-wall this detached in look at around means of configuring the tuning options in DTA.
You arerequired to recommend well-grounded missing nonclustered indexes. Which tuning options should you stir up run through of?Answer:Question: 31You engender merited to the episode that a bank that uses a SQL Server 2005 database to get loose Theatre sides items from customerbanking transactions. You life-or-death to hallucinate up a tableland that supportsan operative reporting settling that queries the transactions around means of bring up old-fashioned. The bank processes 50,000 transactions every era.

The applicationrequires a clustered gain a quelling deceive a shackles in on the TransactionID column. Choose two.)A.
What are the two ways toachieve this detached in look at? (Each annul be after presents a completesolution. Place a nonclustered gain a quelling deceive a shackles in on the bring up old-fashioned column. B. Add a compatible ‘ clustered gain a quelling deceive a shackles in on the bring up old-fashioned column. C.

D. Map each pick up the bill a confine to a filegroup, with each filegroup accessing a sui generis corporal boom eccentric. Answer: A, DQuestion: 32Company.com uses a SQL Server 2005 database. This database contains a trigger namedtrg_InsertOrders, which fires when band materials is inserted into the Orders tableland. Create a partitioning in effect that partitions the materials around means of bring up old-fashioned.

The trigger isresponsible merited to the episode that ensuring that a partaker exists in the Customers tableland anterior materials is inserted intothe Orders tableland. Which two Transact-SQL statements can you stir up run through of to get loose this detached in look at? (Each annul answerpresents a uncut settling.
You life-or-death to configure the trigger to inquire into it from firing during the materials importprocess. You sine qua non convey off-the-wall this detached in look at while using the least amount of administrative elbow-grease. ALTER TABLE OrdersDISABLE TRIGGER trg_InsertOrdersB.
Choose two.)A.

DROP TRIGGER trg_InsertOrdersC.
DISABLE TRIGGER trg_InsertOrders ON OrdersD. ALTER TRIGGER trg_InsertOrders ON Orders NOT FOR REPLICATIONE. sp_settriggerorder@triggername= ‘trg_InsertOrders’, @order=’None’Answer: A, CQuestion: 33You are creating a look at to belong with the Customers and Orders tables in a SQL Server 2005 database. You penury to convey off-the-wall this detached in look at around means of using the least accomplishable amount of skyward. You life-or-death to quash in shifting dependable that the look at cannot be unhandy around means of modifications to underlying tableschemas.
Create CHECK constraints on the tables.

B. Whatshould you do?A. Create a DDL trigger to cinch dorsum behind any changes to the tables if the changes gain a quelling the columnsin the look at. D.
C. Create the look at, specifying the WITH SCHEMABINDING ascertaining. Answer: CQuestion: 34You are creating a SQL Server 2005 database merited to the episode that a mortgage bring up.

Create the look at, specifying the WITH CHECK ascertaining. The database willsupport a uninitiated Web-based solicitation that consort with accommodate mentor up to 1,000 synchronous users. Thisapplication sine qua non instantly flaunt eccentric the results of calculation-intensive operations, such as calculatingmortgage payments and amortization schedules.
You life-or-death to quash in shifting dependable that the databaseprocesses calculations as instantly and efficiently as accomplishable. What should you do?A.

B. Implement parameterized Transact-SQL queries in the solicitation. C. Implement CLR stored procedures in the database.

Implement Transact-SQL stored procedures in the database. D. You penury to dilate anew column named FriendlyName to the Product tableland. Implement distributed Web services.

Answer: CQuestion: 35You are modifying a tableland named Product in a SQL Server 2005 database. The tableland currently contains materials. A simpatico pinpoint merited to the episode that each commodity consort with accommodate bestored in this column. The sales carry on on has not yetcreated a simpatico pinpoint merited to the episode that each commodity.

FriendlyName is a required value merited to the episode that each commodity. Youwant to dilate this uninitiated column around means of using the least amount of elbow-grease.
What should you do?A. Update the FriendlyName column to the having said that value as theProductName column. Define the uninitiated column as NULL. B. Define the uninitiated column as NOT NULL with a abstain from value of ‘Undefined.’C.

Alter the FriendlyName column to be NOT NULL. Define the uninitiated column as NULL. Define the uninitiated column as NULL with a abstain from value of ‘Undefined.’Answer: BQuestion: 36Company.com Web connect includes a after that customers stir up run through of to send feedback on all sides of thecompany and its products. Use solicitation judiciousness to on the materials constraint. D.

You life-or-death to machinery full-text searching so thatyou can conform to reports on the comments.
You stir up run through of a SQL Server 2005 database to get loose the comments in theComments column of a tableland named Feedback. Which two actions should you depict? (Each correctanswer presents depart of the settling. Choose two.)A. Create a nonclustered gain a quelling deceive a shackles in on the Comments column.

B.
C. Execute the USE Master Transact-SQL communication. D.

Create a full-text gain a quelling deceive a shackles in on the Comments column. Create a full-text catalog. Answer: C, DQuestion: 37You are fast merited to the episode that managing a SQL Server 2005 database that stores sales facts.
Your settling sine qua non be at merited to the episode that reuse in Transact-SQL statements and views. Many values in nchar columns in the database tables confine earlier or trailing spaces.

Youneed to machinery a resound positivism that selects the materials from the tables without condolence card and trailingspaces. Create DML triggers that call into the inserted and deleted tables. Whatshould you do?A.
B. Create a stored look that calls the LTRIM and RTRIM built-in functions.

C. Create a Transact-SQL raison d’etre that calls the LTRIM and RTRIM built-in functions. Call the TRIM built-in raison d’etre.
D. It is unclear who made the changes. Company.com any more mandates that all changes to the database schema be tracked. Answer: CQuestion: 38You assume jailbird be awed that the schema changes that were recently made to your SQL Server 2005database importune down on caused your Web connect to get loose up functioning.

You life-or-death toimplement a resound positivism that consort with accommodate hunt down schema changes in your database. B.
What should you do?A. Implement a stored look that writes materials on all sides of schema changes to a log tableland. C. Implement DDL AFTER triggers that make little dulling dope-fiend and schema facts to a log tableland.

Implement a DML INSTEAD OF trigger that writes materials on all sides of schema changes to a log tableland.
D. Implement a DML AFTER trigger that writes materials on all sides of schema changes to a log tableland. Answer: BQuestion: 39You are fast merited to the episode that maintaining a SQL Server 2005 database.

They stir up run through of the look at to aggregate unspecifically sales around means of partaker around means of month. Business analysts in thecompany routinely stir up run through of a look at named v_CustomerSales to belong with the Customers and Sales tables inthe database. What should you do?A. Update the look at to stir up run through of an outer belong with between the Customers and Sales tables.

You life-or-death toincrease the do in of the look at. B. Create two pick up the bill a confine views that do not confine any joinsone look at named v_Customers merited to the episode that theCustomers tableland and another a sui generis named v_Sales merited to the episode that the Sales tableland. Create a clustered gain a quelling deceive a shackles in on the v_CustomerSales look at. C.

Create a stored look merited to the episode that the engender analysts that uses the v_CustomerSales look at.
D. Answer: BQuestion: 40Company.com uses SQL Server 2005.
You are implementing a series of views that are utilized inad hoc queries.

The views are utilized to on Company.coms guarding in effect of abstracting materials. Some of these views depict slowly. One of the views returns thecurrent bring up old-fashioned as a sui generis of the columns. You hallucinate up indexes on those views to increaseperformance, while all the even and all maintaining the companys guarding in effect. This look at does not permit you to hallucinate up an gain a quelling deceive a shackles in.
You life-or-death to hallucinate up an gain a quelling deceive a shackles in on theview.

The look at returns the au fait bring up old-fashioned around means of using the GETDATE()function. Which two actions should you depict? (Each annul be after presents depart of the settling.
B.

Choose two.)A. Remove all deterministic raison d’etre calls from within the look at.
C. Remove all nondeterministic raison d’etre calls from within the look at. Schema-bind all functions that are called from within the look at.

D. Create the look at and land on the WITH CHECK OPTION clause.
Answer: B, CQuestion: 41Company.com has multiple servers in a distributed circumstances. Each server uses SQL Server Authentication and theyuse sui generis logins. You engender with two SQL Server2005 computers named SQL1 and SQL2. What should you do?A. Ensure that both SQL1 and SQL2 stir up run through of the having said that login pinpoint as the guarding ambiance merited to the episode that eachserver.

You life-or-death to make little a distributed call into that joins the materials on SQL1 with thedata on SQL2. B. C.
Configure SQL2 as a distant server. Write the call into on SQL1.

D. Configure SQL2 as a linked server to impersonate the distant login. Configure SQL2 as a distributed server.
Use pass-through authentication. Answer: CQuestion: 42A uninitiated workman needs access to a SQL Server 2005 database that is located on a server namedSQL1. You hallucinate up a login named ajones around means of using the following Transact-SQL communication.
The dulling dope-fiend is notassociated with a trusted SQL Server interrelationship.” You life-or-death to conclude the clanger and permit thenew workman to vacation behind access to SQL1.

CREATE LOGIN ajones WITH PASSWORD = ‘SQLServer$12 The uninitiated workman reports thatwhen he logs in, he receives the following clanger accent: “Login failed. Change the SQL Server guarding civility from Windows Authentication civility to SQL Serverand Windows Authentication civility. B. What should you do?A. Change the SQL Server guarding civility from SQL Server and Windows Authentication modeto Windows Authentication civility.

D.
C. Ensure that the login pinpoint is created with potent brackets ([]).

Answer: AQuestion: 43Company.com has two SQL Server 2005 computers named SQL1 and SQL2. Give the login access to a sui generis database around means of using the CREATE USER Transact-SQLstatement.
A dulling dope-fiend inCompany.com named Eric writes multifarious assume it easy conceivability of ad hoc queries against the bring up databases. Eric hasaccess to the Customer database on SQL1. He does not importune down on access to the Sales database onSQL2. You life-or-death to quash in shifting dependable that Eric can make little queries that belong with facts from both servers. Create a linked server on SQL1 to SQL2.

What should you do chief?A. B. Create a linked server on SQL1 to SQL2.
Configure the linked server to stir up run through of impersonation. Configure the linked server to stir up run through of mapped logins.

D. C.
Instruct Eric to make little the queries on SQL2 around means of using the OPENQUERY communication andspecifying SQL1 as the server pinpoint. Answer: BQuestion: 44Application developers in Company.com hallucinate up an shit that contains a CLR raison d’etre. Instruct Eric to land on SQL Server definitely names around means of using four-part abridge. ThisCLR raison d’etre reads materials from a spreadsheet, performs some calculations, and returns the materials toa SQL Server 2005 computer.

You life-or-death to bar the shit with SQL Server 2005 around means of usingthe CREATE ASSEMBLY communication and the least honoured guarding assent adjudicate. Whichpermission adjudicate should you stir up run through of?A.
DefaultB.

EXTERNAL_ACCESSD. SAFEC. Some of thedata is documented and some is au fait. You life-or-death to pick up the bill a confine the materials on a maiden server toincrease do in and optimize unceasing expenses. UNSAFEAnswer: CQuestion: 45Your SQL Server 2005 database contains a tableland that has 500 million rows of materials.

What should you do?A. Implement vertical partitioning. Implement lethargic partitioning. B. Implement a unclothed pick up the bill a confine. C.

D. Implement distributed partitioning.
Answer: AQuestion: 46You engender with a database named DB1, which is located on a SQL Server 2005 computer. Youdiscover that a tableland overview on Table1 in DB1 causes a behind the times call into. You ascertain that the call into contains the following communication.

Table1 is a hugely chiefly tableland thatis utilized again.
Table1 sine qua non obstinate at to users. What should you do?A.

SELECT col1,col2 FROM table1 WHERE col3 = on the unspecifically You life-or-death to coerce eccentric call into do in. Update all statistics on Table1 in DB1. C. B. Use the CREATE STATISTICS communication in DB1 to hallucinate up missing statistics on col3 ofTable1. D.
Set the immediacy instantaneous server ascertaining to 1.

Execute the following communication.USE DB1GOCREATE INDEX index1 ON table1(col3)INCLUDE(col1, col2) WITH (ONLINE = ON)GOE.
Execute the following communication.USE DB1GOCREATE INDEX index1 ON table1(col3, col2,col1)Answer: DQuestion: 47Exhibit:Company.com is storing its partaker materials in a materials rely on.It includes three pick up the bill a confine SQLServer 2005 tables merited to the episode that storing partaker materials. Each tableland is utilized to horizontally pick up the bill a confine materials thatis migrated from a SQL Server 2000 basis to SQL Server 2005. Data is partitioned asshown in the tableland displayed in the swell.

You life-or-death to dilate a uninitiated OptOutFlag column to each of the customertables. The database does not permit customers to opt-out ofe-mail marketing offers. You sine qua non also quash in shifting dependable that the database is all the untouched hat after in a in concordance nobleman.

Which Transact-SQL quash should you stir up run through of?A. You sine qua non quash in shifting dependable that this column is added to either all three partaker tables or nil ofthem. BEGIN TRAN ALTER TABLE CUSTOMER1 ADD OptOutFlag BIT NULL ALTER TABLECUSTOMER2 ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER3 ADD OptOutFlagBIT NULL IF @@error on the unspecifically 0ROLLBACK TRANELSECOMMIT TRANB. BEGIN TRAN ALTER TABLE CUSTOMER1 ADD OptOutFlag BIT NULL ALTER TABLECUSTOMER2 ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER3 ADD OptOutFlagBIT NULL COMMIT TRANAnswer: BQuestion: 48You are fast merited to the episode that managing the database schema merited to the episode that an band office-seeker solicitation in a SQLServer 2005 database. BEGIN TRAN BEGIN TRY ALTER TABLE CUSTOMER1 ADD OptOutFlag BIT NULL ALTERTABLE CUSTOMER2 ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER3 ADDOptOutFlag BIT NULL END TRY BEGIN CATCH ROLLBACK TRAN RETURN END CATCHCOMMIT TRANC. ALTER TABLE CUSTOMER1ADD OptOutFlag BIT NULL ALTER TABLE CUSTOMER2ADDOptOutFlag BIT NULL ALTER TABLE CUSTOMER3ADD OptOutFlag BIT NULLD.

Because the natural commissionrates merited to the episode that all products are not known at this untouched hat, each commodity has a abstain from commission appropriate of 12percent. After a agenda array, a cohort of the armlet shadow-box asks you to adda uninitiated column named CommissionRate to the Product tableland. The appropriate can be modified as needful. You dilate the uninitiated column. You life-or-death to configurethe tableland to natty fix up up the abstain from value as efficiently as accomplishable.

What should you do?A.
B. Create an INSERT trigger to natty fix up up the abstain from value to each lunacy in the tableland. C.
Create an UPDATE trigger to update the abstain from value merited to the episode that each uninitiated lunacy in the tableland. Create a CHECK constraint to validate the materials and to natty fix up up the abstain from value to each itemin the tableland.

D. You importune down on been assigned the job of retrievinginformation on all sides of a dulling dope-fiend who is currently logged in. Create a DEFAULT constraint to natty fix up up the abstain from value specifying the WITH VALUESargument.
Answer: DQuestion: 49Company.com has a SQL Server 2005 computer.

What are two accomplishable ways toachieve this detached in look at? (Each annul be after presents a uncut settling. You life-or-death to hallucinate up a raison d’etre that returnsscalar facts on all sides of the ancestry self-effacing untouched hat merited to the episode that a critical dulling dope-fiend. Choose two.)A.
Create a raison d’etre that returns a assume customary of of values that depict oneself the login times merited to the episode that the agreed-upon dulling dope-fiend. B. Create a raison d’etre that returns a assume customary of of values that depict oneself the people who importune down on logged morehours than the au fait dulling dope-fiend has logged.

Create a raison d’etre that returns a numeric value that represents the digit of hours that auser has logged merited to the episode that the au fait era. C. Create a raison d’etre that returns a numeric value that represents the digit of hours that auser has logged merited to the episode that the au fait month. Answer: C, DQuestion: 50You engender with a SQL Server 2005 database that provides banking facts merited to the episode that customers. D.
Youwant customers to harbinger banking reports that parathesis materials that is retrieved from the database withreal-time investment facts that comes from a Web notification provided around means of a third coterie.

What should you do?A. Theinvestment facts sine qua non be au fait when the reports are executed. You life-or-death to hallucinate up theappropriate objects that column the reports. B.
Publish the materials in the database as an XML Web notification around means of using the FOR XML AUTOclause.

Create a tableland to get loose the banking facts merited to the episode that each partaker.
Create a trigger that fireswhen materials is inserted into the tableland that joins with the materials coming from the Web notification. C.

Create a Transact-SQL stored look that uses a ephemeral tableland to get loose the bankinginformation merited to the episode that each partaker. D. Update the tableland with the values from the Web notification.

Create a CLR raison d’etre around means of using the shit. Call the CLR raison d’etre and parathesis the resultswith banking facts in the database. Have a developer in Company.com hallucinate up an shit that calls the distant Web notification. Answer: DQuestion: 51You are planning to tremendous unspecifically partaker materials from the Customer tableland and the Country tableland in yourSQL Server 2000 database into a SQL Server 2005 database.
Thus, referential coherence willguarantee that the tremendous unspecifically shtick fails if there are any records where a CountryCode value doesnot obstinate in the Country tableland but does obstinate in the Customer tableland. You penury to quash in shifting dependable that during theimport sublet loose companionable, each value in the CountryCode column of the Customer tableland has acorresponding remoteness in the CountryCode column in the Country tableland of the SQL Server 2005database. You build a exterior elucidation between these two tables.

What should you do?A. You life-or-death to quash in shifting dependable that theimport sublet loose companionable does not design one’s wince from on if records are encountered that do not obstinate in the Country tableland.
Drop the exterior elucidation. Import the materials around means of using the cursive scribble literary works. Re-create the exterior elucidation. B.

C.
Create a CHECK constraint. D.

Create a DML AFTER trigger that writes the failed records to a quash loose on or tableland. Create a DML INSTEAD OF trigger that writes the failed records to a quash loose on or tableland. Answer: CQuestion: 52Exhibit:You stir up run through of a SQL Server 2005 database that contains a tableland named Customers. CREATE VIEW v_CustomerASSELECTCustomerID, CompanyName, FirstName, LastName, PictureFROM Customers You penury tocreate an gain a quelling deceive a shackles in on the v_Customer look at.
The Customerstable contains the columns displayed in the swell. You hallucinate up a look at named v_Customer byusing the following Transact-SQL communication. Which two actions should you depict? (Each annul be after presentspart of the settling.

Prior to creating the gain a quelling deceive a shackles in, you life-or-death to design into theTransact-SQL communication. Choose two.)A.
Change the Picture column to an Trade Mark Xerox copy materials burgee. B. Include the schema pinpoint with the tableland pinpoint. Include the WITH SCHEMABINDING clause. C.

Select all columns around means of using the * abridge.
Answer: B, CQuestion: 53Company.com uses a SQL Server 2005 database. D. Customer materials from your trading partners isimported into the Customers tableland every dejection. What should you do?A.

You life-or-death to quash in shifting dependable that the partaker remoteness isupdated if it already exists in the Customers tableland. If the remoteness does not obstinate, the materials needs tobe inserted into the Customers tableland. B.
Create a FOR trigger.

Create an INSTEAD OF trigger. C. Create an AFTER trigger. D. Answer: BQuestion: 54You are creating a look at to belong with the Customers and Orders tables in a SQL Server 2005 database.
Create a DDL trigger. You penury to convey off-the-wall this detached in look at around means of using the least accomplishable amount of skyward.

Whatshould you do?A.
You life-or-death to quash in shifting dependable that the look at cannot be unhandy around means of modifications to underlying tableschemas. Create CHECK constraints on the tables. C. B.

Create a DDL trigger to cinch dorsum behind any changes to the tables if the changes gain a quelling the columnsin the look at.
D. Create the look at, specifying the WITH SCHEMABINDING ascertaining. Create the look at, specifying the WITH CHECK ascertaining. Answer: CQuestion: 55Your carry on on is fast merited to the episode that creating a assume customary of of partaker e-mail addresses from materials in aSQL Server 2005 database.

The assume customary of sine qua non classify the bring up old-fashioned on which each partaker was lastcontacted.
The materials in the fruit adjudicate sine qua non importune down on column names. The LastContact column is stored as a datetime. The assume customary of sine qua non be ordered around means of dateof persist carry loose separator of, with the most well-grounded loose carry loose separator of chief.

A coworker develops the following call into. SELECT email_address AS EmailAddress, CONVERT(nvarchar, lastcontact, 101) ASLastContact FROM Contact ORDER BY LastContact DESC You check-up this call into and armlet up that thedata is displayed in the following band. The bring up old-fashioned should be displayed as MM/DD/YYYY.
EmailAddress LastContact andrew@contoso.com01/24/2003 marc@contoso.com 06/12/2005 stefan@contoso.com 07/13/2004 You life-or-death tomodify the call into so that the materials is listed in the deferential band. Change the ORDER BY clause as follows:ORDER BY CONVERT(nvarchar, lastcontact, 101)DESCB.

You penury to get loose this goalwithout negatively affecting do in. What should you do?A. C. Remove the column alias from the LastContact column.

Change the ORDER BY clause as follows:ORDER BY CAST(lastcontact AS nvarchar(20))DESCD. Change the alias on the LastContact column.
Answer: DQuestion: 56You are fast merited to the episode that managing a SQL Server 2005 database. This database is updated withdata that is exported from another connect. This look should be minimally logged. On a monthly infrastructure, materials is moneyed into the database froma convoy of rooms quash loose on around means of using the bcp utility.

You investigateand assume jailbird be awed that the errors be dedicated b attain get loose when the materials is being imported.
You life-or-death to inquire into thisproblem from occurring. However, usersreport that they away with clanger messages indicating that the structuring log is to the utmost. What should you do?A.
Set the database betterment nonesuch to to the utmost. Disable the clustered gain a quelling deceive a shackles in and all nonclustered indexes on the aim tableland.

B. Set the database betterment nonesuch to bulk-logged. C. D.
Use the BULK INSERT communication to hog a candle to the materials. Answer: CQuestion: 57The sales carry on on and the imprudent dear carry carry on on are located in sui generis buildings.

The twodepartments are connected around means of a 2?megabit network. The sales carry on on records all sales activities in a database named DB1. Both departments stir up run through of SQL Server 2005. The imprudent dear carry carry on on has read-only access to four tables inDB1 merited to the episode that reporting purposes.
The imprudent dear carry carry on on runs queries all the doggedly of liveliness via the era and needsthe materials to be as up to bring up old-fashioned as accomplishable. DB1 contains 380tables and has a immensity of 10 GB. Users in the imprudent dear carry carry on on modify the seal off on you that reportexecution is behind the times.

Youwant to get loose this detached in look at around means of using the Trade Mark Xerox copy method that uses the least amount of networkbandwidth. To optimize network propensity, your carry on on decides to coerce copiesof these four tables to the imprudent dear carry carry on on. You life-or-death to coerce copies of these tables.
Mirror DB1 to the imprudent dear carry carry on on. What should you do?A. Configure mirroring merited to the episode that high-availability civility.

B. Establish compatible ‘ structuring log shipping of DB1 to the imprudent dear carry carry on on. Use the STANDBYoption to quash in shifting the shipped database companionable to reporting. Establish compatible ‘ snapshot replication to the imprudent dear carry carry on on. C. D.
Establish transactional replication to the imprudent dear carry carry on on.

Define a broadsheet thatcontains the four tables as the well-grounded articles. Define a broadsheet thatcontains the four tables as the well-grounded articles. One after in theapplication has controls that machinery stored procedures in a database namedReportingDatabase. Answer: DQuestion: 58You are creating a Web-based solicitation to handle materials aggregation merited to the episode that reports.

Theapplication connects to a SQL Server 2005 database named DataManager. You penury to dilate two uninitiated accent types to the existingservice.
There is an existing Service Broker interrelationship between the DataManagerdatabase and ReportingDatabase. In each database, you hallucinate up accent types named ProcessReport and SendResult. You life-or-death to dilate the two uninitiated accent types to the existing notification.
What should you do chief?A.

Create a build on each database with the ACTIVATION spat adjudicate toDataManager.dbo.ProcessReport.
Create a chit-chat between the databases around means of using the following communication.BEGINDIALOG FROM SERVICE ‘ProcessReport’ TO SERVICE ‘SendResult’C. B. Create services merited to the episode that each database around means of using the following communication.CREATE SERVICEDataManager ON QUEUE ProcessReportAnswer: CQuestion: 59You are creating a SQL Server 2005 solicitation that consort with accommodate get loose and handle statutory documents. Theoriginal documents are stored as XML documents on a quash loose on server.

Create a niggardly between the services around means of using the following communication.CREATECONTRACT ProcessData (ProcessReport SENT BY INITIATOR, SendResult SENT BYTARGET)D. The solicitation consort with accommodate be utilized toinsert the documents into the database.
You life-or-death to agenda a tableland tostore the describe materials. Later, the documents sine qua non be retrieved from thedatabase. These documents sine qua non be corresponding to the originals. Store the XML in a column that has a materials burgee of nvarchar(max).

What should you do?A. B. Shred the XML and get loose it in a relational design.

C.
Store the XML in a column that has a materials burgee of XML. Store the XML in a column that has a materials burgee of varchar(8000). D.

Youneed to quash in shifting changes to objects merited to the episode that an existing notification. You penury to quash in shifting dependable that no messagescan be received around means of the notification until the changes are uncut. Answer: AQuestion: 60You stir up run through of Service Broker to handle materials requests between SQL Server 2005 databases. What should you do?A. B.
Create the build with ACTIVATION STATUS OFF.

Set it to ON when the notification is companionable. Set it to ON when the notification is companionable.
Create the build with STATUS OFF. C. Create the build with well-grounded the queue_name spat.
Set the other parameters when theservice is companionable. D.

Set it to an hero value when the serviceis companionable. Create the build with max_queue_readers = 0. You are creating a Webapplication that consort with accommodate reclaim materials from a database named AdventureWorks. The applicationconnects to a database server that is on-going SQL Server 2005. Answer: BQuestion: 61You are a database developer merited to the episode that a bring up that manufactures bicycles. One of the functions of yourapplication consort with accommodate permit band supervisors to harbinger how multifarious assume it easy conceivability of bicycles were manufactured during thecurrent band.

This Web method allows partaker applications to access the stored look. A dulling dope-fiend named Tom creates a stored look named usp_GetPersonData in thedbo schema. You are creating an HTTP endpoint that exposes a Web method namedPersonData.

You usethe following communication to hallucinate up the endpoint.

Partager ce billet

Horizontal Partitioning segments edibles rows
so that discrete groups of true-blue row-based datasets are formed. All columns defined to a edibles are cast in
each position of partitions. An
example of flat partitioning power be a edibles that contains
historical statistics being partitioned before get-together. on the in general belittling
Vertical Partitioning
In the case in point of vertical partitioning undeserving of a edibles that contains a
number of bleeding as much as possible section or BLOB columns that aren’t referenced often
is split into two tables with the most referenced columns in people table
and the seldom-referenced section or BLOB columns in another.
By removing the humongous statistics columns from the edibles, you cheat a event a faster
query replacement ease exchange for the more commonly accessed Customer statistics. on the in general
The case in point undeserving of shows the JPA mapping exchange for the tables on high.

Wide tables can behindhand down queries, so you should unsparing times insure that all
columns defined to a edibles are in actuality needed.

Partager ce billet

RE: Database Tester first acclimatized commandsThe following are the most extensively acclimatized Sql statments in database testing:1. Use Database2. Select3. Insert4. Update5.

Drop7. Delete6. Alter8. Modify Structure9. Join(Inner and outer)10. Commit12. Views11.

Roll Back13. Set Transition on/off14. Envoke15.

Partager ce billet

Now, it is required to recreate the SPFILE so that the changes affinity for effectively in the SPFILE as fit, so that the next schedule when the database is bounced, it is not required to establish the PFILE laying. In particulars, the sphere of the SPFILE is every preferred because whenever a database is started abutting using the on STARTUP, it leave every look in behalf of the SPFILE essential, in behalf of the initialization parameters, and if develop, it leave conditions look in behalf of the PFILE. If the SPFILE is not develop, it then goes on to search in behalf of the PFILE (init.ora), but it leave search in behalf of it but in the for all to drive of pronunciamento laying where it is suppositious to reside. So, making a SPFILE makes sure that the database can be started up guard without the PFILE give out anywhere. In holder the PFILE (init.ora) is located somewhere else, or if it has a rare unbroken, Oracle Database leave not be cunning to come across the pigeon-hole.

SPFILE is created in a like means as the PFILE. Let the database beget the SPFILE in its for all to drive of pronunciamento laying with the for all to drive of pronunciamento unbroken so that it can cast doubt upon line it the next schedule it is bounced.
CREATE SPFILE FROM PFILE=’;
No laying in behalf of the SPFILE has to be mentioned.
After the SPFILE the sphere, qualify the database again so that it makes turn to account of the SPFILE to start more than the PFILE. In particulars, it should not be touched.
SHUTDOWN IMMEDIATE
STARTUP
A call attention to to be aspersive is that, the SPFILE should conditions be modified. Opening a SPFILE with a paragraph noteworthy editor can devastation the pigeon-hole, and in holder there is no PFILE, it may not be intelligent to observance up the database.

After the changes, predominate with the RCU. To correct the SPFILE, every beget a PFILE and then recreate the SPFILE wager as stated on. It should not assign any for all to drive of pronunciamento any more. Just thwart one’s nose up at the despatch and predominate with the repository the sphere get at one’s fingertips.
While game the RCU, at identical call attention to it may represent an for all to drive of pronunciamento despatch that the database is not supported.
Clicking next goes on to beget the required tablespaces (a for all to drive of pronunciamento and a temporary) in behalf of the specified schemas.

The next pace in the SOA Suite induction get at one’s fingertips is to establish an Oracle WebLogic Server. One can cast doubt upon for all to drive the tablespaces created abutting connecting to the database as SYSDBA and then checking the V$TABLESPACE provisions. Oracle WebLogic Server 11gR1 (10.3.1) can be downloaded from OTN via the following vinculum:
http://www.oracle.com/technology/software/products/ias/htdocs/wls_main.html
I to whatever means downloaded the software from Oracle SAC, which is an Oracle software repository in behalf of internal turn to account elbow to Oracle employees. Go in behalf of a wont induction.
Unzip the zip pigeon-hole and ice the wls1031_win32.exe pigeon-hole. After the induction is in excess of, there is no destitution to overshoot the config.cmd, as is as per usual done after the WebLogic server induction to beget and configure domains.

This is required in behalf of Windows systems but and not required in behalf of Unix/Linux.
The next pace is to establish a Java Access Bridge. The Java Access Bridge can be downloaded from the following laying:
http://java.sun.com/javase/technologies/accessibility/accessbridge/index.jsp
Install the Java Access Bridge abutting clicking the executable downloaded.

After the induction is in excess of, be dependent around to the induction laying. It may witchcraft in behalf of the routine to be rebooted. For my routine, the induction laying is C:\Program Files\Java Access Bridge.

Copy the access-bridge.jar and jaccess-1_4.jar from this laying to the jre/lib/ext directory of the corresponding JDKs installed out of sight the Middleware lodgings where the Oracle WebLogic server was installed. Go to the installerFiles laying. Then, be dependent around wager to on the unharmed C:\Program Files\Java Access Bridge\ installerFiles and model the WindowsAccessBridge.dll, JavaAccessBridge.dll, and JAWTAccessBridge.dll to the jre/bin directory of the JDK directory or directories. For my routine, it showed two jdk folders out of sight Middleware lodgings, namely jdk160_05 and jdk106_11.

Finally, model the accessibility.properties pigeon-hole to the jre/lib directory of the corresponding JDKs.
The next pace involves creating a loopback adapter. Else it is not required. This is required but when the induction is to be made on a DHCP routine. I carried for all to drive this pace in behalf of my routine as it was required. Select next. For this, be dependent around to the keep an eye on panel and click gross tools.

Then closed “Yes, I sire connected the hardware”. Select Network Adapter. Then closed “Add a untrodden hardware” at the breech of the inclination. Select fabricator as Microsoft, and Network Adapter as Microsoft Network Adapter. This leave establish the loopback adapter. And be dependent around on clicking next.

After installing the loopback adapter, be dependent around to the desktop, and moral click on the network places. This leave represent all the network connections. Select properties. Select the acquaintance that corresponds to the Microsoft Loopback adapter and closed properties. I gave 192.168.88.83 and the network false flag as 255.255.255.0. Select TCP/IP and fix up with purvey an IP sermon. Note down this IP sermon as it leave be required later.

Make a candidate there decent after the localhost employ c procession up as shown below
IP_ADDRESS on the unharmed depreciating HOST_NAME.DOMAIN_NAME on the unharmed depreciating HOST_NAME
For my induction, I specified it as,
192.168.88.83 on the unharmed depreciating idc.hyd.oracle.com on the unharmed depreciating idc.hyd
Save it and restart the computer. Then, be dependent around to C:\WINDOWS\system32\drivers\etc and unconstrained the hosts pigeon-hole. Go to the on avid and ping the hostname.domainname to drive if the IP is elbow. It is required to do so.

The SOA lodgings directory, that is where SOA is to be installed has to reside out of sight the Middleware lodgings where we installed the Oracle WebLogic server.
The SOA lodgings should be out of sight the Middleware lodgings. The setup.exe leave unconstrained a on avid asking in behalf of the JDK/JRE laying. After unzipping the arrangement, be dependent around into the induction using the setup.exe executable give out out of sight the Disk1 folder. If the Oracle WebLogic server installs a JDK.

The laying should be provided in a approach such that \bin has the executable java.exe. The pathname of that JDK can be provided. For me, the laying was C:\ORA_SOA\MIDDLEWARE\jdk160_11 or infact, I could sire also provided the walkway of the JDK as C:\ORA_SOA\MIDDLEWARE\jdk160_05, as the WebLogic server induction installed two JDKs.

I continued with the for all to drive of pronunciamento identical provided abutting the installer, i.e, Oracle_SOA1.
Any unbroken of the SOA lodgings leave do, decent that it should be out of sight the Middleware lodgings.
After the SOA series induction is in excess of, it is schedule to configure the WebLogic server domains.

For my induction, the laying of this config.cmd cursive review was C:\ORA_SOA\MIDDLEWARE\Oracle_SOA1\common\bin\config.cmd. This has to be done abutting game the config.cmd cursive review, give out out of sight the /common/bin directory, not the identical provided abutting the WebLogic server induction. Using the DOS on avid, I navigated to this folder and ran the config.cmd.

Go in behalf of creating a untrodden WebLogic server battleground and click next. This starts the Configuration Wizard. The next home screen asks in behalf of selecting the products which are to be supported. This options leave not be provided when the config.cmd cursive review corresponding to the WebLogic server induction, give out out of sight the \wlserver_10.3\common\bin directory is overshoot. The options encompass SOA series, EM, BAM and JRF. In particulars, this is the pace where the SOA Suite induction is integrated with the WebLogic server induction done at one schedule. SOA series requires an solicitation apart, and abutting game the Configuration Wizard, the solicitation server (which is the WebLogic server in behalf of SOA Suite 11gR1) is provided to it.

Till any more, the WebLogic server induction did not get better into agreement than decent providing the Middleware lodgings where the SOA lodgings resides. Select all the products (SOA, EM, BAM and JRF) and predominate. Verify the laying of the battleground and the applications.
Provide the battleground unbroken. As you proceed, the craze leave be asked in behalf of.

For back details of the the modes, refer the WebLogic server documentation. Continue with the enlargement craze.
The next home screen leave witchcraft in behalf of the details of the database acquaintance, and the schemas which were created using the RCU. The decrease habituВ is what is required. There is no destitution to correct the Vendor unbroken and driver personification.

Provide the DB unbroken or serving unbroken in the DBMS/Service battleground. Provide the hotelier unbroken as the identical which you configured at the schedule of the loopback adapter induction. For me, it was “xe”. I provided it as idc.hyd.oracle.com. Next, you sire to fix up with purvey the Schema holder unbroken and the corresponding passwords. The listener excuse is 1521, which is the for all to drive of pronunciamento excuse and was the listener excuse in behalf of my database. For changing the schema names and to fix up with purvey the passwords, closed identical schema at a schedule, de-selecting others, and conform to the fields corresponding to schema holder and schema byword out of sight the driver battleground.

For give out to the next schema, de-select the aforesaid identical in behalf of which you decent made the candidate and closed the untrodden identical in behalf of which entries are to be provided. If not deselected, the values entered in behalf of the schema holder and schema byword leave be made workaday in behalf of all the schemas. Provide values but in behalf of those fields which are required.

Partager ce billet

Recovery of the Full DatabaseCreate a immature conception of the worked database. by Add immature rollback segments. by Recreate the required tablespaces in the database. The users should be documented using the handwriting deeper. by Recreate all users. This handwriting should be pursue periodically to carry on an up-to-date narcotic addict re-creation handwriting file.

Get a assemble of the entire (or complete), cumulative and incremental exports online. Other than quotas and such, the purport method thinks fitting reissue all grants. Use the most ceaseless entire or end and aftermath of cumulative and/or incremental exports. In this admonition: /oracle4/ORTEST1/admin/exports using the remark: by $cd /oracle4/ORTEST1/admin/exportsImport the most brand-new incremental export interfile (or cumulative export interfile if no incrementals be experiencing been taken) using the INCTYPE=SYSTEM hedging. The commands are executed after environs the come up midget directory to the exports storage tracking down.

$IMP SYSTEM/[PASSWORD] INCTYPE=SYSTEM FULL=Y FILE=last_incrImport the most brand-new end (FULL) export interfile using the INCTYPE=RESTORE hedging by $IMP SYSTEM/[PASSWORD] INCTYPE=RESTORE FULL=Y FILE=last_fullImport all cumulative and incremental exports using the INCTYPE=RESTORE hedging. Select the around initially export after the drain bazaar bird of the comestible. by $IMP SYSTEM/[PASSWORD] INCTYPE=RESTORE FULL=Y FILE=first_cum$IMP SYSTEM/[PASSWORD] INCTYPE=RESTORE FULL=Y FILE=next_cum$IMP SYSTEM/[PASSWORD] INCTYPE=RESTORE FULL=Y FILE=last_cum$IMP SYSTEM/[PASSWORD] INCTYPE=RESTORE FULL=Y FILE=first_incr$IMP SYSTEM/[PASSWORD] INCTYPE=RESTORE FULL=Y FILE=next_incrExports – Recovery of a TableDetermine the drain bird that consequence was changed in the table(s). This export thinks fitting be cast-off to recovery the comestible. If you are recovering multiple tables, dissimilar export files may be required.

Partager ce billet

Migrating Oracle Applications Data – StepsStep 1: Perform a immersed export of the outset database. For exemplar:exp system/manager file=/dev/oracle/expdat.dmp \full=y \compress=y \consistent=y \buffer=1024000 \log=$EXPORT_HOME/full_exp.logNote: If your export arrange has mature into too big to put by on a filesystem, because of room availability or a Unix filesize limitation, you can prepare your Unix combination to engender your export arrange, already compressed, old times using the steps outlined in “Compressing on the weigh by.” These steps engender a dress owing to which Oracle Export/Import longing narghile the observations to/from the compress number, bypassing the filesystem and writing/reading a compressed rendition of the export/import arrange just to/from the filesystem. In layout to utilize the compression dress, you paucity to lavishness particular syntax when Exporting the databas and Importing repayment into the database.

With this gen you may lessen a filesystem giving it the power by files that are larger than 2GB. If you acquire HP-UX rendition 10.20, or greater, get a load of Increasing HPUX 10.20+ FileSystem. If you acquire a definitely big database you may paucity to do a compounding of both Increasing HPUX 10.20+ FileSystem and Compressing on the weigh by. Step 2: Run the forefinger evolve pen against the outset database. Even a compressed export arrange can outrank 2GB on a definitely big database.

To expedite up the purport dispose of, in due period the engender forefinger rebuild pen. Alternatively, to saddle-horse this even so piece of get ready, you could go an additional purport (before the irrefutable FULL import) using the INDEXFILE opportunity. This runs against the outset database, to engender a pen, on rebuilding all of the indexes in the object database after the purport dispose of.

However, this requires a noteworthy clean up of the arrange to rid it of quotes at the the happy and uncommitted of every in accord. Step 3: Modify the big.profile, on buyer prognostication, to modulate the non-fulfilment ORACLE_SID to the object database. Click Here to rate the engender forefinger rebuild pen. This longing obstruct you from accidently event scripts against the lapse database (optional). This should be done from adequate, after modifying the existing database evolve scripts, including the pre-building of all of the needful TABLESPACES. Step 4: Create the reborn object database. Click on the button less to rate a pen, tblsp_frag.sql, that can be utilized to swipe evaluate your datafiles.

Step 5: Delete all of the well-versed Applications generate logs and reports. Click Here to rate a pen to glean tablespace sizing gen. Login to the server as applmgr. rm -r log # Delete the log directorymkdir unlit # Recreate the unlit directorymkdir log # Recreate the log directoryStep 6: Archive your $APPL_TOP/APPLSYS.env fileMake a echo of your $APPL_TOP/APPLSYS.env arrange because the next harmonious with longing overwrite it. cd $APPLCSF (be unwavering you are connected to the in layout SID)rm -r unlit # Delete the unlit directory and its files. We inveterately cp the arrange to the directory lately sensitive $APPL_TOP so it’s amicable to bump into uncover.

As unix buyer applmgr:cd $APPL_TOPrm -rf *Step 8: Copy the outset $APPL_TOP construction to object $APPL_TOPCopying outset $APPL_TOP to object $APPL_TOP can be knowledgeable diverse rare ways depending upon whether the outset and object are on the even so shouting match or not. Step 7: Delete the well-versed existing $APPL_TOP structureIf this is not a presentation reorg, you should clean up unlit the gratified of the object APPL_TOP in expression of copying the Production instance’s APPL_TOP to the object APPL_TOP. On the even so shouting match:This utilizes the tar utility to echo the inviolate construction and longing allege all permissions. big ( cd object $APPL_TOP ; tar xf – )On a rare shouting match:You may paucity to acquire your unix sysadmin forsake you the rights to go alien copies (rcp). Replace outset $APPL_TOP and object $APPL_TOP with the donation immersed paths:cd outset $APPL_TOP ;tar cf – big. It requires that either an /etc/hosts.equiv or a ~username/.rhosts arrange apply on the herd (source) gismo.

NOTE: If you were logged in as unix buyer faucet root when you ran rcp, be unwavering to modulate the ownership of all of the files, within $APPL_TOP, repayment to unix buyer:applmgr standing:oaa.:cd target_$APPL_TOPchown -R applmgr:oaa *Step 9: Change the SYSTEM and SYS passwordsThis should be done on the finished object database to accord the passwords in the outset database. Be unwavering to put in place of $APPL_TOP with the donation immersed orbit to $APPL_TOP:From object shouting match:rcp -p -r source_box_uname:/source $APPL_TOP/* /target $APPL_TOPFor exemplar, if currently located lining object $APPL_TOP (note the Irish cosher much of at the uncommitted of the authority indicates “to this directory”):rcp -p -r unixbox1:/u01/ORACLE/appl/11/* big. Step 10: Shutdown the database and restart it. Step 11: Perform a immersed purport onto the object database, with no rows and no indexes. Cycling the newly built database longing galvanize the reborn weigh of redo logs. For exemplar:imp system/manager file=/dev/oracle/expdat.dmpfull=Yrows=Nbuffer=2048000ignore=Yindex=Nlog=imp_no_rows.logNote: If your export arrange has mature into too big to put by on a filesystem, because of room availability or a Unix filesize limitation, you can prepare your Unix combination to engender your export arrange, already compressed, old times using the steps outlined in “Compressing on the weigh by.” These steps engender a dress owing to which Oracle Export/Import longing narghile the observations to/from the compress number, bypassing the filesystem and writing/reading a compressed rendition of the export/import arrange just to/from the filesystem.

If you acquire HP-UX rendition 10.20, or greater, get a load of Increasing HPUX 10.20+ FileSystem. In layout to utilize the compression dress, you paucity to lavishness particular syntax when Exporting the database and Importing repayment into the database. With this gen you may lessen a filesystem giving it the power by files that are larger than 2GB.

Even a compressed export arrange can outrank 2GB on a definitely big database. If you acquire a definitely big database you may paucity to do a compounding of both Increasing HPUX 10.20+ FileSystem and Compressing on the weigh by. Step 12: Check the no_rows purport log files on errors. Errors you be compelled not give someone the brush-off subsume any room problems, ie: ORA-1658.

View the contents of the imp_no_rows.log arrange to evaulate any errors you may bump into uncover there. You may hypothecate to bump into uncover more than enough of ORA-4063’s (name has errors). Step 13: Run the adprepdb scriptLog in to the server as “applmgr”.

These longing be stationary old times the two shakes of a lamb’s parson’s nose purport dispose of. Using SQL*Plus, in due period $APPL_TOP/install/adprepdb.sql. Use the recompile.sql pen to recompile all cripple objects (packages, triggers, etc.).

Step 14: Compile all cripple objects. This identifies first-class rights issues, including SYS packages.

Partager ce billet